Skip to content
Advertisement

Why PSQL create table returns [42P01] ERROR: relation does not exist

I’m new to SQL. I was trying to run sql schema, here is a part of code

It returns[2020-07-03 15:28:19] [42P01] ERROR: relation "people" does not exist

In fact all foreign key reference table reference not exist. When i remove the reference, the table can be created, can someone please help me ?

Advertisement

Answer

The script is run sequentially. So when the pagecolours table is created, the users table does not yet exist and thus the references users fails.

You need to re-order the script, so that the users table is created first. But as you have a circular reference (users references pagecolours and pagecolours references users) you need to create the tables without an “inline” reference, and then at the end of the script you need to run an ALTER TABLE to create the foreign keys.

But having a circular reference like that, is usually not a good idea. But if you are 100% sure you need it, you should at least declare the foreign keys as deferrable, to make inserting rows easier.


Also: serial is not a datatype. A foreign key column that references a serial column should be defined as an integer. In general it is recommended to move away from serial and use integer generated always as identity instead.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement