Skip to content
Advertisement

Migrating data from old table to new table Postgres with extra column

Table Structure:

Old Table Structure:

Old Table Structure

New Table Structure:

enter image description here

Query:

Following error occurs:

ERROR: null value in column “created_by” violates not-null constraint DETAIL: Failing row contains (1, Test Hotel, THREE_STAR, t, null, null, null, null, null, null). SQL state: 23502

I tried to insert other required columns

Note: created_by as Jsonb

How can I pass default values for created_by and created_date column while moving data from the old table?

Advertisement

Answer

There are several choices.

First the INSERT is failing because the field is NOT NULL. You could ALTER TABLE(https://www.postgresql.org/docs/12/sql-altertable.html)as to unset that for the import, update the fields with values and the reset NOT NULL.

Two, as @XraySensei said you could add DEFAULT values to the table using ALTER TABLE:

Third option is to embed the defaults into the query:

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