Home » PostgreSQL Tutorial » PostgreSQL ALTER TABLE ADD COLUMN Statement

PostgreSQL ALTER TABLE ADD COLUMN Statement

Summary: in this tutorial, you’ll learn how to use the PostgreSQLALTER TABLE ADD COLUMN statement to add a new column to a table.

Getting Started with the PostgreSQL ALTER TABLE ADD COLUMN statement #

Due to new requirements, you may need to add one or more columns to a table. In PostgreSQL, you can use the ALTER TABLE ... ADD COLUMN statement to do that.

Here’s the basic syntax of the statement:

ALTER TABLE table_name
ADD COLUMN new_column data_type constraint;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

  • First, provide the name of the table you want to add a column in the ALTER TABLE clause.
  • Second, specify the new column name, data type, and constraint in the ADD COLUMN clause.

The ALTER TABLE ... ADD COLUMN appends the new column at the end of the column list of the table.

PostgreSQL does not allow you to insert a new column at a specified position in the column list like MySQL. But there is a workaround that I will cover shortly.

If you want to add multiple columns at the same time, you can use multiple ADD COLUMN clauses:

ALTER TABLE table_name
ADD COLUMN new_column1 data_type constraint,
ADD COLUMN new_column2 data_type constraint,
ADD COLUMN new_column3 data_type constraint;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Creating a sample table #

First, open your terminal and connect to the PostgreSQL server using psql tool:

psql -U postgres -d inventoryCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, create a new table called vendors:

