PostgreSQL插入冲突报错：无唯一/排他约束，求每日表更新方案

Hey there, let's break down why you're running into this error and how to fix it quickly.

What's Causing the Error? #

That error message is pretty straightforward: when you use PostgreSQL's ON CONFLICT clause to do an upsert (insert new rows if they don't exist, update existing ones if they do), the database needs a clear way to identify what counts as a "conflict" for your userid field. Right now, your summary_songs table doesn't have a unique constraint or primary key on the userid column—so PostgreSQL can't tell when a new row from daily_report would clash with an existing entry for the same user.

Step 1: Add a Unique Constraint to summary_songs #

First, we need to give PostgreSQL the constraint it needs. You have two good options here, depending on your data model:

Option 1: Make userid the Primary Key (Recommended if userid uniquely identifies rows)

If each row in summary_songs corresponds to one unique user, setting userid as the primary key is the cleanest approach:

ALTER TABLE summary_songs ADD PRIMARY KEY (userid);

Option 2: Add a Standalone Unique Constraint

If you don't want userid to be the primary key (maybe you have another primary key column), add a unique constraint instead:

ALTER TABLE summary_songs ADD CONSTRAINT summary_songs_userid_unique UNIQUE (userid);

Step 2: Run Your Corrected Upsert Query #

With the constraint in place, your upsert will work as expected. Here's the proper query to use—note we use EXCLUDED to reference the row that was trying to be inserted (this is more efficient than joining back to daily_report):

INSERT INTO summary_songs (userid, countid)
SELECT userid, countid FROM daily_report
ON CONFLICT (userid) DO UPDATE
SET countid = summary_songs.countid + EXCLUDED.countid;

Verify the Constraint (Optional) #

If you want to double-check that the constraint was added correctly, run this query to list all constraints on summary_songs:

SELECT conname, contype
FROM pg_constraint
WHERE conrelid = 'summary_songs'::regclass;

Look for a row with contype = 'p' (primary key) or contype = 'u' (unique constraint) linked to userid.

内容的提问来源于stack exchange，提问作者Chandra