Basic PostgreSQL commands and PostgreSQL commands

Basic PostgreSQL commands and PostgreSQL commands

1. Change the table name

Alter table name rename to new table name

2. Change the field name

Alter table name rename field name to new field name

3. Change the field type.

For example, the original type of the ID field is character varying (50) and the new type is integer.

Where, the ID contains numbers such as 1, 2, and 3.

Use the following statement to change

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

To add a field, run the following command:

ALTER TABLE products ADD COLUMN description text;

The new field will first fill in the given DEFAULT value for existing rows in the table (if you do not declare the DEFAULT clause, the DEFAULT value is NULL ).

You can also define constraints on the field and use the common syntax:

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

In fact, all the options described in create table that can be applied to fields can be used here. However, we should note that the default value must meet the given constraints, otherwise the ADD operation will fail. In addition, you can add constraints after the values of the new fields are correctly filled (see the following section ).

Tip: adding a field and filling in the default value will update all rows in the table (to store the value of the new field). However, if the default value is not specified, PostgreSQL can avoid physical updates. Therefore, if most of the values to be filled in the new field are not equal to the default value, it is best to add a field without the default value and then UPDATE the data using UPDATE, finally, use the following method to add the default value.

To delete a field, run the following command:

ALTER TABLE products DROP COLUMN description;

No matter what data is in the field, it will disappear and the constraints related to this field will also be deleted. However, if this field is referenced by the foreign key of another table, PostgreSQL will not implicitly Delete this constraint. You can use CASCADE to specify to delete anything that depends on this field:

ALTER TABLE products DROP COLUMN description CASCADE;

See section 5.11 to obtain information about the mechanisms behind these operations.

To add a constraint, you must use the table constraint syntax. For example:

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-empty 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 meet the constraint before adding the constraint.

To delete a constraint, you need to know its name. If you give it a name, it's easy. Otherwise, you need to find the name automatically assigned by the system. The psql command \ d tablename can help you with this; Other interfaces may also provide methods to check the details of the table. Then, run the following command:

ALTER TABLE products DROP CONSTRAINT some_name;

If you are processing a generated constraint name, such as $2, do not forget to add double quotation marks to it to make it a valid identifier.

Like deleting a field, if you want to delete the dependent constraint, you need to use CASCADE. In one example, a foreign key constraint depends on the unique constraint or primary key constraint on the referenced field.

Except for non-null constraints, all constraint types are used in this way. To delete a non-empty constraint, you can:

ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

Remember that the non-empty constraint has no name.

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

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

Note that this does not affect the existing data rows in any table. It only changes the default value for future INSERT commands.

To delete the default value, you can use

ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

This is equivalent to setting the default value to null. The result is that it is not an error to delete a default value that has not been defined, because the default implicit value is NULL.

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

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

Only when each existing item in the field can implicitly convert the new type of the city can it be successful. If you need more complex conversions, you can add a USING clause that declares how to calculate new values from the old values.

PostgreSQL will try to convert the default value (if any) of a field to a new type, and there are any constraints related to this field. However, these conversions may fail or may generate strange results. Before modifying a field type, you 'd better delete those constraints and then add the appropriate constraints.

Rename a field:

ALTER TABLE products RENAME COLUMN product_no TO product_number;

Rename a table:

ALTER TABLE products RENAME TO items;