PostgreSQL is a powerful, enterprise class, open source RDBMS. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability and data integrity. It runs on all major operating systems, including Linux, UNIX and Windows. It prides ...
0
votes
0answers
8 views
Are PostgreSQL dynamic arrays more wasteful than plain text?
Today, someone was helping me to make a function in PostgreSQL. I was wanting the function to return in arrays of 100+ geometries per two columns instead of delimited text to make it easier to ...
0
votes
0answers
11 views
encrypt data without performance issues?
I've googled around for the last 45 minutes or so and it seems like that I can't figure out what kind of options I got when it comes up to encryption in databases.
Problem:
I got a SaaS product and ...
0
votes
1answer
16 views
Matching a value with every element of a set of values - Postgresql
I'm new in pgsql and I can't achieve what I'm trying to do.
I have a list of films in a Films table
ID_FILM FILM NAME
1 Terminator
2 Forrest Gump
3 Nueve reinas
A ...
0
votes
1answer
7 views
why pg_restore ignores --create ? Error: failed: FATAL: database “new_db” does not exist
I am trying to run following command:
sshpass -p "pass" ssh [email protected] "pg_dump -Fc -U foo some_db" | pg_restore --create --dbname=new_db
I get:
failed: FATAL: database "new_db" does not exist
1
vote
1answer
15 views
script for restore of postgres database localy from dump given on stdin - ambiguous redirect
Runing this command:
sshpass -p "pass" ssh [email protected] "pg_dump -U foo some_db"
runs perfectly fine. It returns on stdandard output the dump of db.
Then I would like to restore new_db based on dump ...
0
votes
1answer
23 views
Query optimization for multiple joins postgres
ER Diagram
Brief description
The database to be used is postgres.
The database might contain upto 2 million AccountHolder records.
Since the AccountHolder table is accessed and updated frequently ...
2
votes
2answers
37 views
Call UPSERT function multiple times to different inputs
I have a function:
merge_vehicles(vid, cid, vname, reg_no, name, name_1st)
Can i call it multiple times on inputs like
(2335, 55, '246BDH', '246BDH', '811', 1),
(2336, 55, '038THX', '038THX', ...
3
votes
2answers
49 views
Why postgres is using an odd index with no relation to the query
(Debian 7, Postgres 9.3, dedicated machine with huge cache)
I have one big table (14gb) and another tiny lookup table. I'm doing a query between these two and in the explain query Postgres is using ...
0
votes
0answers
20 views
multi-column FKs with NULL values
I understand that in PostgreSQL if there is a multi-column FK from table B to table A and one or more of the FK columns in table B are nullable then the FK is still considered as "honored" for the ...
1
vote
2answers
80 views
Assign to NEW by key in a Postgres trigger
In the trigger body, how can I assign a value to NEW by it's field name?
That's what I want to do:
some_key = "some_column";
NEW[some_key] = 5;
0
votes
0answers
13 views
pgpool-II Master Slave mode / Load Balance Mode / Watchdog On result many primary servers
I installed pgpool-II in Centos6.5, Master Slave mode / Load Balance Mode / Watchdog On
but three postgresql servers are all primary server, shows as below:
IP Address Port Status Weight
...
0
votes
1answer
20 views
PostgreSQL: Why is the schema owner not the database owner
I've a simple question, yet I am unable to find the answer from the official documentation. When I create a database in PostgreSQL as a specific user, I'm the owner of all the objects under the ...
0
votes
1answer
26 views
Postgres JSON field for many to many relationships
I'm designing a schema that has a many-to-many relationship as such:
A lead can have several stages and each stage can have several leads. I currently have three tables, with one of them as being a ...
0
votes
0answers
61 views
Best way to handle multiple query keys
I am designing a postgresql database for an online card game. I want to provide users with the option to access their play history - users can see log of their games.
The data I want to save has the ...
2
votes
0answers
30 views
How to optimize update using join in postgresql
I'm using PostgreSQL 9.3.5 on a CentOS system. I have a very large table (twenty million rows, 50+ column) that aggregates data from a few hundred systems. Several times a day each of the systems ...
-1
votes
0answers
5 views
i have problem in connecting postgresql using eclipse in linux below problem was occur
org.postgresql.util.PSQLException: FATAL: Ident authentication failed for user "postgres"
at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:408)
at ...
2
votes
2answers
40 views
Check whether empty strings are present in character-type columns
I have an application that (as part of its logic) trims strings and replaces empty strings with NULL prior to insertion in the database. I guess one way to ensure that this is enforced would be to ...
1
vote
0answers
43 views
Optimizing SELECT queries
I'm not very experienced with database query optimization. I've been reading through similar questions here and Postgres tuning articles online, but unfortunately I haven't had any luck. Here's what I ...
0
votes
0answers
11 views
sorting by partial key name in json column
I have column document with data type json. I save here data for logging.
So document has:
{blocks: {
participant-information_primary__Primary__39011__2288960:
first_name: Firsst name,
...
0
votes
1answer
18 views
For each record, count records dated earlier in PostgreSQL
I have a table that contains a column year. I need, for each row, calculate the number of entries where the year is less than that in the row.
So that the output would look like:
col1 | col2 | ...
0
votes
0answers
20 views
How to optimize aggregation
I have a query which is sometimes taking too long to execute. I tried to optimize it in every possibile way (AFAIK). When I run this query repeatedly I get fast results. In production environment ...
0
votes
0answers
17 views
Join does not not use index in PostgreSQL?
I have a statement that selects from two tables news and status. It seems that the index on the column status of the table news is not used.
This table has about 70.000 rows and the query costs too ...
-3
votes
1answer
76 views
Best design to handle multiple query keys [on hold]
I am designing a postgresql database for an online card game. I want to provide users with the option to access their play history - users can see log of their games.
The data I want to save has the ...
0
votes
1answer
18 views
How to use pgbench? [on hold]
I would like to benchmark my database (running on an Amazon AMI EC2 instance) with pgbench. I'm using postgreSQL 9.3.
In my database I have 3 tables, but I only want to benchmark one table, i.e. I ...
0
votes
1answer
25 views
modeling data dimensions as columns - some dimensions are highly variable while others have low variability
Please excuse my question if it is a bad one - I am not a DBA...
I would like to model the following data:
Let's say that I have one column that is the row id as generated by a sequence. Let's also ...
1
vote
1answer
28 views
MySQL data to Postgres every X time
Our team (Uni students) have a project that we are updating.
Old project team has a Live MySQL database. We want to make a archive/warehouse from it in PostgreSQL.
My question is: How can we import ...
0
votes
0answers
19 views
multipley queries in php for postgres [migrated]
Hello i have try to get from html file the points an make to sql queries to find the gid from the roads where nearly and in finaly query i find the best routing with dikjstra algorithm with the gid ...
1
vote
1answer
42 views
Deadlock detected on CREATE TABLE in PostgreSQL
I'm creating multiple tables asynchronously when I get deadlock detected. Both tables (Table A & B) have FK to one table (Table C) and I get deadlock on relation to Table C.
Scripts
CREATE TABLE ...
1
vote
1answer
19 views
Is it possible to set a timeout for optimization in PostgreSQL?
In PostgreSQL is there a timeout when the optimizer stops the optimization process and returns the least expensive plan it has found so far? Is it possible to change its value?
1
vote
0answers
15 views
Pgpool load-balancing vs. consistency
In what ways is consistency weakened when using pgpool-II load-balancing on Postgresql servers running streaming replication?
Obviously with asynchronous replication you can insert a row on the ...
0
votes
1answer
19 views
Postgres automatic failover, multiple clients to multiple masters
I'm looking into providing a method of having multiple hot spare PostgreSQL instances to our website.
The majority of implementations that I have found require a single point of failure with PgPool ...
1
vote
2answers
45 views
Equality in JOIN or WHERE clause? Efficiency and style
Of the two queries below, which is the most efficient for postgresql?
And which has better style in terms of readability, etc.
The difference is the placement of the statement doctor.type != ...
2
votes
2answers
58 views
PostgreSQL and storing configs
The problem is to store and use global configs such as currency/date format etc. Since this functions are commonly called I'm concerned with the performance impact.
Lets take currency as an example. ...
1
vote
1answer
30 views
How to index a query `WHERE field IS NULL` (postgres)
So I basically have a table with lots of inserts, setting one of the fields (uploaded_at) to NULL. Then a periodic task selects all the tuples WHERE uploaded_at IS NULL, processes them and updates, ...
0
votes
0answers
13 views
PostgreSQL buffer page size limitations - rebuild with larger size?
We just ran into a limit on indexing due to the 'index size cannot be more than 1/3 of page size (2712)' error.
This indicates the buffer page size is 8192 (8k). This seems awfully small during the ...
0
votes
1answer
20 views
Multiple queries and a report
im fairly new to databases. I have multiple query's that i normally run individually at the end of loading data to my table doing simple integrity checks like length ,char types and so on (see ...
0
votes
1answer
27 views
Populate a table with “WITH” clause [closed]
How to perform the following select query?
with x (x, y, z) as
((2 5 6), (6,3,2))
select * from x
I am not sure if this approach is possible. Not even sure if the syntax is correct. But I don't ...
0
votes
1answer
33 views
PostgreSQL data directory/files portability
I have lost my PostgreSQL 9.1 installation on my Ubuntu machine by update to Ubuntu 14.10. Now I have no (reasonably easy) way to export the data from my PostgreSQL 9.1 database. Is it possible to ...
0
votes
0answers
19 views
How to store multiple raster files in postgres / postgis efficiently
This question is partly linked with another question on GIS:stackexchange, but I believe it is has more to do with clever data management.
I have a Postgresql server with Postgis extension running ...
4
votes
2answers
97 views
CTE works as expected, but not when wrapped into a function
--testing table
CREATE TABLE public.test_patient_table (
entity_id INTEGER NOT NULL,
site_held_at INTEGER NOT NULL,
CONSTRAINT entityid_pk PRIMARY KEY ...
2
votes
2answers
49 views
Slow index scans in large table (PostgreSQL 9.2)
I'm having trouble with slow queries on a relatively large table (200+ million rows). I'm trying anything crazy, just add historic values. Below is the query and the query plan output.
I sadly expect ...
1
vote
1answer
41 views
Different kinds of pricing PostgreSQL
I would like to hear your opinion about handling different kinds of pricing. We have a product table which looks almost like this:
+----+------+-----------+-----------+
| ID | Name | price_old | ...
2
votes
2answers
43 views
Is a Postgres long-running query aborted if the connection is lost/broken?
If I open a connection to Postgres and issue a long-running query, and then break the connection (e.g., kill the client process that opened the connection), will the long-running query continue to ...
1
vote
1answer
19 views
Postgres: modify existing search_path (preserve current values)
Sometimes I would like to extend the existing search path, rather than replace it, say:
To start with say it is already set like so:
SET search_path TO schema_b, schema_c, public;
I want to add my ...
2
votes
1answer
19 views
postgresql - a local replication entry doesn't work
Maybe it's just too early in the morning, but I can't seem to get pg_basebackup working for a local user. This is part of testing for an hourly backup automated script. I added a line to pg_hba.conf ...
2
votes
3answers
178 views
SELECT all substrings (n-grams) of length n?
How could I select all n-grams, ie. substrings of length n from a string using SQL? For example, the 3-grams of string example are exa, xam, amp, mpl, ple.
I'm using PostgreSQL to be more precise.
1
vote
0answers
17 views
How to keep the sync active with Bucardo
I have a master-master sync setup between 3 nodes or 3 different host machines using bucardo. All works good.
However when one of the database or a host is not reachable or goes down for some reason, ...
2
votes
2answers
51 views
Postgresql and UTF8 to Latin1 conversion?
We have a Postgresql database where the data is in Latin1. Apparently someone loaded some UTF8 encoded data into it since when I select data from some of the columns, I get stuff like:
SELECT ...
0
votes
1answer
27 views
PostgreSQL Auditing Tool [closed]
Does PostgreSQL 9.3 have any auditing tool that close to Oracle Audit Vault? Audit Trigger, pgaudit or Cyan Audit look behind than Oracle Audit Vault.
1
vote
2answers
48 views
Insert New Foreign Key Row for new Strings
Lets say I have a table new_data like this:
ndx | someval | somemachine
serial | integer | integer
----------------------------
And let's say I have a reference table new_data_someval_ref:
...
