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)

0
votes
2answers
111 views

Schema for lists of relationship differences

Say there is a data model that allows for relationships to be defined either explicitly or as a list of records added or removed relative to some existing item: Item <–> *Properties and Item ...
3
votes
4answers
917 views

Backup a sample of a PostgreSQL database

Is it possible to back up a PostgreSQL database with only a fraction of the data (1000 Rows from each table)? I am able to back up that database using pg_dump. In some case I need db with minimum ...
3
votes
1answer
52 views

Rollback on \include error in psql

I'm trying to execute an sql script file in a single transation. The file contains includes for some other scripts which in my example create some tables. It looks like this: \include ...
4
votes
2answers
3k views

Which Postgresql Replication Solution to Use? (Asynchronous Multimaster / Bucardo?)

First, I'm not a DBA, so pardon me if any of this question seems "off." I've written a peer-to-peer multiplayer game (the client) which connects to one of multiple servers for match making. ...
2
votes
0answers
29 views

Can CTE sub-statements be prevented from executing at all?

Through this strategy, time consumed by queries for my application have dropped to extreme lows; however, some queries consume more milliseconds than necessary. The reason why is that upon ...
0
votes
0answers
28 views

Does someone have a cannonical example of setting up PostgreSQL with PLV8?

I'm hoping that someone can either post or point me to a guide for setting up PostgreSQL (9.4) and PLV8. I'm not an expert at PostgreSQL by any means and have been reading about and wanting to play ...
0
votes
0answers
33 views

PostgreSQL replace primary key and converting non-serial to serial type

I'm basically building my own version of phpPgAdmin (if I get good enough to make it good enough I'll post it on a repository hosting service) and currently I'm revising my primary key tool to replace ...
1
vote
0answers
34 views

How to use the value from record like as varchar?

I'm trying update a old table with result of selec for another tables but get a error using this sintaxe (e.g.): DO $$ DECLARE _roles refcursor; _onerole varchar; _record RECORD; ...
1
vote
2answers
67 views

Refresh materalized view incrementally in PostgreSQL

