docs for using postgres sql cli tool psql

postgres.md

Start the server (for installation see Install instructions)

pg_ctl -D /opt/homebrew/var/postgresql@14 start

Stop the server

pg_ctl -D /opt/homebrew/var/postgresql@14 start

Start psql cli

psql postgres

• \! clear to clear the console output
• \? for help
• \q to quit the cli
• \conninfo to see connection info.
• \du list all existing users

Creating a database

CREATE DATABASE mydatabasename;

• \l to list all the databases
• \c databasename to connect to connect specific database
• \dt to list tables in that database

example output

postgres=# \l
                          List of databases
   Name    | Owner  | Encoding | Collate | Ctype | Access privileges 
-----------+--------+----------+---------+-------+-------------------
 company   | mahesh | UTF8     | C       | C     | =Tc/mahesh       +
           |        |          |         |       | mahesh=CTc/mahesh
 postgres  | mahesh | UTF8     | C       | C     | 
 template0 | mahesh | UTF8     | C       | C     | =c/mahesh        +
           |        |          |         |       | mahesh=CTc/mahesh
 template1 | mahesh | UTF8     | C       | C     | =c/mahesh        +
           |        |          |         |       | mahesh=CTc/mahesh
(4 rows)

postgres=# \c postgres
You are now connected to database "postgres" as user "mahesh".
postgres=# \dt
         List of relations
 Schema |  Name   | Type  | Owner  
--------+---------+-------+--------
 public | weather | table | mahesh
(1 row)

postgres=# \c company
You are now connected to database "company" as user "mahesh".
company=# \dt
Did not find any relations.
company=# 

Creating a table

You can create a new table by specifying the table name, along with all column names and their types:

CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- low temperature
    temp_hi         int,           -- high temperature
    prcp            real,          -- precipitation
    date            date
);

Add constraints to each column followed by the data type

company=# CREATE TABLE project(
    Pname           varchar(15) NOT NULL,
    Pnumber         int PRIMARY KEY,        -- project number
    Plocation       varchar(15) NOT NULL,   -- project location
    Dnum            int NOT NULL,         
    date            date,      
    UNIQUE(Pname)                                                                               );

Finally, it should be mentioned that if you don't need a table any longer or want to recreate it differently you can remove it using the following command:

DROP TABLE tablename;

Inserting into TABLE

INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');

or

The syntax used so far requires you to remember the order of the columns. An alternative syntax allows you to list the columns explicitly:

INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
    VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');

or

You could also have used COPY to load large amounts of data from flat-text files. This is usually faster because the COPY command is optimized for this application while allowing less flexibility than INSERT. An example would be:

COPY weather FROM '/home/user/weather.txt';

Querying from table

SELECT * FROM "weather";

Change User Password

ALTER USER yourusername WITH PASSWORD 'yournewpass';

Create new User

CREATE USER yourname WITH SUPERUSER PASSWORD 'yourpassword';

Ref: https://www.postgresql.org/docs/current/

Dump and restore the data from the database

Dump (creates a exported_data.dump file which can be restored)

pg_dump -h your_host -d your_database -U your_username -t your_table -a -F c -f /path/to/exported_data.dump

restore

pg_restore -h your_host -d your_database -U your_username -F c -c /path/to/your/dump_file.dump

CREATE TABLE Employee(
    Fname        VARCHAR(15) NOT NULL,
    Minit           CHAR,
    Lname        VARCHAR(15) NOT NULL, 
    Ssn             CHAR(9) PRIMARY KEY NOT NULL,               
    Bdate         DATE, 
    Address     VARCHAR(50),
    Sex             CHAR,
    Salary         Decimal(10,2),
    Super_ssn  CHAR(9),
    Dno           INT
);

CREATE TABLE Department(
    Dname          VARCHAR(15) NOT NULL,
    Dnumber      INT PRIMARY KEY NOT NULL,
    Mgr_ssn       CHAR(9) NOT NULL, 
    Mgr_start_date      DATE,
    UNIQUE(Dname)
);

CREATE TABLE DeptLocations(
    Dnumber          INT  NOT NULL,
    Dlocation         VARCHAR(15) NOT NULL,
    PRIMARY KEY(Dnumber, Dlocation)           
);

