This article enlist common daily use queries in PostgreSQL like Create, Alter, Drop etc. This will be updated periodically.

Introduction

In this article I have enlisted common commands in PostgreSQL like Create, Alter, Drop etc.

Common queries

Postgres queries are similar to SQL except few. I have also noticed that Postgres is case-sensitive and by default it takes lower case means if I write simple query to create a schema ike below, it will create it in lower case like employee.

create schema EMPLOYEE

But if I want my schema to show something like EMPLOYEE, then I should write it like below

create schema "EMPLOYEE"

I will discuss this in detail in next article.

Create database

CREATE DATABASE EMPLOYEEDB;

Drop database

DROP DATABASE EMPLOYEEDB;

Create schema

CREATE SCHEMA EMPLOYEE;

Drop schema

DROP SCHEMA EMPLOYEE;

Drop schema with all tables

DROP SCHEMA EMPLOYEE CASCADE

Create table

Create Table EMPLOYEE.PersonalDetails (
 ID INT GENERATED ALWAYS AS IDENTITY Primary Key,
 Name VARCHAR(100) NOT NULL,
 Phone VARCHAR(20) NOT NULL,
 Address VARCHAR(250) NOT NULL,
 Active BOOLEAN NOT NULL
);

Setting Identity Column

Use below query. By default identity starts from 1 and increment by 1. You can customize it too.

ID INT GENERATED ALWAYS AS IDENTITY

Above command will create a column "ID" with identity to generate always. You will not be able to add value to to this column explicitly. If you want to allow external value to enter in ID column either use below command while creating column.

ID INT GENERATED BY DEFAULT AS IDENTITY

Or you can use OVERRIDING SYSTEM VALUE as shown below.

INSERT INTO EMPLOYEE.PersonalDetails (ID,Name) 
OVERRIDING SYSTEM VALUE 
  VALUES(1,'Rahul',);

NOTE: Please remember that semi colon <b(;)is important to put at the end of each command to tell Postgre that command is finished, when you are executing multi commands together.

Adding reference to another table

Create Table EMPLOYEE.Salary (
 ID INT GENERATED ALWAYS AS IDENTITY Primary Key,
 Salary INT NOT NULL,
 EmployeeID INT references EMPLOYEE.PersonalDetails(ID)
 IsActive BOOLEAN NOT NULL
);

To rename table name

ALTER TABLE EMPLOYEE.PersonalDetails RENAME TO person

Above query will rename PersonalDetails table to person

To modify datatype of a column

ALTER table EMPLOYEE.PersonalDetails ALTER column Address type varchar(500)

ALTER table to set default value

Below query will set default constrant with column IsActive and set default value to true.

ALTER TABLE EMPLOYEE.Salary ALTER COLUMN IsActive SET DEFAULT True;

You can set default value for boolean type as True, true, 'yes','y', '1', False, false, 'no','n','0'

To add new column

Below query will add a column IsActive of boolean type and set it as NOT NULL. Remember that BOOLEAN is different than BIT in PostgreSQL.

ALTER table EMPLOYEE.Salary add column IsActive BOOLEAN NOT NULL;

To drop a column

Use below query to remove a column from existing table.

ALTER TABLE EMPLOYEE.Salary DROP COLUMN IsActive;

Use below query to remove a column from existing table and all the dependend objects like trigger, view, store procedure etc.

ALTER TABLE EMPLOYEE.Salary DROP COLUMN IsActive CASCADE;

To remove a column from existing table if it exist.

ALTER TABLE EMPLOYEE.Salary DROP COLUMN IF EXISTS IsActive;

If column IsActive does not exist in Salary table then it will skip the query with below message.

NOTICE: column "IsActive" of relation "Salary" does not exist, skipping
ALTER TABLE

To remove multiple columns in a single query from existing table use below query. You can use "IF EXISTS" clause as well.

ALTER TABLE EMPLOYEE.Salary DROP COLUMN IsActive, DROP COLUMN Salary;

Adding NOT NULL constraint

Use below query to set not null constraint on a column in existing table.

ALTER TABLE EMPLOYEE.Salary ALTER COLUMN Salary SET NOT NULL;

Drop NOT NULL constraint (Removing NULL from a column)

Use below query to drop not null constraint on a column from existing table.

ALTER TABLE EMPLOYEE.Salary ALTER COLUMN Salary DROP NOT NULL;

To rename a column

Use below query to rename a column in existing table. Below query will rename column ISActive to Active

ALTER TABLE EMPLOYEE.Salary RENAME COLUMN IsActive TO Active

To rename multiple columns you have to run rename query miltiple times one by one like below.

ALTER TABLE EMPLOYEE.Salary RENAME COLUMN IsActive TO Active;


ALTER TABLE EMPLOYEE.Salary RENAME COLUMN Salary TO Salary1;

Adding Unique Constraint

You can add unique constraint while creating table using below methods i.e. with column name or as a separate.

Create Table EMPLOYEE.Salary (
 ID INT GENERATED ALWAYS AS IDENTITY Primary Key,
 EmpSalary INT UNIQUE,
 EmployeeID INT references EMPLOYEE.PersonalDetails(ID)
 IsActive BOOLEAN NOT NULL
);

Or you can write query like this.

Create Table EMPLOYEE.Salary (
 ID INT GENERATED ALWAYS AS IDENTITY Primary Key,
 EmpSalary INT,
 EmployeeID INT references EMPLOYEE.PersonalDetails(ID)
 IsActive BOOLEAN NOT NULL,
 UNIQUE(EMPSalary)
);

You can also add unique constraint on multiple columns.

Create Table EMPLOYEE.Salary (
 ID INT GENERATED ALWAYS AS IDENTITY Primary Key,
 EmpSalary INT,
 EmployeeID INT references EMPLOYEE.PersonalDetails(ID)
 IsActive BOOLEAN NOT NULL,
 UNIQUE(EMPSalary, EmployeeID)
);

You can use alter command with add constraint clause also to add unique key constraints.

ALTER TABLE EMPLOYEE.Salary ADD CONSTRAINT constraint_key_name UNIQUE ("Name");

Adding Unique Index on a Column

To add unique index on a column. Ideally only unique index should not be added on a column but it should only be applied with a unique constraint.

CREATE UNIQUE INDEX index_name ON Employee.Salary("Name");

To Truncate a table

Use below query to truncate a table and remove all the records from table and set identity column to initial.

TRUNCATE EMPLOYEE.Salary;

It will remove all the records from table and set identity column to initial state but above command will throw error if it is referenced in any other table. For that use CASCADE keyword with command like below to successfully delete all the rows.

TRUNCATE EMPLOYEE.Salary CASCADE;

Hope this helps you. More queries will be added soon... You can bookmark this article for further use. If you know more queries, please feel to add in comment box. I will include it in this article.