Movatterモバイル変換

[0]ホーム
URL:

You don’t need a vector database - just use Postgres for everything. Read the case study on switching from Pinecone to Neon
PostgreSQL Tutorial
PostgreSQL Tutorial
/Getting Started/Add Column

PostgreSQL ADD COLUMN: Add One or More Columns to a Table

PostgreSQL Add ColumnSummary: in this tutorial, you will learn how to use the PostgreSQLADD COLUMN statement to add one or more columns to an existing table.

Introduction to the PostgreSQL ADD COLUMN statement

To add a new column to an existing table, you use theALTER TABLE ADD COLUMN statement as follows:

ALTER TABLE table_nameADD COLUMN new_column_name data_type constraint;

In this syntax:

  • First, specify the name of the table to which you want to add a new column after theALTER TABLE keyword.
  • Second, specify the name of the new column as well as its data type and constraint after theADD COLUMN keywords.

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

To add multiple columns to an existing table, you use multipleADD COLUMN clauses in theALTER TABLE statement as follows:

ALTER TABLE table_nameADD COLUMN column_name1 data_typeconstraint,ADD COLUMN column_name2 data_typeconstraint,...ADD COLUMN column_namen data_typeconstraint;

PostgreSQL ADD COLUMN statement examples

Let’s take some examples of using theALTER TABLE...ADD COLUMN statement.

Creating a sample table

The followingCREATE TABLE statement creates a new table namedcustomers with two columns:id andcustomer_name:

DROP TABLE IF EXISTS customers CASCADE;CREATE TABLE customers (    idSERIAL PRIMARY KEY,    customer_nameVARCHAR(255)NOT NULL);

1) Adding a new column to a table

First, add thephone column to thecustomers table using theALTER TABLE...ADD COLUMN statement:

ALTER TABLE customersADD COLUMN phoneVARCHAR(25);

Second, view thecustomers table in psql:

\d customers

Output:

Table "public.customers"    Column     |          Type          | Collation | Nullable |                Default---------------+------------------------+-----------+----------+--------------------------------------- id            | integer                |           | not null | nextval('customers_id_seq'::regclass) customer_name | character varying(255)|           | not null | phone         | character varying(25)|           |          |Indexes:    "customers_pkey" PRIMARY KEY, btree (id)

2) Adding multiple columns to a table

First, add thefax andemail columns to thecustomers table:

ALTER TABLE customersADD COLUMN faxVARCHAR(25),ADD COLUMN emailVARCHAR(400);

Second, view the structure of thecustomers table inpsql:

\d customers

Output:

Table "public.customers"    Column     |          Type          | Collation | Nullable |                Default---------------+------------------------+-----------+----------+--------------------------------------- id            | integer                |           | not null | nextval('customers_id_seq'::regclass) customer_name | character varying(255) |           | not null | phone         | character varying(25)  |           |          | fax           | character varying(25)  |           |          | email         | character varying(400) |           |          |Indexes:    "customers_pkey" PRIMARY KEY, btree (id)

The output shows thefax andemail columns were added to thecustomers table.

3) Adding a column with a NOT NULL constraint to a table that already has data

First,insert data into the customers table:

INSERT INTO   customers (customer_name)VALUES   ('Apple'),   ('Samsung'),   ('Sony')RETURNING *;

Output:

id | customer_name | phone | fax  | email----+---------------+-------+------+-------  4 | Apple         | null  | null | null  5 | Samsung       | null  | null | null  6 | Sony          | null  | null | null(3 rows)INSERT 0 3

Second, attempt to add thecontact_name column to thecustomers table:

ALTER TABLE customersADD COLUMN contact_name VARCHAR(255)NOT NULL;

PostgreSQL issued an error:

ERROR:  column"contact_name" of relation"customers" containsnull values

This is because thecontact_name column has theNOT NULL constraint. When PostgreSQL added the column, this new column receivedNULL, which violates theNOT NULL constraint.

To address this issue, you can follow these steps:

First, add thecontact_name column without theNOT NULL constraint:

ALTER TABLE customersADD COLUMN contact_nameVARCHAR(255);

Second, update the values in thecontact_name column.

UPDATE customersSET contact_name= 'John Doe'WHERE id= 1;UPDATE customersSET contact_name= 'Mary Doe'WHERE id= 2;UPDATE customersSET contact_name= 'Lily Bush'WHERE id= 3;

If you have contact data from other tables, you can update the contact names in thecustomers table based on the data from those tables using theupdate join statement.

Third, modify thecontact_name column to add theNOT NULL constraint:

ALTER TABLE customersALTER COLUMN contact_nameSET NOT NULL;

Summary

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

Last updated on

Was this page helpful?
Thank you for your feedback!
[8]ページ先頭
©2009-2025 Movatter.jp