---
description: The ALTER TABLE command is used to alter the structure of a PostgreSQL table. It is the command used to change the table columns or the name of the table. In this tutorial, you will learn: Syntax Desc
title: PostgreSQL ALTER TABLE: Add & Rename Column
image: https://www.guru99.com/images/postgresql-alter-table.png
---

 

[Skip to content](#main) 

**⚡ Smart Summary**

PostgreSQL ALTER TABLE changes the structure of an existing table, letting you add or drop columns, rename a column or the whole table, set default values, and attach check constraints without recreating the object.

* 🧱 **Syntax:** ALTER TABLE table-name action performs one structural change per statement.
* ➕ **Add Column:** ADD column-name data-type appends a new column to the table.
* ✏️ **Rename:** RENAME COLUMN and RENAME TO change a column name or the table name.
* ⚙️ **Default Value:** ALTER COLUMN SET DEFAULT supplies a value for future INSERT rows.
* 🛡️ **Check Constraint:** ADD CHECK validates new data and rejects values that fail the rule.
* 🤖 **AI Migrations:** AI assistants draft ALTER TABLE scripts and review them for safe deployment.

[ Read More ](javascript:void%280%29;) 

![PostgreSQL ALTER TABLE]()

## What is ALTER TABLE in PostgreSQL?

The **ALTER TABLE** command is used to alter the structure of a PostgreSQL table. It is the command used to change the table columns or the name of the table itself, without dropping and recreating the object.

## Syntax

Here is the syntax for the PostgreSQL ALTER TABLE command:

ALTER TABLE table-name action;

The table-name parameter is the name of the table that you need to change. The action parameter is the action that you need to perform, such as changing the name of a column or changing the data type of a column.

## Description

The ALTER TABLE command changes the definition of an existing table. It takes the following subforms:

* **ADD COLUMN**: this uses similar syntax as the CREATE TABLE command to add a new column to a table.
* **DROP COLUMN**: for dropping a table column. The constraints and indexes imposed on the columns will also be dropped.
* **SET/DROP DEFAULT**: used for setting or removing the default value for a column. The change only applies to subsequent [INSERT statements](https://www.guru99.com/postgresql-insert.html).
* **SET/DROP NOT NULL**: changes whether a column will allow nulls or not.
* **SET STATISTICS**: for setting the statistics-gathering target for each column for ANALYZE operations.
* **SET STORAGE**: for setting the mode of storage for a column, whether inline or in a supplementary table.
* **SET WITHOUT OIDS**: used for removing the old OID column of the table.
* **RENAME**: for changing the table name or a column name.
* **ADD table\_constraint**: used for adding a new constraint to a table. It uses the same syntax as the [CREATE TABLE](https://www.guru99.com/create-drop-table-postgresql.html) command.
* **DROP CONSTRAINT**: used for dropping a table constraint.
* **OWNER**: for changing the owner of a table, sequence, index, or view to a certain user.
* **CLUSTER**: for marking a table to be used for future cluster operations.

## Modifying a Column

A column may be modified in a number of ways. Such modifications are done using the ALTER TABLE command. Let us discuss these below.

### Adding a New Column

To add a new column to a PostgreSQL table, the ALTER TABLE command is used with the following syntax:

ALTER TABLE table-name
  ADD new-column-name column-definition;

The table-name is the name of the table to be modified. The new-column-name is the name of the new column to be added. The column-definition is the [data type](https://www.guru99.com/postgresql-data-types.html) of the new column. See the Book table shown below:

[](https://www.guru99.com/images/1/111119%5F0953%5FPostgreSQLA1.png)

The table has two columns, id and name. We need to add a new column to the table and give it the name author. Just run the following command:

ALTER TABLE Book
  ADD author VARCHAR(50);

After running the above command, the Book table is now as follows:

[](https://www.guru99.com/images/1/111119%5F0953%5FPostgreSQLA2.png)

The new column was added successfully.

### Renaming a Table Column

We can use the ALTER TABLE command to change the name of a column. In this case, the command is used with the following syntax:

ALTER TABLE table-name
  RENAME COLUMN old-name TO new-name;

The table-name is the name of the table whose column is to be renamed. The old-name is the current name of the column, and the new-name is the new name of the column. Consider the table Book shown below:

**Book:**

[](https://www.guru99.com/images/1/111119%5F0953%5FPostgreSQLA3.png)

We need to change the name of the column author to book\_author. Here is the command:

ALTER TABLE Book
  RENAME COLUMN author TO book_author;

After running the command, we can view the structure of the table:

[](https://www.guru99.com/images/1/111119%5F0953%5FPostgreSQLA4.png)

The column name was changed successfully.

### RELATED ARTICLES

* [What is PostgreSQL? Advantages & Disadvantages ](https://www.guru99.com/introduction-postgresql.html "What is PostgreSQL? Advantages & Disadvantages")
* [How to Create & Drop Table in PostgreSQL (Examples) ](https://www.guru99.com/create-drop-table-postgresql.html "How to Create & Drop Table in PostgreSQL (Examples)")
* [PostgreSQL Create View with Example ](https://www.guru99.com/postgresql-view.html "PostgreSQL Create View with Example")
* [PostgreSQL Constraints: Types with Example ](https://www.guru99.com/postgresql-constraints.html "PostgreSQL Constraints: Types with Example")

## Setting a Default Value for a Column

We can set a default value for a column so that even when you do not specify a value for that column during INSERT operations, the default value will be used. In this case, the ALTER TABLE command can be used with the following syntax:

ALTER TABLE table-name ALTER COLUMN column-name [SET DEFAULT value];

The table-name is the name of the table whose column is to be modified. The column-name is the name of the column whose default value is to be set, and the value is the default value for the column. Consider the Book table given below:

[](https://www.guru99.com/images/1/111119%5F0953%5FPostgreSQLA5.png)

We need to set a default value for the book\_author column. We can run the following command:

ALTER TABLE Book ALTER COLUMN book_author SET DEFAULT 'Nicholas Samuel';

Now, let us insert a row into the table:

INSERT INTO Book (id, name)
 VALUES (6, 'PostgreSQL for Beginners');

Note that we inserted values for only two columns, id and name. However, the default value has been used for the book\_author column:

[](https://www.guru99.com/images/1/111119%5F0953%5FPostgreSQLA6.png)

## Adding a Check Constraint

A check constraint helps in validating the records that are being inserted into a table. We can do this by combining the ALTER TABLE command with the ADD CHECK statement. Syntax:

ALTER TABLE table-name ADD CHECK expression;

The table-name is the name of the table to be altered, and the expression is the constraint to be imposed on the table column. Let us modify the book\_author column of the Book table so that it only accepts the values Nicholas and Samuel:

ALTER TABLE Book ADD CHECK (book_author IN ('Nicholas', 'Samuel'));

Now, let us try to insert a value other than Nicholas or Samuel into the book\_author column of the Book table:

INSERT INTO Book
VALUES(7, 'Best PostgreSQL Book', 'Gregory Bush');

The statement will return the following error:

[](https://www.guru99.com/images/1/111119%5F0953%5FPostgreSQLA7.png)

The insert operation failed because we violated the check constraint.

## Renaming a Table

Here is the syntax for the ALTER TABLE command for renaming a table:

ALTER TABLE table-name
  RENAME TO new-table-name;

The table-name is the current name of the table, and the new-table-name is the new name to be assigned to the table. For example, let us change the name of the Book table to Books:

ALTER TABLE Book
  RENAME TO Books;

## Using pgAdmin

So far the actions have been run from the SQL shell. Now let us see how these same actions can be performed visually using pgAdmin.

### Adding a New Column

**Step 1)** Login to your pgAdmin account.

**Step 2)**

1. From the navigation bar on the left, click Databases.
2. Click Demo.

[](https://www.guru99.com/images/1/111119%5F0953%5FPostgreSQLA8.png)

**Step 3)** Type the query in the query editor:

ALTER TABLE Book
  ADD author VARCHAR(50);

**Step 4)** Click the Execute button.

[](https://www.guru99.com/images/1/111119%5F0953%5FPostgreSQLA9.png)

**Step 5)** To check whether the column was added, do the following:

1. Click Databases from the left navigation.
2. Expand Demo.
3. Expand Schemas.
4. Expand Public.
5. Expand Tables.
6. Expand book.
7. Expand Columns.

[](https://www.guru99.com/images/1/111119%5F0953%5FPostgreSQLA10.png)

The column should have been added, as shown below:

[](https://www.guru99.com/images/1/111119%5F0953%5FPostgreSQLA11.png)

### Renaming a Table Column

**Step 1)** Login to your pgAdmin account.

**Step 2)**

1. From the navigation bar on the left, click Databases.
2. Click Demo.

[](https://www.guru99.com/images/1/111119%5F0953%5FPostgreSQLA12.png)

**Step 3)** Type the query in the query editor:

ALTER TABLE Book
  RENAME COLUMN author TO book_author;

**Step 4)** Click the Execute button.

[](https://www.guru99.com/images/1/111119%5F0953%5FPostgreSQLA13.png)

**Step 5)** To check whether the change was successful, expand the book table columns as before:

[](https://www.guru99.com/images/1/111119%5F0953%5FPostgreSQLA14.png)

The columns should now be as follows:

[](https://www.guru99.com/images/1/111119%5F0953%5FPostgreSQLA15.png)

The column was changed successfully.

### Setting a Default Value for a Column

**Step 1)** Login to your pgAdmin account.

**Step 2)**

1. From the navigation bar on the left, click Databases.
2. Click Demo.

[](https://www.guru99.com/images/1/111119%5F0953%5FPostgreSQLA16.png)

**Step 3)** Type the query in the query editor:

ALTER TABLE Book ALTER COLUMN book_author SET DEFAULT 'Nicholas Samuel';

**Step 4)** Click the Execute button.

[](https://www.guru99.com/images/1/111119%5F0953%5FPostgreSQLA17.png)

**Step 5)** To test, run the following command in the query editor:

INSERT INTO Book (id, name)
 VALUES (6, 'PostgreSQL for Beginners')

**Step 6)** Now, we can query the table to check whether the default value was inserted in the book\_author column:

[](https://www.guru99.com/images/1/111119%5F0953%5FPostgreSQLA18.png)

### Adding a Check Constraint

**Step 1)** Login to your pgAdmin account.

**Step 2)**

1. From the navigation bar on the left, click Databases.
2. Click Demo.

[](https://www.guru99.com/images/1/111119%5F0953%5FPostgreSQLA19.png)

**Step 3)** Type the query in the query editor:

ALTER TABLE Book ADD CHECK (book_author IN ('Nicholas', 'Samuel'))

**Step 4)** Click the Execute button.

[](https://www.guru99.com/images/1/111119%5F0953%5FPostgreSQLA20.png)

**Step 5)** To test this, type the following query in the query editor and click the Execute button:

INSERT INTO Book
VALUES(7, 'Best PostgreSQL Book', 'Gregory Bush');

[](https://www.guru99.com/images/1/111119%5F0953%5FPostgreSQLA21.png)

It will return the following:

[](https://www.guru99.com/images/1/111119%5F0953%5FPostgreSQLA22.png)

### Renaming a Table

**Step 1)** Login to your pgAdmin account.

**Step 2)**

1. From the navigation bar on the left, click Databases.
2. Click Demo.

[](https://www.guru99.com/images/1/111119%5F0953%5FPostgreSQLA23.png)

**Step 3)** Type the query in the query editor:

ALTER TABLE Book
  RENAME TO Books;

**Step 4)** Click the Execute button.

[](https://www.guru99.com/images/1/111119%5F0953%5FPostgreSQLA24.png)

**Step 5)** To check whether the table was renamed, expand Databases > Demo > Schemas > Public > Tables:

[](https://www.guru99.com/images/1/111119%5F0953%5FPostgreSQLA25.png)

The table was renamed successfully.

[ Download the Database used in this Tutorial](https://drive.google.com/uc?export=download&id=1BqQSvYTmNz4hvZok7iYG9ha%5Fo48%5FVF2b)

## FAQs

🔧 How do I change a column’s data type in PostgreSQL?

Use ALTER TABLE table\_name ALTER COLUMN column\_name TYPE new\_type. Add a USING clause when values need conversion, for example ALTER COLUMN price TYPE numeric USING price::numeric. PostgreSQL rewrites the column and checks that existing data fits the new type.

🔒 Does adding a column with a default lock the whole table?

Only if the default is volatile. Since PostgreSQL 11, ADD COLUMN with a constant default updates the catalog instantly without rewriting rows. A default that calls a function still forces a full table rewrite and a stronger lock.

♻️ Can I modify an existing constraint directly?

No. PostgreSQL cannot alter a constraint in place. Drop the existing one with DROP CONSTRAINT, then add the corrected one with ADD CONSTRAINT. Wrap both statements in a transaction so the table is never left without the rule.

⏱️ How do I add multiple columns in one ALTER TABLE statement?

List several actions separated by commas: ALTER TABLE Book ADD COLUMN price int, ADD COLUMN isbn text. PostgreSQL applies every change in a single pass, which is faster and keeps the table consistent.

🔑 How do I add a primary key to an existing table?

Use ALTER TABLE table\_name ADD PRIMARY KEY (column) or ADD CONSTRAINT pk\_name PRIMARY KEY (column). The column must hold unique, non-null values; PostgreSQL builds a supporting unique index automatically when the key is created.

📛 Why does renaming a table not break its data?

RENAME only updates the table’s name in the system catalog. The stored rows, indexes, and constraints stay in place and are unaffected. Views or functions that reference the old name may still need updating afterward.

🤖 How does AI help write PostgreSQL ALTER TABLE migrations?

AI assistants draft ALTER TABLE scripts from a described change, order ADD, RENAME, and constraint steps correctly, and add USING clauses for type casts. They also flag operations that rewrite large tables or hold heavy locks.

🧠 Can an AI Copilot review schema changes for safety?

Yes. An AI Copilot reviews a migration, warns about locking or full-table rewrites, suggests batching or concurrent index builds, and recommends wrapping changes in a transaction, making schema updates safer to deploy.

#### Summarize this post with:

ChatGPT Perplexity Grok Google AI 

**Stay Updated on AI** **Get Weekly AI Skills, Trends, Actionable Advice.** 

##### Sign up for the newsletter

Subscribe for Free 

You have successfully subscribed.  
Please check your inbox. 

![AI-Newsletter]() Chosen by over **350,000+** professionals 

[Scroll to top ](#wrapper)Scroll to top 

× 

Toggle Menu Close 

Search for: 

Search

```json
{"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://www.guru99.com/#organization","name":"Guru99","sameAs":["https://www.facebook.com/Guru99Official","https://twitter.com/guru99com"],"logo":{"@type":"ImageObject","@id":"https://www.guru99.com/#logo","url":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","contentUrl":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","caption":"Guru99","inLanguage":"en-US"}},{"@type":"WebSite","@id":"https://www.guru99.com/#website","url":"https://www.guru99.com","name":"Guru99","publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US"},{"@type":"ImageObject","@id":"https://www.guru99.com/images/postgresql-alter-table.png","url":"https://www.guru99.com/images/postgresql-alter-table.png","width":"700","height":"250","caption":"PostgreSQL ALTER TABLE","inLanguage":"en-US"},{"@type":"BreadcrumbList","@id":"https://www.guru99.com/postgresql-alter-add-rename-column-table.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":"1","item":{"@id":"https://www.guru99.com","name":"Home"}},{"@type":"ListItem","position":"2","item":{"@id":"https://www.guru99.com/postgresql","name":"PostgreSQL"}},{"@type":"ListItem","position":"3","item":{"@id":"https://www.guru99.com/postgresql-alter-add-rename-column-table.html","name":"PostgreSQL ALTER TABLE: Add & Rename Column"}}]},{"@type":"WebPage","@id":"https://www.guru99.com/postgresql-alter-add-rename-column-table.html#webpage","url":"https://www.guru99.com/postgresql-alter-add-rename-column-table.html","name":"PostgreSQL ALTER TABLE: Add & Rename Column","dateModified":"2026-07-02T12:04:04+05:30","isPartOf":{"@id":"https://www.guru99.com/#website"},"primaryImageOfPage":{"@id":"https://www.guru99.com/images/postgresql-alter-table.png"},"inLanguage":"en-US","breadcrumb":{"@id":"https://www.guru99.com/postgresql-alter-add-rename-column-table.html#breadcrumb"}},{"@type":"Person","@id":"https://www.guru99.com/author/juniper","name":"Juniper Willow","description":"I am Juniper Willow, a PostgreSQL Developer, offering expert guidance to help you optimize and master PostgreSQL database development.","url":"https://www.guru99.com/author/juniper","image":{"@type":"ImageObject","@id":"https://www.guru99.com/images/juniper-willow-author.png","url":"https://www.guru99.com/images/juniper-willow-author.png","caption":"Juniper Willow","inLanguage":"en-US"},"worksFor":{"@id":"https://www.guru99.com/#organization"}},{"@type":"NewsArticle","headline":"PostgreSQL ALTER TABLE: Add & Rename Column","keywords":"postgresql, sql","dateModified":"2026-07-02T12:04:04+05:30","articleSection":"PostgreSQL","author":{"@id":"https://www.guru99.com/author/juniper","name":"Juniper Willow"},"publisher":{"@id":"https://www.guru99.com/#organization"},"description":"The ALTER TABLE command is used to alter the structure of a PostgreSQL table. It is the command used to change the table columns or the name of the table. In this tutorial, you will learn: Syntax Desc","copyrightYear":"2026","copyrightHolder":{"@id":"https://www.guru99.com/#organization"},"name":"PostgreSQL ALTER TABLE: Add & Rename Column","@id":"https://www.guru99.com/postgresql-alter-add-rename-column-table.html#richSnippet","isPartOf":{"@id":"https://www.guru99.com/postgresql-alter-add-rename-column-table.html#webpage"},"image":{"@id":"https://www.guru99.com/images/postgresql-alter-table.png"},"inLanguage":"en-US","mainEntityOfPage":{"@id":"https://www.guru99.com/postgresql-alter-add-rename-column-table.html#webpage"}}]}
```