PostgreSQL is an enterprise class, open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact PostgreSQL version when asking questions.

learn more… | top users | synonyms (1)

5
votes
2answers
4k views

How do I list all tables in all schemas owned by the current user in Postgresql?

I can list all tables in all schemas using > \dt *.* but that also lists system tables that greatly outnumber my tables that I care about. I'd like all the tables (and possibly views) created by ...
1
vote
0answers
29 views

How to represent multiple unique constraints across hierarchical tables?

My (shortened) table definition is: CREATE TABLE bus ( id integer NOT NULL PRIMARY KEY, name character varying NOT NULL CHECK (name<>'') ); CREATE TABLE network_node ( id ...
1
vote
1answer
62 views

Postgres not using the index even when rows returned is 5% of the table

I have a postgres table with the following structure: +---------+-------------+-------------+----------+---------+---------+ | id | timestamp | numvalues | text1 | text2 | text3 | ...
18
votes
4answers
15k views

Aggressive Autovacuum on PostgreSQL

I'm trying to get PostgreSQL to aggressively auto vacuum my database. I've currently configured auto vacuum as follows: autovacuum_vacuum_cost_delay = 0 #Turn off cost based vacuum ...
0
votes
1answer
41 views

Priority search on columns

Description: We are using PostgreSQL 9.3 - Centos 6 x64 . We have a dtsc_search_data table as below: dtsc_search_data id ---- c1 ---- c2 ---- c3 1 ---- 1 ---- 1 ---- 1 2 ---- 1 ---- 2 ---- ...
2
votes
1answer
177 views

Unsupported languages in Postgres Full text search

My web application supports 3 languages, English, Traditional Chinese, Vietnamese. I understand that Traditional Chinese and Vietnamese is not supported in Postgres' Full Text Search. If I were to go ...
0
votes
2answers
176 views

Can I use `on delete set null` on a compound foreign key which contains a primary key column?

The docs say: Restricting and cascading deletes are the two most common options. RESTRICT prevents deletion of a referenced row. NO ACTION means that if any referencing rows still exist when the ...
2
votes
1answer
2k views

Setting password for 9.3 PostgreSQL server [closed]

I currently have a 9.3 server running (on Ubuntu 14.04) with "trust" set as the authentification method in pg_hba.conf. How can I set the password again. In pgadmin the server currently doesn't allow ...
0
votes
0answers
17 views

Tuning Postgresql autovacuum [duplicate]

Setup I have a problem similar to the one described and not solved here , with some differences... I am using Postgresql 9.3 on Ubuntu 14.04 where I have a Django project running with celery. I ...
2
votes
1answer
63 views

Postgres to Postgres foreign data wrapper, for tables based on queries

I have a db with sensitive data in a secure server, and a set of anonymization queries that I'd like to create foreign tables based on in a less secure server. The Postgres native wrapper only ...
5
votes
1answer
46 views

How to examine PostgreSQL server's SSL certificate?

Suppose there is a PostgreSQL server running and it has SSL enabled. Using "standard" Linux and PostgreSQL tools, how can I examine its SSL certificate? I'm hoping for output similar to what you ...
4
votes
1answer
35 views

Postgres query plan has high rowcount, slow execution

I've identified this snippet of a query as running at incredibly slow speeds, although it's quite simple. It aims to find all the records in Table1 which don't have a more recent entry in Table2 with ...
0
votes
0answers
12 views

Running `gman_servers_set` in PostgreSQL's startup

I've compiled PostgreSQL's gearman extension and now I can connect to a gearman server using triggers and such in PostgreSQL. As you may know, before a job can be registered in a gearman server, you ...
9
votes
3answers
993 views

How to implement business logic permissions in PostgreSQL (or SQL in general)?

Let's assume I have a table of items: CREATE TABLE items ( item serial PRIMARY KEY, ... ); Now, I want to introduce the concept of "permissions" for each item (please note, I'm not talking ...
3
votes
1answer
81 views

A costly approach to filtering on a calculated date

This builds off my previously wonderfully answered question Running a complex query for every date in a range I've got an expanded problem and I'm certainly not solving it in a performant way. The ...
0
votes
1answer
202 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 ...
0
votes
2answers
378 views

How to implement a dynamic string into a (prepared) sql statement?

I have some spatial datasets which are available in a open source metadata catalog software GeoNetwork and in a PostGIS database. The problem is that the student who created the database used ...
0
votes
2answers
262 views

Postgresql 9.4.1 stuck all queries when making multi updates

Before i used 9.3.5 and servers with ubuntu 12 with 32GB memory. After upgrade to 9.4.1, with more power server 60GB memory on each in wall replication and ubuntu 14, started get db stucks when run ...
1
vote
1answer
28 views

Pausing the import of an SQL file in Postgresql?

I am currently importing a large database (6 - 7 GB's) into my postgresql on Ubuntu. It's taking sometime to do. A problem has come up where I need to physically relocate the laptop the postgresql is ...
2
votes
1answer
17 views

Postgres streaming replication between hosts with different timezone

I am running Postgres 9.1 on Windows 2012 servers. I am using postgres's streaming replication to replicate data between 2 servers. It has so happened that the time zone of the slave has not been set ...
1
vote
1answer
224 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 ...
2
votes
1answer
607 views

Designing a High Score/Leaderboard table

I'm looking for input on how to design a highscore table for a game I'm creating. The database I'm using is postgres. That's not really something I can or want to change (unless there's very good ...
8
votes
2answers
162 views

Running a complex query for every date in a range

I've got a table of orders Column | Type | Modifiers ...
2
votes
1answer
133 views

Should database be denormalized if I need an index across multiple tables

I have a catalog of album and song names. The song has a foreign key to the album table. I expect queries like: SELECT * FROM song s JOIN album a ON a.id = s.album_id WHERE LOWER(CONCAT_WS(' ', ...
2
votes
1answer
198 views

Postgres query plans and tuning parameters

I have a query for which the explain plan indicates that the cost for using a seq scan is lower than the cost for using an index that I have set up. However the actual time for the query is 20 times ...
0
votes
2answers
725 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
152 views

Replication Lag on Postgres AWS RDS Service

We have a single master/single streaming replica Postgres 9.3 db on AWS. The load is not terribly high - this is a development/staging environment. (The production shows similar metrics). Point is the ...
8
votes
2answers
3k views

What is a valid use case for using TIMESTAMP WITHOUT TIME ZONE

There is a long and quite elucidating answer on the differences between TIMESTAMP WITH TIME ZONE -vs- TIMESTAMP WITHOUT TIME ZONE available in this SO post. What I would like to know is: are there ...
1
vote
1answer
197 views

How to create Postgres DB with case insensitive collation

I am using the postgres version 9.4.1 64-bit on windows 7 64-bit. I need to create DB with the setting "case sensitive = OFF" but couldn't make this work. I refereed already many question on SO and ...
1
vote
1answer
16 views

Use of smallserial gives error with PostgreSQL

I know the very basics of databases. I worked in the past with MySQL and recently switched to PostgreSQL (9.1) with PostGIS 2.0.1. At the moment I'm designing a new database and I use the tool ...
1
vote
0answers
42 views

Postgres - Error while adding constraints

Can anyone tell me what this error is? And How to resolve it? ERROR: could not read block 27651 in file "base/114689/118316": read only 4096 of 8192 bytes CONTEXT: SQL statement "SELECT ...
2
votes
2answers
235 views

Optimise huge Postgres table for fast read

I'm about to create a Postgres table with 1.5 billion rows. The table will just have a single TEXT column. The table is effectively a blacklist. When a user of my software saves certain data, this ...
3
votes
2answers
5k views

Tool for Reverse Engineering of PostgreSQL Database on Linux (Ubuntu) [closed]

Please suggest a tool to reverse engineer a PostgreSQL DB into an entity-relationship-diagram (ERD) or equivalent ?? Ideally, I'm looking for a simple ERD or UML class diagram in a format that is ...
3
votes
2answers
156 views

CRUD operations on json data

I have a large chunk of json data associated with each id and I have to somehow get the data for my front end by querying through that large chunk. My TaskDetails table has a structure something ...
10
votes
1answer
6k views

postgresql: how do I dump and restore roles for a cluster?

Where are roles stored in a cluster, and how do I dump them? I did a pg_dump of a db and then loaded it into a different cluster, but I get a lot of these errors: psql:mydump.sql:3621: ERROR: role ...
3
votes
1answer
43 views

Why does this fast postgres query show up in the slow query log?

On investigating an entry in our Postgres log, I noticed a large discrepancy between the reported duration and the speed of the query. 2015-07-28 17:27:26 UTC [dms_segment_3] LOG: duration: 2924.763 ...
1
vote
0answers
129 views

Generally unsolvable problem? How to ORDER BY aggregated columns GROUPED BY many possible columns very fast?

Is there general solution or some best practises or at least some vague hint, how to solve following problem (general, but concretised in following example)? I think this must have been solved many ...
1
vote
1answer
29 views

Storing Large Nested Objects

What is the best way to store a large (thousands of properties) nested JSON object in Postgres? It does not need to be index, queried, or anything. Just written and read. Write speed isn't an issue ...
0
votes
2answers
142 views

Lower() vs ilike on postgresql

What I want to do is to check if value of column equals some string, but case insensitive. Is there a performance difference when using ilike without wildcards (percents) and lower() ? I was planning ...
5
votes
1answer
74 views

Bulk insert M:N relation in PostgreSQL

I need to import data from an old database to a new one, with slightly different structure. For example, in the old database, there is a table recording employees and their supervisors: CREATE TABLE ...
6
votes
5answers
20k views

Why won't PostgreSQL 9.3 start on Ubuntu?

I have successfully installed PostgreSQL 9.3 from the APT repository on 2 VM's running Ubuntu 12.04 and 13.04...however, I cannot get it to install properly on my host machine running Ubuntu 12.04. ...
12
votes
3answers
1k views

Can spatial index help a “range - order by - limit” query

Asking this question, specifically for Postgres, as it has good supoort for R-tree/spatial indexes. We have the following table with a tree structure (Nested Set model) of words and their ...
0
votes
0answers
24 views

Postgres 9.0 data huge folder

On our production environment we are using alfresco-4.0.c as content repository. Alfresco uses an instance of postgresql to index the contents. Looking at the filesystem size, I noticed I'm running ...
0
votes
1answer
22 views

SELinux is preventing pg_ctl from read access on the file postgresql.conf

I am trying to setup a master-slave PostgreSQL 9.4 database on Fedora 22, with the slave database's PGDATA being different from the default /var/lib/pgsql/data/ directory. I wrote this to ...
0
votes
1answer
156 views

Postgres Partition Table Trigger inserting duplicate records

I have a trigger applied on my database table. But when i insert data into it via hibernate jpa it creates duplicate rows in master table. Here is the trigger CREATE OR REPLACE FUNCTION ...
0
votes
0answers
22 views

How to populate values in drop down list in php postgresql [migrated]

I am getting error while populating the values in drop down list using php postgresql. Error means no output in drop down list. Below is my code. <!DOCTYPE html> <html> <body> ...
1
vote
3answers
63 views

Intentionally corrupting an index in PostgreSQL

I need to know if there is any method for intentionally corrupting an index in PostgreSQL. I have been searching for it for a long time but never seen anyone mentioning such an act.
3
votes
1answer
201 views

PostgreSQL server problem in Windows XP

2014-10-01 09:22:49 IST LOG: database system was interrupted; last known up at 2014-09-30 18:30:24 IST 2014-10-01 09:22:49 IST FATAL: the database system is starting up 2014-10-01 09:22:49 IST LOG: ...
1
vote
1answer
45 views

How to optimize the sort in Postgres query

I've been trying to optimize the sort in the following query. I ran EXPLAIN ANALYZE and the majority of the time is during the sort when it arranges the output by distance. I've tried converting the ...
1
vote
1answer
22 views

How to determine if pg_dumpall is still running

Environment: Postgres 8.4 Debian Squeeze LTS Recently we switched from a backup solution around hot_backups to using pg_dumpall. This solution used a pid file as long as the hot_backup was ...