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
1answer
44 views

Guid vs. integer ID for unique fields

This is really a generic question. Suppose I have a replication pattern of a single write DB and multiple read DB replication slaves - I do not have to have GUID's as my unique IDs fields. Is that a ...
1
vote
0answers
24 views

Trouble renaming a PostgreSQL database [duplicate]

Trying to rename a postgres database: $ psql -d some_other_database some_other_database=# ALTER DATABASE myapp_development RENAME TO myapp_development_old some_other_database-# \l ...
2
votes
1answer
66 views

Updating table of versioned rows with historical records in PostgreSQL

I have a master table of versioned rows: CREATE TABLE master ( id SERIAL PRIMARY KEY, rec_id integer, val text, valid_on date[], valid_during daterange ); INSERT INTO master ...
1
vote
1answer
30 views

Stop PostgreSQL from logging statements on error

My postgresql 9.3 logs are sprinkled with entries like these (redacted to protect the guilty): STATEMENT: INSERT INTO table (key, value) VALUES ($1, $2) ERROR: duplicate key value violates unique ...
2
votes
1answer
33 views

Does Postgres preserve insertion order of records?

For example when I'm using query which returns record ids INSERT INTO projects(name) VALUES (name1), (name2), (name3) returning id; Which produce output: 1 2 3 Will this ids point to ...
2
votes
2answers
54 views

Unique across tables

I have a table containing users. Each user has a primary email and a flag indicating if the user is deleted or not (we never hard-delete users). However, each user can also have additional emails. ...
0
votes
0answers
24 views

Postgresql: Update or insert stuck

I'm running update-or-insert query on a table of approximatelly 3mln rows, 4 columns. Query is part of every day process, and for same days, the query stucks forever. It's just blocked. It's ...
0
votes
2answers
47 views

Postgres: difference between CTE and temporary table

In Postgres is there a difference between a CTE and a temporary table other than the fact that the CTE exists just for the context of one statement? Documentation says that Common Table ...
0
votes
0answers
24 views

Postgres equivalent of SQL Server recovery model Simple

I am migrating an SQL Server database to PostgreSQL. The original database recovery model is Simple, mainly because the log size and performance are issues. What are the recovery models of Postgres ...
0
votes
1answer
24 views

Clarification on UNION ALL of JSONB_EACH result

