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)

8
votes
2answers
178 views

Why can't I see my table (PostgreSQL) when I use \dt(+) inside psql?

I have created the table donor in the schema reference as per: CREATE TABLE reference.donor ( donor_code smallint PRIMARY KEY, donor_name character varying NOT NULL, donor_type smallint ...
1
vote
1answer
30 views

Debugging Postgres error: “could not send data to client: Broken pipe”

I'm running a Django 1.8 site, served by nginx and Gunicorn, with a Postgres 9.4 backend. When I make lots of data-intensive calls to the site's API in succession, I've noticed about 1 in 100 ...
1
vote
1answer
162 views

PostgreSQL: How to get a sum of values and the total rows that went into each value? [closed]

I have a table, MyData in the following format: |id|theme_id|priority_id|value| I am trying to get the following result for all the rows: theme_id | priority_id | total | sum ...
0
votes
2answers
42 views

Trying to get count of childs rows

I'm trying to get the count of child of a parent row. The sql query I'm using as follow only pulls the immediate count, how can I get total count as childs can have more childs. select c.id, ...
0
votes
1answer
32 views

PostgreSQL full-text search retrival from id gap

I have a full-text search column on one of my tables in PostgreSQL 9.3 implemented based on this tutorial (without the South part). There is a postgres function where I SELECT concat_ws(' ', ...
2
votes
2answers
242 views

Why the WHERE clause is not pushed down in the view's query?

With Postgres 9.4, I'm doing the following query quite often: SELECT DISTINCT ON(recipient) * FROM messages LEFT JOIN identities ON messages.recipient = identities.name WHERE timestamp BETWEEN timeA ...
2
votes
1answer
35 views

Can Postgres index regular expressions stored in a column?

I've got regular expressions stored as varchars in a column, which I need to match against incoming input. For example, the table might contain: | field | value | |-------|---------------| | ...
1
vote
2answers
42 views

Backup a large postgres database for PITR

I have one postgres 9.1 master and 2 slaves setup with streaming replication with WAL archive. Now, I am trying to set up for PITR for at least a window of 1 week. This is to cover for any ...
6
votes
1answer
77 views

SELECT DISTINCT ON subquery uses inefficient plan

I've got a table progresses (contains on the order of hundreds of thousands of records currently): Column | Type | Modifiers ...
0
votes
1answer
37 views

Postgres 8.4 VACUUM FULL performance

I am trying to run a vacuum full analyze on a postgres 8.4 machine where it appears that autovacuum has not been able to keep up. The database is very large and under heavy load. The problem started ...
0
votes
1answer
29 views

combining 2 sql queries based on different conditions

I have a table : Person : (integer) (varchar) (Date) (date) id state d1 d2 1 'ALIVE' '2015-08-07' '2015-08-08' 2 'DEAD' '2015-08-08' ...
0
votes
0answers
32 views

How to call Oracle Function from POSTGRESQL

I am sorry if this topic already exists. I have: PostgreSQL 9.3 , Oracle 11gR2 on Linux . I used oracle_fdw to query Oracle table from Postgres but oracle_fdw can not call function. Questions: How ...
2
votes
3answers
43 views

Cap on number of rows matching a condition in Postgres

Say I have a db schema like this user_id text photo_id text date_created timestamp and I want to enforce a constraint that says "a user can only have 30 photos". A naive way to implement this would ...
0
votes
2answers
23 views

is it “bad” to bulk update a boolean column semi-regularly on potentially a large set of records in Postgres?

pertaining to a previous question I asked: how do you prevent dead rows from hanging around in postgresql? If I have a table with 300k rows, and I were to update all rows in the table, setting a ...
1
vote
1answer
32 views

Continuously arriving data with fast filtering on date in PostgreSQL

I have a huge amount of data, which arrives continously. (About 10 million rows/day, ~3000 rows/10 seconds in peak intervals of the day.) I want to store them in a PostgreSQL database (I use PostGis ...
0
votes
1answer
36 views

PostgreSQL bug(?) - logging with a minimum log level

I am using the log_min_error_statement - Setting in the PostgreSQL configuration file, but the logger does not react on the setting, either if I turn it on, or off, or set it to another level, the ...
0
votes
1answer
20 views

Safe to Restore Database from Untrusted Sources

I am asking someone to aggregate around 100GB of data for me. I would prefer this to be in a Postgres database instead of something like CSV. I was thinking pg_dump and pg_restore custom format. Is it ...
0
votes
1answer
59 views

Columns names are case sensitive on postgres

I have successfully migrated database from MySQL to PostgreSQL. While I am trying to connect with ofbiz, I am getting an error saying that "user_id" not found. When I query against postgres by ...
0
votes
1answer
26 views

PostgreSQL to MS Access

Is it possible to create a database in PostgreSQL move it into other computer and use this database with MS Access? How to do that?
1
vote
2answers
21 views

PostgreSQL Extension Multiple SQL Files

I'm creating a custom extension for PostgreSQL. I can get the extension to create successfully, but my foo--0.1.sql is getting rather large and complex. I'd like to break it out into multiple files ...
7
votes
1answer
60 views

How to reclaim space taken by an index that partially built and was terminated by a power outage

I'm running postgres (postgis) 9.4.2 on a mac (10.10.4). I've got a couple big tables (several TBs). During an index build on one of them that takes about a week, I watched the available HD space ...
0
votes
0answers
43 views

Postgres query timing divergence

I have been using EXPLAIN ANALYSE and \timing option to clock the server side execution time and the client side time (using psql in Ubuntu). On running a same query, say an update query repeatedly, I ...
3
votes
1answer
35 views

Why does Postgres run SORT on many rows, before GROUP BY?

I'm using Postgres 9.4, with a recently analyzed database. These are my tables: Materialized view "public.vw_chemical_summary_by_ccg" Column | Type | Modifiers ...
2
votes
0answers
28 views

Recursive BOM (Bill Of Material) type query

I have what I've been told is a Bill Of Material type query - which requires a 'recursive' query. I've never heard of such a thing - and was hoping to get some help. My schema stores asset / ...
3
votes
0answers
41 views

Efficiently computing aggregate functions over subqueries with incremental data

I have a PostgreSQL database (version 9.3.6) containing a large number of orders. As the orders are processed, scan_events are triggered and stored, with multiple events per order. Scan events have a ...
0
votes
1answer
32 views

Tuning postgresql.conf when benchmark

Description: We build a new database server Enterprisedb v9.3 (PostgreSQL commercial), so we use JMX to benchmark it with 1000 users. Here our detailed information: Operating System: Centos 6 x64 ...
1
vote
2answers
38 views

Apply limit and conditions per grouped data

I am building an api endpoint to send statistics data that then is going to be plotted on a graph. The hardest part is being the query I need to execute. To sum up I want the first n most sold ...
1
vote
1answer
46 views

Count childs of parent with sql or save in column?

In postgres I have a threaded setup where parent id has many childs and childs can have childs. What is the most efficient way to get the count with a sql statement similar to this: WITH RECURSIVE ...
0
votes
1answer
29 views

how can I count by week, month, and YTD with postgres?

under PGSQL 9.3 I am trying to run a query to summarize some data from table a: | Entry date-Date | Customer ID-charvar | received-Boolean | Processed-Boolean | finalized-Boolean | billed-Date | I ...
5
votes
1answer
70 views

What do the shared buffers metrics for various types of index scans represent?

TL;DR: When an "EXPLAIN (BUFFERS)" shows an "Index Scan", it does not show the number of pages needed to be read from the index. Is it just omitting that number, or does it actually not read pages ...
1
vote
1answer
24 views

PostgreSQL - Who or what is the “PUBLIC” role?

I was looking at information_schema.role_table_grants when I saw public in grantee column, then I've checked at information_schema.enabled_roles but this role_name does not exist. => Who or what is ...
0
votes
0answers
15 views

A query to know RDS instance name in Amazon RDS for Postgres

Do you know a way to fetch the RDS instance name by a query run against a RDS-powered PostgreSQL database in Amazon? Something similar to: curl http://169.254.169.254/latest/meta-data/ami-id in ...
0
votes
1answer
30 views

Create postgres schema in db with multi user in AUTHORIZATION

Is it possible to create a schema that is owned or accessible by more than one user in the same database? I was reading postgres create schema documentation and postgres alter schema documentation ...
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 ...
3
votes
0answers
147 views

“could not write to log file, Bad file descriptor” error when working with huge table

I'm having a big problem with a PostreSQL database. Whenever I do something with a huge table (over 9 million rows) the whole postgres process crashes with the following entries in the log file: ...
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 ---- ...
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 ...
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 ...
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
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 ...
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 ...
5
votes
1answer
47 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 ...
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 ...
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 ...
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
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
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> ...
2
votes
1answer
38 views

How to have remote standby PostgreSQL server in different location (AWS)?

We have a primary PostgreSQL 9.4 server running multiple instances in our DC. It is using Continuous Archiving via WAL-E out to AWS S3. I would like to have a hot/warm standby server in our VPC within ...