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
473 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, ...
-5
votes
1answer
45 views

how to Write a SQL Statement to display EmployeeID from Table1 and EmployeeName from Table2 a [on hold]

how to Write a SQL Statement to display Claim Number, EmployeeID from Table1 and EmployeeName from Table2 above. Write a SQL Statement to display all the Information from Table1 and Table2 for ...
0
votes
0answers
7 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 ...
1
vote
2answers
997 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 ...
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
1answer
25 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 ( ...
0
votes
0answers
20 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 ...
0
votes
2answers
48 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
25 views

Which replication technique to use?

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
19 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
92 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
6 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
20 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
21 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 ...
1
vote
1answer
32 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
34 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
1answer
165 views

Problems using “Excel PostgreSQL Import, Export & Convert Software”

I want to export data from Postgresql, and I tried many ways then find the tutorial of Excel PostgreSQL Import, Export & Convert Software on Youtube. I installed the software, but I could not ...
0
votes
0answers
12 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
18 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
35 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
11 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
0answers
40 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
30 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
22 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 ...
2
votes
1answer
56 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 ...
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 ...
0
votes
0answers
23 views

Get columns from NEW when execute update on postgresql [closed]

I need know if determide fields are into result of update execution, e.g. IF NEW.niveope is null THEN _new_nivope := 0; ELSE _new_nivope := NEW.nivope; END IF; When I execute: EXPLAIN ...
2
votes
1answer
37 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 ...
1
vote
1answer
338 views

Upgrading using pg_upgrade on windows fails

When upgrading from postgres 9.2 to 9.3 by following the upgrade procedure found in PostgreSQL 9.3.4 documentation about pg_upgrade, the following command fails: RUNAS /USER:postgres "CMD.EXE" ...
2
votes
1answer
65 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
27 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
2answers
292 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): ...
3
votes
1answer
160 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 ...
0
votes
1answer
410 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
1answer
35 views

Postgresql csv to db translate foreign keys

I'm trying to import data from a csv to a postgresql database. the db table looks like this: CREATE TABLE texts( text_id serial PRIMARY KEY, topic_id integer REFERENCES topics, format_id integer ...
2
votes
1answer
87 views

SELECT query with DISTINCT on a table-structure for graphs is very slow

I have 2 tables where nodes contains about 60m rows and edges about 500m rows (both growing fast). These two tables represent a directed graph. CREATE TABLE edges ( node_from bigint NOT NULL, ...
3
votes
1answer
37 views

Possible reasons for irregularly high query execution time?

The above is a graph showing the average response times per second (including error bars) for an experiment where I simulate 30 clients which are all invoking the same query multiple times. I ran ...
0
votes
0answers
21 views

Are A or D series VMs better for deploying PostgreSQL on Azure?

In Azure there are VMs that have local storage either as spinning disk (A-series) or SSD (D-series). Considering this space is wiped on reboot... Is there a benefit to choosing the SSD option for ...
1
vote
1answer
22 views

How do I use pg_dump and pg_restore to move a large database to another machine?

I'm a bit unclear on how to use the pg_dump/pg_restore duo to move a large-ish database. Here is the command I use to dump the database (the host and username are taken from environmental variables): ...
1
vote
2answers
41 views

Group by range of years

I've got a big table (~9M rows) and want to group the rows on a field containing the year. So far that's pretty easy: // greatly simplified: SELECT count(*), year FROM dataset GROUP BY year ORDER BY ...
-5
votes
0answers
23 views

We are trying to set up a database to be able to read and write messages on a website [closed]

We are trying to set up a database to be able to read and write messages on a website but cannot figure out how to make a function for a button to take the information from the webpage text box and ...
2
votes
0answers
59 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 ...
1
vote
1answer
117 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 - ...
0
votes
0answers
16 views

PostgreSQL: PANIC: heap2_redo: unknown op code 32

I was doing migrations while the server was still accessing the slave DB. I have a master / slave setup. After I restarted the server, I realized I couldn't connect to the SLAVE anymore. I went into ...
0
votes
0answers
22 views

ETL - Moving data from Postgres/Oracle to a TripleStore (RDF)

I'm looking for pointers on how to handle an ETL process that I have to implement for my organization. I know how to query SQL database but i'm not SQL DB specialist. I work more in the sphere of the ...
0
votes
0answers
39 views

Unique index on columns which are allowed to be NULL

I'm having a little problem right now, we use this table in our database: CREATE TABLE offer ( offer_id serial PRIMARY KEY , product_id int NOT NULL REFERENCES product , price_old ...
5
votes
2answers
136 views

Managing CTE execution order under uncertainty

I am writing a large, multi-step CTE for performance reasons. In one query, data must be moved from one table to another, but the quantity of rows moved is uncertain and could be zero. In a ...