PostgreSQL ADD COLUMN + 17 Examples

In this PostgreSQL tutorial, we will discuss, about PostgreSQL ADD COLUMN statement to add one or more columns to an existing table and will cover the following topic:

• PostgreSQL ADD COLUMN
• How to ADD COLUMN after another column in PostgreSQL
• PostgreSQL ADD COLUMN at position
• How to ADD COLUMN with default value in PostgreSQL
• PostgreSQL ADD COLUMN integer default value
• PostgreSQL ADD COLUMN boolean
• How to ADD COLUMN float in PostgreSQL
• PostgreSQL ADD COLUMN bigint
• How to add calculated column in a table in PostgreSQL
• PostgreSQL ADD COLUMN datetime
• PostgreSQL ADD COLUMN IF NOT EXISTS
• PostgreSQL add multiple columns if not exists
• PostgreSQL ADD COLUMN varchar length
• How to ADD COLUMN with NOT NULL constraint in PostgreSQL
• PostgreSQL ADD COLUMN PRIMARY KEY autoincrement
• How to ADD COLUMN with FOREIGN KEY constraint in PostgreSQL
• PostgreSQL ADD COLUMN check constraint

PostgreSQL ADD COLUMN

To add a new column to an existing table in PostgreSQL, you can use the ALTER TABLE ADD COLUMN statement. The syntax is as follows:

ALTER TABLE table_name
ADD COLUMN new_column_name data_type constraint;

In the above syntax,

• table_name specifies the name of the table where you want to add a new column.
• new_column_name specifies the name of the new column to be added in the table.
• data_type specifies the data type for the new column’s values.
• constraint specifies the constraint for the new column.

NOTE – When you add a new column to the table, PostgreSQL appends it at the end of the table. There is no option to specify the position of the new column in the table in PostgreSQL.

Example:

CREATE TABLE client_data (
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(50),
gender VARCHAR(50)
);

INSERT INTO client_data (first_name, last_name, email, gender) VALUES ('Morlee', 'Niblett', 'mniblett0@gnu.org', 'Female');
INSERT INTO client_data (first_name, last_name, email, gender) VALUES ('Ebony', 'Shorbrook', 'eshorbrook1@weibo.com', 'Female');
INSERT INTO client_data (first_name, last_name, email, gender) VALUES ('Ross', 'Mardoll', 'rmardoll2@google.pl', 'Female');
INSERT INTO client_data (first_name, last_name, email, gender) VALUES ('Silvana', 'Forbes', 'sforbes3@skype.com', 'Genderqueer');
INSERT INTO client_data (first_name, last_name, email, gender) VALUES ('Allissa', 'Antoshin', 'aantoshin4@miibeian.gov.cn', 'Bigender');
INSERT INTO client_data (first_name, last_name, email, gender) VALUES ('Stafani', 'Guitt', 'sguitt5@blogger.com', 'Bigender');
INSERT INTO client_data (first_name, last_name, email, gender) VALUES ('Brenden', 'Duley', 'bduley6@sina.com', 'Agender');
INSERT INTO client_data (first_name, last_name, email, gender) VALUES ('Derby', 'Lusk', 'dlusk7@free.fr', 'Non-binary');
INSERT INTO client_data (first_name, last_name, email, gender) VALUES ('Lucky', 'Mulles', 'lmulles8@elpais.com', 'Female');
INSERT INTO client_data (first_name, last_name, email, gender) VALUES ('Warren', 'Masi', 'wmasi9@state.gov', 'Female');

\d

\d client_data

SELECT *
FROM client_data;

ALTER TABLE client_data
ADD COLUMN address VARCHAR(60);

SELECT *
FROM client_data;

Read PostgreSQL vs SQL Server: Detailed Comparison

PostgreSQL ADD COLUMN after another column

PostgreSQL also provides a way to add column after column (multiple columns) in one go. And to do so, you have to use multiple ADD COLUMN clauses in the ALTER TABLE statement, the syntax is as follows:

ALTER TABLE table_name
ADD COLUMN column_name1 data_type constraint,
ADD COLUMN column_name2 data_type constraint,
...
...
...
ADD COLUMN column_nameN data_type constraint;

In the above syntax,

N number of columns with column_names – column_name1, column2, …, columnN are added with their respective data types and constraints in one query/statement.

Example:

\d client_data

ALTER TABLE client_data
ADD COLUMN address VARCHAR(60),
ADD COLUMN city VARCHAR(30),
ADD COLUMN country VARCHAR(30),
ADD COLUMN zip VARCHAR(10);


\d client_data

This is how to add a column after another column in PostgreSQL.

PostgreSQL ADD COLUMN at position

When a new column is added to the table, PostgreSQL appends it at the end of the table. PostgreSQL has no option to set the position of the new column in the table.

To create a new column in another position in PostgreSQL, you need to recreate the table and copy the data from the old table in this new table.

Read How to create a table in PostgreSQL [Terminal + pgAdmin]

PostgreSQL ADD COLUMN with default value

PostgreSQL facilitates the method of adding a new column with a default value to each row at the time of the ADD COLUMN in ALTER TABLE statement, the syntax for the same is as follows:

ALTER TABLE table_name
ADD COLUMN column_name data_type constraint DEFAULT default_value;

In the above syntax,

The default_value can be any constant value or any expression resulting in some constant, if not specified the default_value is NULL.

Example:

\d client_data

ALTER TABLE client_data
ADD COLUMN activity VARCHAR(20) NOT NULL DEFAULT 'active';

\d client_data

SELECT * FROM client_data;

This is how to add a default value while adding a column in PostgreSQL.

Read PostgreSQL ALTER TABLE

PostgreSQL ADD COLUMN integer default value

To add a new column with a default integer value in PostgreSQL, you can follow the below syntax:

ALTER TABLE table_name
ADD COLUMN column_name integer constraint DEFAULT default_value;

In the above syntax, the default_value can be any constant value or any expression resulting in some integer constant.

Example:

\d client_data

ALTER TABLE client_data
ADD COLUMN pack integer NOT NULL DEFAULT 0;

\d client_data

SELECT * FROM client_data;

This is how to add a column with a default integer value in PostgreSQL.

Read How to connect to PostgreSQL database

PostgreSQL ADD COLUMN of different TYPES

Let us see a few examples of how to add columns of different types like boolean, float, bigint, etc. to a PostgreSQL table.

PostgreSQL ADD COLUMN boolean

You can add a boolean column to an existing table in PostgreSQL, as follows:

ALTER TABLE table_name
ADD COLUMN column_name BOOLEAN;

Example:

\d client_data

ALTER TABLE client_data
ADD COLUMN employed BOOLEAN;

\d client_data

You can also set a default value for the new column in a separate statement:

ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT FALSE;

The above statement will set FALSE as the DEFAULT value for the column_name

Example:

ALTER TABLE client_data
ALTER COLUMN employed SET DEFAULT FALSE;

\d client_data

Now, Update the constraint of this new column to NOT NULL:

ALTER TABLE table_name
ALTER COLUMN column_name SET NOT NULL;

The above statement will make the column_name take non-null values.

NOTE – You can also combine the above statements all in a single statement as given below, but it might take longer if the operation is huge:

ALTER TABLE table_name
ADD COLUMN column_name BOOLEAN DEFAULT FALSE;

Example:

\d client_data

ALTER TABLE client_data
ADD COLUMN employed BOOLEAN DEFAULT FALSE;

\d client_data

SELECT * FROM client_data;

This is how to add a boolean value column to PostgreSQL table.

Read How to Uninstall PostgreSQL (Linux, Mac, and Windows)

PostgreSQL ADD COLUMN float

You can add a float column to an existing table in PostgreSQL, as follows:

ALTER TABLE table_name
ADD COLUMN column_name FLOAT CONSTRAINT;

Example:

\d client_data

ALTER TABLE client_data
ADD COLUMN pack_price float DEFAULT 270.38;

\d client_data

This is how to add a column of type float in PostgreSQL table.

PostgreSQL ADD COLUMN bigint

You can add a float column to an existing table in PostgreSQL, as follows:

ALTER TABLE table_name
ADD COLUMN column_name BIGINT;

Example:

\d client_data

ALTER TABLE client_data
ADD COLUMN phone bigint;

\d client_data

This is how to add a bigint type column in a table in PostgreSQL.

Read How to Restart PostgreSQL (Linux, Windows, Mac)

PostgreSQL add calculated column

PostgreSQL uses the term ‘Generated‘ columns for Calculated/Computed columns. The value of the column is always calculated from other columns in the table. A generated column can either be virtual or stored.

• The values for virtual columns are calculated during query time and they do not take storage space.
• The values for stored columns are pre-computed and stored as part of table data.

You can achieve, creating generated columns in PostgreSQL 12.x and newer versions by using GENERATED ALWAYS AS clause, and can then add that generated column to an existing table. The syntax for the same is as follows:

ALTER TABLE table_name
ADD COLUMN column_name data_type GENERATED ALWAYS AS (generation_expression)
 STORED;

In the above syntax, generation_expression is the expression used to define a generated column.

You can see, it looks a bit similar to the DEFAULT clause, so let’s discuss the difference between a Generated Column and a Regular Column with a DEFAULT clause?

1. The column generated by DEFAULT constraint is evaluated once when the row is first inserted if no other value was provided, while a generated column is updated whenever the row changes and cannot be overridden.
2. A column with DEFAULT constraint can be given a value in an INSERT or UPDATE statement. Generated columns cannot be given values, they’re always computed/calculated.
3. A column default cannot refer to other columns of the table, whereas a generated columns is specifically has this purpose.
4. A column default can use volatile functions, like, random() or current_time, while generated columns cannot.

Example:

\d client_data

ALTER TABLE client_data
ADD COLUMN full_name VARCHAR(101) GENERATED ALWAYS AS
(CASE WHEN first_name IS NULL THEN last_name
WHEN last_name IS NULL THEN first_name
ELSE first_name || ' ' || last_name END) STORED;

\d client_data

In PostgreSQL 11.x and older versions, there are two ways to achieve this:

Use a View: In this approach, the table will not have the new column generated_column_name. The view is used wherever generated_column_name is needed. You can create a View in PostgreSQL as follows:

CREATE VIEW view_name AS
SELECT [column_name1, column_name2, ...], expression as generated_column_name
FROM table_name;

In the above syntax,

• view_name specifies the name of the view.
• column_name1, column_name2, … are the name of the columns of the table_name which are needed in the expression evaluvation for the generated column.
• generated_column_name is the name of the generated column.
• table_name is the name of the table for which you are creating generated column as a view

Example:

\d

CREATE VIEW client_view AS
SELECT date_of_birth, date_part('year', NOW()) - date_part('year', date_of_birth) as age
FROM client_data;

\d

SELECT * FROM client_data;

SELECT * FROM client_view;

Use a Normal Column and Update It With a Trigger: In this approach, the new column column_name is added as a regular column in the table and a trigger is used to populate the column_name during INSERT and UPDATE operations on the table. The steps are as follow:

• Add a regular column to the table:

ALTER TABLE table_name
ADD COLUMN column_name data_type constraint;

• Create a Trigger, that will populate the column_name according to the expression, whenever any INSERT or UPDATE operation is performed:

CREATE OR REPLACE FUNCTION function_name() RETURNS TRIGGER AS $body$
BEGIN
  NEW.column_name := expression;
  RETURN NEW;
END;
$body$ LANGUAGE plpgsql;

Example:

\d client_data

ALTER TABLE client_data
ADD COLUMN age integer;

