How to UPDATE or INSERT in PostgreSQL

Question

I want to UPDATE or INSERT a column in PostgreSQL instead of doing INSERT or UPDATE using INSERT ... ON CONFLICT ... because there will be more updates than more inserts and also I have an auto incrementing id column that's defined using SERIAL so it increments the id column everytime it tries to INSERT or UPDATE and that's not what I want, I want the id column to increase only if it's an INSERT so that all ids would be in an order instead

The table is created like this

CREATE TABLE IF NOT EXISTS table_name (
    id SERIAL PRIMARY KEY,

    user_id varchar(30) NOT NULL,
    item_name varchar(50) NOT NULL,
    code_uses bigint NOT NULL,

    UNIQUE(user_id, item_name)
)

And the query I used was

INSERT INTO table_name
VALUES (DEFAULT, 'some_random_id', 'some_random_name', 1)
ON CONFLICT (user_id, item_name)
DO UPDATE SET code_uses = table_name.code_uses + 1;

Thanks :)

Auto-generated/Sequenced IDs in a table should not be expected to be sequential w/no gaps in all instances. But why don't you provide the statements you're already using? You've given us nothing to really help you other than a generic description. Ultimately you might need to utilize some PL/pgSQL (SQL Procedural Language) to achieve your goal. — Marc, Sep 19 '20 at 14:45
I don't understand what exactly the problem with `insert ... on conflict..` is — , Sep 19 '20 at 15:23
Added the query, you can look into that now and the problem with `insert ... on conflict ...` is that the ids are having a gap here and I thought like trying to insert many times is not a good idea when in the most cases it would just want to be updated — Awesome Stickz, Sep 20 '20 at 10:08
gaps in sequence generated values are nothing to worry about. The **only** job of a generated primary key value is to be unique. It does not matter if that value is 1,2, -34 or 48756485. — , Sep 21 '20 at 10:20

Jim Jones · Accepted Answer · 2021-07-31T22:18:02.287

18

Upserts in PostgreSQL do exactly what you described.

Consider this table and records

CREATE TABLE t (id SERIAL PRIMARY KEY, txt TEXT);
INSERT INTO t (txt) VALUES ('foo'),('bar');

SELECT * FROM t ORDER BY id;

 id | txt 
----+-----
  1 | foo
  2 | bar
(2 Zeilen)

Using upserts the id will only increment if a new record is inserted

INSERT INTO t VALUES (1,'foo updated'),(3,'new record')
ON CONFLICT (id) DO UPDATE SET txt = EXCLUDED.txt;

SELECT * FROM t ORDER BY id;

 id |     txt     
----+-------------
  1 | foo updated
  2 | bar
  3 | new record
(3 Zeilen)

EDIT (see coments): this is the expected behaviour of a serial column, since they're nothing but a fancy way to use sequences. Long story short: using upserts the gaps will be inevitable. If you're worried the value might become too big, use bigserial instead and let PostgreSQL do its job.

Related thread: serial in postgres is being increased even though I added on conflict do nothing

edited Jul 31 '21 at 22:18

answered Sep 19 '20 at 15:24

Jim Jones

18,404
3
35
44

I'm not sure whether it works here because of you inserting the id directly or not but in my case, I use `DEFAULT` to do it, it skipped a lot of ids and btw, I've added the query I used in the question, please look on that and let me know whether I'm doing something wrong – Awesome Stickz Sep 20 '20 at 10:10
@AwesomeStickz I just added a comment to your message. – Jim Jones Sep 21 '20 at 10:17
Alright, thanks for letting me know about it, I just worried that it may end up reaching max of bigserial too but it's just really really hard to do that so it's not a problem anymore – Awesome Stickz Sep 22 '20 at 11:08

How to UPDATE or INSERT in PostgreSQL

1 Answers1