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
401 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
59 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
3k 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
236 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
58 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
383 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
209 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
82 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
388 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 ...
1
vote
1answer
230 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
155 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
116 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 ...
1
vote
0answers
164 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
2answers
82 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 ...
1
vote
0answers
133 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
56 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 ...
2
votes
2answers
39 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 ...
0
votes
1answer
76 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
votes
1answer
182 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 ...
5
votes
1answer
91 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
175 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 ...
0
votes
1answer
1k views

Error: /var/lib/postgresql/9.3/main is not accessible or does not exist

I have a PostgreSQL db that was running on my server. I tried to do a db_dump and got a weird error message. I eventually thought I would just restart PostgreSQL. However its now stopped and won't ...
1
vote
1answer
62 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 ...
4
votes
2answers
938 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 ...
2
votes
1answer
57 views
0
votes
0answers
44 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
133 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
60 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 ...
1
vote
1answer
415 views

Slow max, min query in Postgresql View

I have a view and it's a left join between two tables (table_a has hundreds of millions of rows, table_b has ~2 millions). The query SELECT MIN("id") AS "min_id",MAX("id") AS "max_id" FROM "the_view" ...
0
votes
1answer
350 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
90 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 ...
2
votes
1answer
60 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
25 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 ...
3
votes
1answer
166 views

How can I achieve clustering of rows without the exclusive lock and logging overhead of the `cluster` command?

The CLUSTER command on a big table can take a long time, and blocks both reads and writes to the table while it runs. I don't need the data in my table to be strictly sorted in index order, I just ...
3
votes
1answer
86 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 ...
1
vote
1answer
4k views

Cannot enter psql command “FATAL: the database system is starting up” [closed]

I cannot login into postgresql server using psql command (to create / update / delete table) because some sort of error like below. the command : [root@Server-Database pg_log]# su postgres ...
0
votes
2answers
207 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 ...
1
vote
2answers
2k views

How to CREATE TABLE … LIKE without the NOT NULL Constraints?

I'm trying recreate a table's structure inside a function by using some dynamic SQL. EXECUTE 'CREATE TABLE ' || my_table_name || '_bk' || ' (like ' || _my_table_name || ')'; That will be ...
9
votes
1answer
3k views

How to determine if there are [idle connections with] uncommitted transactions in PostgreSQL?

According to a comment on this question I asked about idle connections in PostgreSQL 9.2, some uncommitted transactions (possibly related to some of those idle connections) might cause some ...
1
vote
0answers
77 views

Postgres drop type XX000 “cache lookup failed for type”

I'm currently working on a PostgreSQL 9.2.x Database with a lots of Clients, and tables and functions. We deploy code constantly and sometimes it is necessary to even drop a type or a function due ...
1
vote
0answers
67 views

Postgresql service run failure

Trying to setup replication through the wal shipping using Postgresql 9.3 x64. Used this instruction from youtube. The problem is - the slave postgresql service won't run. This is what in logs ...
0
votes
0answers
137 views

How to copy remote database to local cluster using pgAdmin over SSH tunneling with Postgres 9.x?

I have successfully gotten pgAdmin on my development machine to do ssh tunneling over the internet to my production server. I can see the remote database and browse its tables and such. ➥ How to suck ...
1
vote
0answers
91 views

PostgreSQL Idle Session in Transactions with RELEASE_EXEC_SVP queries and lock

I am having a problem with having idle sessions in transactions. In pgAdmin Server Status, it is showing "RELEASE_EXEC_SVP_XXXXXX" (XXXXXX data are varied) as its query and it's locks also contain a ...
9
votes
3answers
7k views

Why did Postgres UPDATE take 39 hours?

I have a Postgres table with ~2.1 million rows. I ran the below update on it: WITH stops AS ( SELECT id, rank() OVER (ORDER BY offense_timestamp, defendant_dl, ...
0
votes
2answers
1k views

Running a UPDATE on 3 million records

I have two actors in this transaction. One actor is a table "update_assets" is a set of records with data that is up-to-date and new. The second actor is a table "application_assets" which is a table ...
0
votes
1answer
75 views

Moving Local PostgreSQL 8.4.20 Database to Amazon EC2 or RDS

I'm running PostgresSQL 8.4.20 on a local server and plan to migrate the data to either Amazon EC2 or RDS. I have working knowledge of databases and need advice from experts. I've been researching ...
0
votes
0answers
38 views

Getting PAM-compatible password hashes from pgcrypto

I want to keep user records in Postgres database and faced one issue which block me. It is password encryption. For example, this is correcetly encrypted password 12345 via default Ubuntu crypt ...
1
vote
1answer
193 views

How to remove object from json array?

My table: CREATE TABLE items ( id BIGINT PRIMARY KEY NOT NULL, name VARCHAR, images json ); images format: [ { "id": "owner", "full": "<url>", "thumb": ...
3
votes
1answer
163 views

How do you truncate a `date` (not `timestamptz`) to month?

Postgres has date_trunc which operates on timestamp or interval, and: Values of type date and time are cast automatically to timestamp or interval, respectively. In other words we can use ...
1
vote
2answers
486 views

How to calculate the cumulative difference between rows in Postgres?

I am in need of calculating the difference from the cumulative values for the previous day in Postgres based on session_id. Example as mentioned below: Date session_id upload_usage ...