2.3 PostgreSQL Creating a new table

You can create a table by declaring the name of the table and the names of all the fields and their types:

CREATE TABLE Weather (    City            varchar),    temp_lo         int,           --Lowest temperature    temp_hi         int,--           maximum temperature    PRCP            Real,          --precipitation    date            date);

You can type these things in psql with a newline character. Psql can recognize the command until the semicolon ends.

You can freely use whitespace (spaces/tab/line breaks) in SQL commands. This means that you can type commands with different alignments (even on the same line) as above. A double dash ("--") introduces a comment, and anything that follows it is ignored until the end of the line. SQL is a language that is insensitive to keywords and identifiers, and only retains their casing properties if the identifiers are surrounded by double quotation marks (which is not the case).

varchar declares a data type that can store any string up to 80 characters long. int is an ordinary integer type. Real is a type for storing single-precision floating-point numbers. The date type should be self-explanatory. Yes, the field name of type date is also date . This may be convenient or confusing, and you can see for yourself.

Postgres SQL supports standard SQL types:int, smallint, real, double precision, char (N ), varchar (N), date, time, timestamp, interval , other common types and rich geometry types are also supported. PostgreSQL allows you to customize any number of data types. Thus the type name is not a syntax keyword, except for the special exceptions that SQL standard requires.

The second example will save the city and their associated geographic location:

CREATE TABLE Cities (    name            varchar, location point        );

The point type is an example of a PostgreSQL-specific data type.

Finally, we also mention that if you no longer need a table, or if you want to create a different table, you can delete it with the following command:

TableName;

For more information, please refer to Http://www.infocool.net

2.3 PostgreSQL Creating a new table