CODEWITHSUNDEEP
sqlpostgresql
YAM
YAM

Reputation: 494

how to add column while creating table using select query?

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

Upvotes: 1

Views: 5441

Answers (2)

Robins Tharakan
Robins Tharakan

Reputation: 2473

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;

Upvotes: 2

Saadi
Saadi

Reputation: 2237

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
)

Upvotes: 0

Related Questions