Stack Overflow
Stack Overflow

Questions tagged [postgresql-9.5]

For PostgreSQL questions specific to version 9.5

On 7 January 2016 PostgreSQL-9.5 was released. Among new major features available in this release - from the docs:

  • Block-Range Indexes (BRIN) which enable compact indexing of very large tables
  • Large speed improvements in in-memory sorting and hashing
  • Automated management of transaction log size
  • INSERT ON CONFLICT UPDATE, otherwise known as "UPSERT"
  • Grouping Sets, CUBE and ROLLUP analytic operations
  • Row-Level Security (RLS) support
  • More JSONB data manipulation functions and operators
  • Added the pg_rewind tool and other high availability improvements to replication
  • Multiple enhancements to Foreign Data Wrappers, including IMPORT FOREIGN SCHEMA
  • Large increases in multi-core and large memory scalability

Use this tag for questions specific to any of the above new features.

1198 questions
304
votes
25 answers

Postgresql - unable to drop database because of some auto connections to DB

Whenever I try to drop database I get the following error: ERROR: database "pilot" is being accessed by other users DETAIL: There is 1 other session using the database. When I use: SELECT pg_terminate_backend(pg_stat_activity.pid) FROM…
Andrius
  • 19,658
  • 37
  • 143
  • 243
226
votes
2 answers

PostgreSQL INSERT ON CONFLICT UPDATE (upsert) use all excluded values

When you are upserting a row (PostgreSQL >= 9.5), and you want the possible INSERT to be exactly the same as the possible UPDATE, you can write it like this: INSERT INTO tablename (id, username, password, level, email) VALUES (1,…
Sebastian
  • 5,471
  • 5
  • 35
  • 53
157
votes
12 answers

Use multiple conflict_target in ON CONFLICT clause

I have two columns in table col1, col2, they both are unique indexed (col1 is unique and so is col2). I need at insert into this table, use ON CONFLICT syntax and update other columns, but I can't use both column in conflict_targetclause. It…
Oto Shavadze
  • 40,603
  • 55
  • 152
  • 236
85
votes
5 answers

No unique or exclusion constraint matching the ON CONFLICT

I'm getting the following error when doing the following type of insert: Query: INSERT INTO accounts (type, person_id) VALUES ('PersonAccount', 1) ON CONFLICT (type, person_id) WHERE type = 'PersonAccount' DO UPDATE SET updated_at =…
Tiago Babo
  • 1,064
  • 1
  • 7
  • 9
81
votes
4 answers

Appending (pushing) and removing from a JSON array in PostgreSQL 9.5+

For versions less than 9.5 see this question I have created a table in PostgreSQL using this: CREATE TEMP TABLE jsontesting AS SELECT id, jsondata::jsonb FROM ( VALUES (1, '["abra","value","mango", "apple", "sample"]'), (2,…
Evan Carroll
  • 78,363
  • 46
  • 261
  • 468
64
votes
3 answers

PostgreSQL rename attribute in jsonb field

In postgresql 9.5, is there a way to rename an attribute in a jsonb field? For example: { "nme" : "test" } should be renamed to { "name" : "test"}
T. Kong
  • 643
  • 1
  • 5
  • 5
54
votes
1 answer

Differentiate inserted and updated rows in UPSERT using system columns

Several questions have been asked recently here on SO about how to differentiate inserted and updated rows in a PostgreSQL UPSERT statement (INSERT ... ON CONFLICT ... DO UPDATE ...). Here is a simple example: create table t(i int primary key, x…
Abelisto
  • 14,826
  • 2
  • 33
  • 41
54
votes
4 answers

Postgres shuts down immediately when started with docker-compose

Postgres shuts down immediately when started with docker-compose. The yaml file used is below version: '2' services: postgres: image: postgres:9.5 container_name: local-postgres9.5 ports: - "5432:5432" The…
arjunurs
  • 1,062
  • 2
  • 17
  • 29
43
votes
3 answers

How to correctly do upsert in postgres 9.5

correct syntax of upsert with postgresql 9.5, below query shows column reference "gallery_id" is ambiguous error , why? var dbQuery = `INSERT INTO category_gallery ( category_id, gallery_id, create_date, create_by_user_id ) VALUES ($1, $2, $3,…
user1575921
  • 1,078
  • 1
  • 16
  • 29
41
votes
3 answers

Why isn't row level security enabled for Postgres views?

I need strict control of the reading and writing of my Postgres data. Updatable views have always provided very good, strict, control of the reading of my data and allows me to add valuable computed columns. With Postgres 9.5 row level security has…
Calebmer
  • 2,972
  • 6
  • 29
  • 36
40
votes
4 answers

Adding value to Postgres integer array

I am looking for help in adding a value 10 to an int[] in PostgreSQL 9.5. Looking at the documentation I should be able to use this format to update it but it is not working: int[] + int push element onto array (add it to end of array) I have…
Kyle K
  • 503
  • 1
  • 4
  • 9
38
votes
1 answer

will pg_restore overwrite the existing tables?

Say I have two host servers s1 and s2. In both the servers i have a schema named n1, now i have made some changes to some of the tables present in schema n1 of s1. I want the same change to be done to schema n1 of server s2. what i am planning to do…
Karthik
  • 629
  • 2
  • 8
  • 12
36
votes
4 answers

How to find out if an upsert was an update with PostgreSQL 9.5+ UPSERT?

Writable CTEs were considered a solution to UPSERT prior to 9.5 as described in Insert, on duplicate update in PostgreSQL? It is possible to perform an UPSERT with the information whether it ended up as an UPDATE or an INSERT with the following…
Paul Guyot
  • 6,257
  • 1
  • 20
  • 31
31
votes
7 answers

PostgreSQL compare two jsonb objects

With PostgreSQL(v9.5), the JSONB formats give awesome opportunities. But now I'm stuck with what seems like a relatively simple operation; compare two jsonb objects; see what is different or missing in one document compared to the other. What I have…
Joost Döbken
  • 3,450
  • 2
  • 35
  • 79
28
votes
1 answer

Return rows from INSERT with ON CONFLICT without needing to update

I have a situation where I very frequently need to get a row from a table with a unique constraint, and if none exists then create it and return. For example my table might be: CREATE TABLE names( id SERIAL PRIMARY KEY, name TEXT, …
ira
  • 735
  • 1
  • 7
  • 12
1
2 3
…
79 80