Postgres relation does not exist when adding column to table?

Question

I'm trying to create a schema and add tables to it via node using node-postgres (pg). The basic order of events is as follows:

1. Create schema
2. Create table in schema
3. Create columns in table in schema

I can verify that the schema and the table are being created without issues, but I get a relation does not exist error when trying to add the first column to the table. The query string for creating the column looks like this:

"ALTER TABLE " +
schemaName +
".process ADD COLUMN process_id bigint NOT NULL DEFAULT nextval('" +
schemaName +
".process_process_id_seq'::regclass)";

I've confirmed in the console log that the schema name variable matches what is was used to successfully create the table. Any ideas on why the error is being thrown here?

@stickybit it does not. I thought it was created in the code block defining the column. Does the sequence need to be created prior to creating the column? — userNick
– userNick, Commented Jun 28, 2020 at 16:19
Note that the code in the question and answer are vulnerable to SQL injection. — Laurenz Albe
– Laurenz Albe, Commented Jun 29, 2020 at 6:24
Try with a schemaName that contains a double quote. Then your query will go boom or worse. — Laurenz Albe
– Laurenz Albe, Commented Jun 29, 2020 at 12:27

sticky bit · Accepted Answer · 2020-06-28 16:35:55Z

1

The sequence needs to be created beforehand.

But you could also use the bigserial type which is a shortcut for a bigint column with a sequence and a respective DEFAULT created automatically. Something along the lines of:

"ALTER TABLE " +
schemaName +
".process ADD COLUMN process_id bigserial";

Maybe you fell over that. Later, when the table/column was created you cannot see the ...serial anymore but the actual type and the DEFAULT.

More on ...serial types can be found in the documentation.

answered Jun 28, 2020 at 16:35

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

userNick Over a year ago

Thanks, this worked! I initially set up a sample table in pgAdmin where I defined the column as bigserial. It looks like pgAdmin converts it to bigint if you look at the CREATE script that it generates (which I was copying before).

sticky bit Over a year ago

@userNick: It's not pgAdmin which converts it but the DBMS itself. But yes, afterwards you cannot see the ...serial in the CREATE that pgAdmin creates but the real type and the DEFAULT.

Collectives™ on Stack Overflow

Postgres relation does not exist when adding column to table?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related