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

Rolling sum / count / average over date interval

In a database of transactions spanning 1,000s of entities over 18 months, I would like to run a query to group every possible 30-day period by entity_id with a SUM of their transaction amounts and ...
-1
votes
0answers
18 views

How to use two data for different purposes [closed]

I want to use Cassandra along with Postgres, efficiently. I want to use Postgres materialize views with cassandra for scalability. How do I go about doing this, for an example the reddit model. I ...
3
votes
1answer
41 views

COUNT number of column values with INNER JOIN [closed]

I'm new to PostgreSQL and I am making a Report. This is my SQL Fiddle: http://sqlfiddle.com/#!15/6ce465/5 So basically I have two tables, responses and questions. The structure is responses ...
9
votes
3answers
66k views

How do I insert a row which contains a foreign key?

Using PostgreSQL v9.1. I have the following tables: CREATE TABLE foo ( id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY, type VARCHAR(60) NOT NULL UNIQUE ); CREATE TABLE bar ( id BIGSERIAL ...
0
votes
1answer
428 views

pgpool2 not working on my Ubuntu i686 athlon i386 GNU/Linux

I am trying to implement replication and load balancing on my Ubuntu i686 athlon i386 GNU/Linux.for creating database clustures I have installed postgresql contrib 9.1.11 having initially database ...
0
votes
1answer
7k views

Using postgres with the command line without pgAdmin

I started working with PostgreSQL and till now I only had issues with it ( pgAdmin3 issues the most ) now I work a lot with MySQL and sometimes on the command line by just doing mysql -u ... -h ...
1
vote
2answers
266 views

How to best store a timestamp in PostgreSQL?

I'm working on a PostgreSQL DB design and I am wondering how best to store timestamps. Assumptions Users in different timezones will use the database for all CRUD functions. I have looked at 2 ...
2
votes
1answer
61 views

Avoid joining to the same table multiple times

I have two tables: CREATE TABLE one ( id int4 primary key, p_id int4, k_id int4, c_id int4 ); CREATE TABLE two( id int4 primary key, p_id int4, k_id int4, t_id int4, pos int4 ); Table ...
1
vote
2answers
40 views

Is it recommended to install extensions into pg_catalog schema?

Since objects in pg_catalog schema are implicitly in the search_path (docs), would it be recommended to install extensions in that schema?
2
votes
1answer
37 views

How to validate column while creating Table?

Need to validate the column while creating the tables in postgres , An example before developer create table We are in need to validate the Audit columns, Example : create table tab_1 ( a integer, b ...
3
votes
1answer
167 views

11.2GB text file import MySQL or PostgreSQL

I am out of my depth a bit perhaps but submit this query hoping someone has some experience importing large (11.2GB) tab delimited text file (2.2GB tar.gz file) into either MySQL 5.5.41 or PostgreSQL ...
0
votes
1answer
387 views

Taking hot backup of slave node in postgres (master-slave config with repmgr)

I have a postgresql setup with master-slave configuration using repmgr. I'm struggling with taking a consistent snapshot backup (pg_start_backup) of the slave node, this is due the fact that the slave ...
1
vote
1answer
1k views

How to add FK on a column of composite type in PostgreSQL?

I have a table which contains a composite type and I am trying to connect a column of it to another table's PK. CREATE TABLE aaa (col1 SERIAL PRIMARY KEY); CREATE TYPE bbb AS (col2 INTEGER); CREATE ...
6
votes
1answer
1k views

Best practice for storing record metadata

What is the best practice for storing metadata of individual records in a database? I need to store common meta data such as creation time and time of last update for many tables in my database. I ...
0
votes
1answer
269 views

Pgpool II : unable to read message length between two network interfaces

Start log file: 2015-03-06 01:57:56: pid 2760: LOG: Setting up socket for 0.0.0.0:9999 2015-03-06 01:57:56: pid 2760: LOG: Setting up socket for :::9999 2015-03-06 01:57:56: pid 2760: LOG: ...
1
vote
1answer
92 views

Copying CSV file to temp table with dynamic number of columns?

I am wondering if there is a way to copy a csv file into a temp table where the number of columns is unknown in the csv file. The DB software I'm using is PgAdmin III. I figured out that if I do know ...
3
votes
1answer
39 views

Automatic aging-out (deletion) of old records in Postgres

Does Postgres have any features to support aging-out old records? I want to use Postgres for logging, as a sort of queue, where records (log events) older than two weeks are automatically deleted.
1
vote
3answers
146 views

Remove duplicate values from query result

I am using Postgres 9.3.9. I have 2 tables : f_agent : CREATE TABLE f_agent ( f_agent__id bigserial NOT NULL, f_agent__total_users integer NOT NULL, f_agent__dim_time_id integer NOT NULL, ...
1
vote
2answers
188 views

PostgreSQL : dependencies on a schema

I need to delete a schema in my PostgreSQL database. I would like to make sure that the schema is not used in another schema (for instance : this schema is used for a view in another one). Is there a ...
1
vote
1answer
141 views

How do I debug an Idle Query?

I have a batch query that I'm running daily on my database. However, it seems to get stuck in idle state, and I'm having a lot of difficulty debugging what's going on. The query is an aggregation on ...
2
votes
0answers
17 views

pg_dump with ssl certificates

I would like to run the pg_dump command from a remote host off my database server that only allows fully verified certificate based connections. How should I go about doing this? The man pages for 9.1 ...
1
vote
1answer
15 views

Postgres or MapReduce to Match Reciprocal Needle Pairs from a Stack of Needles?

I have a Postgres (latest version) table with about a million lines (a_lat, a_lon, b_lat, b_lon) that each have about a dozen criteria. These lines go together in reciprocal pairs (A - B, B - A), ...
2
votes
1answer
579 views

Why Django session table grows on PostgreSQL

I use PostgreSQL 9.1.6 via PgBouncer 1.4.2 in my Django 1.4.8 project. django_session table grows all the time. VACUUM does not help. VACUUM FULL runs for hours. Only TRUNCATE helps usually. The ...
1
vote
2answers
28 views

Proper usage of Group by in Inner Join

EDIT I updated my question with a SQL Fiddle Sample http://sqlfiddle.com/#!15/8d88b/1 I'm currently making a report from a database records but I don't know how my query should look like, first of ...
4
votes
1answer
1k views

What is actually happening when postgresql discovers the trigger file and promotes it self from slave to master?

I have a streaming replication setup between two postgres servers (master: server A, slave: server B). I'm wondering what's actually happening under the hood when I touch the trigger file and the ...
5
votes
3answers
206 views

Postgres is performing sequential scan instead of index scan

I have a table with about 10 million rows in it and an index on a date field. When I try and extract the unique values of the indexed field Postgres runs a sequential scan even though the result set ...
27
votes
6answers
39k views

How do I efficiently get “the most recent corresponding row”?

I have a query pattern that must be very common, but I don't know how to write an efficient query for it. I want to look up the rows of a table that correspond to "the most recent date not after" the ...
0
votes
0answers
6 views

Updating the details of an ACCESS-2003 database on completion of a PostgreSQL UPDATE trigger

I am currently stuck and looking for some further guidance. I have a database in access, which on UPDATE (trigger function) will take the data inputted into the PostgreSQL table in QGIS, and update ...
2
votes
0answers
17 views

PostgreSQL/PostGIS: Query seems inordinately time-consuming

I'm doing what I would consider a pretty straightforward query on two tables that aren't huge (~626k records in one; ~47k records in the other). Both tables have GIST indices on their spatial ...
-1
votes
0answers
23 views

How would I make this query run faster in postgres [closed]

Query: with customer_total_return as (select wr_returning_customer_sk as ctr_customer_sk ,ca_state as ctr_state, sum(wr_return_amt) as ctr_total_return from web_returns ...
0
votes
1answer
222 views

Postgres DB - Commit xlog files after server crash

After a server crash, it would seem that all transactions going back a number of months are not showing up in the live Postgres DB. Investigation has found all xlog files that does contain the data ...
1
vote
0answers
28 views

IF EXIST before many insertions in PostgreSQL? [migrated]

I would like to INSERT only if table exists. Pseudocode IF EXISTS TABLE file_headers( INSERT INTO file_headers (measurement_id, file_header_index_start, file_header_index_end) VALUES (1, 1, 100); ...
1
vote
1answer
43 views

How to store time series data

I have what I believe is a time series data set (please correct me if I'm wrong) that has a bunch of associated values. An example would be modeling a car and tracking its various attributes during a ...
4
votes
2answers
66 views

Grouping by sequential relationships with Postgres

This is quite similar to this question about continuous ranges, and this question about grouping by sequential numbers, but differs in that the sequences are not numeric. Given the following ...
2
votes
2answers
67 views

How to cast to int array in Postgresql?

I would like to cast from ARGV[] which is text to int array in PostgreSQL where I marked the pseudocode by TODO in the code. Code in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc ...
3
votes
1answer
54 views

Finding optimal indices, different query competing indices

My schema: create TABLE "logs" ( "id" serial not null default nextval('logs_id_seq'::regclass), "request" varchar(1024), "token" varchar(512) default NULL, "was_batch_request" bool ...
0
votes
2answers
31 views

PostgreSQL, merge join results

I have 2 tables with common columns and would like to know if it's possible to request this 2 tables by merging the results. Table "job" : parent_id, target_id, start_date, end_date, jobposition_id ...
3
votes
2answers
120 views

Filter and append data to row

My query and my data set (copied from sqlfiddle): CREATE TABLE one ( id int4 primary key, p_id int4, k_id int4 ); CREATE TABLE two( id int4 primary key, p_id int4, k_id int4, ...
0
votes
1answer
36 views

PostgreSQL window functions very slow on big table

I have a PostgreSQL table constructed as device_id | point | dt_edit ----------+-----------+---------- 100 | geometry | timestamp 101 | geometry | timestamp 100 | geometry | ...
1
vote
0answers
35 views

Having problems trying to back up a Postgres database from AWS RDS

I am trying to backup a Postgres database from RDS and I am getting this error message. pg_dump: [archiver (db)] query failed: SSL error: unexpected message pg_dump: [archiver (db)] query ...
2
votes
1answer
112 views

PostGIS: Cutting Holes in Self Geometry

I have a table of geometries in PostGIS, some of which overlap. When I display the geometries, this has the unfortunate consequence of blending the two stacked geometries together and producing an ...
0
votes
1answer
261 views

WAL archiving from a standby backup strategy Postgresql 9.3

I am trying to figure out if wal archiving from a standby is possible and a viable option for a backup strategy on Postgresql 9.3. The idea is to be able to take regular base backups and wal ...
3
votes
3answers
195 views

Do I want an index if all I care to query is field not null

In SQL, do all the common considerations about indexes apply if everything I'll ever want to query (in the where condition) is when a field is not null? Is there a special configuration of the index ...
0
votes
0answers
24 views

Merging data from two slightly different datasets

A bit of backstory: we used to have a single Postgresql db that we had to duplicate when we opened a new office. So at that moment there were two indipendent dbs with perfectly identical data. Over ...
2
votes
1answer
21 views

How to Choose Between VALUES and SELECT for INSERT?

This answer raised the question for me how to choose between VALUES and SELECT in such a function . Using PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit: ...
1
vote
0answers
26 views

How to use variables in a PL/pgSQL function with dynamic queries?

...and when must I use EXECUTE ... quote_ident(variable)? Taking an input to the function as text and storing part as a variable: wtype := split_part(message, ' ', 4); realval := ...
10
votes
2answers
4k views

Is there a means to set the owner of all objects in a PostgreSQL database at the same time?

http://stackoverflow.com/questions/1348126/modify-owner-on-all-tables-simultaneously-in-postgresql describes some nifty ways to change table and other objects to a specific user, and it works ...
0
votes
0answers
14 views

Postgres where clause compare datetime [migrated]

Hello guys I am new to postgres, I have a table where column is of datatype datetime Which contains records multiple records for a day I want to select all rows corresponding to day How do I ...
0
votes
2answers
27 views

Get min and max row from table

From a table with the columns: id(pk), type_id, book_id, title_id, page_id, lines(int4), created_at(date) How can I select the row with min(lines) and max(lines) for (type_id, book_id, title_id, ...
1
vote
1answer
29 views

Challenges with Key with Gaps?

I had a mistake with a trigger which resulted in errors in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit. Each time during the error, the primary key ...