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
22 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
7 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
0answers
5 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. .-----. .--------. | | ...
0
votes
1answer
11 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
22 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 ...
1
vote
0answers
26 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
28 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
19 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
47 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 ...
0
votes
0answers
18 views

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

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 ...
0
votes
1answer
25 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 ...
2
votes
1answer
31 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 ...
0
votes
0answers
17 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
20 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): ...
2
votes
1answer
64 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 ...
-5
votes
0answers
22 views

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

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 ...
3
votes
1answer
36 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
14 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
1answer
34 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 ...
0
votes
0answers
21 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
38 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 ...
1
vote
2answers
38 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
2answers
134 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 ...
2
votes
2answers
27 views

Get all (non repeated) distribuidores with their paises given a producto_solicitud

I have this schema: And as title says I need to get all records from fabricante_distribuidor by given producto_solicitud.id value. I'm doing in this way: SELECT ...
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 ...
0
votes
1answer
22 views

Initialize, Start Server and Create Database in Single Script

I am writing a provision script for a dev environment with the following: sudo su - postgres <<EOF cd ~/data pg_ctl -D . initdb pg_ctl -D . -l run.log start createdb mydb pg_ctl -D . stop EOF ...
1
vote
1answer
30 views

What are the side-effects of disabling nested loops in postgres?

A query of multiple joins spends a lot of time on nested loops. I disabled the nested loops and the query ran much faster. However, I have been told that nested loops might give me wrong results by ...
3
votes
2answers
27 views

How can I determine if a table exists in the current search_path with PLPGSQL?

I'm writing a setup script for an application that's an addon for another application, so I want to check if the tables for the other application exist. If not, I want to give the user a useful ...
5
votes
1answer
67 views

Sort by number of related rows in referencing table

Let there be two tables: Users id [pk] | name --------+--------- 1 | Alice 2 | Bob 3 | Charlie 4 | Dan Emails id | user_id | email ----+---------+------- 1 | 1 ...
0
votes
0answers
13 views

How to do a pgdump with strings encoded?

When I perform a dump with pg_dump tool, with a "flat" sql format, pg_dump create a regular sql file but the restoration with pg_restore fails because the strings are not escaped. For example, single ...
1
vote
1answer
41 views

Migrate SQL Server binary datadump to postgresql

I just got a very big dataset (+/-55GB) in a binary SQL Server format. Is there an easy way to import this file into a Postgresql Server? I found some converter-programs, but they 'only' convert ...
0
votes
0answers
11 views

Backing up AWS database to an on premise server

I have 2 questions - 1) Are there tools, AWS provided or otherwise, which can continuously backup a cloud-based database (Postgresql RDS) to an on-premise server ? 2) I want to do this, so that I ...
2
votes
1answer
45 views

What is the etymology of the hstore datatype in postgres?

Where did the datatype get its name? Hash store?
1
vote
2answers
42 views

Add function parameter to SQL query WHERE clause

I have a web application in java and it uses a query. I don't want to write the query into Java, so I made a function: CREATE OR REPLACE FUNCTION testFunc(inputs text) RETURNS TABLE(...) AS $$ ...
0
votes
0answers
36 views

Translate psql meta-command to SQL

How can I translate a psql meta-command (like \dt or \ds) to the equivalent SQL query? I found some of the translations here. But it would be nice to have a command to do it automatically.
0
votes
0answers
33 views

Getting wrong results from fulltext search in PostgreSQL

This is the query which I am running. The keyword "exhausted" is present only in one of the rows but I am getting all 3 rows. How to avoid the rows where the keyword is not present? DDL: CREATE ...
0
votes
1answer
14 views

create insertion rule on view returning id

I have a table private.products and a view public.products representing that table. And I want to create entries in private.products while inserting into view, it all works right, except that I also ...
0
votes
1answer
23 views

Can I use `on delete set null` on a compound foreign key which contains a primary key column?

The docs say: Restricting and cascading deletes are the two most common options. RESTRICT prevents deletion of a referenced row. NO ACTION means that if any referencing rows still exist when the ...
-1
votes
1answer
26 views

How to update values from bytea to varchar in Greenplum?

I need to convert hexa to values and need to update the values in a varchar field, I have tried: select pg_catalog.decode(column,'hex') The above code converted hexa to values but when I try to ...
2
votes
1answer
35 views

Dropped an index and recreated it in PostgreSQL 9.2, not used anymore

I have a table with 15M rows, with two indexes; one of them is a very simple BTREE on a string column. While trying to optimize some queries, I created a new partial index on the same column. Using ...
0
votes
1answer
18 views

Optimizing for Modulo Queries

I have a use case where I want to be able to query tens of thousands of records for the modulo of a certain field value with another number, i.e. queries like SELECT * FROM EVENTS WHERE MOD(OFFSET)=5 ...
0
votes
0answers
10 views

hstore type does not exist with hstore installed postgresql

I recently had to copy over a databases structure from one database to another with the command pg_dump -c -S database_name > pg_dump_date.sql I then ran the dump file on the new database with ...
0
votes
1answer
31 views

Slow Postgres updates if OTHER columns are indexed?

Certain updates take far too long on large Postgres tables. Given these conditions: only one column is being updated, and it is non-indexed the column already has data in it for every row due to a ...
0
votes
1answer
11 views

Adding hstore entries to an uninitialized column

I got bitten by this 'feature' recently. If your hstore column is uninitialized and you start adding entries to it, they are all silently swallowed without error. Is this expected? create table ...
0
votes
0answers
23 views

How to make pg_dump skip extension?

This is on 9.3 but I can remember simillar things happening since 7.x. So I create database and install plpgsql extension into it. Later on I create a pg_dump and before restoring it into databse I ...
3
votes
1answer
45 views

Counting repeated occurences for a group of fields

I am trying to count the continuous repeated value for a field in order to obtain the maximum repeated occurrences for each person in the table. eg: Time person Result 08:12:36 ...
0
votes
0answers
26 views

How to Insert records from remote database to local database using dblink in postgresql 9.3

I have a scenario like i have a two DB, one resides in remote server and other resides local. Now i have to fetch the records from remote DB when new records are inserted on tables (in remote DB) and ...
2
votes
0answers
50 views

Why is the CTE much worse than inline subqueries

Im trying to better understand how the query planner work in postgresql. I have this query: select id from users where id <> 2 and gender = (select gender from users where id = 2) ...
0
votes
2answers
34 views

Alter table statement waiting (for hours, on dev machine), but no locks shown

I have been trying to issue a simple: ALTER TABLE tablename ADD COLUMN id_col character varying (30) type statement on a Postgres 9.1.13 build on Debian. The application is still in private beta, so ...
0
votes
0answers
37 views

Querying really slow Postgres

I have multiple tables on Postgres wit GIS and when I query SELECT * FROM rxmsg JOIN Vehicles ON rxmsg.vid=Vehicles.vid JOIN stops on ST_DWithin(rxmsg.point, stops.point,1000) JOIN drivers ON ...