CREATE TABLE Project(
    Pname           varchar(15) NOT NULL,
    Pnumber        int PRIMARY KEY,        -- project number
    Plocation       varchar(15),   -- project location
    Dnum            int NOT NULL,              
    UNIQUE(Pname)                                                                               
);

CREATE TABLE WorksOn(
    Essn       Char(9) NOT NULL,
    Pno        int NOT NULL,
    Hours      Decimal(3,1) NOT NULL,                       
    PRIMARY KEY(Essn,Pno) 
);

CREATE TABLE dependent(
    Essn       Char(9) NOT NULL,
    Dependent_name    VARCHAR(15) NOT NULL,
    Sex    CHAR,                       
    Bdate DATE,                                                                                  
    Relationship VARCHAR(8),                                                                     
    PRIMARY KEY(Essn,Dependent_name)                                                          
);

ALTER TABLE DEPARTMENT ADD FOREIGN KEY(mgr_ssn) REFERENCES Employee(ssn);

ALTER TABLE DeptLocations ADD FOREIGN KEY(Dnumber) REFERENCES Department(Dnumber);

ALTER TABLE Project ADD FOREIGN KEY(Dnum) REFERENCES Department(Dnumber);

ALTER TABLE WorksOn ADD FOREIGN KEY(Essn) REFERENCES Employee(ssn);

ALTER TABLE WorksOn ADD FOREIGN KEY(Pno) REFERENCES Project(Pnumber);

ALTER TABLE Dependent ADD FOREIGN KEY(Essn) REFERENCES Employee(Ssn);

Drop Table

DROP TABLE Employee CASCADE;
DROP TABLE Project CASCADE;
DROP TABLE DeptLocations cascade;
DROP TABLE Department cascade;
DROP TABLE Dependent cascade;
DROP TABLE WorksOn cascade;

Sample data

INSERT INTO employee VALUES 
  ('James','E','Borg','888665555','10-NOV-27','450 Stone, Houston, TX','M',55000,null,null);
INSERT INTO employee VALUES 
  ('Franklin','T','Wong','333445555','08-DEC-45','638 Voss, Houston, TX','M',40000,'888665555',null);
INSERT INTO employee VALUES 
  ('Jennifer','S','Wallace','987654321','20-JUN-31','291 Berry, Bellaire, TX','F',43000,'888665555',null);
INSERT INTO employee VALUES
  ('Jared','D','James','111111100','10-OCT-1966','123 Peachtree, Atlanta, GA','M',85000,null,null);
INSERT INTO employee VALUES
  ('Alex','D','Freed','444444400','09-OCT-1950','4333 Pillsbury, Milwaukee, WI','M',89000,null,null);
INSERT INTO employee VALUES
  ('John','C','James','555555500','30-JUN-1975','7676 Bloomington, Sacramento, CA','M',81000,null,null);
--
INSERT INTO department VALUES ('Research', 5, '333445555', '22-MAY-78');
INSERT INTO department VALUES ('Administration', 4, '987654321', '01-JAN-85');
INSERT INTO department VALUES ('Headquarters', 1, '888665555', '19-JUN-71');
INSERT INTO department VALUES ('Software',6,'111111100','15-MAY-1999');
INSERT INTO department VALUES ('Hardware',7,'444444400','15-MAY-1998');
INSERT INTO department VALUES ('Sales',8,'555555500','01-JAN-1997');
--
UPDATE employee SET dno = 5 WHERE ssn = '333445555';
UPDATE employee SET dno = 4 WHERE ssn = '987654321';
UPDATE employee SET dno = 1 WHERE ssn = '888665555';
UPDATE employee SET dno = 6 WHERE ssn = '111111100';
UPDATE employee SET dno = 7 WHERE ssn = '444444400';
UPDATE employee SET dno = 6 WHERE ssn = '555555500';
--
INSERT INTO employee VALUES 
  ('John','B','Smith','123456789','09-Jan-55','731 Fondren, Houston, TX','M',30000,'333445555',5);
INSERT INTO employee VALUES 
  ('Alicia','J','Zelaya','999887777','19-JUL-58','3321 Castle, Spring, TX','F',25000,'987654321',4);
