Обсуждение: Null option and Default value Lost when use CREATE TABLE AS to backup a table.

Поиск
Список
Период
Сортировка

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

От
"Xiao, Bing (Benny)"
Дата:

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.

 

 

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

      ('David','William','david.william@postgresqltutorial.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.doe@postgresqltutorial.com');

 

 

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

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



Вложения

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

От
Vik Fearing
Дата:
On 10/21/21 7:47 AM, Xiao, Bing (Benny) wrote:
>
>   1.  Create back up table.
> 
>                create table contact_bckp as table contacts with data;

This is not the correct way to do this.  Do it this way instead:

BEGIN;
CREATE TABLE contact_bckp (LIKE contacts INCLUDING ALL);
INSERT INTO contact_bckp TABLE contacts;
COMMIT;
-- 
Vik Fearing

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

От
Francisco Olarte
Дата:
Seems like wrong command used.
On Thu, 21 Oct 2021 at 12:18, Xiao, Bing (Benny) <bing.xiao@dxc.com> wrote:
> Create back up table.
>                create table contact_bckp as table contacts with data;

"Create table as" needs a select query. "table contacts" is just a
fancy way of saying "select * from contacts". Create table as uses a
query result to create, query results do not have constraints. It is
easier to see why if you replace "table contacts" with its
"expansion".

From the two different commands ( quoting the manual ).

CREATE TABLE — define a new table
CREATE TABLE AS — define a new table from the results of a query

You are using the second, you need the first, look specifically at the
LIKE options:

LIKE source_table [ like_option ... ]
The LIKE clause specifies a table from which the new table
automatically copies all column names, their data types, and their
not-null constraints.

And then copy the data with an insert/select ( I do not remember now
if you can do it in one step, I always do it in two ).

So, IMHO, not a bug, working as it should.

Francisco Olarte.