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

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