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
47 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
18 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
288 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
156 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
400 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
30 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
7 views

Get products of category plus all child categories till leave 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

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
28 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
16 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
15 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
34 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 ...
-4
votes
0answers
20 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 ...
2
votes
0answers
58 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
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
0answers
20 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
37 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
128 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 ...
5
votes
1answer
491 views

What fillfactor for caching table?

I have heavily updated / accessed table where I store serialized java objects. They are in the table for 2-3 hours (also are being updated during that period) and then removed. Size of table is around ...
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 ...
0
votes
1answer
1k views

postgres service suddenly not working after the computer crashed while working

I've been working with postgres on a linux server for the last couple of months. Last week, half of our servers crashed and my one with them. I was in the middle of uploading data to the postgres ...
0
votes
1answer
21 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
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 ...
5
votes
1answer
65 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
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
0answers
24 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
226 views

Taking hot backup of slave node in postgres (master-slave config with repmgr)

I have a postgresql setup with master-slave configuration using repmgr. I'm struggling with taking a consistent snapshot backup (pg_start_backup) of the slave node, this is due the fact that the slave ...
4
votes
2answers
924 views

Postgres log files not rotating

I have a postgres system with a hot standby server. They use the same config file. I've setup the log rotation to use log_rotation_size and log_rotation_age so that the master server is rotated every ...
0
votes
1answer
301 views

Sync offline PostgreSQL server

I am new to PostgreSQL. I am looking for a solution wherein 2 mirror images of a database sit on a local and remote server. All the work is done on the local server and at periodically synced with the ...
7
votes
2answers
836 views

Migrate heroku database to Amazon RDS with minimum downtime

I have a heroku postgres database and want to migrate it to Amazon RDS to save cost. What's a way to do so with minimum downtime? Usually this involves replicating database in real time and then ...
1
vote
1answer
130 views

PostgreSQL's Query Hierarchical data poor performance

Badly need some technical assistance on this query performance issue I'm working on. Summary of the data structure is: 1 project has many homeprofiles, 1 homeprofile has many tasks, 1 task has many ...
0
votes
1answer
98 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
28 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
26 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
45 views
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
40 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 ...
1
vote
2answers
41 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
1answer
839 views

What is best strategy for backup on streaming replication(hot standby) environment

I have questions. What is best strategy for backup on streaming replication(hot standby) environment. I’d like to do backup on slave server and restore(PITR). First, I set up streaming ...
0
votes
0answers
8 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 ...
0
votes
0answers
32 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
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
vote
1answer
56 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
30 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 ...
2
votes
1answer
18 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 ...
3
votes
1answer
111 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 ...