Обсуждение: 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.
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.
- PostgreSQL version.
- 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
);
- 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');
- Create back up table.
create table contact_bckp as table contacts with data;
- 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');
- 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');
- 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.
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.
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.