Skip to content

Instantly share code, notes, and snippets.

@maheshj01
Last active February 10, 2024 20:10
Show Gist options
  • Save maheshj01/f9149479a3eda49d34ae53aa4c6e80dc to your computer and use it in GitHub Desktop.
Save maheshj01/f9149479a3eda49d34ae53aa4c6e80dc to your computer and use it in GitHub Desktop.
docs for using postgres sql cli tool psql

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
@maheshj01
Copy link
Author

maheshj01 commented Feb 12, 2023

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;

@maheshj01
Copy link
Author

maheshj01 commented Feb 12, 2023

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);

@maheshj01
Copy link
Author

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);

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment