CODEWITHSUNDEEP
postgresql
ACD
ACD

Reputation: 1431

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

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

Upvotes: 3

Views: 2322

Answers (2)

ACD
ACD

Reputation: 1431

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'...)
    ");
}

Upvotes: 1

Belayer
Belayer

Reputation: 14861

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);

Upvotes: 1

Related Questions