Subscribe to Postgresql Guide site RSS Feed
Skip Navigation Links » PostgreSQL Guide » Creating Tables

Creating Tables

To create a new table in PostgreSQL you use Create Table statement. Create Table statement creates a new, initially empty table in the current database you are working with. The owner of the table will be the user issuing the Create table statement.

To create a table you need to specify followings criteria:
  • Table name
  • Column list along with their data type, length of column and optionally column constraints. Column constraints include: NULL/ NOT NULL, UNIQUE, PRIMARY KEY,...
  • Table constraints. Table constrains includes: UNIQUE, PRIMARY KEY, FOREIGN KEY...
In PostgreSQL, when you create a new table, it also creates a data type which represents the composite type corresponding to one row of the table. Because of this reason, the table name has to be unique within its specific schema.

Let's take a look at several example of using Create Table statement.

Here is the query to create item table:

CREATE TABLE item
(
  item_id serial NOT NULL,
  description character varying(64) NOT NULL,
  cost_price numeric(7,2),
  sell_price numeric(7,2),
  CONSTRAINT item_pk PRIMARY KEY (item_id)
)
WITH (OIDS=FALSE);
  • First you specify the table name after the CREATE TABLE keyword.
  • Next you define the column list within the parentheses and separated them by commas. In each column definition, you define the column name, its data types and its constraints. For example the description  column has column name "description", data type is character with maximum length is 64 and its constraint is NOT NULL.
  • Then you define the table constraint name item_pk. It indicates that item_id column is the primary key of the table.
  • Finally WITH (storage_parameters = values,...) specifies the optional storage parameters. OIDS is an abbreviation for Object IDentifierS. If you assign OIDS to true, you specify that the new row of the table should have object identifiers assign to it.

Creating temporary table

With the Create Table statement, you can also create a temporary table. Temporary tables are automatically dropped at the end of session. Normally the temporary table is used to store the immediate results from other query in stored procedures. Here is the syntax of creating temporary table: 
CREATE {TEMPORARY | TEMP} TABLE table_name
(
 column_name1 data_type(length) constraints,
 column_name2 data_type(length) constraints,
 ...
)
You just specify the keyword TEMPORARY or TEMP before the table name to create temporary table. Here the query to create temporary table tmp 
CREATE TEMP TABLE tmp
( 
   id smallint NOT NULL,
   name character varying(25) NOT NULL,
   CONSTRAINT id_pk PRIMARY KEY (id)
)

Create inheriting table

One of the advantages of PostgreSQL in comparison with other RDMBS products is inheriting table. That is the reason why PostgreSQL is called ORDMBS (Object Relational Database Management System). Yes, you can create a table which inherit from the other table called parents tables. When we say table A inherits table B and C, it means table A inherits all columns of table B and C. Table A is called child table or inheriting table and table B and C are called parents table.
In this example, we create vehicle table as a parent table and then we create car table which inherits from the vehicle table. Let's take a look in more details to see how it works. 
CREATE TABLE vehicle
(
  "name" character varying(25) NOT NULL,
  id serial NOT NULL,
  CONSTRAINT vehicle_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

CREATE TABLE car
(
-- Inherited:   "name" character varying(25) NOT NULL,
  model character varying(25) NOT NULL
-- Inherited:   id integer NOT NULL DEFAULT
-- nextval('vehicle_id_seq'::regclass)
)
INHERITS (vehicle)
WITH (OIDS=FALSE);

INSERT INTO car(name,model)
VALUES ('Car','Lexus');

INSERT INTO car(name,model)
VALUES ('Car','Ferrari');
First we created vehicle table, then we created car table with INHERITS addition and finally we insert data into the car table. When we inserted data into the car table the data is automatically inserted to the vehicle table as well. The car table has model column and inherits two columns from the parent table vehicle.
There are some restrictions while using inheriting table as follow:
  • Column name has to be uniques in the parents tables. If there is a same column name exists in parent tables their data types has to be match otherwise an error will be occurred. If there is no conflict the columns are merged to form a single column in the inheriting table.
  • If the inheriting column has the same name as a column name of parents table. Their data type has to be match ad the column definition will be merged into one. All constraints from the columns of parents table are inherited by the column of the inheriting table.
  • In the inheriting table, if it specify explicitly default value for inheriting column, the default value from parents tables is overridden. Otherwise, any parents table which specify default values for the column must all specify the same default, or an error will be occurred.