PostgreSQL Create Table

Tables are the fundamental unit in any DBMS or RDBMS. Tables save data in columns and rows. In schema world we call it as “Entity”. When we convert this “Entity” into the database it is called as “TABLE”.

A table has fixed number of columns and each column has a name and data type defined. (To learn more about PostgreSQL Data Type).

Number of rows are dependent on how much data we are inserting into it. So in general the number of row is variable.

Below sql statement create a table with table name as “test_table” with two columns “no” and “name”

create table test_table ( no integer, name varchar(20) );

postgres=# create table test_table ( no integer, name varchar(20) );
CREATE TABLE
postgres=# \d test_table;
                    Table "public.test_table"
 Column |         Type          | Collation | Nullable | Default 
--------+-----------------------+-----------+----------+---------
 no     | integer               |           |          | 
 name   | character varying(20) |           |          | 

table “test_table” has two columns, “no” with data type as integer and “name” as varchar

Note that \d table_name command will describe the table_name

Create table script with all data types

Below create table script contains almost all data type available in PostgreSQL 14

Create table alldatatype_table 
(
coluuid	      uuid,          --  universally unique identifier	
colsmserial   smallserial,   --  auto incrementing two-byte integer
colserial     serial,        --  auto incrementing four-byte integer
colbigserial  bigserial,     --  auto incrementing eight-byte integer
colsmallint   smallint,      --  signed two-byte integer
colbigint     bigint,        --  eight-byte integer    
colint	      int,           --  signed four-byte integer		
colfloat      float,         --	 double precision floating-point number		
coldecimal    decimal(10,3), --	 exact numeric of selectable precision
colreal       real,          --	 single precision floating-point number 
colinterval   interval,      --  time span		
colbytea      bytea,	     --  binary data		
colbit        bit,           --	 fixed-length bit string		
colvarbit     varbit(5),     --	 variable-length bit string		
colchar       char(1),	     --  fixed-length character string		
colvchar      varchar(20),   --  variable-length character string		
coltext       text,          --	 variable-length character string		
coldate       date,          --  calendar date (year, month, day)		
coltime       time,          --  time without time zone
coltimetz     timetz,        --  time with time zone
coltimestamp   timestamp,    --  timestamp without time zone
coltimestamptz timestamptz,  --  timestamp with time zone
coljson	      json,          --  textual JSON data		
coljsonb      jsonb,         --	 binary JSON data
colmoney      money,         --  currency amount				
colbool       bool,          --  logical Boolean
colxml        xml,           --  xml type
colpg_lsn     pg_lsn,        -- PostgreSQL Log Sequence Number			
coltsquery    tsquery,       -- text search query	TEXT SEARCH	
tsvector      tsvector,      -- text search query	TEXT SEARCH	
colline	      line,          -- infinite line on a plane GEOMETRIC	
collseg	      lseg,          -- line segment on a plane	GEOMETRIC	
colpath	      path,          -- geometric path on a plane GEOMETRIC	
colpoint      point,         -- geometric point on a plane GEOMETRIC	
colpolygon    polygon,       -- closed geometric path on a planeGEOMETRIC	
colbox	      box,           -- rectangular box on a plane	NETWORK	
colcidr	      cidr,          -- IPv4 or IPv6 network address	NETWORK	
colcircle     circle,        -- circle on a plane	NETWORK	
colinet	      inet,          -- IPv4 or IPv6 host address	NETWORK	
colmacaddr    macaddr,       -- MAC  address	NETWORK	
colmacaddr8   macaddr8       -- MAC  address	NETWORK	
);

Use above table script handy if you need any reference

Note :- “–” used to comment code in PostgreSQL(in general SQL files), it ignores all text after “–“

     Column     |            Type             | Collation | Nullable |                                                   
----------------+-----------------------------+-----------+----------+
 coluuid        | uuid                        |           |          | 
 colsmserial    | smallint                    |           | not null | 
 colserial      | integer                     |           | not null | 
 colbigserial   | bigint                      |           | not null | 
 colsmallint    | smallint                    |           |          | 
 colbigint      | bigint                      |           |          | 
 colint         | integer                     |           |          | 
 colfloat       | double precision            |           |          | 
 coldecimal     | numeric(10,3)               |           |          | 
 colreal        | real                        |           |          | 
 colinterval    | interval                    |           |          | 
 colbytea       | bytea                       |           |          | 
 colbit         | bit(1)                      |           |          | 
 colvarbit      | bit varying(5)              |           |          | 
 colchar        | character(1)                |           |          | 
 colvchar       | character varying(20)       |           |          | 
 coltext        | text                        |           |          | 
 coldate        | date                        |           |          | 
 coltime        | time without time zone      |           |          | 
 coltimetz      | time with time zone         |           |          | 
 coltimestamp   | timestamp without time zone |           |          | 
 coltimestamptz | timestamp with time zone    |           |          | 
 coljson        | json                        |           |          | 
 coljsonb       | jsonb                       |           |          | 
 colmoney       | money                       |           |          | 
 colbool        | boolean                     |           |          | 
 colxml         | xml                         |           |          | 
 colpg_lsn      | pg_lsn                      |           |          | 
 coltsquery     | tsquery                     |           |          | 
 tsvector       | tsvector                    |           |          | 
 colline        | line                        |           |          | 
 collseg        | lseg                        |           |          | 
 colpath        | path                        |           |          | 
 colpoint       | point                       |           |          | 

Data type uuid used as a universally unique identifier,
smallserial, serial
and bigserial data type used to create auto incrementing integer value, in general primary column can bu used with this data type

create table dept
(
deptno serial primary key,
deptname varchar(20),
location varchar(30)
);

postgres=# \d dept;
                                       Table "public.dept"
  Column  |         Type          | Collation | Nullable |                Default                
----------+-----------------------+-----------+----------+---------------------------------------
 deptno   | integer               |           | not null | nextval('dept1_deptno_seq'::regclass)
 deptname | character varying(20) |           |          | 
 location | character varying(30) |           |          | 
Indexes:
    "dept1_pkey" PRIMARY KEY, btree (deptno)
Referenced by:
    TABLE "emp" CONSTRAINT "emp_deptno_fkey" FOREIGN KEY (deptno) REFERENCES dept(deptno)

create table emp
(
empno serial primary key,
empname varchar(20),
salary integer,
dob date,
deptno integer references dept(deptno)
);

postgres=# \d emp;
                                     Table "public.emp"
 Column  |         Type          | Collation | Nullable |              Default               
---------+-----------------------+-----------+----------+------------------------------------
 empno   | integer               |           | not null | nextval('emp_empno_seq'::regclass)
 empname | character varying(20) |           |          | 
 salary  | integer               |           |          | 
 dob     | date                  |           |          | 
 deptno  | integer               |           |          | 
Indexes:
    "emp_pkey" PRIMARY KEY, btree (empno)
Foreign-key constraints:
    "emp_deptno_fkey" FOREIGN KEY (deptno) REFERENCES dept(deptno)

DROP table command used to drop a table

ALTER table used to alter the table column data types

ALTER table RENAME column used to drop a column

We will examine these commands in another post

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: