how to add column while creating table using select query?

Question

I am creating table using following way:

CREATE TABLE test AS 
  SELECT    t1.column1, 
            t1.column2, 
            t1.column3, 

            t2.column4 , 
            t2.column6 , 
            t1.column8 
  FROM      table_1 t1
  JOIN table_2 t2 
  ON        t1.column1 = t2.column2 ;

after that I am adding column in newly created table using following alter command

ALTER TABLE test ADD COLUMN New_column1 varchar NOT NULL DEFAULT 0,
              ADD COLUMN New_column2    varchar NOT NULL DEFAULT 0, 
              ;

Now I want to merge both query into One.

How I can do that ?

Thank You

Add the new columns as literals to the Select (maybe you need to cast to get the correct data type). Btw, a varchar with a numeric default zero is strange... — dnoeth
– dnoeth, Commented Jan 11, 2016 at 8:09

Robins Tharakan · Accepted Answer · 2016-01-11 08:22:49Z

2

Although its difficult to set the NOT NULL constraint in a CREATE TABLE AS statement, you should still be able to create new columns in the new table in a single SQL statement.

However, you would require a second (Faster) SQL to set the NOT NULL for the two new columns.

SELECT 
    t1.column1, 
    t1.column2, 
    t1.column3, 
    t2.column4, 
    t2.column6, 
    t1.column8, 
    0 AS New_column1, 
    0 AS New_column2
  INTO test
FROM table_1 t1
JOIN table_2 t2 ON t1.column1 = t2.column2;

ALTER TABLE test ALTER COLUMN New_column1 SET DEFAULT NOT NULL;
ALTER TABLE test ALTER COLUMN New_column1 SET DEFAULT NOT NULL;

answered Jan 11, 2016 at 8:22

Robins Tharakan

2,52122 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jarlh · Accepted Answer · 2016-01-11 08:14:09Z

0

There multiple ways available:

1) This will create the table and insert data.

Select * into test from (
SELECT  t1.column1, 
        t1.column2, 
        t1.column3, 
        t2.column4 , 
        t2.column6 , 
        t1.column8 
FROM table_1 t1
JOIN table_2 t2 
ON   t1.column1 = t2.column2
)

edited Jan 11, 2016 at 8:14

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

answered Jan 11, 2016 at 8:12

Saadi

2,2376 gold badges24 silver badges53 bronze badges

3 Comments

user330315 Over a year ago

Using the non-standard select into is discouraged. It's better to use the standard create table as as shown in the question. This also doesn't answer the question on how to create a new column. Plus it will fail, because the derived table does not have an alias

Robins Tharakan Over a year ago

Thanks @a_horse_with_no_name. I didn't know that SELECT INTO was discouraged. Could you point to any references to that? Would consider that into account hereon if so. Thanks.

user330315 Over a year ago

From the manual: "The PostgreSQL usage of SELECT INTO to represent table creation is historical. It is best to use CREATE TABLE AS for this purpose in new code"

Collectives™ on Stack Overflow

how to add column while creating table using select query?

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related