Is it possible to refresh a materialized view incrementally in PostgreSQL i.e. only for the data that is new or has changed? Consider this table & materialized view: CREATE TABLE graph ( ...
8
votes
3answers
940 views

Email address unique or primary key?

I am a newbie in databases. I read around and found out that it's probably not a great idea to use email address as primary key because string comparisons are slower which effects performance in ...
2
votes
1answer
451 views

PostgreSQL Change owner of all tables under a specific schema

I am trying to change the owner of all tables under the same schema in one command line. i.e: alter table schema_name.* owner to newowner Is there a way to accomplish that?
0
votes
0answers
28 views

How to set a variable from select in PostgreSQL?

Via psql I'm trying to do something like \set cr_count = (select count(*) from cr_table); but no luck. Spent a hour googling around, but neither select into, nor := helped me.
0
votes
1answer
613 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 ...
3
votes
1answer
99 views

Are CTEs multithreaded?

Here and here seem to suggest that they are, but I've also been told (source unlocatable) that a multi-row single-statement insert will only use one thread for all writing, that to multithread it, the ...
1
vote
1answer
87 views

How to get back the PostgreSQL database to a specific poin in time?

I am running a batch script(10:40) in postgresql database which will insert values in differnt tables on postgresql till (11:00).Later i came to know that the script was executed mistakenly.Now how ...
0
votes
1answer
88 views

mongodb on-premise: will it work?

We are planning to package MongoDB-PostgreSQL hybrid solution in our application that will be deployed on customer site on-premise. It will be packaged as a software virtual appliance. Our reason for ...
0
votes
1answer
588 views

Configuring Postgres 9.2 streaming replication with Redhat Clustering Suite for High Availability

I'm in the process of configuring postgresql 9.2 streaming replication with Redhat Clustering Suite for high availability. I have 1 master and 1 hot standby configured to replicate the data from ...
2
votes
1answer
90 views

Prepared statement overhead

I am using libpqxx to communicate with a PostgreSQL 9.3 server. For multiple table inserts, if they are split into individual prepared statements, they cost approximately 2ms each. If they are all ...
2
votes
1answer
81 views

Monitoring the Postgres query parser

As I understand it, when you send a query to Postgres like: SELECT id FROM table1 WHERE name = $1; Postgres will create a query plan. The plan will be cached for the same query in the same ...
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 ...
0
votes
1answer
584 views

pgpool2 setup on master-slave replication using postgreql 9.1

I have already setup 2 ubuntu server 12.04 LTS on my local machine using virtual box and is using the master-slave replication (postgresql 9.1). 192.168.0.109 = master 192.168.0.110 = slave my ...
1
vote
1answer
29 views

postgresql: accept subdomain wildcards?

I want to accept all db connections from servers with domain mydomain.com so that all subdomains like tiger.mydomain.com, venus.mydomain.com will have access to the postgresql server. I've read that ...
0
votes
1answer
313 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 ...
1
vote
1answer
327 views

How can I get data from broken PostgreSQL server?

I have installed CentOS on a virtual machine and installed the latest version of PostgreSQL server 9.3. I have added PostGIS extension and some data. For some reason, the PostgreSQL service now fails ...
1
vote
0answers
20 views

pgAdmin-III - Option to load a favorite automatically when opening query window

pgAdmin-III - Query window option Does pgAdmin-III has any option to load a default favorite file automatically when opening new query window? This would be much useful to have commit/rollback ...
0
votes
1answer
84 views

PostgreSQL replication with fsync disabled?

I need to run PostgreSQL in-memory (for performance reasons), so I intend to disable fsync, which means that no writes will be sent to the WAL. However, as part of my scheme to meet another ...
2
votes
2answers
1k views

pg_upgrade unrecognized configuration parameter “unix_socket_directory”

I'm trying to upgrade Postgresql from 9.2 to 9.3 in Fedora 18 using this command as the postgres user $ pg_upgrade -b /bin -B /usr/pgsql-9.3/bin -d /var/lib/pgsql/data -D /var/lib/pgsql/9.3/data/ -j ...
0
votes
0answers
36 views

PostgreSQL indexing table on multiple columns

I have a table that holds half hour metering values in a compact form on a daily basis. The table has a structure as described below: day: timestamp without time zone, // describes a day ...
1
vote
2answers
70 views

How to compare an Oracle database with a Postgresql database?

We are currently migrating an Oracle database 10gR2 to a Postgresql database 9.3. Thanks to Hibernate, all tables can be recreated seamlessly. However, the Oracle database is huge. We are going to ...
2
votes
2answers
2k views

To select from all tables inside the schema in PostgreSQL

Is it possible to select from all tables inside the schema? I got all the table names from select table_name from information_schema.tables but I am unable to use it to do my query.
0
votes
0answers
43 views

Which replication technique to use? [closed]

I would like use Postgresql 9.3, i'm looking for a replication solution permitting this use cases. there's 2 servers both can write data, so they must be synchronized with some frequency if the DSL ...
0
votes
1answer
31 views

Postgres Table Ownership

I'm new to PostgreSQL and I'm trying to change the table owner for a bunch of tables. I was able to change the ownership by logging in a the postgres user and executing alter table owner to user1, but ...
4
votes
2answers
99 views

Multi-row insert vs multiple single row inserts

In my app I do multi-row inserts when I can just because it reduces the number of round trips between the db and the app. However, I was curious, is there any other advantages? For example, if ...
1
vote
0answers
21 views

export data to xml by the use of query to xml then importing it using xpath

i have use QUERY_TO_XML function in order to export data to xml , after that i want to export this data. this data might contain reserved chars in the xml tags like "' & ..." ex electrical ' ...
0
votes
0answers
44 views

Concurrent SELECT and UPDATE in multiprocessing python

I have to randomly match about 20 million rows in two tables without using the same row twice using PostgreSQL 9.3.5. Both tables have the following structure CREATE TABLE de_sim_points_end ( -- ...
0
votes
0answers
36 views

What minimum RAM should the server have for a Django application with Postgresql server?

I have a Django application. The database is Postgresql with 8GB size. We are also using Nginx with gunicorn. Will a 4GB server be enough for good performance ( like on Digital Ocean) ? I was under ...
2
votes
1answer
42 views

What does the Postgres <-> operator do?

I was reading through the Postgres documentation on materialized views and in one example they use the following: SELECT word FROM words ORDER BY word <-> 'caterpiler' LIMIT 10; I tried to ...
1
vote
2answers
57 views

How to create a temporary table using VALUES in PostgreSQL

I am learning PostgreSQL and trying to figure out how to create a temporary table or a WITH declaration that can be used in place of regular table, for debugging purposes. I looked at the ...
0
votes
0answers
19 views

Possible to get log file with only statements sent to Postgres from my app (and not from psql prompt)?

I'm using an ORM in my app and would like to see all statements sent to Postgres so that I can figure out exactly what the ORM is doing. In the Postgres config file I added log_statement = 'all' to ...
0
votes
1answer
24 views

How can I aggregate multiple results from another table using something like BOOL_AND?

I'm using PostgreSQL as my database. CREATE TABLE users ( id SERIAL NOT NULL, name TEXT NOT NULL, PRIMARY KEY (id), UNIQUE (name) ); CREATE TABLE groups ( id ...
0
votes
2answers
60 views

insert inserted id to another table

Here's the scenario: create table a ( id serial primary key, val text ); create table b ( id serial primary key, a_id integer references a(id) ); create rule a_inserted as on insert to a do ...
0
votes
0answers
27 views

What should be the ideal network configuration for a Postgresql Streanming Replication and PgPool HA setup

I have the following configuration using Pgpool, Postgresql with streaming replication. Pgpool is used for load balancing and automatic failover. .-----. .--------. | | ...
2
votes
1answer
2k views

Sharding Postgres Database

I have a Postgres database that has grown to the size where it is no longer feasible to store everything on a single database node. There is a Customer table in my schema where each row represents a ...
1
vote
1answer
50 views

Performance of single vs multiple column unique constraint

I'm using PostgreSQL 9.3. I want to understand if I have an option to make a constraint unique across the entire table vs. unique across a subset of the table (i.e. by using 2 columns in the unique ...
1
vote
1answer
29 views

Table-based custom type does not verify check constraints

Is there any way for a table-based custom type to obey the table check constraints, other than creating my own type based on the table? Assume the following table that serves as a source for the ...
3
votes
1answer
2k views

How to remove known elements from a JSON[] array in PostgreSQL?

I'm facing an issue regarding using the JSON data type in PostgreSQL. I try to achieve storing a Java model denormalized in the DB. The model features lists of complex objects. Thus, I decided to ...
2
votes
1answer
62 views

Get products of category plus all child categories till leaf nodes

We have a hierarchy which looks this: Now the problem is that most products are only connected with 1 category, which is most of the time the lowest level. I am trying to get ALL products below a ...
2
votes
1answer
85 views

Recursive CTE to find unique slug

I have an article table where I want the slug to be unique. CREATE TABLE article ( title char(50) NOT NULL, slug char(50) NOT NULL ); When the user enters a title e.g. News on Apple, I want ...
0
votes
1answer
29 views

How can I create a bool column for a view based on the result of a statement?

I'm using PostgreSQL as my database. CREATE TABLE groups ( id SERIAL NOT NULL, name TEXT NOT NULL, PRIMARY KEY (id), UNIQUE (name) ); CREATE TABLE users ( id ...
3
votes
1answer
186 views

Add NOT NULL constraint to large table without table scan

Trying to add a NOT NULL constraint to a table with 1 billion rows. I cannot afford a table lock for more than a couple of seconds. Is there a way to prevent a full table scan during the alter table ...