PostgreSQL to create a table
PostgreSQL uses the CREATE TABLE statement to create a database table.
CREATE TABLE The syntax is as follows:
CREATE TABLE table_name( Column1 datatype, Column2 datatype, Column3 datatype, ..... columnN datatype, PRIMARY KEY( one or more columns ) );
CREATE TABLE is a keyword that tells the database system to create a data table.
The table name must be unique among other tables, sequences, indexes, views, or external table names in the same schema.
CREATE TABLE Creates a new, blank table in the current database that will be owned by the user who issued the command.
Each field in the table defines the data type as follows:
The following table was created with a table named COMPANY table with a primary key of ID and NOT NULL indicating that the field is not allowed to contain NULL value:
CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
Next we will create a table that will be used in later chapters:
CREATE TABLE DEPARTMENT( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT NOT NULL );
We can use the \d command to see if the form was created successfully:
welookupsdb=# \d List of relations Schema | Name | Type | Owner --------+------------+-------+-------- -- public | Company | table | postgres public | Department | table | postgres (2rows)
\d tablename View the table information:
welookupsdb=# \d Company Table "public.company" Column | Type | Collation | Nullable | Default ---------+---------------+-----------+ ----------+--------- Id | integer | | not null | Name | text | | not null | Age | integer | | not null | Address | character(50) | | | Salary | real | | | Indexes: "company_pkey" PRIMARY KEY, btree (id) Pre>