Example
Add a varchar column to the table:
ALTER TABLE distributors ADD COLUMN address varchar(30);
Delete a field from a table:
ALTER TABLE distributors DROP COLUMN address RESTRICT;
Modify the types of two existing fields in one operation:
ALTER TABLE distributors
ALTER COLUMN address TYPE varchar(80),
ALTER COLUMN name TYPE varchar(100);
Convert an integer field containing a UNIX timestamp to a timestamp with time zone field using a USING clause:
ALTER TABLE foo
ALTER COLUMN foo_timestamp TYPE timestamp with time zone
USING
timestamp with time zone ‘epoch’ + foo_timestamp * interval ‘1 second’;
Likewise, when a field has a default value expression that is not automatically converted to the new type:
ALTER TABLE foo
ALTER COLUMN foo_timestamp DROP DEFAULT,
ALTER COLUMN foo_timestamp TYPE timestamp with time zone
USING
timestamp with time zone ‘epoch’ + foo_timestamp * interval ‘1 second’,
ALTER COLUMN foo_timestamp SET DEFAULT now();
To rename an existing field:
ALTER TABLE distributors RENAME COLUMN address TO city;
To change the name of an existing table:
ALTER TABLE distributors RENAME TO suppliers;
Add a not-null constraint to a field:
ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
Remove a not-null constraint from a field:
ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
Add a check constraint to a table:
ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
Drop watch constraints on a table and all its child tables:
ALTER TABLE distributors DROP CONSTRAINT zipchk;
Add a foreign key constraint to the table:
ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) MATCH FULL;
Add a (multi-column) unique constraint to the table:
ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
Add an automatically named primary key constraint to a table. Note that a table can only have one primary key:
ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
Move the table to another tablespace:
ALTER TABLE distributors SET TABLESPACE fasttablespace;
Move the table to another schema:
ALTER TABLE myschema.distributors SET SCHEMA yourschema;
Compatibility
The ADD, DROP, SET DEFAULT forms are compatible with the SQL standard. Other forms are PostgreSQL extensions to the SQL standard. Also, declaring multiple operations in one ALTER TABLE command is also an extension.
ALTER TABLE DROP COLUMN can be used to drop only one column in a table, leaving a table with zero columns. This is an extension to SQL that does not allow zero-field tables.
altertable test alter columnm_name type columnm_type; 1
2. Convert the var_date field type varchar in the table alter_table to timestamp
Experimental steps:
1) Create a table
2) Insert data
3) Use the modify table field statement to perform field type conversion
The experimental results are shown in the figure
Getting error: error: cloumn “var_date” cannot be autmatically to type timestamp without time zone Hint: You might need to specify “using var_date::timestamp without time zone”
Check information:
The general meaning is: when there is an implicit type conversion when converting the type, it will be converted automatically. If not, then you must use using to explicitly specify the conversion rule.
Then we use such as alter table to successfully convert:
altertable alter_table alter var_date typetimestamp using var_date::timestamp without time zone 1
The result is as follows:
3. Fail two alter table
The above-mentioned explicit modification of the table fields, I thought that in this way, my official table could be modified successfully, but another problem occurred, as shown in the figure:
An error occurs when explicitly modifying the field type as follows:
After checking a lot of official documents, there is no such explanation. Later, after expert guidance, the data in the table may be of null type and cannot be converted. You can first convert the data in the table to timestamp type, and then use the alter statement to convert the data type. .
The experiment is as follows:
1) First look up the data in the field in the table, it really is “NULL”
2) Modify the data in the table to “timestamp” type
3) Explicitly modify the field type in the table to “timestamp” type
as the picture shows:
4. Change the varchar of id to int
postgres=# alter table tb101 alter idtypeint; ERROR: column "id" cannot be cast automatically totype integer HINT: Specify a USING expression to perform the conversion.
In the absence of implicit conversion, you need to specify the conversion using Using.
5. Use Using for type conversion
postgres=# altertable tb101 alter id type intusing id::int;
ALTERTABLE
postgres=# \d tb101
Table "public.tb101"
Column|Type|Modifiers
-------- + --------- + -----------
id |integer|
id::int can also use cast(id as int)