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
SQLANDPLSQL.COM 
Leave a Reply