CREATE TABLE vendors (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, show the vendors table:

\d vendorsCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

                                Table "public.vendors"
 Column |          Type          | Collation | Nullable |           Default
--------+------------------------+-----------+----------+------------------------------
 id     | integer                |           | not null | generated always as identity
 name   | character varying(255) |           | not null |
Indexes:
    "vendors_pkey1" PRIMARY KEY, btree (id)
Code language: PHP (php)

Adding one column to a table #

First, add a new column called address to the vendors table:

ALTER TABLE vendors
ADD COLUMN address TEXT;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Second, show the table structure using the \d command:

\d vendorsCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

                                 Table "public.vendors"
 Column  |          Type          | Collation | Nullable |           Default
---------+------------------------+-----------+----------+------------------------------
 id      | integer                |           | not null | generated always as identity
 name    | character varying(255) |           | not null |
 address | text                   |           |          |
Indexes:
    "vendors_pkey1" PRIMARY KEY, btree (id)Code language: PHP (php)

The output shows the address column at the end of the column list of the vendors table.

Adding multiple columns to a table #

First, add two columns email and phone to the vendors table:

ALTER TABLE vendors
ADD COLUMN email VARCHAR(255) NOT NULL,
ADD COLUMN phone VARCHAR(25) NOT NULL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Second, display the structure of the vendors table:

\d vendorsCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

                                 Table "public.vendors"
 Column  |          Type          | Collation | Nullable |           Default
---------+------------------------+-----------+----------+------------------------------
 id      | integer                |           | not null | generated always as identity
 name    | character varying(255) |           | not null |
 address | text                   |           |          |
 email   | character varying(255) |           | not null |
 phone   | character varying(25)  |           | not null |
Indexes:
    "vendors_pkey1" PRIMARY KEY, btree (id)Code language: PHP (php)

The output shows the new email and phone columns at the end of the column list.

Adding a new column to a table with data #

First, insert three rows into the vendors table:

INSERT INTO
  vendors (name, address, email, phone)
VALUES
  (
    'Samsung',
    '129 Samsung-ro, Yeongtong-gu, Suwon-si, Gyeonggi-do, South Korea',
    '[email protected]',
    '+82-2-2255-0114'
  ),
  (
    'Apple',
    'One Apple Park Way, Cupertino, CA 95014, USA',
    '[email protected]',
    '+1-408-996-1010'
  ),
  (
    'Google',
    '1600 Amphitheatre Parkway, Mountain View, CA 94043, USA',
    '[email protected]',
    '+1-650-253-0000'
  );Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Second, add a website column to the vendors table with a NOT NULL constraint:

ALTER TABLE vendors
ADD COLUMN website VARCHAR NOT NULL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

PostgreSQL issues the following error:

ERROR: column "website" of relation "vendors" contains null valuesCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

When you add the website column, the default values of the column are NULL, which violates the NOT NULL constraint.

To make it work, you need to follow these steps:

  • Step 1. Add the website column without the NOT NULL constraint.
  • Step 2. Update values for the existing rows to ensure that the website column does not contain NULL.
  • Step 3. Add the NOT NULL constraint to the website column.

Third, add the website column without the NOT NULL constraint:

ALTER TABLE vendors
ADD COLUMN website VARCHAR;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Fourth, verify the change:

\d vendorsCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

                                 Table "public.vendors"
 Column  |          Type          | Collation | Nullable |           Default
---------+------------------------+-----------+----------+------------------------------
 id      | integer                |           | not null | generated always as identity
 name    | character varying(255) |           | not null |
 address | text                   |           |          |
 email   | character varying(255) |           | not null |
 phone   | character varying(25)  |           | not null |
 website | character varying      |           |          |
Indexes:
    "vendors_pkey1" PRIMARY KEY, btree (id)Code language: PHP (php)

Fifth, update the values in the website column for all rows:

UPDATE vendors
SET
  website = 'https://www.samsung.com'
WHERE
  name = 'Samsung';

UPDATE vendors
SET
  website = 'https://www.apple.com'
WHERE
  name = 'Apple';

UPDATE vendors
SET
  website = 'https://www.google.com'
WHERE
  name = 'Google';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

If you have lots of data to update, you can write a script that reads data from an external source (an API, a CSV file, etc.) and loads it to the website column.

Sixth, add the NOT NULL constraint to the website column:

ALTER TABLE vendors
ALTER COLUMN website SET NOT NULL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Finally, verify the changes:

\d vendorsCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

                                 Table "public.vendors"
 Column  |          Type          | Collation | Nullable |           Default
---------+------------------------+-----------+----------+------------------------------
 id      | integer                |           | not null | generated always as identity
 name    | character varying(255) |           | not null |
 address | text                   |           |          |
 email   | character varying(255) |           | not null |
 phone   | character varying(25)  |           | not null |
 website | character varying      |           | not null |
Indexes:
    "vendors_pkey1" PRIMARY KEY, btree (id)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL ALTER TABLE ADD COLUMN – Adding a column at a specific position #

PostgreSQL does not support adding a new column at a specified position. Fortunately, you have a workaround:

  • Step 1. Rename the existing table to a new one.
  • Step 2. Recreate the table with the desired column order.
  • Step 3. Copy data from the old table to the new table.
  • Step 4. Drop the old table.

For example, we’ll add a contact_person after the phone column to the vendors table:

First, rename the vendors table:

ALTER TABLE vendors
RENAME TO vendors_copy;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Second, recreate the vendors table with the new contact_person column after the phone column:

CREATE TABLE vendors (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  address TEXT,
  email VARCHAR(255) NOT NULL,
  phone VARCHAR(25) NOT NULL,
  contact_person VARCHAR(255),
  website VARCHAR NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Third, copy data from the vendors_copy to the vendors table:

INSERT INTO
  vendors (
    id,
    name,
    address,
    email,
    phone,
    contact_person,
    website
  ) 
OVERRIDING SYSTEM VALUE
SELECT
  id,
  name,
  address,
  email,
  phone,
  NULL,
  website
FROM
  vendors_copy;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

The clause OVERRIDING SYSTEM VALUE allows insert values into the identity column with the GENERATED ALWAYS AS IDENTITY constraint.

The contact_person will have NULL as the default value.

Fifth, verify the column layout of the vendors table:

\d vendorsCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

                                    Table "public.vendors"
     Column     |          Type          | Collation | Nullable |           Default
----------------+------------------------+-----------+----------+------------------------------
 id             | integer                |           | not null | generated always as identity
 name           | character varying(255) |           | not null |
 address        | text                   |           |          |
 email          | character varying(255) |           | not null |
 phone          | character varying(25)  |           | not null |
 contact_person | character varying(255) |           |          |
 website        | character varying      |           | not null |
Indexes:
    "vendors_pkey" PRIMARY KEY, btree (id)Code language: PHP (php)

Finally, drop the old table vendors_copy:

DROP TABLE vendors_copy;

Try it

Using a migration library #

When you want to add a column to a table in the production database, you should use a migration library so that your code and database structure go to the production simultaneously. If you change the table structure before changing our code, the app may not work as expected.

For example, if you have an API that inserts data into a table. The table has a new NOT NULL column, but the API does not require it. Other systems using API can pass the validation check in the application layer but fail when it reaches the database.

Summary #

  • Use ALTER TABLE ... ADD COLUMN statement to add one or more columns to a table.

Quiz #

Was this tutorial helpful ?
PostgreSQL ALTER TABLE Statement
Previously
PostgreSQL ALTER TABLE Statement
Up Next
PostgreSQL RENAME TABLE
PostgreSQL RENAME TABLE