How do I Create a basic Table in PostgreSQL

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 table contains all data type available in PostgreSQL 14

Data TypeDescription
uuiduniversally unique identifier
smallserialauto incrementing two-byte integer
serialauto incrementing four-byte integer
bigserialauto incrementing eight-byte integer
smallintsigned two-byte integer
bigint eight-byte integer
intsigned four-byte integer
floatdouble precision floating-point number
decimalexact numeric of selectable precision
realsingle precision floating-point number 
intervaltime span
byteabinary data
bitfixed-length bit string
varbitvariable-length bit string
charfixed-length character string
varcharvariable-length character string
textvariable-length character string
datecalendar date (year, month, day)
timetime without time zone
timetztime with time zone
timestamptimestamp without time zone
timestamptztimestamp with time zone
jsontextual JSON data
jsonbbinary JSON data
moneycurrency amount
boollogical Boolean
xmlxml type
pg_lsnPostgreSQL Log Sequence Number
tsquerytext search query TEXT SEARCH
tsvectortext search query TEXT SEARCH
lineinfinite line on a plane GEOMETRIC
lsegline segment on a plane GEOMETRIC
pathgeometric path on a plane GEOMETRIC
pointgeometric point on a plane GEOMETRIC
polygonclosed path on plane GEOMETRIC
boxrectangular box on a plane NETWORK
cidrIPv4 or IPv6 network address NETWORK
circlecircle on a plane NETWORK
inetIPv4 or IPv6 host address NETWORK
macaddrMAC address NETWORK
macaddr8MAC address NETWORK

Creating a table alldatatype_table with all available data type

Create table alldatatype_table 
(
coluuid	      uuid,          	
colsmserial   smallserial,   
colserial     serial,        
colbigserial  bigserial,     
colsmallint   smallint,      
colbigint     bigint,           
colint	      int,           		
colfloat      float,         		
coldecimal    decimal(10,3), 
colreal       real,          
colinterval   interval,      
colbytea      bytea,	     
colbit        bit,           
colvarbit     varbit(5),     	
colchar       char(1),	     	
colvchar      varchar(20),   		
coltext       text,          	
coldate       date,          	
coltime       time,          
coltimetz     timetz,        
coltimestamp   timestamp,    
coltimestamptz timestamptz,  
coljson	       json,          		
coljsonb       jsonb,         
colmoney       money,         			
colbool        bool,          
colxml         xml,           
colpg_lsn      pg_lsn,        			
coltsquery     tsquery,       
tsvector       tsvector,      	
colline	       line,          	
collseg	       lseg,          		
colpath	       path,           	
colpoint       point,         	
colpolygon     polygon,       	
colbox	       box,           
colcidr	       cidr,          	
colcircle      circle,        
colinet	       inet,          	
colmacaddr     macaddr,       
colmacaddr8    macaddr8       
);

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       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 w/o 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 be 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  | Nullable |             
----------+-----------------------+---
 deptno   | integer | not null | 
 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