In this tutorial, we will appear at how to change a PostgreSQL desk and add a column.
To add a column to a table, we initially need to specify the “alter query” adopted by the table name.
We can convey the general syntax to incorporate a column as:
Alter Desk desk_name Add COLUMN IF NOT EXISTS column_title Knowledge_Variety
In the syntax represented previously mentioned, we start by contacting the Alter Table question adopted by the name of the table to which we want to insert the column. Upcoming, specify the column title immediately after the Insert COLUMN statement.
Despite the fact that the IF NOT EXISTS statement is optional, it can be a valuable way to prevent problems if a column with a comparable title exists.
At last, we specify the facts kind of the column and the column constraints.
You can also include various columns by specifying their title, information sort, and constraints a single immediately after a further (separated by commas).
Notice: PostgreSQL adds any new column to the finish of the desk as it does not have a approach of specifying column posture. That’s why, to develop a new column format in a PostgreSQL table, you have to recreate the new table or use a check out.
Let us insert a column to the city desk in the sakila database.
Alter Table town Insert COLUMN city_code VARCHAR(20)
The previously mentioned question appends the column metropolis_code to the metropolis desk. You can verify by working with the decide on statement as:
Select * from metropolis Restrict 5
As shown in the screenshot under, the higher than query must return the records in the city table with the new column:
To insert several columns simultaneously, specify the column values in a listing as:
Add COLUMN IF NOT EXISTS inhabitants SERIAL NOT NULL,
Add COLUMN IF NOT EXISTS streets VARCHAR(255),
Add COLUMN other INT
The over question should really include 3 columns sequentially as specified. The get of appending the columns to the desk is as specified.
For example, in the previously mentioned query, the populace column will come before the streets.
In the earlier illustrations, all the columns additional contains NULL values. To specify a placeholder worth, we can use the DEFAULT search phrase.
Contemplate the city code column. Let us start off by dropping the column.
Change Table town Drop COLUMN town_code
Up coming, append the column with the Incorporate COLUMN search phrase the default price is in the query under:
Note: The metropolis_code column will be at the stop of the desk.
ALTERTABLE metropolis ADDCOLUMNcity_codeVARCHAR(20) DEFAULT ‘AA’
In the earlier mentioned illustration, PostgreSQL will use the specified default benefit to populate the values of the town_code column.
Working with the specified default benefit can be useful when the column has a NOT NULL constraint.
Including a column can consider any supported PostgreSQL knowledge kind. For case in point, the next consists of a column of Boolean values.
Alter Table town Insert COLUMN IF NOT EXISTS is_active BOOLEAN NOT NULL DEFAULT real
Note: Considering the fact that PostgreSQL will add NULL values to a column if no default values are specified, it is good to insert a default worth. If not, PostgreSQL will return an mistake!
For example, we can develop a desk as:
Alter Table city Add COLUMN IF NOT EXISTS no_null BOOLEAN NOT NULL
In this situation, we will get an SQL Condition 23502.
Mistake: column “no_null” of relation “city” includes null values
SQL state: 23502
SQL Condition 23502 indicates that the procedure (UPDATE, Set OR INSERT is NULL but object does not accept NULL values).
This tutorial has walked you by way of how to to use and perform with the Incorporate COLUMN query in PostgreSQL, making it possible for you to insert columns to an existing table.