This function used to merge 2 JSONB and delete certain key when the value is null, refer to this question. CREATE FUNCTION jsonb_merge(JSONB, JSONB) RETURNS JSONB AS $$ WITH json_union AS ( ...
0
votes
1answer
43 views

PostgreSQL update and delete property from JSONB column

From this article, I tried to update or delete property of a JSONB column: CREATE TABLE xxx (id BIGSERIAL, data JSONB); INSERT INTO xxx(data) VALUES( '{"a":1,"b":2}' ); SELECT * FROM data; id | ...
1
vote
0answers
25 views

Postgres: Can't free space because I have no space

I have a Postgres DB that someone was brilliant enough to install in the log partition of my linux box. It has a serious index problem, with indexes ballooning up to 50GB per. As soon as I start ...
0
votes
1answer
19 views

Uniq join 3 tables with relationship table not uniq

I am using Postgres. I have 3 tables: table_1 id table_1_2 table_1_id, table_2_id table_2 id The problem is the relationship table table_1_2 is not uniq. I.e., table_1_2 will have the ...
0
votes
1answer
22 views

What limits the number of connections?

As per https://devcenter.heroku.com/articles/heroku-postgres-legacy-plans the connection limit is 500 As per https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server "Generally, PostgreSQL on ...
1
vote
1answer
29 views

Can the number of tables and functions affect performance? [closed]

I have some big database with hundreds of functions and tables. They also have a lot of sequences, custom types and some views and triggers. There is a point when the quantity of objects will be a ...
0
votes
0answers
15 views

Getting SSL Working

So, I'm trying to configure PostgreSQL to allow SSL connections, on a Windows box. First, per http://www.postgresql.org/docs/9.4/static/auth-pg-hba-conf.html I added this line to pg_hba.conf (and ...
0
votes
1answer
17 views

Could this ActiveRecord/SQL statement in my code base be susceptible to SQL injection?

I was reading up on SQL injection attacks from the Rails Guides and was wondering if the below line could be susceptible to a SQL Injection attack: accounts.where("lower(name) LIKE ...
1
vote
0answers
21 views

Is C collation suitable for my Postgres database?

I am working with 500m rows (120GB) in Postgres 9.4, and I am trying to make SELECT statements as fast as possible. I am less concerned about the speed of INSERT and UPDATE, because my data only ...
0
votes
2answers
26 views

Get ID which has two rows with different values for one column [closed]

Basically, I have a dataset that is a list of internal location identifiers attached to an ID (so each 'set' of geographical points has its own ID). So an 'id' column and a 'location' column exist in ...
0
votes
0answers
46 views

Thousands of the same weird syntax error after 9.4 upgrade

We moved our production database from 9.1 to 9.4.1 and started seeing the following errors all throughout the logs: ERROR: syntax error at or near "''" at character 1 STATEMENT: '' LOG: statement: ...
1
vote
1answer
59 views

Poor query performance due to recheck condition on index scan

We are testing the performance of metadata when stored in JSONB compared to a traditional EAV catalogue (commonly used in biomedical scenarios), using a PostgreSQL 9.4 server. I get a problem when ...
4
votes
3answers
235 views

Custom sorting of a query

The application I am working on displays some tasks based on status, and date (in that order). However, for a particular status, the sort condition should be inverted. For example, a table which looks ...
6
votes
2answers
198 views

Count data using multiple date ranges

Probably this has been asked before, but I can't figure it out. I have a phone_clicks table (sql fiddle http://sqlfiddle.com/#!15/855e0/1) CREATE TABLE phone_clicks ( id integer NOT NULL, ...
1
vote
0answers
32 views

How to return updated data from UPDATE … FROM query?

UPDATE "tbl1" SET "col" = 'val', FROM "tbl2" WHERE "tbl2".id = 1 RETURNING * This query returns values from tbl2 instead of updated data from tbl1 - how can I get updated data? (PG 9.3).
0
votes
1answer
37 views

Distinct rows by order

I have the following SQL, generated from ActiveRecord in Rails 4 (partly with the Geocoder gem) problem is, that even though DISTINCT is used, hopefully to get only unique items, the items returned ...
2
votes
3answers
127 views

Aggregate query with min

Having a product_stock table with following structure: product_id warehouse_id stock price Which has bunch of records (same product can be in different warehouses): 1, 1, 0, 500 1, 2, 5, 505 1, 3, ...
0
votes
1answer
71 views

Where do you find the citext module in postgres 9.3 on ubuntu 14.04?

So I'm trying to use citext, but it appears to be MIA from ubuntu 14.04 postgres install. Where can you find this mythical beast called citext for postgresql 9.3? $ manage dbshell Password for user ...
3
votes
1answer
237 views

how can an additional natural join with the same table twice *reduce* the row count

This is real results from my database. I spend some time trying to reproduce this behavior with an SSCCE but failed. How is the last of the below results possible? SELECT COUNT(*) FROM rr.resource ...
1
vote
1answer
65 views

Recommended Postgres settings to speed up indexing on a 100GB database?

I have just installed Postgres 9.1 on a server with 32GB of RAM and 320GB solid-state disk. I will be using this server to serve both my site and my database (i.e. it's not just a database server). ...
0
votes
0answers
52 views

pg_hba.conf and postgresql.conf in old installation directory

I have installed the PostgreSQL 9.4 on my MacBook via MacPorts, and I am trying to edit pg_hba.conf, but I don't see it in my current installation. Furthermore, psql is reporting that config_file is ...
0
votes
1answer
53 views

What is the most efficient way to count the number of rows in a table?

I am using postgres with the following query: select count(*) from image; The primary key on this table is non-incrementing; it's a unique serial number for the images stored in the table. Our app ...
1
vote
0answers
18 views

Techniques for checking that data loaded correctly in Postgres?

Apologies for the newbie question! I am working with a largeish dataset (500m rows, 150GB of data) and Postgres 9.1 running on Debian. I have a Python import script for importing the source data, ...
0
votes
0answers
87 views

upgrading posgresql 9.0 to 9.4 with pg_upgrade on CentOS 6.6

I'm trying to upgrade a postgres v9.0 database to v9.4 with pg_upgrade on CentOS 6.6, but I keep hitting a wall. I am calling pg_upgrade thru a shell script as postgres, first exporting the needed ...
0
votes
1answer
21 views

Tool to find CPU utilization for a particular query in Postgres

I just want to know is there any free tool to identify CPU utilization for a particular query in PostgreSQL.
-1
votes
1answer
63 views

Out of the box why is a simple query from a 50gb table taking 5x longer in postgres vs sql server--help? [closed]

As you can see I'm a newbie and been flailing for 2 weeks. I am doing a simple query such as: "select * from all_visits where reason_for_visit in ('CE233','4E233','GH399') This query in Postgres ...
0
votes
1answer
25 views

how to delete all rows with empty field via function/trigger combo in Postgres v 9.3?

In PostgreSQL 9.3 I am trying to write a trigger function that will be called after updates to table test2 and that will delete rows of test2 where field "email" is empty. Here's my attempt: CREATE ...
0
votes
1answer
19 views

PostgreSQL copy text type column to text[] array type column

I have two columns, material_size (text type) and material_size_temp (text[] array type). When I ran UPDATE parts SET material_size_temp[0] = material_size; I would get values like [0:0]={".021 x ...
2
votes
1answer
40 views

Postgresql import from SQL Server - how to auto-generate sequences

As the title states I'm switching from SQL Server to Postgresql via SQL dump. The tables and views have been migrated correctly, alas the IDENTITY columns have not been properly converted into ...
0
votes
1answer
51 views

Select on multiple tables with group by

Using Postgres 9.3, I have problem with a select over few tables. I have simcards stock and I would like to get something like this: | network | supplier | total topups (EUR) | 1st topup qty| | ...
4
votes
1answer
56 views

SQL - Inverse Selection

I have two tables in my schema students(id text, matric text) attendance(id serial, a_date date, matric text references students matric) To get records showing date and student present, I can run ...
0
votes
1answer
41 views

Working with Materialized View

I have a materialized view which takes around 57 second to be created and I'm using PostgreSQL 9.4. When I do an insert into a table, a trigger will call a trigger function which will do a REFRESH ...
3
votes
0answers
69 views

Postgres pg_basebackup reports directory exists but is not empty

I have postgreSQL 9.4.1 installed on a Win 2012 Server. I am looking to setup replication between this and another server of the same spec. So I am following guidance on preparing the master ...
1
vote
1answer
40 views

anonymous blocks inside a transaction [closed]

I have the following script: DROP TABLE IF EXISTS a; CREATE TABLE a( val VARCHAR NOT NULL ); DO $do$ BEGIN INSERT INTO a(val) VALUES('a1'); INSERT INTO a(val) VALUES('a2'); END; $do$ ...
0
votes
0answers
23 views

CPU utilization for a particular query in Postgres [duplicate]

Is there any way to know how much CPU particular query is utilizing in PostgreSQL?
0
votes
1answer
41 views

PostgreSQL - is it possible to use a plpgsql function in a bash script?

Target : What i'm trying to achieve is to restore a partial backup of a database (only certain rows that satisfied certain conditions have been backed up). Problem : Since between the partial-backup ...
0
votes
0answers
27 views

Please help: kerberos authentication setup on postgresql

Requirement: Backend working with RHEL and postgresql and front-end working with windows,.net. please consider how the authentication method must be done. our main idea will be to have the ...
0
votes
1answer
29 views

Moving PostgreSQL database to new server

I had PostgreSQL 8.3 running on a windows machine. The hard drive recently became unbootable, but the data is fine. We got a new machine and I'm re-installing Postgres. The new installer only has ...
0
votes
1answer
30 views

Errors importing data using COPY comand at postgresql 9.3.5

I am trying import a database table to Postgres 9.3.5 database server using COPY command as follows: COPY comment (generatedid, id, "timestamp", message, bugreport_id, personcontainer_id) FROM ...
0
votes
1answer
28 views

What does a CONSTRAINT have to do with my unique index?

I have to indexes on my table. The first was created by Django and the second by me. I'm not completely certain what the extra CONSTRAINT means in the first index and am wondering how I would change ...
0
votes
2answers
49 views

Match multiple components of a subquery CTE in PostgreSQL

I've got an app that grants access to users in varying different ways, and I'm attempting to write a query that determines access in all the different ways, but is readable, by breaking the access out ...