Creating Tables

PostgreSQL support all standard objects like table, view, materialized views, types, sequences, triggers, functions, etc. Let’s take a look at these ones by one.

Creating tables

Tables are used to store the data in form of rows and columns. The columns can be any of the data types discussed earlier.

Some features of the tables:

• Table may have primary key or unique key(s) on a set of column(s) to enforce the uniqueness among the data of rows of the table. Such keys prevent the insert of duplicate data in the table.
• Tables can be created with their column(s) pointing to auto incrementing sequence numbers so that value of those columns will automatically populated from the sequences.
• Table can also be created in foreign key relationships so that rows in a child table will be related to rows of another parent table.
• Tables can also be partitioned dividing the rows into logical and physical groups which may improve the performance of certain queries.

Creating a simple table

$ psql -U manoj -h 192.168.0.111 -d appdb  -p 5433

# CREATE TABLE city(name TEXT, pop INT);

# \d

# \d city

appdb=> CREATE TABLE city(name TEXT, pop INT);
CREATE TABLE
appdb=> \d
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 public | city | table | manoj
(1 row)

appdb=> \d city
                Table "public.city"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 name   | text    |           |          |
 pop    | integer |           |          |

appdb=>

Creating a table with a primary key

# DROP TABLE city;

# CREATE TABLE city (
  city_id INT PRIMARY KEY,
  name TEXT,
  pop INT
);

# \d city

appdb=> DROP TABLE city;
DROP TABLE
appdb=>
appdb=> CREATE TABLE city (
appdb(>   city_id INT PRIMARY KEY,
appdb(>   name TEXT,
appdb(>   pop INT
appdb(> );
CREATE TABLE
appdb=>

Creating a table with auto-increment primary key

# DROP TABLE city;

# CREATE TABLE city (
  city_id serial PRIMARY KEY,
  name TEXT,
  pop INT
);

# \d city

appdb=> DROP TABLE city;
DROP TABLE
appdb=> CREATE TABLE city (
appdb(>   city_id serial PRIMARY KEY,
appdb(>   name TEXT,
appdb(>   pop INT
appdb(> );
CREATE TABLE
appdb=> \d city
                               Table "public.city"
 Column  |  Type   | Collation | Nullable |                Default
---------+---------+-----------+----------+---------------------------------------
 city_id | integer |           | not null | nextval('city_city_id_seq'::regclass)
 name    | text    |           |          |
 pop     | integer |           |          |
Indexes:
    "city_pkey" PRIMARY KEY, btree (city_id)

appdb=>

Creating a table with foreign key

# CREATE TABLE street (
  street_id serial PRIMARY KEY,
  city_id INT,
  name TEXT,
  pop INT,
  FOREIGN KEY (city_id) REFERENCES city(city_id)
);

# \d street

appdb=> CREATE TABLE street (
appdb(>   street_id serial PRIMARY KEY,
appdb(>   city_id INT,
appdb(>   name TEXT,
appdb(>   pop INT,
appdb(>   FOREIGN KEY (city_id) REFERENCES city(city_id)
appdb(> );
CREATE TABLE
appdb=> \d street
                                 Table "public.street"
  Column   |  Type   | Collation | Nullable |                  Default
-----------+---------+-----------+----------+-------------------------------------------
 street_id | integer |           | not null | nextval('street_street_id_seq'::regclass)
 city_id   | integer |           |          |
 name      | text    |           |          |
 pop       | integer |           |          |
Indexes:
    "street_pkey" PRIMARY KEY, btree (street_id)
Foreign-key constraints:
    "street_city_id_fkey" FOREIGN KEY (city_id) REFERENCES city(city_id)

appdb=>

Creating indexes on the tables

# CREATE INDEX idx_street_city_id ON street(city_id);

# \di

#\d street

#\di idx_street_city_id

appdb=> CREATE INDEX idx_street_city_id ON street(city_id);
CREATE INDEX
appdb=> \di
                  List of relations
 Schema |        Name        | Type  | Owner | Table
--------+--------------------+-------+-------+--------
 public | city_pkey          | index | manoj | city
 public | idx_street_city_id | index | manoj | street
 public | street_pkey        | index | manoj | street
(3 rows)

appdb=> \d street
                                 Table "public.street"
  Column   |  Type   | Collation | Nullable |                  Default
-----------+---------+-----------+----------+-------------------------------------------
 street_id | integer |           | not null | nextval('street_street_id_seq'::regclass)
 city_id   | integer |           |          |
 name      | text    |           |          |
 pop       | integer |           |          |
Indexes:
    "street_pkey" PRIMARY KEY, btree (street_id)
    "idx_street_city_id" btree (city_id)
Foreign-key constraints:
    "street_city_id_fkey" FOREIGN KEY (city_id) REFERENCES city(city_id)


appdb=> \di idx_street_city_id
                  List of relations
 Schema |        Name        | Type  | Owner | Table
--------+--------------------+-------+-------+--------
 public | idx_street_city_id | index | manoj | street
(1 row)

appdb=>