CREATE OR REPLACE FUNCTION age_calculation() RETURNS TRIGGER AS $body$
BEGIN
NEW.age := date_part('year', NOW()) - date_part('year', date_of_birth);
RETURN NEW;
END;
$body$ LANGUAGE plpgsql;

The drawback to this approach is the additional maintenance overhead of creating and maintaining views/triggers and remembering to update them when logic changes.

This is how to add a calculated column in PostgreSQL table.

Read PostgreSQL WHERE with examples

PostgreSQL ADD COLUMN datetime

To add a column that can consist of DateTime, you just need to specify the type to timestamp, you can also add the time zone by writing with time zone, after the timestamp. The syntax is as follows:

ALTER TABLE table_name
ADD COLUMN column_name timestamp [with time zone] CONSTRAINT;

Example:

SELECT * FROM client_data;

ALTER TABLE client_data
ADD COLUMN entry_date_time timestamp with time zone DEFAULT NOW();

SELECT * FROM client_data;

This is how to add datetime column to a table in PostgreSQL.

Read PostgreSQL TO_NUMBER() function

PostgreSQL ADD COLUMN IF NOT EXISTS

In PostgreSQL, you will encounter an error if you try to add a column with a name that already exists. And to avoid this error you can use the IF NOT EXISTS option with your ADD COLUMN clause.

This option makes PostgreSQL add the new column only if the column name does not exist in the table. The syntax to do so is as follows:

ALTER TABLE table_name
ADD COLUMN IF NOT EXISTS column_name data_type constraint;

In the above syntax,

The IF NOT EXISTS option will check if the given column name already exists in the table irrespective of the data type or constraint. By applying this option, no error will be returned if you try to add a column that already exists, but it skips the ALTER TABLE statement and just show a message alerting you that a column with the same name already exists.

Example:

\d client_data

ALTER TABLE client_data
ADD COLUMN first_name VARCHAR(30);

ALTER TABLE client_data
ADD COLUMN IF NOT EXISTS first_name VARCHAR(30);

This is how to add a column if not exists in PostgreSQL table.

Read PostgreSQL CASE with Examples

PostgreSQL add multiple columns if not exists

You can also add multiple columns with IF NOT EXISTS keyword by writing comma separated ADD COLUMN clauses. The syntax is as follows:

ALTER TABLE table_name
ADD COLUMN IF NOT EXISTS column_name1 data_type constraint,
ADD COLUMN IF NOT EXISTS column_name2 data_type constraint,
...
...
...
ADD COLUMN IF NOT EXISTS column_nameN data_type constraint;

Example:

\d client_data

ALTER TABLE client_data
ADD COLUMN IF NOT EXISTS first_name VARCHAR(30),
ADD COLUMN IF NOT EXISTS city VARCHAR(30),
ADD COLUMN IF NOT EXISTS age integer;

\d client_data

This is how to add multiple columns if not exists in PostgreSQL.

Read PostgreSQL TO_CHAR() function

PostgreSQL ADD COLUMN varchar length

In PostgreSQL, you can increase the length of a varchar type column without losing any data. You can also decrease the length of the varchar but only when you already know or have checked that, in the column, there is not any row with a length greater than the new length. The syntax is as follows:

ALTER TABLE table_name
ALTER COLUMN column_name TYPE varchar(n);

In the above syntax,

The statement will extend or narrow down the varchar column field size to n, if n > previous length or n < previous length respectively.

Example:

\d client_data

ALTER TABLE client_data
ALTER COLUMN gender TYPE varchar(20);

\d client_data

ALTER TABLE client_data
ALTER COLUMN gender TYPE varchar(10);

\d client_data

This is how to add a varchar column with length in PostgreSQL.

Read PostgreSQL WHERE IN with examples

PostgreSQL ADD COLUMN with NOT NULL constraint

You can also add a column with the NOT NULL constraint to an existing table in PostgreSQL. The Syntax is as follows:

