0

I have a table that should contain a unique list of values ('activityid') from the other tables in the database. I can generate the list of unique values, but when I try to add it to the table (which is empty to start with), PostgreSQL returns the error: 

ERROR:  null value in column "id" violates not-null constraint

This is my SQL query: 

INSERT INTO catalog_activityid(activityid1)
SELECT distinct activityid from
(select activityid from d115ablackboxes
union select activityid from d115afilecabinets
union select activityid from d115arolledmaps
union select activityid from rockboxes1040)
as activityid

How can I have SQL add a value to the column 'id' at the same time as the INSERT command? Or, is there another workaround?

Evan
  • 1,960
  • 4
  • 26
  • 54

1 Answers1

1

If you define the 'id' as serial then it should automatically be assigned an incremented value.

https://www.postgresql.org/docs/8.1/datatype.html See bigserial or serial

Joshua Waring
  • 619
  • 7
  • 23
  • I had tried changing the column type to serial, but since that's not apparently a true type it returned an error. After your suggestion, I tried Googling again and saw [this answer](https://stackoverflow.com/a/9490532/5900093), and an adaptation of that allowed my original command to work. Good to know. – Evan Dec 18 '18 at 20:45