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

learn more… | top users | synonyms (1)

4
votes
1answer
2k views

set session - custom variable to store user id

I want to store user id in custom session variable and use it (read) in trigger procedures to authorize user actions. I found something like this: set session "myapp.user" = '12345'; ... SELECT ...
4
votes
3answers
370 views

Scalable query for running counts of events within x previous days

I already posted this question on stackoverflow but I thought that I might get a better answer here. I have a table storing millions of events occurring to users: ...
1
vote
1answer
470 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 ...
4
votes
2answers
942 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
36 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 ...
2
votes
1answer
754 views

How to temporarily disable foreign keys in Amazon RDS PostgreSQL?

I'm migrating existing test environment to Amazon RDS PostgreSQL. The test framework has a feature of reloading data in certain tables to an earlier state. For this it disables foreign keys, deletes ...
4
votes
2answers
253 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
2answers
630 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
104 views

Upgraded Postgres; how to access now-missing databases?

My local connection on my Mac (10.9.5) wasn't working to my Postgres (9.3) database (and therefore I couldn't actually export the data), so I tried upgrading it to 9.4. Luckily I can now connect to ...
-2
votes
1answer
89 views

PsQl data dump import [closed]

Psql databasename < file.db is the recommended method to import to a database. This doesn't work why?
1
vote
2answers
64 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
136 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
vote
1answer
265 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 ...
4
votes
2answers
3k 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" ...
2
votes
1answer
257 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 ...
1
vote
1answer
1k 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 ...
4
votes
1answer
2k 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. ...
2
votes
1answer
273 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 ...
2
votes
2answers
892 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
449 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
106 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 ...
3
votes
2answers
178 views

Have: Postgres database files. Need: Functioning database

I am running Ubuntu 14.04 with a postgres/postgis setup and in a weak moment removed PostgreSQL 9.1 (seemingly a legacy install from 12.04 from before I upgraded to 14.04), having erroneously ...
2
votes
1answer
1k 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, ...
0
votes
2answers
114 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" ...
2
votes
1answer
650 views

VACUUM FULL fails with 'ERROR: Check free disk space'

I am using Postgres 9.4. I have a database that \l+ tells me is 164 GB. When I try to run VACUUM FULL on the database, I get this error (after many hours): ERROR: could not extend file ...
-2
votes
1answer
39 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 ...
3
votes
1answer
509 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", ...
0
votes
1answer
236 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? ...
1
vote
1answer
28 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
136 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) ...
4
votes
1answer
529 views

Can't get Dell PE T420 (Perc H710) perform better than a MacMini with PostgreSQL

It's months that I'm trying to solve a performance issue with PostgreSQL. SYSTEM CONFIGURATION Our deployment machine is a Dell PowerEdge T420 with a Perc H710 RAID controller configured in this ...
1
vote
0answers
38 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 ...
1
vote
1answer
45 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.
0
votes
2answers
220 views

PostgreSQL pg_upgrade --link mode performance issue after upgrade

Trying to do PostgreSQL upgrade from 9.0 to 9.3. I'm using pg_upgrade with the --link mode since the downtime is very minimal compared to other options like pg_dumpall and pg_upgrade without using ...
3
votes
1answer
175 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. ...
2
votes
2answers
2k views

Creating a database under a different owner

I'm trying to set up a role in Postgres which can a) Create a new Role b) Create a new Database c) Make that new Role the owner of the database d) Have no other rights (as far as is possible!) I have ...
2
votes
0answers
96 views

Unique Index much bigger than the table itself

Pg 9.3 on debian 7. I have a very huge table in rows, about 178 million (will turn 250 million in a week and then grow 500 thousand a day), but not that huge in size, wich is 20GB, it's a small ...
1
vote
0answers
62 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
47 views

How to keep updates (diffs) of some entity in the database

What is the best way to keep updates (diffs) of the some entity in the database? At StackOverflow we can edit questions and answers. And then we can look at any revision of the question or answer we ...
1
vote
1answer
196 views

Postgres - Insert performance with indexes

We have a table with about 20 columns in a Postgres 9.2.10 DB. For better performance on some SELECT queries, we plan to add an index on one column with data type timestamp. As the index also can slow ...
0
votes
1answer
1k views

Trigger function check if all NEW.* fields exist in another table

In PostgreSQL I need to code a trigger function that checks if all the fields in NEW.* exist in another table that has the exact same name as the table that fired the trigger but appended with "_hv". ...
1
vote
0answers
82 views

Making ordinal synonyms for Postgres full text search

I have a Postgres 9.3 database where users are mostly searching for locations by a combination of name and/or address (or fragments of). An example of an address would be "123 8th st". I've been ...
0
votes
1answer
105 views

Postgres - count of GROUP BY

I am trying to determine the total number of contributors in an image database who have contributed at least five images. The following query will give me counts of images by contributor, but that's ...
2
votes
1answer
153 views

Return type of JOINing stored procedure - PostgreSQL

Is it possible to express return type of stored procedure that returns joined tables using table type of one of joined tables? My stored procedure looks like this: CREATE or REPLACE FUNCTION ...
2
votes
1answer
104 views

Select into specific position with array_agg on postgresql

Is there a way to set value in specific position inside array based on information from other columns (PostgreSQL-9.3 and later)? For example I would like to select an item and its stock information ...
1
vote
0answers
29 views

Restricting user to a single db in postgresql [duplicate]

I've a PostgreSQL Server with 5 db and 2 users: postgres and forge, both with Superuser attribute. I want to create a third user that can only see and use one and just one of the db and by using it I ...
0
votes
0answers
218 views

PostgreSQL trigger copy record to another table with different columns order

I have a trigger that before every update on hundreds of tables it inserts into a table with the same name and the _hv suffix a copy of the record being updated to maintain a kind of historical ...
1
vote
1answer
527 views

How to return values from “upsert” query in case when UPDATE has been performed?

WITH upsert AS (UPDATE tbl SET a = 2 WHERE a = 1 RETURNING tbl.*) INSERT INTO tbl (a) SELECT 1 WHERE NOT EXISTS( SELECT * FROM upsert ) RETURNING * This "upsert" statement works however I would like ...
0
votes
1answer
143 views

Maximum Partitioning PostgreSQL

I have 400,000 records I need to partition. The records will increase 100,000 per year and those new records might need to have 1000 new partitions added. Here are some of my concerns: How many ...
2
votes
1answer
35 views

Search for authors whose records are separated by at least X days

I am using Postgres and have a table which contains metadata about several million images - many of which have the same author. I am looking for contributors who add content regularly - not people ...