CREATE table, ALTER table, DROP table, and table column additions, deletions, and column modifications

A, create table-a new table

CREATE [Temporary | TEMP] Table Table (
Column type
[NULL | Not NULL] [UNIQUE] [DEFAULT value]
[Column_constraint_clause | PRIMARY KEY} [...]]
[, ... ]
[, PRIMARY KEY (column [, ...])]
[, CHECK (condition)]
[, Table_constraint_clause]
) [INHERITS (Inherited_table [, ...])] Temporary This table is only created for this session and is automatically deleted at the end of the session. A permanent table with the same name is not visible when a temporary table exists. The name of the new table to be created by table. column/Field name. Type column/field type. This can include the declaration of the array. Refer to the PostgreSQL user's manual for more information about data types and arrays. Default value a column/field defaults. Please refer to the DEFAULT clause for more information. Column_constraint_clause an optional column/field constraint clause that declares a series of consolidated constraints and tests that must be met when an update or insert operation on a table is required to succeed. Each constraint must generate a Boolean. Although SQL92 requires Column_constraint_clause to specify a row, Postgres allows multiple columns to be indexed with a single column/field constraint. Please refer to the column constraint clause for more information. Table_constraint_clause an optional table (CONSTRAINT) constraint clause that declares a series of consolidated constraints that must be met when the table is updated or inserted. Each constraint must generate a Boolean expression. You can use the same constraint for more than one column. A table can declare only one PRIMARY key clause; PRIMARY key column (table constraint) and PRIMARY key (column/field constraint) are mutually exclusive. Please refer to the table constraint clause for more information. INHERITS inherited_table an optional (inherited) INHERITS clause declares a list of table names that will automatically inherit all fields from those tables.  If any inherited fields appear more than once, Postgres will report an error. Postgres automatically allows the table created to inherit all functions of its parent table.

In addition: the inheritance of functions is based on the custom of the Common Lisp Object System (CLOS).

CREATE TABLE Films (
Code CHARACTER (5) CONSTRAINT Firstkey PRIMARY KEY,
Title CHARACTER varying (+) not NULL,
Did DECIMAL (3) Not NULL,
Date_prod DATE,
Kind CHAR (10),
Len INTERVAL HOUR to MINUTE
);
Please see the details: http://www.linuxforum.net/books/postgresNEW/sql-createtable.htm

ALTER TABLE--modifying the definition of tables

Add a varchar column to the table:

ALTER TABLE Distributors ADD COLUMN address VARCHAR (30);
   

To rename an existing column:

ALTER TABLE Distributors RENAME COLUMN address to City;
   

Change the name of an existing table:

ALTER TABLE Distributors RENAME to suppliers;
   

Add a check constraint to a table:

ALTER TABLE Distributors ADD CONSTRAINT zipchk CHECK (char_length (zipcode) = 5);   

Deletes the OMV constraint for a table and all of 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 (multiple-field) Unique constraint to a table:

ALTER TABLE Distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, ZipCode);
   

compatibility SQL92

The ADD COLUMN form is compatible except for the default (value) and not NULL constraints mentioned above. The ALTER COLUMN form is fully compatible.

SQL92 a number of additional PostgreSQL features that are not currently directly supported by ALTER TABLE :

ALTER table Table DROP [column] column {RESTRICT | CASCADE}
      

Deletes a column from a table. Currently, to delete an existing column, the table must be re-created and reloaded:

CREATE TABLE Temp As SELECT did, city from distributors;    
DROP TABLE Distributors;
CREATE TABLE Distributors (
    did      DECIMAL (3)  DEFAULT 1,
    name     VARCHAR () not NULL
);
INSERT into Distributors SELECT * from temp;
DROP TABLE temp;

Program Examples:

1if (Drop_column_type. SelectedItem.Text.Trim () = = "int") | (Drop_column_type. SelectedItem.Text.Trim () = = "DateTime")
{
Strsql= "ALTER TABLE" +str_table_mc+ "ADD" +li_mc+ "" + Drop_column_type. SelectedItem.Text.Trim () + "" +str_isnull;
}
Else
{
Strsql= "ALTER TABLE" +str_table_mc+ "ADD" +li_mc+ "" +drop_column_type. SelectedItem.Text.Trim () + "(" +txt_column_l.text.trim () + ")" +str_isnull;
}
int run;
Run=sql. ExecuteNonQuery (strSQL);

Please see the details: http://www.pgsqldb.org/pgsqldoc-7.2c/sql-altertable.html

Three, Drop table-deletes a table from the database 

DROP TABLE name [, ...]
  

inputName the existing table or view to delete. OutputDROP returns this information if the command completes successfully. ERROR relation "name" does not exist! If the declared table or view does not exist in the database. Description

drop table deletes tables or views from the database. Only its owner can delete a table or view. Using delete A table may not have any rows, but it will not be deleted.

If the deleted table has a from index, they will be deleted first. Deletion from the index will have no effect on the contents of the owning table. Attention

Refer to the Create table and ALTER table for information about creating or changing tables. usage

Delete Films and distributors tables:

DROP TABLE Films, distributors

compatibility SQL92

SQL92 declares some additional features for the DROP TABLE:

DROP table Table {RESTRICT | CASCADE}

RESTRICT ensure that only tables that do not have a related view or consolidation constraint can be deleted. Any views or consolidation constraints that are referenced by CASCADE will be deleted.

Tip : at present, to delete a view, you must explicitly delete the

Example:

Strsql= "DROP TABLE" + STR_TAB_MC + "";
Sql. ExecuteNonQuery (strSQL);

Detailed look: http://www.linuxaid.com.cn/engineer/eight/postgrenew/sql-droptable.htm

Iv. Changes in table columns: Alter COLUMN

Alter TABLE MyTable ALTER COLUMN Nullcol NVARCHAR () not NULL

If NULL or NOT NULL is specified in ALTER COLUMN, new_data_type [(precision [, scale]) must be specified at the same time. If you do not change the data type, precision, and scale, specify the current value of these values for the column
Cases:

1, alter TABLE "+str_mcc+" Alter COLUMN "+str_mc+"   "+str_lx+" ("+str_cd+")   "+str_null";