INSERT INTO employee VALUES 
  ('Ramesh','K','Narayan','666884444','15-SEP-52','971 Fire Oak, Humble, TX','M',38000,'333445555',5);
INSERT INTO employee VALUES 
  ('Joyce','A','English','453453453','31-JUL-62','5631 Rice Oak, Houston, TX','F',25000,'333445555',5);
INSERT INTO employee VALUES 
  ('Ahmad','V','Jabbar','987987987','29-MAR-59','980 Dallas, Houston, TX','M',25000,'987654321',4);
insert into EMPLOYEE values
  ('Jon','C','Jones','111111101','14-NOV-1967','111 Allgood, Atlanta, GA','M',45000,'111111100',6);
insert into EMPLOYEE values
  ('Justin',null,'Mark','111111102','12-JAN-1966','2342 May, Atlanta, GA','M',40000,'111111100',6);
insert into EMPLOYEE values
  ('Brad','C','Knight','111111103','13-FEB-1968','176 Main St., Atlanta, GA','M',44000,'111111100',6);
insert into EMPLOYEE values
  ('Evan','E','Wallis','222222200','16-JAN-1958','134 Pelham, Milwaukee, WI','M',92000,null,7);
insert into EMPLOYEE values
  ('Josh','U','Zell','222222201','22-MAY-1954','266 McGrady, Milwaukee, WI','M',56000,'222222200',7);
insert into EMPLOYEE values
  ('Andy','C','Vile','222222202','21-JUN-1944','1967 Jordan, Milwaukee, WI','M',53000,'222222200',7);
insert into EMPLOYEE values
  ('Tom','G','Brand','222222203','16-DEC-1966','112 Third St, Milwaukee, WI','M',62500,'222222200',7);
insert into EMPLOYEE values
  ('Jenny','F','Vos','222222204','11-NOV-1967','263 Mayberry, Milwaukee, WI','F',61000,'222222201',7);
insert into EMPLOYEE values
  ('Chris','A','Carter','222222205','21-MAR-1960','565 Jordan, Milwaukee, WI','F',43000,'222222201',7);
insert into EMPLOYEE values
  ('Kim','C','Grace','333333300','23-OCT-1970','6677 Mills Ave, Sacramento, CA','F',79000,null,6);
insert into EMPLOYEE values
  ('Jeff','H','Chase','333333301','07-JAN-1970','145 Bradbury, Sacramento, CA','M',44000,'333333300',6);
insert into EMPLOYEE values
  ('Bonnie','S','Bays','444444401','19-JUN-1956','111 Hollow, Milwaukee, WI','F',70000,'444444400',7);
insert into EMPLOYEE values
  ('Alec','C','Best','444444402','18-JUN-1966','233 Solid, Milwaukee, WI','M',60000,'444444400',7);
insert into EMPLOYEE values
  ('Sam','S','Snedden','444444403','31-JUL-1977','987 Windy St, Milwaukee, WI','M',48000,'444444400',7);
insert into EMPLOYEE values
  ('Nandita','K','Ball','555555501','16-APR-1969','222 Howard, Sacramento, CA','M',62000,'555555500',6);
insert into EMPLOYEE values
  ('Bob','B','Bender','666666600','17-APR-1968','8794 Garfield, Chicago, IL','M',96000,null,8);
insert into EMPLOYEE values
  ('Jill','J','Jarvis','666666601','14-JAN-1966','6234 Lincoln, Chicago, IL','F',36000,'666666600',8);
insert into EMPLOYEE values
  ('Kate','W','King','666666602','16-APR-1966','1976 Boone Trace, Chicago, IL','F',44000,'666666600',8);
insert into EMPLOYEE values
  ('Lyle','G','Leslie','666666603','09-JUN-1963','417 Hancock Ave, Chicago, IL','M',41000,'666666601',8);
insert into EMPLOYEE values
  ('Billie','J','King','666666604','01-JAN-1960','556 Washington, Chicago, IL','F',38000,'666666603',8);
insert into EMPLOYEE values
  ('Jon','A','Kramer','666666605','22-AUG-1964','1988 Windy Creek, Seattle, WA','M',41500,'666666603',8);
insert into EMPLOYEE values
  ('Ray','H','King','666666606','16-AUG-1949','213 Delk Road, Seattle, WA','M',44500,'666666604',8);
