maheshmnj/postgres.md

## postgres.md

      
    Raw
  

              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