All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.

learn more… | top users | synonyms (1)

1
vote
2answers
2k 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 x@1.2.3.4 "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
170 views

script for restore of postgres database localy from dump given on stdin - ambiguous redirect

Runing this command: sshpass -p "pass" ssh x@1.2.3.4 "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
2k 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
316 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
1answer
136 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 called process_data (14gb) and another tiny lookup table called process_location. I'm doing a query between these two ...
0
votes
1answer
40 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
596 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
1answer
125 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 ...
1
vote
1answer
646 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
77 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
126 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 ...
4
votes
2answers
5k 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
1answer
120 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
1answer
294 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
51 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
51 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
162 views

Best design to handle multiple query keys [closed]

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
119 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
50 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 ...
1
vote
1answer
314 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
31 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
142 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 ...
1
vote
1answer
387 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 ...
0
votes
1answer
726 views

Order specific columns in json_agg

I want to sort the rows inside the json_agg function. I found this: PostgreSQL : select columns inside json_agg Which gave me a solution to selecting specific columns for json_agg, but now I want to ...
1
vote
2answers
165 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
107 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. ...
2
votes
1answer
2k 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
299 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
53 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
43 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 ...
1
vote
1answer
314 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 ...
5
votes
2answers
2k 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 ...
4
votes
2answers
2k views

Slow index scans in large table

Using PostgreSQL 9.2, I have troubles with slow queries on a relatively large table (200+ million rows). I'm not trying anything crazy, just adding historic values. Below is the query and the query ...
1
vote
1answer
105 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 | ...
6
votes
2answers
1k 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 ...
3
votes
3answers
151 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
1k 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 ...
3
votes
3answers
606 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.
2
votes
1answer
267 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
4k 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 ...
1
vote
2answers
119 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: ...
0
votes
3answers
704 views

Can i have one foreign key to both parent and it's childs in postgresql?

I want to have a foreign key to reference table and it's children. Here's how I tried and failed: CREATE TABLE parent ( id serial, CONSTRAINT parent_pk PRIMARY KEY(id) ); CREATE TABLE child ( ...
1
vote
2answers
478 views

Memory usage of PostgreSQL

I'm using postgreSQL 9.3 database and I have increased the shared_buffers and effective_cache_size in the settings. Will postgreSQL always take this amount of memory or only when needed? I'm ...
0
votes
1answer
20 views

Postgres Grouping of Like Results

Our database is running on Postgres 9.3. We have a table called notifications. Notifications contains id, type, triggering id, source id, and some other information. What I am trying to do is select ...
0
votes
1answer
1k views

PostgreSQL + PG_Monz (Zabbix) DB size too large > 1073741824

I an new to Postgres (9.3 on Ubuntu 14.04) monitoring and I have been using Zabbix/PG_monz. I am seeing errors that my database has "too many temp bytes" and that my database is "too large". My ...
6
votes
1answer
354 views

Why does a SELECT statement dirty cache buffers in Postgres?

I am running EXPLAIN (ANALYZE, BUFFERS) SELECT ... in my Postgres 9.3 server. I end up seeing something like Buffers: shared hit=166416 dirtied=2 in the output. From the documentation, "dirtied" ...
5
votes
2answers
1k views

Partial db dump/restore

A part of my database (PostgreSQL 9.3) relies on extra tables (ex. County, City, Town, ... ). I don't manage these tables, they are updated regularly by a third party. Each time I get a new full dump, ...
6
votes
3answers
1k views

PostgreSQL procedural languages overhead (plpython / plsql / pllua…)

I'm trying to find information about PostgreSQL user defined functions in procedural languages performance for real time tasks. How does they compare to builtin functions? Is there any difference ...
0
votes
1answer
124 views

Trying to map postal codes to their respective cities, but running into some issues

The Problem I am creating a database of Countries, Regions, Cities and Postal Codes. Here's an example to give an idea of the database structure as it is now: Now, let's take Tuskegee University ...
0
votes
1answer
412 views

Array column vs another table when the relationship is quite small

The Data I am creating a table of cities and their zip codes for the United States. Some zip codes have a list of acceptable cities, as designated by the USPS. For example, the zip code 36613 is ...