PostgreSQL basic Commands

1. Change the table name

alter table 表名 rename to 新表名

2. Change the field name

alter table 表名 rename 字段名 to 新字段名

3. Change the field type

For example: ID field Original type is character varying (50) new type is integer

Where the ID has data for the number of

Use the following statement to change

alter table dbo.titemtype alter column id type integer using to_number(id,‘9‘ ) ;

1. Add Field

To add a field, use a command such as the following:

ALTER TABLE products ADD COLUMN description text;

The new field is initially populated with the given default values for rows already present in the table (or NULL If you do not declare the default clause).

You can also define constraints on the field at the same time, using the usual syntax:

ALTER TABLE products ADD COLUMN description text CHECK (description <> ‘‘ ) ;

In fact, all the options described in CREATE TABLE that can be applied to a field can be used here. However, it is important to note that the default value must satisfy the given constraint, otherwise the add will fail. Additionally, you can add the constraint after the value of the new field is correctly populated (see below).

"Tip" adding a field and populating the default will cause all rows in the table to be updated (in order to store the value of the new field), but PostgreSQL avoids physical updates without specifying a default value. So if the value you're about to populate in the new field is mostly not equal to the default, then it's a good idea to add a field that doesn't have a default value, then update the data with update, and finally add the default value using the following method.

1. Delete a field

To delete a field, use the following command:

ALTER TABLE products DROP COLUMN description;

No matter what data is in the field, it disappears, and the constraint associated with the field is deleted. However, if this field is referenced by a foreign key in another table, PostgreSQL does not implicitly delete the constraint. You can use CASCADE to indicate that anything that depends on the field is deleted:

ALTER TABLE products DROP COLUMN description CASCADE;

See section 5.11 For information about the mechanisms behind these operations.

1. Add constraint

To add a constraint, you must use the table constraint syntax. Like what:

ALTER TABLE products ADD CHECK (name <> ‘‘ ) ;ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no) ;ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;

To add a non-null constraint that cannot be written as a table constraint, use the following syntax:

ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

This constraint is checked immediately, so the table must conform to the constraint before adding the constraint.

1. Delete Constraint

To delete a constraint, you need to know its name. If you've given it a name, it's easy. Otherwise you will need to find out the automatic name assigned by the system. Psql's command \d TableName can help with this; other interfaces may also provide a way to check the details of the table. And then this is the command:

ALTER TABLE products DROP CONSTRAINT some_name;

If you're dealing with a generated constraint name, like $ $, don't forget you need to add double quotes to it to make it a valid identifier.

As with the Delete field, if you want to delete a dependent constraint, you need to use CASCADE. An example is a FOREIGN key constraint that relies on a unique constraint or a PRIMARY KEY constraint on the referenced field.

All constraint types are used in addition to non-null constraints. To remove a non-empty constraint, you can:

ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

Remember that the non-null constraint has no name.

1. Change the default value of a field

To set a default value for a field, you can use a command such as the following:

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

Note that doing so does not affect existing rows of data in any table, it simply changes the default values for future INSERT commands.

To delete a default value, you can use the

ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

This is actually equivalent to setting the default to NULL. As a result, if we delete a default value that has not yet been defined, it is not an error, because the default implication is NULL.

1. Modify the data type of a field

To convert a field to another data type, use the following command:

ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2) ;

It is possible to succeed only if each existing item in the field can be implicitly converted to a new type of city. If you need a more complex conversion, you can add a USING clause that declares how to calculate the new value from the old value.

PostgreSQL will attempt to convert the default value of the field (if it exists) to a new type, as well as any constraints that involve that field. However, these conversions may fail, or may produce strange results. Before modifying a field type, you'd better delete those constraints, and then add the appropriate constraints.

1. Rename Field

Rename a field:

ALTER TABLE products RENAME COLUMN product_no TO product_number;

1. Renaming a table

To rename a table:

ALTER TABLE products RENAME TO items;

PostgreSQL basic Commands