Postgres Insert if not exists, Update if exists on non-unique column?

Question

I am currently using ON CONFLICT SET to update if there's a duplicate value on a unique column.

INSERT INTO `table`(col1, col2) VALUES ('v1', 'v2') 
ON CONFLICT (col1)
DO UPDATE SET
col2 = 'v3'

From the example, col1 is a unique field. But how do I do this if col1 is not unique?

I tried without the unique constraint now I'm getting:

Invalid column reference: 7 ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

Belayer · Accepted Answer · 2020-09-04 08:08:08Z

1

By the very definition you cannot have a conflict on a non-unique column. But since you do not duplicates just make it unique.

Alter table "table" add constraint col1_uk unique(col1);

answered Sep 4, 2020 at 8:08

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

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

1 Comment

ACD Over a year ago

Not an option, the col1 here really isn't unique. There are just some modules that requires this part to only have 1 col1. Quite hard to explain the details

ACD · Accepted Answer · 2020-09-07 07:31:59Z

1

Ended up using 2 successive queries. (1) Try to update first, if no rows updated, (2) try to insert.

In php:

// Try update
$return = execute("UPDATE table SET ... WHERE ...col1='unique_value'...");

// If update returns no value, insert
if (!$return) {
    execute("
        INSERT INTO table (...)
            SELECT ...values...
            WHERE NOT EXISTS (SELECT 1 FROM table WHERE ...col1='unique_value'...)
    ");
}

answered Sep 7, 2020 at 7:31

ACD

1,4511 gold badge14 silver badges26 bronze badges

Collectives™ on Stack Overflow

Postgres Insert if not exists, Update if exists on non-unique column?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related