ALTER TABLE table_name 
ADD COLUMN column_name data_type NOT NULL;

But the above statement will lead you to an error if the existing table already has data (rows), as the new column added has the NOT NULL constraint. When PostgreSQL added the column, this new column receives NULL, which violates the NOT NULL constraint.

Example:

\d client_data

ALTER TABLE client_data
ADD COLUMN phone VARCHAR(10) NOT NULL;    -- Gives an Error

You can solve this problem by adding the column without the NOT NULL constraint first, updating the values in the newly added column, and then setting the NOT NULL constraint to that column. The syntax for doing it is as follows:

ALTER TABLE table_name
ADD COLUMN column_name data_type;

ALTER TABLE table_name
ALTER COLUMN column_name SET NOT NULL;

The above syntax will update the column_name column’s constraint to NOT NULL.

Example:

\d client_data

ALTER TABLE client_data
ADD COLUMN phone VARCHAR(10);

\d client_data

UPDATE client_data
SET phone = '-';

ALTER TABLE client_data
ALTER COLUMN phone SET NOT NULL;

\d client_data

This is how to ADD COLUMN with NOT NULL constraint in PostgreSQL.

Read PostgreSQL DATE Format + Examples

PostgreSQL ADD COLUMN PRIMARY KEY autoincrement

In PostgreSQL, you can add a column with auto-increment PRIMARY KEY constraint by using the SERIAL keyword before PRIMARY KEY in the ADD COLUMN statement. The syntax for the same is given below:

ALTER TABLE table_name
ADD COLUMN column_name SERIAL PRIMARY KEY;

The above syntax will add a new column with the name column_name with auto-increment PRIMARY KEY constraint.

Now, let’s take a look at the keyword SERIAL:

• It is a pseudo-type, to define auto-increment columns in tables in PostgreSQL.
• It actually creates a Sequence. A sequence is a special kind of database object that generates a sequence of integers in PostgreSQL.

By assigning the SERIAL pseudo-type to any column, PostgreSQL performs the following:

• Firstly, it will create a sequence object and set the next value generated by the sequence as the default value for the column.
• Then, it will add a NOT NULL constraint to that column because a sequence always generates an integer, which is not a null value.
• At last, it will assign that column as the owner of the sequence, as to make the sequence object get deleted itself when that column or table is dropped.

Example:

\d client_data

ALTER TABLE client_data
ADD COLUMN client_data SERIAL PRIMARY KEY;

\d client_data

SELECT * FROM client_data;

You can also add a new column to the existing table with auto-increment PRIMARY KEY constraint using a conventional method (not using SERIAL keyword). The steps are as follows:

CREATE SEQUENCE sequence_object_name;

ALTER TABLE table_name
ADD COLUMN column_name integer PRIMARY KEY DEFAULT nextval('sequence_object_name');

ALTER SEQUENCE sequence_object_name
OWNED BY table_name.column_name;

In the above syntax,

• Firstly, a sequence object is created with the name sequence_object_name by using CREATE SEQUENCE statement.
• Then, a new column column_name of integer TYPE is added to the existing table table_name with PRIMARY KEY constraint and DEFAULT as the sequence object created before by using the method nextval().
• And at last, the ownership of the sequence object gets changed to the column_name column of the table_name table.

Example:

\d client_data

CREATE SEQUENCE id_sequence;

ALTER TABLE client_data
ADD COLUMN client_id integer PRIMARY KEY DEFAULT nextval('id_sequence');

ALTER SEQUENCE id_sequence
OWNED BY client_data.client_id;

\d client_data

This is how to add a primary key column in PostgreSQL table.

Read PostgreSQL CREATE INDEX

PostgreSQL ADD COLUMN with FOREIGN KEY constraint

In PostgreSQL, you can add a column with the FOREIGN KEY constraint by using the REFERENCES keyword in the ADD COLUMN statement. The syntax for the same is given below:

