This article enlist common daily use queries in PostgreSQL like Create, Alter, Drop etc. This will be updated periodically.
In this article I have enlisted common commands in PostgreSQL like Create, Alter, Drop etc.
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 EMPLOYEEDB;
DROP DATABASE EMPLOYEEDB;
CREATE SCHEMA EMPLOYEE;
DROP SCHEMA EMPLOYEE;
DROP SCHEMA EMPLOYEE CASCADE
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
);
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.
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
);
ALTER TABLE EMPLOYEE.PersonalDetails RENAME TO person
Above query will rename PersonalDetails table to person
ALTER table EMPLOYEE.PersonalDetails ALTER column Address type varchar(500)
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'
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;
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;
Use below query to set not null constraint on a column in existing table.
ALTER TABLE EMPLOYEE.Salary ALTER COLUMN Salary SET NOT NULL;
Use below query to drop not null constraint on a column from existing table.
ALTER TABLE EMPLOYEE.Salary ALTER COLUMN Salary DROP NOT NULL;
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;
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");
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");
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.