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

Postgres streaming replication between hosts with different timezone

I am running Postgres 9.1 on Windows 2012 servers. I am using postgres's streaming replication to replicate data between 2 servers. It has so happened that the time zone of the slave has not been set ...
1
vote
1answer
14 views

Pausing the import of an SQL file in Postgresql?

I am currently importing a large database (6 - 7 GB's) into my postgresql on Ubuntu. It's taking sometime to do. A problem has come up where I need to physically relocate the laptop the postgresql is ...
2
votes
0answers
33 views

A costly approach to filtering on a calculated date

This builds off my previously wonderfully answered question Running a complex query for every date in a range I've got an expanded problem and I'm certainly not solving it in a performant way. The ...
2
votes
0answers
17 views

How to examine PostgreSQL server's SSL certificate?

Suppose there is a PostgreSQL server running and it has SSL enabled. Using "standard" Linux and PostgreSQL tools, how can I examine its SSL certificate? I'm hoping for output similar to what you ...
1
vote
1answer
13 views

Use of smallserial gives error with PostgreSQL

I know the very basics of databases. I worked in the past with MySQL and recently switched to PostgreSQL (9.1) with PostGIS 2.0.1. At the moment I'm designing a new database and I use the tool ...
1
vote
0answers
21 views

Postgres - Error while adding constraints

Can anyone tell me what this error is? And How to resolve it? ERROR: could not read block 27651 in file "base/114689/118316": read only 4096 of 8192 bytes CONTEXT: SQL statement "SELECT ...
6
votes
2answers
120 views

Running a complex query for every date in a range

I've got a table of orders Column | Type | Modifiers ...
1
vote
1answer
20 views

Storing Large Nested Objects

What is the best way to store a large (thousands of properties) nested JSON object in Postgres? It does not need to be index, queried, or anything. Just written and read. Write speed isn't an issue ...
0
votes
0answers
17 views

Postgres 9.0 data huge folder

On our production environment we are using alfresco-4.0.c as content repository. Alfresco uses an instance of postgresql to index the contents. Looking at the filesystem size, I noticed I'm running ...
0
votes
0answers
22 views

How to populate values in drop down list in php postgresql [migrated]

I am getting error while populating the values in drop down list using php postgresql. Error means no output in drop down list. Below is my code. <!DOCTYPE html> <html> <body> ...
1
vote
1answer
23 views

How to have remote standby PostgreSQL server in different location (AWS)?

We have a primary PostgreSQL 9.4 server running multiple instances in our DC. It is using Continuous Archiving via WAL-E out to AWS S3. I would like to have a hot/warm standby server in our VPC within ...
0
votes
1answer
20 views

Restore Postgresql data after hdd crash

My HDD crashed, so, I can't make a Postgresql backup. It seems that my data tables are OK (I can view the data via pgAdmin). However, when I run pg_dump I get this: pg_dump: Error message from ...
-1
votes
2answers
31 views

postgres9.4 streaming replication not streaming [on hold]

I configured a postgres pair for streaming replication, the pg_basebackup command was invoked successfuly on the slave, so I now have a copy of the master on it. The problem is that the slave does ...
3
votes
1answer
32 views

Can jsonb do fast search by *value* or even a partial value? Other types?

I have a person table where arbitrary data must be inserted. Since I'm using Postgres 9.4, jsonb appears to be the right choice. Example data: id: 1, name: "Joe Doe", preferences: { color: "red" , ...
1
vote
1answer
16 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
0answers
20 views

Postgres 9.1.16 Hot Standby

I am using postgres 9.1.16 (and i cannot upgrade :/) with Hot Standby. We have a few batch that perform some heavy writes, resulting in a lot of WAL produce in a short period of time. The standby ...
2
votes
1answer
39 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 ...
0
votes
0answers
27 views

insert value based on check constraint on another column [on hold]

I want to have domain_list field in db which will contain multiple domain values only if allowed_domain field is set to 1. I want to insert values in domain_list based on value of allowed_domain ...
1
vote
1answer
42 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
56 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
18 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
1answer
32 views

Best Admin Tool for Postgres [on hold]

can anyone tell me best the open source tool for postgres 9.4. Right now i am using pgadmin3. please suggest me if any other best tool is there for both developer level and admin level. Note : Tool ...
0
votes
0answers
3 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 ...
5
votes
1answer
51 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
22 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
36 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 ...
-1
votes
0answers
20 views

Modifying a query for the UPSERT command [closed]

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 ...
12
votes
2answers
140 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
45 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
26 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
34 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
26 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 ...
7
votes
3answers
169 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
115 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
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
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, ...
5
votes
1answer
35 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
38 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
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 ...
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
16 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
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, ...
-1
votes
0answers
24 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
55 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
60 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 [on hold]

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 ...