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)

1
vote
1answer
552 views

Time series and gap filling [closed]

I have to choose a database server for storing big time series data. Currently I use PostgreSQL, but I've found that Vertica supports automatic gap filling for missing data and that's really nice. ...
1
vote
1answer
22 views

Search results mismatch after postgresql update

Today we had to update the PostgreSQL version on our database servers. Before update the version was 9.3.4 and the new one is 9.3.6. Despite the small difference in the database version and the ...
0
votes
1answer
123 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: ...
2
votes
1answer
244 views

Should one always VACUUM ANALYZE before REINDEXing in PostgreSQL 8.4?

Early in the morning every day a pgAgent job refreshes the contents of table A from table B on my PostgreSQL 8.4 database. Table A contains around 140k records across 91 columns and has two indexes - ...
3
votes
2answers
108 views

How to get exception context for a manually raised exception in PL/pgSQL?

In Postgres, we get the "stack trace" of exceptions using this code: EXCEPTION WHEN others THEN GET STACKED DIAGNOSTICS v_error_stack = PG_EXCEPTION_CONTEXT; This works fine for "natural" ...
10
votes
1answer
1k views

Generate an exception with a Context

When PostgreSQL throws an exception, there is a line "CONTEXT" like: ERROR: INSERT has more target COLUMNS than expressions LINE 3: ... ...
1
vote
1answer
58 views

SQL query to sum a column prior to date and show all entries after that date

I have a database function that sum a column prior to a date and after that date show all entries one by one. that function return the data ordered by a common field and apply the sum by date every ...
7
votes
2answers
1k views

Migrate heroku database to Amazon RDS with minimum downtime

I have a heroku postgres database and want to migrate it to Amazon RDS to save cost. What's a way to do so with minimum downtime? Usually this involves replicating database in real time and then ...
0
votes
0answers
22 views

Postgres count of delimited items in bytea encoded string

How would you get a count for each of $ delimited Individuals in each of the group_names? (Example: SomeGroup has 1, This Is Another Group has 8) thedb=# select group_name,encode(group_data,'escape') ...
4
votes
1answer
128 views

PostgreSQL operator uses index but underlying function does not

I'm attempting to use JSONB with JDBC, which means that I have to avoid any of the operators which use the '?' character (as the PostgreSQL JDBC driver has no escaping for this character). Taking a ...
2
votes
2answers
74 views

Check if contiguous date interval exists

