From: | Francisco Olarte <folarte(at)peoplecall(dot)com> |
---|---|
To: | "Xiao, Bing (Benny)" <bing(dot)xiao(at)dxc(dot)com> |
Cc: | "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Null option and Default value Lost when use CREATE TABLE AS to backup a table. |
Date: | 2021-10-21 11:40:15 |
Message-ID: | CA+bJJbyYHqpDY+LXtxpQcz=ZHD7+-BhvtkRk-ppUs8wZ-n28vQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Seems like wrong command used.
On Thu, 21 Oct 2021 at 12:18, Xiao, Bing (Benny) <bing(dot)xiao(at)dxc(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | PG Bug reporting form | 2021-10-21 13:25:16 | BUG #17241: llvm::install_bad_alloc_error_handler error |
Previous Message | Vik Fearing | 2021-10-21 10:25:32 | Re: Null option and Default value Lost when use CREATE TABLE AS to backup a table. |