Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [postgresql-9.5]

Specifically for PostgreSQL version 9.5

Version details

  1. Version: 9.5

  2. Version type: major.

  3. Released: January 2016.

  4. EOL: January 2021 (in accordance with Postgres 5 year rule).

New features highlight

Far from complete list, only few chosen from across whole (long) list:

  1. Conclicting INSERTs now may be ignored or turned into UPDATEs

  2. Block Range INdex added (BRIN)

  3. High number of performance changes

  4. ALTER SYSTEM RESET (resets value in postgresql.auto.conf file)

Complete list linked below.

Sources

  1. http://www.postgresql.org/support/versioning/

  2. http://www.postgresql.org/docs/9.5/static/release-9-5.html

358 questions
32
votes
3 answers

How to get the ID of the conflicting row in upsert?

I have a table tag with 2 columns: id (uuid) and name (text). I now want to insert a new tag into the table, but if the tag already exists, I want to simply get the id of the existing record. I assumed I could just use ON CONFLICT DO NOTHING in…
Oliver Salzburg
  • 422
  • 1
  • 5
  • 12
31
votes
2 answers

PostgreSQL UPSERT issue with NULL values

I'm having an issue with using the new UPSERT feature in Postgres 9.5 I have a table that is used for aggregating data from another table. The composite key is made up of 20 columns, 10 of which can be nullable. Below I have created a smaller…
Shaun McCready
  • 413
  • 1
  • 4
  • 6
31
votes
2 answers

UPSERT with ON CONFLICT using values from source table in the UPDATE part

Given: CREATE TABLE A ( PK_A INT8 NOT NULL, A INT8, PRIMARY KEY (PK_A) ); CREATE TABLE B ( PK_B INT8 NOT NULL, B INT8, PRIMARY KEY (PK_B) ); This query: insert into table_b (pk_b, b) select pk_a,a from table_a on conflict (b) do update set…
Tony Indrali
  • 311
  • 1
  • 3
  • 3
29
votes
2 answers

Querying JSONB in PostgreSQL

I have a table, persons, which contains two columns, an id and a JSONB-based data column (this table has just been made for demonstrational purposes to play around with PostgreSQL's JSON support). Now, supposed it contains two records: 1, { name:…
Golo Roden
  • 422
  • 1
  • 5
  • 9
27
votes
6 answers

Delete all data in Postgres database

I have created a fresh db dump from a production server with the --data-only and --column-inserts flags, so I only have a bunch of insert statements to insert data when performing a restore on a staging server. pg_dump -h localhost -U adminuser…
uberrebu
  • 481
  • 1
  • 7
  • 12
26
votes
1 answer

PostgreSQL DELETE FROM fails with `Error: attempted to delete invisible tuple`

The error Trying to delete tuples containing invalid timestamps with DELETE FROM comments WHERE date > '1 Jan 9999' OR date < '1 Jan 2000' OR date_found > '1 Jan 9999' OR date_found < '1 Jan 2000'; ends in ERROR: attempted to delete invisible…
Kai
  • 781
  • 5
  • 10
24
votes
1 answer

Postgres: How is SET NOT NULL "more efficient" than CHECK constraint

In PostgreSQL docs for Constraints, it says A not-null constraint is functionally equivalent to creating a check constraint CHECK (column_name IS NOT NULL), but in PostgreSQL creating an explicit not-null constraint is more efficient. I'm…
Robin Joseph
  • 341
  • 2
  • 6
24
votes
2 answers

Understanding "max_wal_size" and "min_wal_size" parameters default values from postgresql.conf file

Default values are, according to documentation for min_wal_size and max_wal_size parameters: For max_wal_size: The default is 1 GB For min_wal_size: The default is 80 MB Then I look this parameters from my database config: select name, setting, unit…
Oto Shavadze
  • 575
  • 1
  • 7
  • 15
20
votes
3 answers

How can I return multiple rows of records in PL/pgSQL

I am trying to return multiple records using RECORD data type, is there a way I can append to RECORD and add/append a new value with each iteration to this RECORD. that is, I want to append to rec so that rec becomes a set of rows when the loop is…
hky404
  • 313
  • 1
  • 2
  • 8
19
votes
1 answer

psql 9.5: gen_random_uuid() not working

SELECT gen_random_uuid() produces output ERROR: function gen_random_uuid() does not exist SQL state: 42883 Hint: No function matches the given name and argument types. You might need to add explicit type casts. I ran CREATE EXTENSION pgcrypto; on…
d9k
  • 293
  • 1
  • 2
  • 8
18
votes
2 answers

ON CONFLICT ON CONSTRAINT fails saying constraint doesn't exist

I'm trying to use new Postgresql 9.5 upsert feature. But for some reason my query saying constraint doesn't exist (when it does). My query is this INSERT INTO journals (ext_ids, title) VALUES ('{"nlmid": "000"}', 'blah') ON CONFLICT ON CONSTRAINT…
expert
  • 285
  • 1
  • 2
  • 7
16
votes
1 answer

Why does time zone have such a crazy offset-from-UTC on year 0001 in Postgres?

In Postgres 9.5, I was surprised to see the result seen below while experimenting with year 0001 (no year zero 0000). Offset of -07:52:58? Some example code. Note that I mixed use of TIMESTAMP WITH TIME ZONE and TIMESTAMP WITHOUT TIME ZONE, so read…
Basil Bourque
  • 11,188
  • 20
  • 63
  • 96
15
votes
1 answer

Changing foreign key to ON DELETE CASCADE with least amount of impact

I have an existing foreign key that has ON DELETE NO ACTION defined. I need to change this foreign key to ON DELETE CASCADE. I can do this within a transaction: begin; alter table posts drop constraint posts_blog_id_fkey; alter table posts add…
TheCloudlessSky
  • 337
  • 1
  • 2
  • 9
15
votes
1 answer

How to create an index on CASE expression in Postgres

I am trying to create an index on CASE expression, as follows CREATE TABLE test(i INT, j INT); CREATE UNIQUE INDEX test_index ON test(CASE WHEN i=1 THEN j END); Getting this error: ERROR: syntax error at or near "CASE" LINE 1: CREATE UNIQUE INDEX…
AlexC
  • 319
  • 3
  • 9
15
votes
1 answer

How to compare xmin and txid_current() after transactions ID wraparound?

Besides its regular columns, Postgres tables also have various system columns available. One of them, xmin, stores the transaction ID used to create a row. Its data type is xid, a four byte integer that wraps around at some point (i.e. not…
tomka
  • 967
  • 1
  • 10
  • 16
1
2 3
…
23 24