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
22 views

How to determine if pg_dumpall is still running

Environment: Postgres 8.4 Debian Squeeze LTS Recently we switched from a backup solution around hot_backups to using pg_dumpall. This solution used a pid file as long as the hot_backup was ...
0
votes
1answer
35 views

Postgres 9.1.16 Hot Standby

I am using PostgreSQL 9.1.16 (and I cannot upgrade) with Hot Standby. We have a few batches that perform some heavy writes, resulting in a lot of WAL bein produced in a short period of time. The ...
2
votes
1answer
54 views

Data synchronization across multiple databases

I have a Django application which uses PostgreSQL as a database. The application stores hierarchical data. It is like a warehouse management system with multiple branches and hierarchy. The current ...
1
vote
1answer
45 views

How to optimize the sort in Postgres query

I've been trying to optimize the sort in the following query. I ran EXPLAIN ANALYZE and the majority of the time is during the sort when it arranges the output by distance. I've tried converting the ...
1
vote
3answers
63 views

Intentionally corrupting an index in PostgreSQL

I need to know if there is any method for intentionally corrupting an index in PostgreSQL. I have been searching for it for a long time but never seen anyone mentioning such an act.
0
votes
0answers
23 views

DEFERRABLE INITIALLY DEFERRED in postgres after migration

I have used dbconvertool to migrate database from mysql to postgres. It is migrated successfully. But while comparing the table on mysql and postgres, i am seeing "DEFERRABLE INITIALLY DEFERRED" on ...
0
votes
0answers
6 views

PostGIS Intersects Query takes lot of time to get the result

I am developing a webmapping application in which I used PosgreSQL with PostGIS plugin as my DB. I have this two tables namely; evidensapp_polystructures and evidensapp_seniangcbr. Both tables ...
9
votes
2answers
113 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 ...
1
vote
0answers
31 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 ...
3
votes
1answer
43 views

Why does this fast postgres query show up in the slow query log?

On investigating an entry in our Postgres log, I noticed a large discrepancy between the reported duration and the speed of the query. 2015-07-28 17:27:26 UTC [dms_segment_3] LOG: duration: 2924.763 ...
12
votes
2answers
173 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 ...
2
votes
1answer
56 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)? My app records data ...
4
votes
0answers
29 views

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

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
39 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 ...
0
votes
1answer
30 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 ...
8
votes
3answers
188 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
1answer
139 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 ...
3
votes
1answer
28 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. ...
2
votes
2answers
106 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, ...
6
votes
1answer
52 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
2answers
59 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 ...
1
vote
1answer
21 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 ...
1
vote
1answer
40 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 ...
0
votes
0answers
52 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
22 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 ...
0
votes
1answer
20 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 ...
1
vote
1answer
28 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, ...
-1
votes
0answers
39 views

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

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 ...
2
votes
0answers
88 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
29 views

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

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
0answers
129 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 ...
1
vote
1answer
91 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 ...
2
votes
0answers
23 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 ...
1
vote
1answer
54 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: ...
0
votes
1answer
21 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 ...
0
votes
2answers
20 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', ...
0
votes
0answers
19 views

I want to how to setup point intime recovery in postgres [closed]

What are the steps for doing point in time recovery frist of all i worked on the database and many changes morning to evening ,but i dont want to afternoon session what am i doing ,by using point ...
0
votes
1answer
38 views

Setting shared_buffers over 320 MB prevents Postgres from restarting

I'm running Postgres 8.1 on a CentOS 5 system with 4 GB of RAM. Whenever I set shared_buffers above 320 MB, it won't run. I've checked SHMMAX and it's at 4294967295 which is the max allowed for a ...
1
vote
0answers
17 views

PostgreSQL: native library learn the filesystem path that was used to load it?

From the _PG_init of a dynamic library that postgres has loaded (either because of a function declaration referring to it, or just LOAD pathname; in SQL), is there any call I can make, not ...
3
votes
1answer
16 views

PostgreSQL: restrict a GUC variable to only be set in proconfig?

If a C extension defines a custom GUC variable, is there any combination of GUC "context","source","flags", etc., that can be used to make the system only allow it to be set from the SET (i.e. ...
4
votes
3answers
52 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 ...
2
votes
1answer
22 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 ...
1
vote
1answer
50 views

DB design for employee working hours constraints [closed]

I have a simple employee schedule database (I use PostgreSQL): employees: - id - name - email shifts: - start - stop - employee_id Now there is need to apply constraint for working hours. ...
0
votes
1answer
22 views

PostgreSQL visibility of records changes from other transaction inside the current one

I have a table "tst" which has a "status" column using which we process records in that table. Once the records are processed we set the status to 'Y'; #\d tst Table "core.tst" ...
0
votes
1answer
45 views

What is the best way to take backup for Remote DB Server

I am building a software, which will supports multiple database like MySQL, Oracle and PostgreSQL. These databases are installed on remote machines. i wanted to use java technology in a non-database ...
-2
votes
1answer
31 views

PostgreSQL - what do tms and mt mean in a SELECT query? [closed]

I'm trying to see if I can optimize the following query which was written by my predecessor: SELECT tms.*, 0 as kyori , (power( (139.745069 - ...
3
votes
1answer
46 views

COUNT number of column values with INNER JOIN [closed]

I'm new to PostgreSQL and I am making a Report. This is my SQL Fiddle: http://sqlfiddle.com/#!15/6ce465/5 So basically I have two tables, responses and questions. The structure is responses ...
2
votes
1answer
40 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 ...
5
votes
1answer
175 views

Rolling sum / count / average over date interval

In a database of transactions spanning 1,000s of entities over 18 months, I would like to run a query to group every possible 30-day period by entity_id with a SUM of their transaction amounts and ...
1
vote
2answers
302 views

How to best store a timestamp in PostgreSQL?

I'm working on a PostgreSQL DB design and I am wondering how best to store timestamps. Assumptions Users in different timezones will use the database for all CRUD functions. I have looked at 2 ...