ALTER TABLE table_name1
ADD COLUMN column_name1 data_type constraint 
REFERENCES table_name2; 

--or REFERENCES table_name2(column_name2);

In the above syntax,

• table_name1 is the name of the table where you want to add a new column.
• column_name1 is the name of the new column added to the table_name1 table.
• table_name2 is the name of the table whose primary key column you want to reference (make foreign key column) in the table_name1.
• column_name2 in the alternative statement is the name of the PRIMARY KEY column in the table_name2.

Note – You can use any of the above REFERENCE statements, either specifying the column name or not. As PostgreSQL has auto naming and primary-key resolution (if only the table-name is specified then you’re referencing the primary key), there is no issue with both.

Example:

\d client_data

\d project_data

ALTER TABLE client_data
ADD COLUMN project_id integer REFERENCES project_data;

\d client_data

You can also add the FOREIGN KEY constraint after adding the column as shown in the following table constraint syntax:

ALTER TABLE table_name1
ADD COLUMN column_name1 data_type;

ALTER TABLE table_name1 
ADD FOREIGN KEY (column_name1) REFERENCES table_name2;

In the above syntax, all the names are following the same convention as in the previous method.

Example:

\d client_data

\d project_data

ALTER TABLE client_data
ADD COLUMN project_id integer;

ALTER TABLE client_data
ADD FOREIGN KEY (project_id) REFERENCES project_data;

\d client_data

This is how to add a column with the FOREIGN KEY constraint in PostgreSQL.

PostgreSQL ADD COLUMN check constraint

A CHECK constraint is a constraint that allows you to specify if values in a column meet a specific requirement. It uses a boolean expression to estimate the values before they are inserted or updated to the column.

If the values passed the check, PostgreSQL will INSERT or UPDATE these values to the column. Otherwise, PostgreSQL will reject the changes and lead to a constraint violation error.

You can use the CHECK constraint in the ALTER TABLE statement by adding a CHECK constraint and providing a boolean expression to the CHECK. The syntax is as follow:

ALTER TABLE table_name 
ADD CONSTRAINT constraint_name 
CHECK (
boolean_expression);

In the above syntax,

The constraint_name is the name given to the added constraint to the columns. If you are applying the CHECK constraint while creating a table then, it is not necessary to give the name to the CHECK constraint because, by default, PostgreSQL gives the CHECK constraint a name using the pattern: {table_name}_{column_name}_check.

Example:

\d client_data

ALTER TABLE client_data
ADD CONSTRAINT check_phone
CHECK (
phone not like '%[^0-9]%');

\d client_data

The CHECK constraints are very useful, as they provide additional logic to restrict values that the columns can accept at the database layer. By using the CHECK constraint, you can ensure that the data is updated to the database correctly.

This is how to check constraints in the PostgreSQL table.

In this PostgreSQL tutorial, we have learned about the PostgreSQL ADD COLUMN statement to add one or more columns to an existing table and have covered the following topic:

• PostgreSQL ADD COLUMN
• PostgreSQL ADD COLUMN after another column
• PostgreSQL ADD COLUMN at position
• PostgreSQL ADD COLUMN with default value
• PostgreSQL ADD COLUMN integer default value
• PostgreSQL ADD COLUMN boolean
• PostgreSQL ADD COLUMN float
• PostgreSQL ADD COLUMN bigint
• PostgreSQL add calculated column
• PostgreSQL ADD COLUMN datetime
• PostgreSQL ADD COLUMN IF NOT EXISTS
• PostgreSQL add multiple columns if not exists
• PostgreSQL ADD COLUMN varchar length
• PostgreSQL ADD COLUMN with NOT NULL constraint
• PostgreSQL ADD COLUMN PRIMARY KEY autoincrement
• PostgreSQL ADD COLUMN with FOREIGN KEY constraint
• PostgreSQL ADD COLUMN check constraint

For a better understanding of the topic watch my YouTube video: