CODEWITHSUNDEEP
sqlpostgresqlinsert
Aren Cambre
Aren Cambre

Reputation: 6740

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

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?

Upvotes: 0

Views: 1633

Answers (1)

mu is too short
mu is too short

Reputation: 434985

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.

Upvotes: 4

Related Questions