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.

2. 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

);

3. Insert the test data to table Contact.

INSERT INTO contact(first_name, last_name, email)

VALUES('John','Doe','john.doe@postgresqltutorial.com'),

      ('David','William','david.william@postgresqltutorial.com');

4. Create back up table.

               create table contact_bckp as table contacts with data;

5. 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.doe@postgresqltutorial.com');

6. 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.doe@postgresqltutorial.com');

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

Best Regards.

Benny

Email:  bing.xiao@dxc.com