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
2answers
77 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, ...
5
votes
1answer
37 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
41 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
16 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
30 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
35 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
17 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
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 ...
1
vote
1answer
24 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
26 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 ...
1
vote
0answers
57 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
124 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
1answer
69 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 ...
-1
votes
0answers
38 views

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

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 ...
2
votes
0answers
20 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
45 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
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 ...
0
votes
2answers
16 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
18 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
15 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
14 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
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 ...
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 ...
1
vote
1answer
43 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
21 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
34 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
42 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
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 ...
5
votes
1answer
128 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
272 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 ...
2
votes
1answer
37 views

How to validate column while creating Table?

Need to validate the column while creating the tables in postgres , An example before developer create table We are in need to validate the Audit columns, Example : create table tab_1 ( a integer, b ...
1
vote
1answer
27 views

Procedure to read from crontab like table and populate another one periodically

Postgres 9.3 Debian 7 I'm working on a mobile game project (poker like) where an admin user can register a recurrent tournament. As it's recurrent, I modeled it crontab like as follows. ...
2
votes
0answers
20 views

pg_dump with ssl certificates

I would like to run the pg_dump command from a remote host off my database server that only allows fully verified certificate based connections. How should I go about doing this? The man pages for 9.1 ...
1
vote
2answers
45 views

Is it recommended to install extensions into pg_catalog schema?

Since objects in pg_catalog schema are implicitly in the search_path (docs), would it be recommended to install extensions in that schema?
1
vote
1answer
15 views

Postgres or MapReduce to Match Reciprocal Needle Pairs from a Stack of Needles?

I have a Postgres (latest version) table with about a million lines (a_lat, a_lon, b_lat, b_lon) that each have about a dozen criteria. These lines go together in reciprocal pairs (A - B, B - A), ...
0
votes
0answers
8 views

Updating the details of an ACCESS-2003 database on completion of a PostgreSQL UPDATE trigger

I am currently stuck and looking for some further guidance. I have a database in access, which on UPDATE (trigger function) will take the data inputted into the PostgreSQL table in QGIS, and update ...
1
vote
2answers
29 views

Proper usage of Group by in Inner Join

EDIT I updated my question with a SQL Fiddle Sample http://sqlfiddle.com/#!15/8d88b/1 I'm currently making a report from a database records but I don't know how my query should look like, first of ...
2
votes
0answers
17 views

PostgreSQL/PostGIS: Query seems inordinately time-consuming

I'm doing what I would consider a pretty straightforward query on two tables that aren't huge (~626k records in one; ~47k records in the other). Both tables have GIST indices on their spatial ...
2
votes
1answer
66 views

Avoid joining to the same table multiple times

I have two tables: CREATE TABLE one ( id int4 primary key, p_id int4, k_id int4, c_id int4 ); CREATE TABLE two( id int4 primary key, p_id int4, k_id int4, t_id int4, pos int4 ); Table ...
-1
votes
0answers
27 views

How would I make this query run faster in postgres [closed]

Query: with customer_total_return as (select wr_returning_customer_sk as ctr_customer_sk ,ca_state as ctr_state, sum(wr_return_amt) as ctr_total_return from web_returns ...
1
vote
0answers
28 views

IF EXIST before many insertions in PostgreSQL? [migrated]

I would like to INSERT only if table exists. Pseudocode IF EXISTS TABLE file_headers( INSERT INTO file_headers (measurement_id, file_header_index_start, file_header_index_end) VALUES (1, 1, 100); ...
2
votes
1answer
48 views

How to store time series data

I have what I believe is a time series data set (please correct me if I'm wrong) that has a bunch of associated values. An example would be modeling a car and tracking its various attributes during a ...
0
votes
2answers
31 views

PostgreSQL, merge join results

I have 2 tables with common columns and would like to know if it's possible to request this 2 tables by merging the results. Table "job" : parent_id, target_id, start_date, end_date, jobposition_id ...
3
votes
2answers
64 views

How to log custom messages from inside a PostgreSQL transaction?

I have a software that generates transactions and executes them. I would like to asynchronously inspect about the transaction progress in term of the simple proportion of queries executed over total ...
4
votes
2answers
69 views

Grouping by sequential relationships with Postgres

This is quite similar to this question about continuous ranges, and this question about grouping by sequential numbers, but differs in that the sequences are not numeric. Given the following ...
3
votes
2answers
121 views

Filter and append data to row

My query and my data set (copied from sqlfiddle): CREATE TABLE one ( id int4 primary key, p_id int4, k_id int4 ); CREATE TABLE two( id int4 primary key, p_id int4, k_id int4, ...
1
vote
0answers
42 views

Having problems trying to back up a Postgres database from AWS RDS

I am trying to backup a Postgres database from RDS and I am getting this error message. pg_dump: [archiver (db)] query failed: SSL error: unexpected message pg_dump: [archiver (db)] query ...