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

Question

Table Structure:

Old Table Structure:

New Table Structure:

Query:

INSERT INTO hotel (id, name, hotel_type, active, parent_hotel_id)
SELECT id, name, hotel_type, active, parent_hotel_id 
FROM dblink('demopostgres', 'SELECT id, name, hotel_type, active, parent_hotel_id FROM hotel')
    AS data(id bigint, name character varying, hotel_type character varying, active boolean, parent_hotel_id bigint);

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

created_by = '{
    "id": 1,
    "email": "tes@localhost",
    "login": "test",
    "lastName": "Test",
    "firstName": "Test",
    "displayName": "test"
}'
created_date = '2020-02-22 16:09:08.346'

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

The table is already created and I'm moving data from old to new table, Now I want to pass the default value for two-column (created_by, created_date) rest are coming from the old table (See the above query) — Ramanuj

Adrian Klaver Adrian Klaver · Accepted Answer · 2020-06-14T17:31:00

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.

ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL

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

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    action [, ... ]
ALTER [ COLUMN ] column_name SET DEFAULT expression

Third option is to embed the defaults into the query:

create table orig_test(id integer NOT NULL, fld_1 varchar, fld_2 integer NOT NULL);

insert into orig_test(id, fld_1, fld_2) values (1, 'test', 4);
insert into orig_test(id, fld_1, fld_2) values (2, 'test', 7);

insert into default_test (id, fld_1, fld_2) select id, fld_1, fld_2 from orig_test ;
ERROR:  null value in column "fld_3" violates not-null constraint
DETAIL:  Failing row contains (1, test, 4, null).

insert into default_test (id, fld_1, fld_2, fld_3) select id, fld_1, fld_2, '06/14/2020' AS fld_3 from orig_test ;
INSERT 0 2

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

1 Answers