Gice

Technology and General Blog

Immediately after generating databases and populating them with details, you will rarely want to change the databases composition. The recurrent thing you will do is to increase or retrieve documents saved in the database. On the other hand, there are uncommon situations in which you may well need to have to increase a new column and populate it with facts.

In this tutorial, we will appear at how to change a PostgreSQL desk and add a column.

Fundamental Usage

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.

Examples

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:

Alter Desk city

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 Desk metropolis Drop COLUMN no_null

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).

Conclusion

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.

Leave a Reply

Your email address will not be published. Required fields are marked *