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
0answers
23 views

postgresql bdr - 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
26 views

Random Sampling with replacement 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 ...
-1
votes
0answers
15 views

Warm Standby Using Point-In-Time Recovery (PITR) - Was it sucessful?

I've set up a warm standby for a postgres 8.3 production server. I've followed the procedure here: http://www.postgresql.org/docs/8.3/static/warm-standby.html Installed the standby with the same ...
-3
votes
0answers
27 views

Please help on “PostgreSQL installation checklist” [on hold]

This task is regarding developing a shell script on checking-up the PostgreSQL installation. I lack expertise in doing shell scripting. So, can you please help me in developing this task please. ...
0
votes
1answer
38 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
44 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
vote
1answer
29 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
63 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
0answers
25 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
22 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
17 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", ...
-2
votes
1answer
22 views

How to implement a distributed database system [on hold]

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 ...
0
votes
1answer
23 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? ...
0
votes
1answer
18 views

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

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) ...
3
votes
0answers
84 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
5 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 ...
0
votes
1answer
17 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
1answer
20 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 ...
3
votes
1answer
28 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. ...
1
vote
1answer
18 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
45 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
42 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 ...
-1
votes
0answers
12 views

How to Insert geom line in geom geometry column? [on hold]

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 ...
0
votes
0answers
18 views

DB space vs query complexity tradeoff

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 ...
0
votes
0answers
12 views

Recovering from a partial recover/migration

I migrated my database with a simple pg_dump -p <port> -U <user> <database> | psql -p <other port> -U <user> <other database> and was kindly interrupted by a ...
2
votes
1answer
31 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
24 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
0answers
38 views

migrating from Postgres to MonetDB [closed]

I have postgres database (24 schemas, about 50 tables for each schema, partitioned tables) and I want to migrate to MonetDB database, but I dont know best way to do this. How can I restore data? I ...
0
votes
1answer
30 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". ...
0
votes
0answers
10 views

How to sync postgres roles between multiple servers

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 ...
0
votes
0answers
27 views

Use a function(select or insert) in a streaming rep with hot standby setup [on hold]

Setup Pgpool Master Slave mode On / stream Load Balance Mode On Health Check On And two nodes(primary and standby) I have this function (the function is in the question), how can I use it in ...
0
votes
0answers
5 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
24 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 ...
0
votes
0answers
15 views

Is there a workaround for the lack of foreign keys constraint to a partitioned table in PostgreSQL?

I have a big table A (~30 million lines) at the core of my model. Another table B has a foreign key referencing A. I would like to partition A using the well-known inheritance hack. Is there a ...
1
vote
1answer
29 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
24 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
26 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
27 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 ...
-2
votes
0answers
19 views

I want query postgresql group by month and type [closed]

I want query postgresql group by month and type like this, please help me.
0
votes
0answers
15 views

how to install a database cluster in postgresql

I am trying to install POSTGRESQL, I have completed the installation and at the end point i got an error stating POSTGRESQL CLUSTER INITIALIZATION is not succeed and this if i install outside the ...
1
vote
1answer
113 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
40 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
28 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 ...
2
votes
1answer
52 views

Why do we need partitions in PostgresSQL? (or any other RDBMS)

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 ...
0
votes
0answers
27 views

List all columns and rows of PostgreSQL table with a view limit

I am currently using select * from items; to show my table during development however I am finding it a bit messy now as some the strings I am adding to the table are 1,000+ chars long. Is there a ...
0
votes
1answer
23 views

Postgres tablespace on mounted usb

I've got postgres 9.1 running on raspbian installed with apt-get. I've got NTFS usb HDD mounted in /mnt/usb. What I'd like to achieve is to create a tablespace pointing to /mnt/usb/postgres so I ...
0
votes
1answer
29 views

PostgreSQL Error Codes [closed]

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 ...
2
votes
1answer
49 views

Problems restoring backup from base backup + WAL with WAL-E

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 = ...
0
votes
1answer
25 views

Display only table names in psql

How can I print only table names from a specified database in psql? That is, if I have tables called "table1" and "table2" then the output will be: table1 table2 and only that. I've been trying ...
2
votes
1answer
31 views

Guid vs. integer ID for unique fields

This is really a generic question. Suppose I have a replication pattern of a single write DB and multiple read DB replication slaves - I do not have to have GUID's as my unique IDs fields. Is that a ...