Null option and Default value Lost when use CREATE TABLE AS to backup a table.

Hi Team,

Null option(NOT NULL) and Default value lost when I try to copy a table with CREATE TABLE AS statement.

List the test step as below.

1. PostgreSQL version.

[cid:image005(dot)jpg(at)01D7C682(dot)25FBB520]

1. Create test table as below.

CREATE TABLE contact(

id SERIAL PRIMARY KEY,

first_name VARCHAR NOT NULL DEFAULT 'Benny',

last_name VARCHAR NOT NULL,

email VARCHAR NOT NULL UNIQUE

);

1. Insert the test data to table Contact.

INSERT INTO contact(first_name, last_name, email)

VALUES('John','Doe','john(dot)doe(at)postgresqltutorial(dot)com'),

('David','William','david(dot)william(at)postgresqltutorial(dot)com');

1. Create back up table.

create table contact_bckp as table contacts with data;

1. Test to insert data with null in column last_name. Failed in table contact.

INSERT INTO contact(first_name, last_name, email) VALUES('benny',null,'john(dot)doe(at)postgresqltutorial(dot)com');

[cid:image011(dot)jpg(at)01D7C682(dot)25FBB520]

1. Test to insert data with null in column last_name. Succeed in table contact_bckp. And even the PK column been set to null.

INSERT INTO contact_bckp(first_name, last_name, email) VALUES('benny',null,'john(dot)doe(at)postgresqltutorial(dot)com');

[cid:image012(dot)jpg(at)01D7C682(dot)25FBB520]

[cid:image013(dot)jpg(at)01D7C682(dot)25FBB520]

1. Check the table definition as below. contact_bckp lost null option and default value compare to table contact.

[cid:image014(dot)jpg(at)01D7C682(dot)25FBB520]

[cid:image018(dot)jpg(at)01D7C682(dot)25FBB520]

Best Regards.
Benny
Email: bing(dot)xiao(at)dxc(dot)com<mailto:bing(dot)xiao(at)dxc(dot)com>