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

How to return values from “upsert” query in case when UPDATE has been performed?

WITH upsert AS (UPDATE tbl SET a = 2 WHERE a = 1 RETURNING tbl.*) INSERT INTO tbl (a) SELECT 1 WHERE NOT EXISTS( SELECT * FROM upsert ) RETURNING * This "upsert" statement works however I would like ...
0
votes
1answer
50 views

Maximum Partitioning PostgreSQL

I have 400,000 records I need to partition. The records will increase 100,000 per year and those new records might need to have 1000 new partitions added. Here are some of my concerns: How many ...
2
votes
1answer
30 views

Search for authors whose records are separated by at least X days

I am using Postgres and have a table which contains metadata about several million images - many of which have the same author. I am looking for contributors who add content regularly - not people ...
2
votes
1answer
60 views

Why do we need partitions in PostgresSQL? (or any other RDBMS)

So, I am trying to teach myself partitions in PostgreSQL. I understand that a database can become slow when a table hits millions+ of lines and indexes don't fit into memory anymore. One thing that ...
0
votes
0answers
31 views

List all columns and rows of PostgreSQL table with a view limit

I am currently using select * from items; to show my table during development however I am finding it a bit messy now as some the strings I am adding to the table are 1,000+ chars long. Is there a ...
0
votes
1answer
32 views

Postgres tablespace on mounted usb

I've got postgres 9.1 running on raspbian installed with apt-get. I've got NTFS usb HDD mounted in /mnt/usb. What I'd like to achieve is to create a tablespace pointing to /mnt/usb/postgres so I ...
2
votes
1answer
79 views

Problems restoring backup from base backup + WAL with WAL-E

I'm trying to do nightly restores of the main database on a separate node. I want to do weekly full restores and nightly restores by consuming the latest WAL files. On the master I have wal_level = ...
0
votes
1answer
34 views

Display only table names in psql

How can I print only table names from a specified database in psql? That is, if I have tables called "table1" and "table2" then the output will be: table1 table2 and only that. I've been trying ...
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
69 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
32 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
25 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
47 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
20 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
23 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
30 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
203 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
78 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
239 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
66 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
57 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
54 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
19 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
53 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
1answer
78 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 ...