Postgresql – How to stop PG from wasting ids when doing ON CONFLICT DO NOTHING

postgresqlquery

I have an empty table with one bigserial id column and one unique text column, and do this:

INSERT INTO tab (col) VALUES ($1) ON CONFLICT DO NOTHING; -- $1 = test
INSERT INTO tab (col) VALUES ($1) ON CONFLICT DO NOTHING; -- $1 = test
INSERT INTO tab (col) VALUES ($1) ON CONFLICT DO NOTHING; -- $1 = test2

The table now has two rows:

1, test
3, test2

I expect and want:

1, test
2, test2

Why is the bigserial id column being increased even when "NOTHING" is happening for the second row due to the uniqueness of the text column?

More like "ON CONFLICT DO SOMETHING".

I doubt that this is related to the real error I'm having and which has been eluding me for months, but I need to straighten this out first as it seems like it could be related.

I could not find any mention in the manual on how to make it not increase the id when it doesn't INSERT any row.

Best Answer

This is Working As Designed™.

Firstly, serial data types are not "real" types, they are just syntactic sugar for integer columns populated from sequences.

Then about sequences the manual says so:

To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used and will not be returned again. This is true even if the surrounding transaction later aborts, or if the calling query ends up not using the value. For example an INSERT with an ON CONFLICT clause will compute the to-be-inserted tuple, including doing any required nextval calls, before detecting any conflict that would cause it to follow the ON CONFLICT rule instead. Such cases will leave unused “holes” in the sequence of assigned values.

Subsequently (no pun intended), you cannot "stop PG from wasting ids" (even if you stop using Postgres altogether, because all other DBMSes behave in the same manner).

Related Solutions

PostgreSQL – Temporal Tables Extension Error with Array Columns

The problem is versioning.c:412:

 errmsg("column \"%s\" of relation \"%s\" is of type %s (%i) but column \"%s\" of history relation \"%s\" is of type %s (%i)",
                NameStr(attr->attname),
                RelationGetRelationName(relation),
                format_type_with_typemod(attr->atttypid,
                                                                 attr->atttypmod),
                attr->attndims,
                NameStr(history_attr->attname),
                RelationGetRelationName(history_relation),
                format_type_with_typemod(history_attr->atttypid,
                                                                 history_attr->atttypmod), history_attr->attndims)));

When I changed to show attndims, result was:

ERROR:  column "directories" of relation "test" is of type text[] (1) but column "directories" of history relation "test_history" is of type text[] (0)

I found the problem is in heap_openrv (PostgreSQL function) does not correctly fill this field for tables created by LIKE. I do not know how to repair it. But I found a solution:

CREATE TABLE test_history (
  id SERIAL PRIMARY KEY,
  a integer,
  directories text[],
  sys_period tstzrange NOT NULL
);

After that:

CREATE EXTENSION
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE TRIGGER
INSERT 0 1
UPDATE 1
 id | a | directories |                            sys_period                             
----+---+-------------+-------------------------------------------------------------------
  1 | 1 |             | ["2017-02-07 15:12:16.383449+02","2017-02-07 15:12:16.387419+02")
(1 row)

PostgreSQL JSON – Create Multi-Key JSON Object with Integers or Floats

Update

According to your sample data and desired result you can get it using json_object_agg Postgres function.

CREATE TABLE test (str text, num double precision);
INSERT INTO test(str, num) VALUES ('a', 1.0);
INSERT INTO test(str, num) VALUES ('b', 2.3);

select json_object_agg(str, num)
from test;

| json_object_agg        |
| :--------------------- |
| { "a" : 1, "b" : 2.3 } |

dbfiddle here

You can use row_to_json Postgres function.

create table foo(f1 int, f2 varchar(10), f3 float, f4 decimal(18,2));
insert into foo values (2, 'name', 2.345, 23.23);

select row_to_json(f)
from   (select f1,f2,f3,f4 from foo) f;

| row_to_json                                |
| :----------------------------------------- |
| {"f1":2,"f2":"name","f3":2.345,"f4":23.23} |

dbfiddle here

Best Answer

Related Solutions

PostgreSQL – Temporal Tables Extension Error with Array Columns

PostgreSQL JSON – Create Multi-Key JSON Object with Integers or Floats

Related Question