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
– Marc, Commented Sep 19, 2020 at 14:45
I don't understand what exactly the problem with insert ... on conflict.. is — user330315
– user330315, Commented Sep 19, 2020 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
– Awesome Stickz, Commented Sep 20, 2020 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. — user330315
– user330315, Commented Sep 21, 2020 at 10:20
@user330315 maybe so, but some of us are a bit OCD and would like to avoid unnecessary gaps :) — Michael
– Michael, Commented May 21, 2024 at 9:25

Jim Jones · Accepted Answer · 2021-07-31 22:18:02Z

22

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, 2021 at 22:18

answered Sep 19, 2020 at 15:24

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Awesome Stickz Over a year ago

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

Jim Jones Over a year ago

@AwesomeStickz I just added a comment to your message.

Awesome Stickz Over a year ago

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

Collectives™ on Stack Overflow

How to UPDATE or INSERT in PostgreSQL

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related