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)

2
votes
0answers
3 views

Find rows where integer sequence contains a given subsequence

Problem Note: I refer to the mathematical sequences, not the sequences mechanism of PostgreSQL. I have a table representing sequences of integers. The definition are: CREATE TABLE sequences ( id ...
7
votes
3answers
132 views

Is it reasonable to mark all columns but one as primary key?

I have a table representing movies. The fields are: id (PK), title, genre, runtime, released_in, tags, origin, downloads. My database cannot be polluted by duplicated rows, so I want to enforce ...
1
vote
0answers
5 views

Single Redshift table vs several mysql/ pgsql (on RDS) tables for small (200 tables * 20 columns * 1 million) monthly aggregation data

We're providing SaaS Data Analytics Dashboard for around 200 clients. We're using Amazon Redshift for storing data in in around 5 tables. We're getting around 30 million hits per day. Currently our ...
11
votes
2answers
104 views

Simultaneous calls to the same function: how are deadlocks occurring?

My function new_customer is called several times per second (but only once per session) by a web application. The very first thing it does is lock the customer table (to do an 'insert if not ...
0
votes
0answers
19 views

Modifying a query for the UPSERT command [on hold]

Here is the current query select pt.name, slr.name from stock_route_product srp inner join product_template pt on srp.product_id = pt.id inner join stock_location_route slr on srp.route_id = slr.id ...
3
votes
0answers
21 views

Should I use a separate postgres schema for application logins? [on hold]

I'm building an application. I have users, roles, and permissions; users can have one role, and a role can have multiple permissions. As such, I'll have a Users table, a Roles table, a Permissions ...
2
votes
0answers
30 views

Improve postgreql insert performance when compared to Oracle. Low memory utilization by Postgresql threads

I am trying to improve the performance of Postgresql (version 9.2.3) inserts for a simple table with 1 bigint, 1 varchar, 1 float and 2 time stamps. A simple replication of my JDBC program is ...
1
vote
1answer
86 views

SELECT in trigger function in two tables

My table and trigger in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit: CREATE TABLE measurements ( measurement_id SERIAL PRIMARY KEY NOT NULL, ...
2
votes
0answers
16 views

Row with latest timestamp

How to get the row with the latest value in a TIMESTAMPZ column? Is an index needed? Does an index change the strategy? Would behavior vary by database (I'm using Postgres 9.4)? I am using this kind ...
0
votes
1answer
25 views

postgres: Converting '' into NULLS

I'm trying to run this query to change my latitude coordinates, which is saved as text into a double precision ALTER TABLE mapdata ALTER COLUMN lat TYPE double precision USING (lat::double ...
1
vote
1answer
28 views

Reg: Default Privileges on PostgreSQL Schemas

I have a situation where many users can create database objects across all the available schema. There is a generic user pg_dump_user used for by all the developers to dump the whole database to ...
1
vote
1answer
102 views

Not able to install PostgreSQL 9.4 on Ubuntu

When I try to install PostgreSQL 9.4.+ on Ubuntu, it is installing version 9.3.9. I have used the following command: sudo apt-get install postgresql postgresql-contrib Then I have tried with sudo ...
2
votes
2answers
142 views

Tuning Postgres for Single connection use? Or is postgres wrong tool?

Any rules of thumb for work_mem, maintenance_work_mem, shared_buffer, etc. for a database that DOESN'T anticipate concurrent connections and that is doing lots of aggregate functions? I'm a social ...
3
votes
1answer
27 views

What is the correct way to map relationships between structurally similar comments tables and commentable entities?

I'm working on the usual comments table in postgres that has a one-to-many relationship with a commentable entity. In other words, one comment belongs to a post, and a post can have many comments. ...
0
votes
0answers
18 views

How to create password and table in Postgres-xl?

I try to create password for user postgres (DB postgres). And try to create table i always get error. I am using Ubuntu 12.04. Postgres-xl v9.2. postgres=# sudo -u postgres psql postgres postgres-# ...
0
votes
1answer
105 views

Postgresql Chained Slaves and Slave Promotion

So I have a setup like Master -> Slave1 -> Slave2. Slave1 replicates from Master and Slave2 replicates from Slave1. If I promote Slave1 to master is it possible for Slave2 to continue replicating from ...
1
vote
1answer
31 views

Access to database denied :ELEPHANTSQL on Bluemix - “No pg_hba.conf entry for host localhost, user ADMIN, database SYSTEM”

While trying to connect to my ElephantSQL database on Bluemix using pgadmin, it throws the following error. Access to database denied The server doesn't grant access to the database: the server ...
0
votes
1answer
46 views

Why seq-scan can be much faster than index-scan and index-only-scan in this simple query?

I am using PostgreSQL 9.4.4. I have a query like this: SELECT COUNT(*) FROM A,B WHERE A.a = B.b a and b are the Primary Keys of tables A and B, so there are B-indexes on a & b By default, ...
1
vote
1answer
14 views

Tables design for conversation

I want to create simple conversation module on my site. So I need a list of users who had conversation with current user. I need to show amount of new messages in each conversation. I think about next ...
5
votes
1answer
34 views

PostgreSQL and table inheritance

I'm not sure if this is the right place for this, so please feel free to tell me if it is off-topic or there is a better place to post. I am working on a fantasy sports based project, and the number ...
1
vote
0answers
110 views

Generally unsolvable problem? How to ORDER BY aggregated columns GROUPED BY many possible columns very fast?

Is there general solution or some best practises or at least some vague hint, how to solve following problem (general, but concretised in following example)? I think this must have been solved many ...
0
votes
0answers
33 views

cannot find -lpq after upgrading PostgreSQL 8 to 9 on CentOS 5

I'm trying to upgrade a (proprietary, closed-source, legacy) database-driven application from using PostgreSQL 8.1 to PostgreSQL 9.4. I have it installed on a virtual machine running CentOS 5.9. ...
0
votes
1answer
292 views

pg_basebackup giving “could not receive data from client: Connection reset by peer”

I'm trying to get data to a new slave box from my new master (previously the slave). I've tried the steps already and it worked previously. Now when I run the following command: pg_basebackup -h ...
2
votes
1answer
351 views

REST webservice as a Foreign Data Wrapper in postgresql

I know it's possible to embed data from a foreign resource in a postgresql database as a foreign table and have seen examples including redis, another postgres database, etc but I can't really find ...
1
vote
1answer
23 views

Unexpected results with soundex() function when combined st_distance()

select soundex(A.name), A.gid, A.name, soundex(B.name), B.gid, B.name, st_distance(B.the_geom,A.the_geom) * 111.325 as dist from (select name, ...
0
votes
1answer
57 views

Database schema for entities with two possible owner/parent types?

I'm using PostgreSQL with Sequelize as my ORM. I have one type, User. The second type is Group, which can have any number of users associated with it through a GroupMemberships table. Users can also ...
0
votes
1answer
791 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 ...
0
votes
1answer
14 views

Full text search on two tsvector columns

I'm trying to do a full text search across two columns of two separate tables in PostgreSQL 9.4.2. The tables and columns are: article.article_title keyword.keyword Where there is a many-to-many ...
0
votes
0answers
9 views

SELinux is preventing pg_ctl from read access on the file postgresql.conf

I am trying to setup a master-slave PostgreSQL 9.4 database on Fedora 22, with the slave database's PGDATA being different from the default /var/lib/pgsql/data/ directory. I wrote this to ...
1
vote
1answer
44 views

Full text search on multiple joined tables

I'm trying to set up a full text search in PostgreSQL but coming across the problem that the more tables I join to the query the fewer the results I get back. The tables I am trying to join are: ...
5
votes
2answers
1k views

Postgres DDL and DML best practices and coding standards [on hold]

Is there a best practice or coding/design conventions for Postgres both DDL and DML/SQL that you could share and recommend. I am looking for something similar to what Google has for programming. ...
-1
votes
0answers
18 views

postgres could not connect to server: No such file or directory in ubuntu 15.04 [on hold]

when I run postgres server I am getting the following error psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain ...
0
votes
0answers
19 views

psql and coordinator not working on postgres-xl?

apple@ubuntu:/usr/local/pgsql/bin$ ./postgres --coordinator -D /usr/local/pgsql/data LOG: could not bind IPv4 socket: Address already in use HINT: Is another postmaster already running on port 9999? ...
1
vote
0answers
48 views

PostgreSQL 9.4 : server closed the connection unexpectedly

I have a website in production with low traffic (10-20 rpm). The application servers runs on the same box as the database, connection to the DB is done via localhost For a few months everything has ...
1
vote
1answer
28 views

Why my BEFORE UPDATE trigger takes values from the NEW record? [on hold]

I'm updating data with QGis on a Postgres table. The operation consists of splitting a polygon in two parts, which means that QGis updates the original polygon (to become one of the 2 new ones) and ...
1
vote
1answer
51 views

Index on column with data type citext not used

In PostgreSQL 9.4, with following schema: CREATE TABLE people ( id INTEGER PRIMARY KEY, name TEXT, junk CHAR(1000) ); INSERT INTO people(id, name) SELECT generate_series(1,100000), ...
0
votes
1answer
394 views

How to install Slony for 64-bit PostgreSQL 9.3 on Windows?

I have installed 64-bit version of PostgreSQL 9.3.5 from EnterpriseDB installer. Now I want to install Slony-I through Application Stack Builder, but it offers only 32-bit version of Slony, ...
2
votes
1answer
264 views

postgresql 9.4 + BDR Fails [on hold]

I'm trying to get postgresql 9.4 and BDR working. Following this tutorial: https://wiki.postgresql.org/wiki/BDR_Quick_Start Actually, my sysadmin provided me with a package to test with. He's taken ...
4
votes
1answer
911 views

Could not open file “pg_clog/0000”: No such file or directory

My local PostgreSql 9.2 database won't start anymore this morning. I am on Windows 7. Yesterday, I performed a Windows Update before going to bed. I have checked the log and found: 2014-05-28 ...
0
votes
0answers
33 views

insert or update on table “ aaa” violates foreign key constraint

I am trying to migrate from mysql to postgres, while restoring into postgres its showing the constraint error. Table1: server : column user_login has an entry for admin user. (It has an entry for ...
0
votes
3answers
12k views

PostgreSQL - insert/update violates foreign key constraints

I am a new one in postgreSQL. I have 3 tables, one table references the other 2 table's primary keys. But I couldn't insert data into the Table3. See the code below : DROP TABLE Table1 CASCADE; ...
0
votes
1answer
16 views

error recovering pg_dump compressed file

I produced this backup: pg_dump -v -Fc -Z5 -f thebackup.dmp.gz thedb I can't open the dump with gzip, 7zip... I could recover the same files in the past without using -Fc parameter. Is it possible ...
3
votes
3answers
3k views

How to get all roles that a user is a member of (including inherited roles)?

Let's say I have two Postgresql database groups, "authors" and "editors", and two users, "maxwell" and "ernest". create role authors; create role editors; create user maxwell; create user ernest; ...
4
votes
3answers
5k views

GROUP BY one column, while sorting by another in PostgreSQL

How can I GROUP BY one column, while sorting only by another. I'm trying to do the following: SELECT dbId,retreivalTime FROM FileItems WHERE sourceSite='something' GROUP BY seriesName ...
2
votes
0answers
18 views

PostgreSQL Streaming Replication Tools - Windows

Is there any clusterware available for PostgreSQL clusters running on Windows OS? I'm specifically interested in automatic failover and STONITH functionality. I've done a fairly extensive Google ...
0
votes
2answers
15 views

PostgreSQL return word after matching first character

I would like to return the words beginning with ':' from 'select :barns, :fowl, :cows from farm' using regexp_matches. So far I have: select regexp_matches('select :barns, :fowl, :cows from farm', ...
2
votes
1answer
34 views

Using an UPDATE trigger to populate Eastings and Northings in PostgreSQL

I am wondering if it is at all possible to create trigger that populates the X and Y of a created point into 2 separate fields called 'easting' and 'northing'? It is an update trigger on a table ...
0
votes
2answers
841 views

Delete returns nothing in Psycopg2?

I have a fairly simple delete query in a PostgreSQL database that I'm interfacing with via psycopg2. Take the following minimal example: def testDelete(): db = DbInterface() cur = ...
2
votes
1answer
15 views

postgresql on windows won't install with data and executables on different disks

I am trying to install postgresql 9.4.4 on a windows 2008 rc2 machine that has 2 HDs. The first HD is, in theory, used only for executables/code/etc while the second one, being faster, should be used ...
4
votes
3answers
47 views

Do not reject entire statement when deleting rows in batch

I have a table who's purpose is to store the application's various images. It is defined this way : CREATE TABLE images ( id lo NOT NULL, name character varying(1024) NOT NULL, type character ...