Tagged Questions

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.

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. ...

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 ...

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 ...

I have been tasked to find out how to implement a distributed database system using the various database management software available like: MS Access 2013, MySQL and PostgreSQL. How would I be able ...

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?
...

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 ...

I am a beginners and I have the coordinates of a lot of points in a table. In pgadmin I want to make a line that passes on these points. I already have my Coordinate point im my table. I want to make ...

I have a dilemma - Right now I have a query that I do constantly - joining 5 tables on different columns, calculate stuff per row, distinct on that, and do that with offset and limit (pagination). I ...

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 ...

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 ...

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".
...

I am looking for a solution in which I can replicate the details of a role (most notably the password) between various postgres servers. (All running the same version).
The idea behind this is that ...

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 ...

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 ...

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 ...

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 ...

So, I am trying to teach myself partitions in PostgreSQL. I understand that a database can become slow when a table hits millions+ of lines and indexes don't fit into memory anymore.
One thing that ...

I'm configuring a monitoring tool to monitor a PostgreSQL 9.3 database error log and notify me whenever there are errors. Now I'm in the phase of understanding the difference between error codes. For ...

I'm trying to do nightly restores of the main database on a separate node. I want to do weekly full restores and nightly restores by consuming the latest WAL files.
On the master I have
wal_level = ...