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
0answers
57 views

How does PostgreSQL compute the cost for index scan?

Consider the following plan: Index Scan using foo_c1_idx1 on foo (cost=0.00..8.38 rows=1 width=37) (actual time=0.012..0.013 rows=1 loops=1) Index Cond: (c1 = 500) Buffers: shared hit=4 Total ...
1
vote
3answers
250 views

Best DB structure for large CSVs?

I have previously posted on stackoverflow, however, I have been directed here. I'm getting to the stage where I need to analyse lots of flat CSVs: Read-in around 500GB of CSVs (e.g. daily data ...
1
vote
1answer
51 views

IOPS needs of Root Volume vs Database/Log Files Volume

I'm setting up a database server via AWS EC2, using: Root EBS Volume, General-purpose SSD EBS Volume, Provisioned IOPS And intending to configure PostgreSQL to have the database and log files on ...
1
vote
1answer
215 views

Deleting many rows on a live table in chunks - without locking for too long (PostgreSQL 9.3)

I am new to postgres, I have a 20 million row table that is on a live server - I need to remove most of the rows but not all. I want to do this without impact to other read/write processes accessing ...
1
vote
0answers
44 views

Can an index become inconsistent for “no reason”?

Of course, when i say no reason i mean "apparently" no reason because nothing happened on the server (forced shutdowns, data corruption, deadlocks, etc...). I ask this because, there was an ...
4
votes
2answers
114 views

How to get row_count diagnostic after EXPLAIN ANALYZE in Postgres?

I normally retrieve the row_count of a query by doing GET DIAGNOSTICS row_count (normally within a function). But if I want to run EXPLAIN ANALYZE on a query AND get row_count, I can't see any easy ...
9
votes
1answer
159 views

Huge mismatch between reported index size and number of buffers in execution plan

The issue We have a query like SELECT COUNT(1) FROM article JOIN reservation ON a_id = r_article_id WHERE r_last_modified < now() - '8 weeks'::interval AND r_group_id = 1 AND ...
0
votes
2answers
71 views

Is there a way to show the creation statement for an index in PostgreSQL

I need to recreate an index in PostgreSQL which has suffered index bloat. Since I need the index to be usable while it's being created, I can't use REINDEX. I am going to recreate the index with a new ...
0
votes
2answers
78 views

select query not returning expected result

I have a table which is having 30 columns. And I have executed the below query (using postgres 9.1, executed via pgAdmin) select column2 || '|' || column3 || '|' || column4 from tablename order by ...
1
vote
1answer
86 views

Postgresql equivalent to sqlite pragma

What would be the equivalent of the sqlite pragma below if I want to get the best performance out of postgresql. pragma synchronous = OFF; pragma journal_mode = OFF; pragma count_changes = OFF; ...
1
vote
2answers
199 views

300 inserts per minute, 6 million rows per month

I have a Django application using Postgresql 9.3 that is about to grow in load to about 300 inserts per minute (peak load) and about 6 million rows per month in one table. Also there should be a lot ...
5
votes
2answers
515 views

How to make this query use my multicolumn index?

Currently, I have a view which is defined like this: View "public.customer_list" Column | Type | Modifiers | Storage | Description -----------+---------...
0
votes
0answers
354 views

pgpoolAdmin for pgpool-II 3.4 : superuser: unknown (Connection error)

I cannot start/stop/reload my nodes through pgpoolAdmin and it says "superuser: unknown" even though I'm logged with the postgres account. What could be the cause? pgpool.conf is identical to another ...
1
vote
1answer
48 views

ORDER BY text doesn't use my index?!

currently this query doesn't use a index even after I created one: EXPLAIN ANALYZE SELECT * FROM customer_list WHERE rating = 3 ORDER BY name ASC LIMIT 20 ; ...
3
votes
2answers
267 views

Can I find all JSON scalars in PostgreSQL 9.3?

We are using Postgres 9.3 and making use of the JSON column which was new to that version. Version 9.4 added a LOT of utility functions for handling JSON, as well as the new JSONB column. You can see ...
3
votes
1answer
125 views

PostgreSQL + Docker: incorrect resource manager data checksum in record at 46F/6A7B6D28

I'm running PostgreSQL 9.3.9, Docker 1.8.2 and Ubuntu 14.04. I have an issue where my hot standby keeps failing with the following error message: incorrect resource manager data checksum in record at ...
-1
votes
1answer
148 views

