You can create a new table by specifying the table name, along with all column names and their types:
CREATE TABLE weather ( city varchar(80), temp_lo int, -- low temperature temp_hi int, -- high temperature prcp real, -- precipitation date date );
You can enter this intoÂ psql with the line breaks.Â psql will recognize that the command is not terminated until the semicolon.
White space (i.e., spaces, tabs, and newlines) can be used freely in SQL commands. That means you can type the command aligned differently than above, or even all on one line. Two dashes (“--“) introduce comments. Whatever follows them is ignored up to the end of the line. SQL is case insensitive about key words and identifiers, except when identifiers are double-quoted to preserve the case (not done above).
varchar(80) specifies a data type that can store arbitrary character strings up to 80 characters in length.Â int is the normal integer type.Â real is a type for storing single precision floating-point numbers.Â date should be self-explanatory. (Yes, the column of typeÂ date is also namedÂ date. This might be convenient or confusing â€” you choose.)
PostgreSQLÂ supports the standardÂ SQL typesÂ int,Â smallint,Â real,Â double precision,Â char(N),Â varchar(N),Â date,Â time,Â timestamp, andÂ interval, as well as other types of general utility and a rich set of geometric types.Â PostgreSQLÂ can be customized with an arbitrary number of user-defined data types. Consequently, type names are not key words in the syntax, except where required to support special cases in theÂ SQL standard.
The second example will store cities and their associated geographical location:
CREATE TABLE cities ( name varchar(80), location point );
TheÂ point type is an example of aÂ PostgreSQL-specific data type.