#Postgresql alter table change data type how to#
#Postgresql alter table change data type code#

The following statement adds a CHECKcondition to the targetcolumn so that the targetcolumn only accepts the following values: _self, _blank, _parent, and _top: ALTER TABLE linksĪDD CHECK (target IN ( '_self', '_blank', '_parent', '_top')) Code language: SQL (Structured Query Language) ( sql ) The following statement selects data from the links table: SELECT * FROM links Code language: SQL (Structured Query Language) ( sql ) VALUES( 'PostgreSQL Tutorial', '') Code language: SQL (Structured Query Language) ( sql ) For example: INSERT INTO links (link_title, url) If you insert the new row into the links table without specifying a value for the target column, the targetcolumn will take the _blank as the default value. SET DEFAULT '_blank' Code language: SQL (Structured Query Language) ( sql ) To set _blank as the default value for the targetcolumn in the linkstable, you use the following statement: ALTER TABLE links The following statement adds a new column named targetto the linkstable: ALTER TABLE linksĪDD COLUMN target VARCHAR( 10) Code language: SQL (Structured Query Language) ( sql ) RENAME COLUMN title TO link_title Code language: SQL (Structured Query Language) ( sql ) To change the name of the title column to link_title, you use the following statement: ALTER TABLE links

The following statement removes the activecolumn from the linkstable: ALTER TABLE linksĭROP COLUMN active Code language: SQL (Structured Query Language) ( sql ) To add a new column named active, you use the following statement: ALTER TABLE linksĪDD COLUMN active boolean Code language: SQL (Structured Query Language) ( sql ) ) Code language: SQL (Structured Query Language) ( sql ) Let’s create a new table called links for practicing with the ALTER TABLE statement. RENAME TO new_table_name Code language: SQL (Structured Query Language) ( sql ) PostgreSQL ALTER TABLE examples To rename a table you use ALTER TABLE RENAME TO statement: ALTER TABLE table_name Generailly, to add a constraint to a table, you use ALTER TABLE ADD CONSTRAINT statement: ALTER TABLE table_nameĪDD CONSTRAINT constraint_name constraint_definition Code language: SQL (Structured Query Language) ( sql ) To add a CHECK constraint, you use ALTER TABLE ADD CHECK statement: ALTER TABLE table_nameĪDD CHECK expression Code language: SQL (Structured Query Language) ( sql ) To change the NOT NULL constraint, you use ALTER TABLE ALTER COLUMN statement: ALTER TABLE table_name Code language: SQL (Structured Query Language) ( sql ) To change a default value of the column, you use ALTER TABLE ALTER COLUMN SET DEFAULT or DROP DEFAULT: ALTER TABLE table_name TO new_column_name Code language: SQL (Structured Query Language) ( sql ) To rename a column, you use the ALTER TABLE RENAME COLUMN TO statement: ALTER TABLE table_name To drop a column from a table, you use ALTER TABLE DROP COLUMN statement: ALTER TABLE table_nameĭROP COLUMN column_name Code language: SQL (Structured Query Language) ( sql ) To add a new column to a table, you use ALTER TABLE ADD COLUMN statement: ALTER TABLE table_nameĪDD COLUMN column_name datatype column_constraint Code language: SQL (Structured Query Language) ( sql ) PostgreSQL provides you with many actions: The following illustrates the basic syntax of the ALTER TABLE statement: ALTER TABLE table_name action Code language: SQL (Structured Query Language) ( sql ) To change the structure of an existing table, you use PostgreSQL ALTER TABLE statement.

Introduction to PostgreSQL ALTER TABLE statement

#Postgresql alter table change data type how to#

Summary: in this tutorial, you will learn how to use the PostgreSQL ALTER TABLE statement to modify the structure of a table.