Search code examples
sqlpostgresqlplpgsqlrdbmsknime

Postgres copy table schema is_null constraints not included

i have a table with the ff columns and constraints :

create table newtable(
    id int4 not null primary key,
    lastname varchar(50) not null,  
    firstname varchar(50) not null,
    middlename varchar(50) not null
)

I'm developing a workflow script (KNIME) that would test the schema from the source to target. so i use the ff. code :

CREATE TABLE xtable 
AS TABLE newtable;

When i checked the is_null constraints, it was set to 'yes' (but in the original table, it was set to 'no'). How can i include the constraints using the above-mentioned code script?

Solution

  • It appears that you can't achieve it with the current approach. The best option I know is the following query:

    CREATE TABLE xtable (LIKE newtable INCLUDING ALL);

INSERT INTO xtable SELECT * FROM newtable;

    You can read about this syntax here.