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

learn more… | top users | synonyms (1)

6
votes
1answer
177 views

moving large PostgreSQL/PostGIS database

I need to move and upgrade a very large (~320 GB) PostGIS database from server1 (PostgreSQL 9.1, PostGIS 1.5) to server2 (PostgreSQL 9.3, PostGIS 2.1). The upgrade process is well-documented. The ...
1
vote
1answer
50 views

json_array_elemnts() in FROM in postgresql 9.3 acting as where clause

I am trying to extract array out of json in the FROM statement But issue occurs when certain json doesn't have that array in it and then query just ignores that row and doesn't get counted in the ...
8
votes
2answers
250 views

Tracking current user through views and triggers in PostgreSQL

I have a PostgreSQL (9.4) database that limits access to records depending upon the current user, and tracks changes made by the user. This is achieved through views and triggers, and for the most ...
1
vote
2answers
228 views

Use array expression from subquery in ANY() condition

I have a relatively complicated query with a subquery fetching an array like so: ... ARRAY(SELECT category_id FROM category_schedule_con con WHERE s.id = con.schedule_id ORDER BY category_id) ...
2
votes
2answers
874 views

PSQL not executing SQL command - Powershell

I have a simple script that should execute a select statement and output it to a CSV. My problem is that the script connects to the database and hangs waiting for my sql instructions (which are on the ...
0
votes
0answers
37 views

Application uses all pgpool children up

I have few applications written in Java EE. They connect to PostgreSQL server through pgpool. PostgreSQL consists from master and slave. Datasources in my application server (Wildfly) establish ...
0
votes
1answer
69 views

Can't create user in postgres

When I run CREATE USER username WITH PASSWORD 'test' in sudo -u postgres psql and run \du only postgres user appears... I'm not prompted anything when running commands like ALTER USER and I know USER ...
1
vote
0answers
148 views

SymmetricDS scheduled sync

I am trying to configure SymmetricDS to run a scheduled replication from one postgres database to another. I've seen people suggesting doing that by setting these fields in node config: ...
6
votes
2answers
92 views

Create smaller row from alias and preserve column names

Using Postgres: SELECT users."name" AS "name" , array_to_json(array_agg(sites)) as sites FROM remodel.users AS users JOIN remodel.user_sites AS user_sites ON users.id=user_sites.user JOIN ...
7
votes
3answers
167 views

Can PostgreSQL use nulls in its indexes?

I've been reading this book which says that The database assumes that Indexed_Col IS NOT NULL covers too large a range to be useful, so the database will not drive to an index from this ...
3
votes
2answers
38 views

Use PostgreSQL builtin operator <@ after including extension intarray

I added the intarray extension to my PostgreSQL database in order to make use of the - operator, but I still want to use the builtin <@ operator, not intarray's <@ operator. Is there a way to do ...
1
vote
2answers
118 views

Retrieve additional columns in recursive CTE

This works as far as getting the amount of children a "thread" has but now I can't seem to get it to pull parent row columns. Parent rows have parent_id is null, trees can be any level deep. I manage ...
3
votes
1answer
257 views

Uniqueness constraint with date range

Consider a prices table with these columns: id (primary key) product_id (foreign key) start_date (date, non-null) end_date (date, non-null) quantity (integer) price (numeric) I'd like the ...
8
votes
2answers
2k 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
824 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
2k 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 ...
1
vote
2answers
125 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, ...
1
vote
1answer
49 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
376 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 ...
3
votes
1answer
64 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
319 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
345 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
63 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
33 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
105 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 ...
3
votes
3answers
157 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 ...
1
vote
2answers
49 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 ...
2
votes
1answer
75 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 ...
1
vote
1answer
112 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 ...
1
vote
1answer
25 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 ...
1
vote
1answer
316 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
58 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
39 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 ...
8
votes
1answer
89 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
55 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 ...
4
votes
2answers
69 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
88 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 / ...
5
votes
1answer
70 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
135 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
42 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
121 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
179 views

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

under Postgres 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 | ...
5
votes
1answer
83 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 ...
2
votes
1answer
97 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
45 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 ...
2
votes
1answer
542 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 ...
2
votes
0answers
49 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
590 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: ...
1
vote
1answer
51 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
18 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 ...