Скачать или смотреть How to Alter a Table in PostgreSQL

Learn how to use PostgreSQL's ALTER TABLE command to modify existing table structures, including adding, dropping, and altering columns, as well as other table adjustments.
---
Disclaimer/Disclosure: Some of the content was synthetically produced using various Generative AI (artificial intelligence) tools; so, there may be inaccuracies or misleading information present in the video. Please consider this before relying on the content to make any decisions or take any actions etc. If you still have any concerns, please feel free to write them in a comment. Thank you.
---
In PostgreSQL, altering a table involves modifying its structure, such as adding or removing columns, changing column data types, or adjusting constraints. The ALTER TABLE command in PostgreSQL provides a flexible way to make these changes efficiently. Let's explore some common operations for altering a table in PostgreSQL.

Adding a Column

To add a new column to an existing table, use the following syntax:

[[See Video to Reveal this Text or Code Snippet]]

For example, to add a new column named email of type varchar(100) to a table named users, you would use:

[[See Video to Reveal this Text or Code Snippet]]

Dropping a Column

To remove an existing column from a table, use the DROP COLUMN clause:

[[See Video to Reveal this Text or Code Snippet]]

For instance, to drop the email column from the users table:

[[See Video to Reveal this Text or Code Snippet]]

Modifying a Column

To change the data type of an existing column, use ALTER TABLE with the ALTER COLUMN clause:

[[See Video to Reveal this Text or Code Snippet]]

For example, to change the data type of the age column in the users table from integer to smallint:

[[See Video to Reveal this Text or Code Snippet]]

Adding Constraints

You can add constraints (like NOT NULL, UNIQUE, CHECK, etc.) to an existing column using the ADD CONSTRAINT clause within ALTER TABLE:

[[See Video to Reveal this Text or Code Snippet]]

Here's an example of adding a NOT NULL constraint to the name column in the users table:

[[See Video to Reveal this Text or Code Snippet]]

Renaming a Column

To rename an existing column, use the RENAME COLUMN clause:

[[See Video to Reveal this Text or Code Snippet]]

For instance, to rename the dob column to date_of_birth in the users table:

[[See Video to Reveal this Text or Code Snippet]]

Renaming a Table

To rename an entire table, use the RENAME TO clause:

[[See Video to Reveal this Text or Code Snippet]]

For example, to rename the users table to customers:

[[See Video to Reveal this Text or Code Snippet]]

These are some of the fundamental ways to alter a table in PostgreSQL using SQL commands. Always remember to review your changes before executing them in a production environment to avoid unintended consequences.