Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://neon.com/postgresql/postgresql-tutorial/postgresql-alter-table below:

PostgreSQL ALTER TABLE

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

To change the structure of an existing table, you use PostgreSQL ALTER TABLE statement.

The following illustrates the basic syntax of the ALTER TABLE statement:

ALTER TABLE table_name action;

PostgreSQL provides you with many actions:

• Add a column
• Drop a column
• Change the data type of a column
• Rename a column
• Set a default value for the column
• Add a constraint to a column.
• Rename a table

To add a new column to a table, you use ALTER TABLE ADD COLUMN statement:

ALTER TABLE table_name
ADD COLUMN column_name datatype column_constraint;

To drop a column from a table, you use ALTER TABLE DROP COLUMN statement:

ALTER TABLE table_name
DROP COLUMN column_name;

To rename a column, you use the [ALTER TABLE RENAME COLUMN](postgresql-rename-column) TO statement:

ALTER TABLE table_name
RENAME COLUMN column_name
TO new_column_name;

To change a default value of the column, you use ALTER TABLE ALTER COLUMN SET DEFAULT or  DROP DEFAULT:

ALTER TABLE table_name
ALTER COLUMN column_name
[SET DEFAULT value | DROP DEFAULT];

To change the NOT NULL constraint, you use ALTER TABLE ALTER COLUMN statement:

ALTER TABLE table_name
ALTER COLUMN column_name
[SET NOT NULL| DROP NOT NULL];

To add a CHECK constraint, you use ALTER TABLE ADD CHECK statement:

ALTER TABLE table_name
ADD CHECK expression;

Generally, to add a constraint to a table, you use ALTER TABLE ADD CONSTRAINT statement:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_definition;

To rename a table you use ALTER TABLE RENAME TO statement:

ALTER TABLE table_name
RENAME TO new_table_name;

Let’s create a new table called links for practicing with the ALTER TABLE statement.

DROP TABLE IF EXISTS links;

CREATE TABLE links (
   link_id serial PRIMARY KEY,
   title VARCHAR (512) NOT NULL,
   url VARCHAR (1024) NOT NULL
);

To add a new column named active, you use the following statement:

ALTER TABLE links
ADD COLUMN active boolean;

The following statement removes the activecolumn from the linkstable:

ALTER TABLE links
DROP COLUMN active;

To change the name of the title column to link_title, you use the following statement:

ALTER TABLE links
RENAME COLUMN title TO link_title;

The following statement adds a new column named targetto the linkstable:

ALTER TABLE links
ADD COLUMN target VARCHAR(10);

To set _blank as the default value for the targetcolumn in the linkstable, you use the following statement:

ALTER TABLE links
ALTER COLUMN target
SET DEFAULT '_blank';

If you insert the new row into the links table without specifying a value for the target column, the target column will take the _blank as the default value. For example:

INSERT INTO links (link_title, url)
VALUES('PostgreSQL Tutorial','https://neon.com/postgresql/');

The following statement selects data from the links table:

SELECT * FROM links;

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
ADD CHECK (target IN ('_self', '_blank', '_parent', '_top'));

If you attempt to insert a new row that violates the CHECK constraint set for the targetcolumn, PostgreSQL will issue an error as shown in the following example:

INSERT INTO links(link_title,url,target)
VALUES('PostgreSQL','http://www.postgresql.org/','whatever');

ERROR:  new row for relation "links" violates check constraint "links_target_check"
DETAIL:  Failing row contains (2, PostgreSQL, http://www.postgresql.org/, whatever).DETAIL:  Failing row contains (2, PostgreSQL, http://www.postgresql.org/, whatever).

The following statement adds a UNIQUE constraint to the url column of the links table:

ALTER TABLE links
ADD CONSTRAINT unique_url UNIQUE ( url );

The following statement attempts to insert the url that already exists:

INSERT INTO links(link_title,url)
VALUES('PostgreSQL','https://neon.com/postgresql/');

It causes an error due to the unique_url constraint:

ERROR:  duplicate key value violates unique constraint "unique_url"
DETAIL:  Key (url)=(/postgresql/) already exists.

The following statement changes the name of the links table to urls:

ALTER TABLE links
RENAME TO urls;

In this tutorial, you have learned how to use the PostgreSQL ALTER TABLE statement to change the structure of an existing table.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4