Cannot connect Eclipse to PostgreSQL [closed]

I see this error message when I'm trying to connect to PostgreSQL from Eclipse. org.postgresql.util.PSQLException: FATAL: password authentication failed for user "postgres" at org....
3
votes
1answer
77 views

Why is my tsv index not being used?

I'm trying to get the postgres full-text-search facility functional. I have two tables, one I created just for testing, and the actual one I want to be able to search: Test Table: webarchive=# \d ...
0
votes
1answer
116 views

Anybody know story behind pgFoundry (PostgreSQL project site) downtime? [closed]

pgFoundry site's been unreachable 2 days and counting.... I have googled up some ominous messages from the Spring about it eventually closing, but I had never seen any notice on the site to say that ...
1
vote
1answer
65 views

Interpreting Decision Time (DT) in temporal databases

Could anyone give me an insight for the need to model decision time in temporal databases and specifically how and why decision time differs from valid time and transaction time? What would be the ...
1
vote
1answer
68 views

Why does dynamic SQL behave differently in SELECT FROM than in SELECT in Greenplum? [closed]

We have a function that executes dynamic SQL (I know, SQL injection, the "Bobby Tables" attack, but that's a separate issue). It takes two parameters, interpolates the first into the second, and ...
1
vote
0answers
19 views

Why can I not alias a column in a view? [duplicate]

I am trying to create a view that provides all quarter starts from a date dimension. Here it is: CREATE OR REPLACE VIEW quarter_starts AS SELECT min(day_number_in_epoch) AS day_num FROM ...
1
vote
0answers
39 views

Postgres stream WAL segment removed

We have a master server and replication server running Postgres 9.3.6 Streaming was going slow due to a secondary failover router. It was running behind but still working. I got a communication ...
0
votes
1answer
18 views

PostgreSQL SQL: updating summaries

I'm trying to optimize a query like: UPDATE master SET count = (SELECT COUNT(*) FROM detail WHERE detail.master_id=master.id) Where the master table is large, so running this results in a huge ...
1
vote
1answer
77 views

UPDATE id table on the columns with the same name

I may be a newbie question but I don't know how to resolve this. I have a table like: name | id | value A 1286487 1286333 B 1286489 1286403 C 1286495 1286455 C ...
2
votes
1answer
388 views

Count children of a parent in recursive CTE

I use this query to find comments by a parent id: WITH RECURSIVE cte (id, content, path, parent_id, depth) AS ( SELECT id, content, array[id] AS path, parent_id, ...
1
vote
0answers
65 views

How can we plan for a High Availability/Disaster Recovery setup in Postgresql 9.3? [closed]

I am working for mission critical project. We are planning for High Availability. At any point of time irrespective of the failure (network, hw, or db) one db server should be available for the ...
2
votes
0answers
123 views

Old records disappearing after upgrade from 9.4.1 to 9.4.4

I recently upgraded from 9.4.1 to 9.4.4. Since then millions of rows have disappeared from several tables containing historical data. The severity of data loss seems to be directly proportional to ...
7
votes
1answer
245 views

Surprising results for data types with type modifier

While discussing a recursive CTE solution for this question: Get the last 5 distinct values for each ID @ypercube stumbled across a surprising exception, which lead us to investigate the handling ...
2
votes
1answer
56 views

Dealing with corruption: file-level backup vs. pg_basebackup

The wiki on corruption recommends immediately taking a file-level backup of your database by stopping the postmaster and using tar, but I was under the impression that using pg_basebackup was a better ...
2
votes
1answer
37 views

Excluding rows by lots of ids

I need to exclude some rows that the user has already seen. Currently I am doing this by simply comparing the ids that the user has seen to the row's ids by running this query (there are also other ...
1
vote
1answer
55 views

Left join not working with sub-query

I've got table of scores where each entry corresponds to a particular (student, subject) pair. CREATE TABLE score ( id serial PRIMARY KEY, value integer NOT NULL, subject_id integer NOT NULL, ...
3
votes
1answer
153 views

How to log all SQL queries for a single connection or user or client host?

Is there a way to log all SQL queries in Postgres 9.3.9 for a single connection or user or client host? In other words, I want log_statement = all but only for a certain username or client hostname ...
4
votes
2answers
233 views

Get the last 5 distinct values for each ID

I'm working with PostgreSQL 9.4. I have a table that contains the following entries: id | postcode | date_created ---+----------+----------------- 14 | al2 2qp | 2015-09-23 14:46:57 14 | al2 ...
3
votes
1answer
59 views

Replacing a large object in PostgreSQL

What should one do when one wishes to replace a large object with a new large object. An example would be to update an uploaded file with a new version. One way would presumably be to create a new ...
3
votes
2answers
279 views

Use composite type to create new table

In ORDBMS databases I have seen that complex types can be used: create type name as( ...) And I can references those types when creating a new table, like: create table example (row_name ref(name))...
3
votes
1answer
165 views

Prevent explicit insert into serial columns

I want to prevent explicit inserts into serial columns. I have come up to the following trigger: drop table test_table; create table test_table( id bigserial primary key, foobar text ); create or ...
4
votes
2answers
217 views

Use column values in ORDER BY and LIMIT clauses

I have this table in a Postgres 9.3 database: I need to use the content of columns order and limit to sort and filter this table. The table will be sorted by column first_name. The final result ...
6
votes
1answer
725 views

PostgreSQL could not fork autovacuum worker process: Cannot allocate memory

Running into some issues every few days with postgres crashing and entering recovery mode. The logs from postgres look like so ... Lots of this for 5-10minutes 2015-09-24 10:07:27 GMT LOG: could ...
2
votes
1answer
97 views

Query result in a daily date from a date range selection Postgres 9.3

I need to make a report that will output a result in a daily row result based on the datarange selected. Example: Table: sales ->id ->type ->gross ->created_at ->updated_at For ...
-3
votes
1answer
44 views

How can postgresql copy tables to another name

Now is Setempber I have 3 tables , t1,t2,t3 When it comes to October, I want to create another 3 tables named t1_09,t2_09,t3_09 which stored data from t1,t2,t3 (copy t1,t2,t3 to t1_09,t2_09,t3_09) ...
0
votes
1answer
229 views

how do you revoke create table from a user on postgresql 9.4?

I'm trying to create a postgresql user that can select but can't create or drop, insert and so on on a database but I cannot for the life of me figure out how to prevent a user from creating its own ...
1
vote
3answers
209 views

Get count estimates from pg_class.reltuples for given conditions

Is it possible to query the reltuples column for a given table with additional conditions like table.name LIKE 'hello%'? Currently on my bigger tables the SELECT count(*) query takes long and I ...
8
votes
1answer
2k views

JSONB with indexing vs. hstore

I am trying to decide on the database design, with as few assumptions (regarding how the web app actually evolves) as possible at this stage. As a first step, understanding that JOINS are expensive, ...
0
votes
1answer
46 views

RETURNING value from SELECT table in INSERT .. SELECT

I have a table that looks like this: | column | def | | ------ | ---- | | id | uuid | | status | int | | data | text | I'd like to write a query to duplicate a row (changing only a few ...
1
vote
1answer
67 views

Postgres : Using WITH … AS insert into to return non-inserted column

I'm creating an import job which uploads to a temporary table and then splits the data into two separate tables, joined by a key. What I'm doing is more complicated than the following but this simple ...
17
votes
2answers
2k views

Are WHERE clauses applied in the order they are written?

I'm trying to optimize a query which looks into a big table (37 millions rows) and have a question about what order the operations are executed in a query. select 1 from workdays day where day....
0
votes
1answer
71 views

Filter column by records matching a timezone in Postgres

I want to check whether any rows in my column (TIMESTAMP WITH TIME ZONE) are not stored using UTC. Is there a query I can write to match values that are stored with a TZ offset of 1 or 8 or 11 or (not ...
0
votes
1answer
21 views

EXPLAIN EXECUTE completes quickly, but is slow without EXPLAIN

When I run the following query in pgAdmin3, it runs in like 74ms, and I can see the execution plan but no rows are returned. I guess that's the behavior of using EXPLAIN? Anyway, when I remove '...
0
votes
1answer
147 views

“parameter name used more than once” error when declaring function - why this is happening?

create function test(a integer, b integer) returns table (a integer, b integer) as $$ begin return query select 1, 2; end; $$ language plpgsql; throws exception: ERROR: parameter name "a" ...