How to add a column in PostgreSQL?

Member

by lizzie , in category: PHP Databases , 2 years ago

How to add a column in PostgreSQL?

Facebook Twitter LinkedIn Telegram Whatsapp

2 answers

Member

by denis , 2 years ago

@lizzie 

To add a column to a table in PostgreSQL, you can use the ALTER TABLE statement and the ADD COLUMN clause.


Here is the basic syntax:

1
2
ALTER TABLE table_name
ADD COLUMN column_name data_type;


For example, if you want to add a price column of type numeric to a table named products, you can use the following statement:

1
2
ALTER TABLE products
ADD COLUMN price numeric;


You can also specify additional options when adding a column, such as a default value or whether the column is nullable:

1
2
ALTER TABLE products
ADD COLUMN price numeric DEFAULT 0 NOT NULL;


This will add a price column to the products table with a default value of 0 and a NOT NULL constraint, which means that the column cannot contain a NULL value.


You can also use the ALTER TABLE statement to add multiple columns at once:

1
2
3
ALTER TABLE products
ADD COLUMN price numeric DEFAULT 0 NOT NULL,
ADD COLUMN quantity integer DEFAULT 0 NOT NULL;


This will add both a price and a quantity column to the products table.


Note that the ALTER TABLE statement is an optional part of the SQL standard and might not be supported by all database systems. The syntax and available options may vary depending on the database you are using.

by mallory_cormier , a year ago

@lizzie 

To add a column to an existing table in PostgreSQL, you can use the ALTER TABLE command with the ADD COLUMN clause. Here is the syntax:

1
2
ALTER TABLE table_name
ADD COLUMN column_name data_type;


Replace table_name with the name of the table to which you want to add the column. Replace column_name with the name of the new column, and data_type with the appropriate data type for the column.


For example, to add a column named email of type varchar to an existing table named users, you would use the following query:

1
2
ALTER TABLE users
ADD COLUMN email varchar;


After executing the above command, the new column will be added to the table.