PostgreSQL中如何按ID匹配从另一表插入数据并生成指定结果？

Hey there! Let's work through this PostgreSQL issue step by step. First, let's unpack why your original query threw that error: when using INSERT ... SELECT, you can't directly reference the target table (a) in the WHERE clause like that—PostgreSQL doesn't know how to link a and b unless you explicitly include a in the query's FROM clause (and even then, that's not what you need here). Also, your original insert only specified the gender column, but you need to include id too to match rows correctly.

To get the exact result you want (each ID in Table A has two rows with the Gender value from Table B), you'll need two simple steps: update the existing rows in Table A, then insert the rows from Table B into Table A. Here's how to do it:

Step 1: Update Existing Rows in Table A #

First, overwrite the original Gender values in Table A to match the corresponding IDs in Table B:

UPDATE a
SET gender = b.gender
FROM b
WHERE a.id = b.id;

This will change Table A's initial Gender values (1, 2, 3) to F, M, F respectively.

Step 2: Insert Rows from Table B into Table A #

Next, add duplicate entries by inserting all rows from Table B into Table A:

INSERT INTO a (id, gender)
SELECT id, gender FROM b;

This will add three new rows to Table A, mirroring the ID and Gender pairs from Table B.

Optional: Run in a Transaction #

To ensure both operations succeed or fail together (so you don't end up with partial updates), wrap them in a transaction:

BEGIN;
-- Update existing rows
UPDATE a
SET gender = b.gender
FROM b
WHERE a.id = b.id;

-- Insert new rows
INSERT INTO a (id, gender)
SELECT id, gender FROM b;
COMMIT;

After running these commands, Table A will look exactly like your desired output:

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