Postgresql – CREATE TABLE LIKE another table but with additional columns

ddlpostgresql

Is there a way to create a table that has the same structure as another table, but with additional columns?
From this question, I can copy a table's structure with:

CREATE TABLE new_table_name ( like old_table_name including all)

And then I can add columns one at a time, with ALTER TABLE new_table_name ADD COLUMN... but I am looking for a way to do this more succinctly, like:

CREATE TABLE new_table_name ( like old_table_name including all), new_col1 new_col1_type, new_col2 new_col2_type,...

Best Answer

You can use the syntax below;

CREATE TABLE old_table_name (
  id serial,
  my_data text,
  primary key (id)
);

CREATE TABLE new_table_name ( 
  like old_table_name including all,
  new_col1 integer, 
  new_col2 text

);

The fiddle is here

Related Solutions

Postgresql – Does PostgreSQL optimize adding columns with non-NULL DEFAULTs

There is no such mechanism in PostgreSQL.

However, you can still avoid the excessive effects of such a table change.

The following statement acquires an access exclusive lock on the table for the duration of the statement/transaction:

ALTER TABLE your_table
    ADD COLUMN new_column integer NOT NULL DEFAULT 0;

This statement changes the catalog, then rewrites the whole table so that the new column contains the default value in all rows. If the table has many rows and being accessed frequently enough, this would cause some temporary problems.

To avoid it, try to hold the exclusive lock for as short as possible:

ALTER TABLE your_table
    ADD COLUMN new_column integer;
ALTER TABLE your_table
    ALTER COLUMN new_column SET DEFAULT 0;

As this is basically only a (actually two) change to the catalog (no data change happens), it will complete pretty fast. Then depending on your needs and table usage, you can update the new column to the default in one step or in batches, and when finished, set the column to NOT NULL.

Update about a wish coming true: PostgreSQL 11 will have this feature. See https://www.depesz.com/2018/04/04/waiting-for-postgresql-11-fast-alter-table-add-column-with-a-non-null-default/ for more.

Postgresql – How to CREATE TABLE … LIKE without the NOT NULL Constraints

This was asked on Stack Overflow in How to drop all NOT NULL constraints from a PostgreSQL table in one go. It appears to give a good range of solutions.

The accepted answer by Denis de Bernardy is:

You can group them all in the same alter statement:
   alter table tbl alter col1 drop not null,
                   alter col2 drop not null,
                   …
You can also retrieve the list of relevant columns from the catalog, if you feel like writing a do block to generate the needed SQL. For instance, something like:
select a.attname
  from pg_catalog.pg_attribute a
 where attrelid = 'tbl'::regclass
   and a.attnum > 0
   and not a.attisdropped
   and a.attnotnull;
(Note that this will include the primary key-related fields too, so you'll want to filter those out.)

If you do this, don't forget to use quote_ident() in the event you ever need to deal with potentially weird characters in column names.

Best Answer

Related Solutions

Postgresql – Does PostgreSQL optimize adding columns with non-NULL DEFAULTs

Postgresql – How to CREATE TABLE … LIKE without the NOT NULL Constraints

Related Question