In PostgreSQL, how to INSERT INTO and use destination table's autoincrement column

0
votes

Suppose you have two tables in PostgreSQL. Table A has field x, which is of type character varying and has a lot of duplicates. Table B has fields y, z, and w. y is a serial column, z has the same type as x, and w is an integer.

If I issue this query:

INSERT INTO B
SELECT DISTINCT ______, A.x, COUNT(A.x)
FROM A
WHERE x IS NOT NULL
GROUP BY x;

I get an error regardless of what I have in ______. I've even gotten as exotic as CAST(NULL as INTEGER), but that just gives me this error:

a null value in column "id" violates not-null constraint

Is there a simple solution?

1
sqlpostgresqlinsert

1 Answers

4
votes

You are allowed and even encouraged to specify your columns when using INSERT (and you really should always specify the columns):

insert into b (z, w)
select x, count(x)
from a
where x is not null
group by x

And I don't see the point of distinct when you're already grouping by x so I dropped that; I also dropped the column prefixes since they aren't needed and just add noise to the SQL.

If you don't specify a column when using INSERT, you get the default value and that will give you the sequence value that you're looking for.