I have a table in PostgreSQL which describes some events that have a start date and an end date: CREATE TABLE my_table ( event_id serial NOT NULL, start_date timestamp without time zone NOT ...
1
vote
1answer
21 views

How to create INOUT assignment cast on Amazon RDS PostgreSQL? If not possible, then why?

I'm migrating existing application to Amazon RDS PostgreSQL. The application has cross-database support and for compatibility reasons it includes the following SQL: CREATE CAST (varchar AS numeric) ...
1
vote
1answer
1k views

PgPool 2 with HA Proxy

So, I'm looking to add high availability to a postgresql server. I've looked into some tutorials on the web but, haven't been able to find exactly what I'm looking for. E.g.: PostgreSQL ...
4
votes
2answers
3k views

PostgreSQL query with max and min date plus associated id per row

I have the following table: CREATE TABLE trans ( id SERIAL PRIMARY KEY, trans_date date, trans_time time ); I want to have the following view CREATE OR REPLACE VIEW daily_trans ...
1
vote
1answer
24 views

purchasing item by client - how to ensure that user has enough funds?

I have to tables: users (id serial, coins integer not null default 0) purchases (id serial, price integer not null, user_id) When new row is inserted into purchases table I want to subtract amount ...
1
vote
2answers
49 views

Slow Select over View

My simple SELECT statement on a view in Postgres 9.3.1 is pretty slow. The receipts table has 20 million entries and the other one has around 17k. But I still think it should be faster than ~16 ...
0
votes
0answers
56 views

Slow query over 10mil rows with group by on indexed column

I've got a slow query which I want to optimize. SELECT pand.bouwjaar AS construction_date, count(*) FROM pand AS pand WHERE pand.begindatumtijdvakgeldigheid <= 'now'::text::timestamp ...
-1
votes
1answer
21 views

PsQl data dump import [closed]

Psql databasename < file.db is the recommended method to import to a database. This doesn't work why?
4
votes
1answer
85 views

Restoring plain-text pg_dump with psql and --disable-triggers

I had to perform some tests with a short script to update some "legacy" data in one of my tables. Cautious as I am, using an untested script, I decided to backup the relevant table before doing so. ...
0
votes
1answer
239 views

How to install Slony for 64-bit PostgreSQL 9.3 on Windows?

I have installed 64-bit version of PostgreSQL 9.3.5 from EnterpriseDB installer. Now I want to install Slony-I through Application Stack Builder, but it offers only 32-bit version of Slony, ...
1
vote
1answer
562 views

Tree structure hierarchical query sorting

I have the following structure of table. CREATE TABLE table1 ( serial_num integer NOT NULL, parent_num integer, parent_key text, key_ary_num integer[], level integer, rank integer, ...
0
votes
1answer
221 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 ...
1
vote
1answer
35 views

PostgreSQL on RDS: How to bulk import data with FK constraints?

Is there an efficient way to bulk import data into a PostgreSQL 9.4 database on RDS that makes extensive use of foreign key constraints? The standard method of using pg_restore fails because RDS does ...
0
votes
0answers
32 views

Online Recovery for pgPool-II “cannot find xlog functions”

I am trying to set up Online Recovery for pgPool-II [Replication,Load Balancing, Connection Pooling] modes enabled. I followed the sample provided on the pgPool-II website for the PITR recovery ...
1
vote
1answer
81 views

postgresql bdr 0.8.x - adding another downstream server only partially works

I have the following postgresql 9.4 bdr setup: upstream server with db called "bdrdemo" running on 10.1.1.1 downstream server(1) with db called bdrdemo running on 10.2.2.2 (replicates with ...
1
vote
1answer
55 views

Best way to drop / recreate a table that has dependent views?

Problem PostgreSQL does not have logical column reordering, at least not in 9.2 If columns need to be added in the middle of a large table and order is important, the best way is to drop and ...
2
votes
1answer
21 views

pg_restore a view

What's the best way to restore a view? pg_dump -h hostname -d databasename -Fc > db.dump DROP VIEW viewname; pg_restore -h hostname -d databasename -t viewname db.dump This does not error, but ...
2
votes
2answers
56 views

Sampling in PostgreSQL

I am looking for possible ways of random sampling in PostgreSQL. I found a couple of methods to do that with different advantages and disadvantages. The naive way to do that is: select * from ...
0
votes
1answer
433 views

Sync offline PostgreSQL server

I am new to PostgreSQL. I am looking for a solution wherein 2 mirror images of a database sit on a local and remote server. All the work is done on the local server and at periodically synced with the ...
3
votes
2answers
455 views

Query planner thinks doing a sequential scan on a very large table is faster than using an index?

I've got a query that selects the most favorited photo for each of the last 12 editions for a particular domain (this is just a segment of a larger query, but the problem seems to stem from here): ...
0
votes
1answer
40 views

PostgreSQL pg_upgrade --link mode performance issue after upgrade

Trying to do PostgreSQL Upgrade from 9.0 to 9.3 latest version. Using pg_uprade --link mode, since the downtime is very minimal compare to other options like pg_dumpall and pg_upgrade with normal ...
1
vote
1answer
326 views

How to make pg_dump skip extension?

This is on 9.3 but I can remember simillar things happening since 7.x. So I create database and install plpgsql extension into it. Later on I create a pg_dump and before restoring it into databse I ...
0
votes
1answer
530 views

pgpool-II configuring multiple master databases

I could not find a solution for this anywhere, not even in the pgpool manuals. How do I configure multiple - master postgresql databases with pgpool-II? Additional requirement is that each master ...
0
votes
2answers
80 views

Query that was working on MySQL is not working on PostgreSQL

I had the following query (with bin_number being a string) which was working on MySQL 5.5: Postgres - was working on MySQL and doesn't work on PostgreSQL SELECT "menu_items".* FROM "menu_items" ...
0
votes
1answer
49 views

SQL update multiple columns matching a pattern

I have a table in a postgresql database in which I have multiple columns matching a pattern /\w*(?:_open|_close)/g. Those are timefields. I would like to substract 1 hour from all the rows on those ...
0
votes
1answer
46 views

What will be best (for performance) transaction isolation level?

There, are two types of queries that are executed in my app: DELETE FROM tbl1 WHERE user_id = 1; INSERT INTO tbl1 VALUES (...), (...), ...; and UPDATE tbl2 WHERE id = 1 SET ...; DELETE FROM tbl3 ...
-1
votes
1answer
26 views

Prioritised table query

I'm looking for a way to query two tables at once but ... I want the query to first run against Table A and then if Table A returns no matching records to query Table B. There many well be instances ...
1
vote
1answer
37 views

Reference column alias in same SELECT list

I'm converting an old MS-Access-based system to PostgreSQL. In Access, fields that were made up in SELECTs could be used as parts of equations for later fields, like this: SELECT samples.id, ...
1
vote
0answers
52 views

Is there a way to improve this query further?

I've got a query that over time I've been improving. Originally it simply selected * from the records table, and the data was formatted on the webapp side. But that was slow and I wanted to make ...
2
votes
1answer
29 views

Postgres: check disk space taken by materialized view?

I know how to check the size of indexes and tables in Postgres (I'm using version 9.4): SELECT relname AS objectname, relkind AS objecttype, reltuples AS "#entries", ...
1
vote
1answer
324 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 ...
5
votes
2answers
200 views

Feasible to have thousands of users in Postgres?

We are creating SAAS where we will at most have 50.000 customers. We are considering creating a user in the Postgres database for each customer. We will map each user that logs into our service to a ...
4
votes
4answers
6k views

How can I insert if key not exist with PostgreSQL?

I have a table: CREATE TABLE mytable (id SERIAL, name VARCHAR(10) PRIMARY KEY) Now I want to add names to this table, but only if they not exist in the table already, and in both cases return the ...
0
votes
1answer
29 views

How can I drop and create postgresql views in dependency order?

I found the following http://stackoverflow.com/a/9712051/61249 which is good but still too much manual labor for me. I need to recreate the views in the right order how would I go about doing that? ...
2
votes
2answers
1k views

pgpool replication not allowing md5 authentication

I have installed both pgpool and postgresql via apt. In pgpool.conf, I have made the changes necessary to use replication mode, as well as my two postgresql databases (currently all running on the ...
0
votes
1answer
22 views

Can I setup database triggers to run on a defined secondary set of servers only? [closed]

This might sound a bit off, but here's what I have been thinking for a while now: Use Case You want to build an activity log for each user action on your application using database (postgreSQL) ...
0
votes
1answer
20 views

Migrating from 32 bit using pg_basebackup

Has anyone migrated a 32 bit postgres 9.2 database to a 64 bit postgres 9.2? I'm trying to use pg_basebackup but I'm not sure whether this is the right approach.
1
vote
0answers
7 views

Error on slave2 after promoting slave1 to master in cascaded streaming replication

I've a setup like MASTER -> SLAVE1 -> SLAVE2. MASTER and SLAVE2 are running on different ports on single server. SLAVE1 in on another server. For testing purpose I've powered off my first server ...
3
votes
1answer
37 views

Migration From Oracle to PostgreSQL Encoding Errors

I'm working on migrating data from an oracle database to a postgresql database. I running into a problem where I export the data from oracle, using sql developer, and importing it into postgresql. ...
0
votes
1answer
296 views

Connect to PostgreSQL via a ssh tunnel using Cygwin

I would like to connect to a Postgres server installed on a remote server using an ssh tunnel. I have Windows so I use cygwin for SSH. I do the following on cygwin: # Create the tunnel ssh -f ...