insert into EMPLOYEE values
  ('Gerald','D','Small','666666607','15-MAY-1962','122 Ball Street, Dallas, TX','M',29000,'666666602',8);
insert into EMPLOYEE values
  ('Arnold','A','Head','666666608','19-MAY-1967','233 Spring St, Dallas, TX','M',33000,'666666602',8);
insert into EMPLOYEE values
  ('Helga','C','Pataki','666666609','11-MAR-1969','101 Holyoke St, Dallas, TX','F',32000,'666666602',8);
insert into EMPLOYEE values
  ('Naveen','B','Drew','666666610','23-MAY-1970','198 Elm St, Philadelphia, PA','M',34000,'666666607',8);
insert into EMPLOYEE values
  ('Carl','E','Reedy','666666611','21-JUN-1977','213 Ball St, Philadelphia, PA','M',32000,'666666610',8);
insert into EMPLOYEE values
  ('Sammy','G','Hall','666666612','11-JAN-1970','433 Main Street, Miami, FL','M',37000,'666666611',8);
insert into EMPLOYEE values
  ('Red','A','Bacher','666666613','21-MAY-1980','196 Elm Street, Miami, FL','M',33500,'666666612',8);
--
INSERT INTO project VALUES ('ProductX',1,'Bellaire',5);
INSERT INTO project VALUES ('ProductY',2,'Sugarland',5);
INSERT INTO project VALUES ('ProductZ',3,'Houston',5);
INSERT INTO project VALUES ('Computerization',10,'Stafford',4);
INSERT INTO project VALUES ('Reorganization',20,'Houston',1);
INSERT INTO project VALUES ('Newbenefits',30,'Stafford',4);
INSERT INTO project VALUES ('OperatingSystem',61,'Jacksonville',6);
INSERT INTO project VALUES ('DatabaseSystems',62,'Birmingham',6);
INSERT INTO project VALUES ('Middleware',63,'Jackson',6);
INSERT INTO project VALUES ('InkjetPrinters',91,'Phoenix',7);
INSERT INTO project VALUES ('LaserPrinters',92,'LasVegas',7);
--
INSERT INTO deptLocations VALUES (1,'Houston');
INSERT INTO deptLocations VALUES (4,'Stafford');
INSERT INTO deptLocations VALUES (5,'Bellaire');
INSERT INTO deptLocations VALUES (5,'Sugarland');
INSERT INTO deptLocations VALUES (5,'Houston');
INSERT INTO deptLocations VALUES (6,'Atlanta');
INSERT INTO deptLocations VALUES (6,'Sacramento');
INSERT INTO deptLocations VALUES (7,'Milwaukee');
INSERT INTO deptLocations VALUES (8,'Chicago');
INSERT INTO deptLocations VALUES (8,'Dallas');
INSERT INTO deptLocations VALUES (8,'Philadephia');
INSERT INTO deptLocations VALUES (8,'Seattle');
INSERT INTO deptLocations VALUES (8,'Miami');
--
INSERT INTO dependent VALUES ('333445555','Alice','F','05-APR-76','Daughter');
INSERT INTO dependent VALUES ('333445555','Theodore','M','25-OCT-73','Son');
INSERT INTO dependent VALUES ('333445555','Joy','F','03-MAY-48','Spouse');
INSERT INTO dependent VALUES ('987654321','Abner','M','29-FEB-32','Spouse');
INSERT INTO dependent VALUES ('123456789','Michael','M','01-JAN-78','Son');
INSERT INTO dependent VALUES ('123456789','Alice','F', '31-DEC-78','Daughter');
INSERT INTO dependent VALUES ('123456789','Elizabeth','F','05-MAY-57','Spouse');
INSERT INTO dependent VALUES ('444444400','Johnny','M','04-APR-1997','Son');
INSERT INTO dependent VALUES ('444444400','Tommy','M','07-JUN-1999','Son');
INSERT INTO dependent VALUES ('444444401','Chris','M','19-APR-1969','Spouse');
INSERT INTO dependent VALUES ('444444402','Sam','M','14-FEB-1964','Spouse');
INSERT INTO dependent VALUES ('888665555','James','M','14-FEB-1964','Son');
--
INSERT INTO worksOn VALUES ('123456789',1, 32.5);
INSERT INTO worksOn VALUES ('123456789',2,  7.5);
INSERT INTO worksOn VALUES ('666884444',3, 40.0);
INSERT INTO worksOn VALUES ('453453453',1, 20.0);
INSERT INTO worksOn VALUES ('453453453',2, 20.0);
INSERT INTO worksOn VALUES ('333445555',2, 10.0);
INSERT INTO worksOn VALUES ('333445555',3, 10.0);
INSERT INTO worksOn VALUES ('333445555',10,10.0);
INSERT INTO worksOn VALUES ('333445555',20,10.0);
INSERT INTO worksOn VALUES ('999887777',30,30.0);
INSERT INTO worksOn VALUES ('999887777',10,10.0);
INSERT INTO worksOn VALUES ('987987987',10,35.0);
INSERT INTO worksOn VALUES ('987987987',30,5.0);
INSERT INTO worksOn VALUES ('987654321',30,20.0);
INSERT INTO worksOn VALUES ('987654321',20,15.0);
INSERT INTO worksOn VALUES ('888665555',20,12.0);
INSERT INTO worksOn VALUES ('111111100',61,40.0);
INSERT INTO worksOn VALUES ('111111101',61,40.0);
INSERT INTO worksOn VALUES ('111111102',61,40.0);
INSERT INTO worksOn VALUES ('111111103',61,40.0);
INSERT INTO worksOn VALUES ('222222200',62,40.0);
INSERT INTO worksOn VALUES ('222222201',62,48.0);
INSERT INTO worksOn VALUES ('222222202',62,40.0);
INSERT INTO worksOn VALUES ('222222203',62,40.0);
INSERT INTO worksOn VALUES ('222222204',62,40.0);
INSERT INTO worksOn VALUES ('222222205',62,40.0);
INSERT INTO worksOn VALUES ('333333300',63,40.0);
INSERT INTO worksOn VALUES ('333333301',63,46.0);
INSERT INTO worksOn VALUES ('444444400',91,40.0);
INSERT INTO worksOn VALUES ('444444401',91,40.0);
INSERT INTO worksOn VALUES ('444444402',91,40.0);
INSERT INTO worksOn VALUES ('444444403',91,40.0);
INSERT INTO worksOn VALUES ('555555500',92,40.0);
INSERT INTO worksOn VALUES ('555555501',92,44.0);
INSERT INTO worksOn VALUES ('666666601',91,40.0);
INSERT INTO worksOn VALUES ('666666603',91,40.0);
INSERT INTO worksOn VALUES ('666666604',91,40.0);
INSERT INTO worksOn VALUES ('666666605',92,40.0);
INSERT INTO worksOn VALUES ('666666606',91,40.0);
INSERT INTO worksOn VALUES ('666666607',61,40.0);
INSERT INTO worksOn VALUES ('666666608',62,40.0);
INSERT INTO worksOn VALUES ('666666609',63,40.0);
INSERT INTO worksOn VALUES ('666666610',61,40.0);
INSERT INTO worksOn VALUES ('666666611',61,40.0);
INSERT INTO worksOn VALUES ('666666612',61,40.0);
INSERT INTO worksOn VALUES ('666666613',61,30.0);
INSERT INTO worksOn VALUES ('666666613',62,10.0);
INSERT INTO worksOn VALUES ('666666613',63,10.0);

A sample procedure

CREATE OR REPLACE PROCEDURE insert_employee (
    Fname        VARCHAR(15),
    Minit           CHAR,
    Lname        VARCHAR(15),
    Ssn             CHAR(9),
    Bdate         DATE,
    Address     VARCHAR(50),
    Sex             CHAR,
    Salary         Decimal(10,2),
    Super_ssn  CHAR(9),
    Dno           INT
LANGUAGE plpgsql
AS $$
BEGIN
     INSERT INTO employee VALUES
    (Fname, Minit, Lname, Ssn, Bdate, Address , Sex, Salary, Super_ssn, Dno);
END;
$$;

CALL insert_employee('Josh','U','Zell','223222201','25-MAY-1964','266 McGrill, Mills, WA', 'M', 56000, '222252200', 6);