pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Null option and Default value Lost when use CREATE TABLE AS to backup a table.
3+ messages / 3 participants
[nested] [flat]

* Null option and Default value Lost when use CREATE TABLE AS to backup a table.
@ 2021-10-21 05:47  Xiao, Bing (Benny) <bing.xiao@dxc.com>
  0 siblings, 2 replies; 3+ messages in thread

From: Xiao, Bing (Benny) @ 2021-10-21 05:47 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

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.jpg@01D7C682.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.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');



[cid:image011.jpg@01D7C682.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.doe@postgresqltutorial.com');

[cid:image012.jpg@01D7C682.25FBB520]

[cid:image013.jpg@01D7C682.25FBB520]

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



[cid:image014.jpg@01D7C682.25FBB520]

[cid:image018.jpg@01D7C682.25FBB520]

Best Regards.
Benny
Email:  bing.xiao@dxc.com<mailto:bing.xiao@dxc.com>

Attachments:

image005.jpg (image/jpeg, 16.7K) download view image
image011.jpg (image/jpeg, 32.3K) download view image
image012.jpg (image/jpeg, 23.0K) download view image
image013.jpg (image/jpeg, 31.4K) download view image
image014.jpg (image/jpeg, 57.3K) download view image
image018.jpg (image/jpeg, 39.7K) download view image
^ permalink raw reply [nested|flat] 3+ messages in thread

* Re: Null option and Default value Lost when use CREATE TABLE AS to backup a table.
@ 2021-10-21 10:25  Vik Fearing <vik@postgresfriends.org>
  parent: Xiao, Bing (Benny) <bing.xiao@dxc.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Vik Fearing @ 2021-10-21 10:25 UTC (permalink / raw)
  To: Xiao, Bing (Benny) <bing.xiao@dxc.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Null option and Default value Lost when use CREATE TABLE AS to backup a table.
@ 2021-10-21 11:40  Francisco Olarte <folarte@peoplecall.com>
  parent: Xiao, Bing (Benny) <bing.xiao@dxc.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Francisco Olarte @ 2021-10-21 11:40 UTC (permalink / raw)
  To: Xiao, Bing (Benny) <bing.xiao@dxc.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2021-10-21 11:40 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-10-21 05:47 Null option and Default value Lost when use CREATE TABLE AS to backup a table. Xiao, Bing (Benny) <bing.xiao@dxc.com>
2021-10-21 10:25 ` Vik Fearing <vik@postgresfriends.org>
2021-10-21 11:40 ` Francisco Olarte <folarte@peoplecall.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox