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
1answer
42 views

Performance issue using view vs. direct statement

I have a table with structure similar to this: table values ( id1 int, id2 int, value real, value_at timestamp, constraint primary key (id1,id2,value_at) ) I need to get the "current value" for a ...
2
votes
1answer
33 views

How to optimize this query on Postgres

I'm trying to make a relatively simple query, but it's taking much longer than I'd expect. I have an index in place, but it doesn't seem to be helping much. Here's the query. It sometimes takes up to ...
1
vote
1answer
51 views

Index performance for CHAR vs VARCHAR (Postgres)

In this answer (http://stackoverflow.com/questions/517579/strings-as-primary-keys-in-sql-database) a single remark caught my eye: Also keep in mind that there's often a very big difference between ...
1
vote
2answers
332 views

Complex view becomes slow when adding ORDER BY in outer query with small LIMIT [closed]

I have a pretty large query in a view (let's call it a_sql), that is really fast unless I use ORDER BY in an outer SELECT with a small LIMIT: SELECT customs.id AS custom_id, customs.custom_name AS ...
11
votes
2answers
80 views

PostgreSQL Recursive Descendant Depth

I need to calculate the depth of a descendant from it's ancestor. When a record has object_id = parent_id = ancestor_id, it is considered a root node (the ancestor). I have been trying to get a WITH ...
2
votes
1answer
26 views

OFFSET and LIMIT on complex query

I am doing a pretty complex query in Postgres 9.3.4: SELECT p.* FROM unnest('{19082, 19075, 20705, 18328, 19110, 24965, 18329, 27600 , 17804, 20717, 27598, 27599}'::int[]) ...
0
votes
0answers
6 views

Using start and end event logs to create a table/view containing spans between the times of each log [duplicate]

To be specific, I have an event table that logs when a user joins or leaves a team. It looks something like the following: ------------------------------------- | user | event | team | timestamp | ...
0
votes
3answers
54 views

Using start and end event logs to create a table/view containing spans between the times of each log

To be specific, I have an event table that logs when a user joins or leaves a team. It looks something like the following: ------------------------------------- | user | event | team | timestamp | ...
1
vote
1answer
41 views

In PostgreSQL, will VACUUM reset the dead tuple count (n_dead_tup in pg_stat_all_tables)?

In PostgreSQL, will VACUUM reset the dead tuple count (n_dead_tup in pg_stat_all_tables)? The situation I'm seeing is that vacuum is apparently being run manually, once daily, and the n_dead_tup / ...
2
votes
2answers
47 views

select/union output from two columns into three

I'm sure there is an easy answer to this, but my PostgreSQL isn't up to it at the moment. I have a table hostname_facts with three columns: hostname name value hostname is a list of ...
1
vote
1answer
56 views

PostgreSql : flatten json array data

From my current query, I obtain this jsonb data : values: "a1", ["b1", "b2"] And I want to flatten it on one level only, like this : values: "a1", "b1", "b2" Here is a simplfied way to get ...
0
votes
1answer
30 views

Will this SQL snippet REINDEX my GIN index? Why is it renaming an old index onto another?

I have a large PostgreSQL 9.3 database. One index on one table is quite large and appears to be bloated. I am using pgcompact from pgtoolkit to reduce database bloat without heavy table locks. However ...
2
votes
2answers
49 views

How to unite 2 subqueries to reduce retrieving data from cumbersome tables?

There are 3 tables: user, conversation, message. Table user: id, login, last_login_time (time of last user's login), ... Table conversation: id, user1_id, user2_id, ...
0
votes
0answers
47 views

How can a select max query return no rows?

I have a process that selects the maximum date from a Greenplum (PostgreSQL 8.2) table, and coalesces it to a default value. Once, and once only, this query returned 0 rows. How is this possible? Even ...
0
votes
1answer
25 views

psql variable holding text with multiple lines

This works as expected, \set x '''2\n3''' but this doesn't \set x '''2 3''' Is there a workaround for later example so I don't have to use \n instead of newline?
0
votes
0answers
7 views

Debian Server failover setup

I need to set up two servers with failover capability (Active - Passive model) sharing a common DB (Postgres)running over NAS. Could you please suggest me some workaround for the same ?
0
votes
1answer
30 views

Migrate Oracle BLOB to Postgres BYTEA or OID [duplicate]

After the BLOB columns of Oracle (11g r2) tables are migrated into a Postgres (9.4.5) table such as using Ora2Pg, I have two questions: 1) Which data type will the new column assume, is it BYTEA or ...
2
votes
1answer
46 views

PostGIS - indexing - speed up query

I have this query (called from PHP, hence the $ variables): SELECT a.name, St_AsText(a.geom), b.name AS name_b, b.id FROM a LEFT JOIN b ON b.id = a.b_id WHERE ...
1
vote
1answer
33 views

PostgreSQL: Restoring 1 file from a directory format dump

I've got a large'ish (> 700GB) PostgreSQL database. I'm working on importing it into AWS RDS. I realize there are modules that will cause problems, we're dealing with that separately, so for right ...
2
votes
1answer
44 views

How do you configure Postgres 9.4 for incremental backups and hot standby?

I want to set up Postgres to do incremental backups. I'm using PostgreSQL 9.4 on Windows with a database that weighs about 1GB and has a growing 10000 records each day. I understood that the steps ...
0
votes
0answers
12 views

empty tablespace in postgres [duplicate]

With SELECT spcname FROM pg_tablespace; one can list tablespaces in Postgres. However, other than using du, how can one determine whether a tablespace is empty or not? If one tries to drop a ...
1
vote
1answer
26 views

How to automatically drop and restore PostgreSQL database

I am using dhis2 with PostgreSQL as database but I want to verify my backups from the cloud by restoring and checking whether the data are ok but I need it to be automatic.
3
votes
0answers
59 views

Recovery from Live to a new Slave Server - PostgreSQL - ERROR

I've started a new SLAVE PostgreSQL server set up. master: 192.168.100.1 slave1: 192.168.100.2 slave2(NEW SLAVE) 192.168.100.3 * NOTE: I run the pg_basebackup from another STANDBY SERVER. Not ...
1
vote
1answer
37 views

Postgres Can't Add Foreign Key Constraint

I have a table with about 220 million records :( and I need to add a foreign key constraint. My command looks something like this: ALTER TABLE events ADD CONSTRAINT events_visitor_id_fkey FOREIGN ...
1
vote
0answers
143 views

Is it safe to update postgres server to 9.5 with ubuntu automatic update

Postgres 9.5 is out and Ubuntu has it in the update manager already. I'm wondering though, is it safe to update that way? Concerns/Questions: Does it install 9.5 in addition to my already running ...
7
votes
1answer
111 views

Postgres with pgpool architecture

Below is an example pgpool architecture: This implies that you only need to have pgpool on a single server; is this true? When I look at the configuration I also see that you configure backends ...
0
votes
0answers
24 views

Postgres Public Synonym Issue

PostgreSQL ver 9.4.5. Linux OS. Application: Web Based Platform: App Server (java) --> jdbc call --> Database Server (PostgreSQL) I do know that PostgreSQL does not support the public ...
0
votes
1answer
31 views

Do Foreign Keys Impose Uniqueness

Do FOREIGN KEYs impose the uniqueness of the the referent? I.e. if I have a foreign key that refers to a UNIQUE constrained column, will the FOREIGN KEY column be unique? I'm particularly wondering ...
1
vote
2answers
27 views

Recreate all foreign keys in all tables as deferrable (batch)

I want to make all foreign keys in my database deferrable. But there is no possibility to alter an existing constraint. So I need to drop and add again each foreign key. How to do it automatically?
3
votes
1answer
28 views

Replace content of only one table with new version (should disable foreign keys?)

I have 2 tables. 2nd has foreign keys to reference 1st. I need to replace the whole content of the 1st table with the new version from another database. The new version satisfies all constraints and ...
0
votes
1answer
29 views

New column to a base table

I want to add a new column to a base table, and add that new column to an existing view... So all selects/updates/deletes translate back to the original table How can I do that? Thanks ...
7
votes
1answer
87 views

Why does adding a smallint column to a postgres query increase execution time 60X?

Here I have what seems to me to be a routine table and a table creation script that I have put in /tmp/try.sql. I am running postgres under OSX 10.10.2. Here is the table: cow_dev=# \d carc ...
2
votes
2answers
45 views

PostgreSQL is using more memory than I'd expect

I have PostgreSQL configured mostly how pgtune recommended, and the settings are supposedly conservative. PostgreSQL is using more memory for a single connection than the settings would suggest is ...
0
votes
0answers
26 views

Is it possible to do a VACUUM FULL and allow read-only access to the table?

I have a PostgreSQL (9.3ish) database which has a lot of data written to it frequently. Disk space is constantly being used. I have had success with VACUUM FULL with trimming the bloat and reducing ...
9
votes
1answer
386 views

What is the overhead for varchar(n)?

I wanted to ask for the meaning of this fragment from Postgres doc regarding varchar(n) type: The storage requirement for a short string (up to 126 bytes) is 1 byte plus the actual string, which ...
0
votes
1answer
41 views

Three queries returning 1 row each

So.. I've got: 3 DB Queries returning 1 row each .... SELECT value FROM ja_settings WHERE varname='clickatel_api_id' SELECT value FROM ja_settings WHERE varname='clickatel_user' SELECT value FROM ...
6
votes
1answer
60 views

Index not used with `=any()` but used with `in`

Table t has two indexes: create table t (a int, b int); create type int_pair as (a int, b int); create index t_row_idx on t (((a,b)::int_pair)); create index t_a_b_idx on t (a,b); insert into t ...
2
votes
1answer
30 views

Postgres plpgsql: Is a view better than a select query in a plpgsql function

I have a recursive query to the general effect of: WITH RECURSIVE text1 (selfid, parentid, text) AS ( SELECT DISTINCT text.selfid, text.parentid, text.text FROM text WHERE text.selfid = ...
1
vote
0answers
42 views

Why does this function return an error by itself, but when called by another function it doesn't return an error?

I have a function that calls a sub-function as part of the function. When running the sub-function with the same data it throws an error, when running the main function it doesn't. I would have ...
1
vote
1answer
50 views

Simplest filesystem backup of Postgres database

Situation: A backup system performs filesystem-level daily backups on a server containing a large but fairly static PostgreSQL 9.2 database. The filesystem backups are incremental and generally take ...
0
votes
0answers
36 views

How to get CPU and memory usage of PostgreSQL server and queries from database system views?

We are looking for the way to get CPU and memory usage data of PostgreSQL 9.x for the monitoring purposes. The requirement is to provide the data on server, database, session and query level. We are ...
3
votes
0answers
102 views

What's the point of prefixing each element in the column by its column number in multicolumn GiN indexes?

According to these slides which, I believe, are incorporated into the current implementation, each column element in multicolumn GiN indexes is prefixed by its column number and is stored separately ...
1
vote
1answer
147 views

Slow query - Optimize - PostgreSQL

I've got a slow query here... could you please help me to optimize it? Query: SELECT concat(company, ' ', customer_name_first, ' ', customer_name_last) AS customer, sum(revenue) AS revenue, ...
4
votes
1answer
47 views

Deadlock best practices

We are running Postgres 9.3, and we have occasional deadlock issues. Currently someone notices and will manually kill off the processes. Is there a best practice in dealing with these when they ...
3
votes
1answer
80 views

Versioning data in a database [duplicate]

I have a database to hold data about projects. Projects have lots of data associated with them that is segmented into other tables such as project_contacts and project_images. Those are one-to-many ...
1
vote
1answer
45 views

Does ALTER on PostgreSQL, Oracle table (respectively) create intermediate copy of the table?

I've heard MySQL admin claim that ALTER on MySQL table creates an intermediate copy of the table during the operation (don't know if the type of table involved were supposed to be MyISAM or InnoDB), ...
3
votes
1answer
15 views

How can I exclude range overlaps if one range may be empty?

Suppose I want to ensure that, for any expected height in feet, I have a single, unambiguous classification. I create this table: CREATE TABLE heights_in_feet( size TEXT NOT NULL, min INTEGER NOT ...
1
vote
1answer
94 views

Mapping between each bit in a bitmap index and tuple-pointers

Bitmap Indexes are described here, but I don't understand some part of it. A plain indexscan fetches one tuple-pointer at a time from the index, and immediately visits that tuple in the table. ...
2
votes
0answers
56 views

Postgresql Streaming Replication Error: WAL segment removed

I want to set up PostgreSQL streaming replication, but get the following error: FATAL: could not receive data from WAL stream: ERROR: requested WAL segment 00000001000000000000006A has already ...
1
vote
0answers
56 views

How to fix postgres stuck in recovery mode?

I have Jira and Confluence installed in a server. We didn't have any free disk space one day, and these services didn't started correctly, so after deleting some files we now have some free space ...