Sunday, March 07, 2021

PostgreSQL intro

PostgreSQL :



psql CLI :

psql option dbname username

i.e. :


export PGPASSWORD=pg-pass

psql -h pg-host -U postgres


psql -c '\x' -c 'SELECT * FROM current_catalog;'


echo '\x \\ SELECT * FROM current_catalog;' | psql


psql <<EOF

\x

SELECT * FROM current_catalog ;

EOF


src: psql CLI doc



CLI FILES :

PasswordLess login:

Linux : ~/.pgpass - pg pass

Win :   %APPDATA%\postgresql\pgpass.conf

format: hostname:port:database:username:password

env :   set PGPASSFILE=C:\Program Files\someapp\pgpass.conf

note :  if env PGPASSWORD is set, ~/.pgpass is ignored

nopass: sleeplessbeastie

bash :  chmod u-x,go-rwx ~/.pgpass



CLI SWITCHES :

-c command

-d dbname

-h host

-U user

-W --password

-f command-input-file

-l == list all available databases

\x = toggle expanded table formatting mode

-o output-file.txt



CLI commands :

\l - Display database

\list

\c - Connect to database

\conninfo

\dn - List schemas

\dt - List tables inside public schemas

\dt schema1. - List tables inside particular schemas. For eg: 'schema1'.

stackexchange



Create table :

CREATE TABLE accounts (

user_id serial PRIMARY KEY,

username VARCHAR ( 50 ) UNIQUE NOT NULL,

password VARCHAR ( 50 ) NOT NULL,

email VARCHAR ( 255 ) UNIQUE NOT NULL,

created_on TIMESTAMP NOT NULL,

        last_login TIMESTAMP 

);

\d = list tables

\d accounts = describe table accounts

DROP TABLE accounts ;

CREATE SCHEMA myschema ;

CREATE TABLE myschema.mytable ... ;

DROP SCHEMA myschema ;

SELECT CURRENT_TIMESTAMP;

src: tutorialspoint

src: postgresqltutorial - M;N table relation



Show current info - session, user, schema, database :

SELECT current_database();

SELECT * FROM current_catalog;

SELECT current_catalog;


SHOW DateStyle;

SHOW search_path ;

SET search_path TO myschema ;


SELECT * FROM pg_stat_activity;

SELECT * 

FROM pg_stat_activity 

WHERE datname = 'dbname' 

 and state = 'active';

src: stackoverflow - stackoverflow



Postgres with Docker :

:: Start Postgres

docker run --rm --name mars-postgres -e POSTGRES_PASSWORD=pg-pass -d postgres

:: Create bridge

docker network create -d bridge mars_bridge_postgres

:: Connect already existing container to network

docker network connect mars_bridge_postgres mars-postgres


:: Disconnect container from network
docker network disconnect mars_bridge_postgres mars-postgres

:: Stop Postgres
docker container stop mars-postgres

:: Remove Docker network
docker network rm mars_bridge_postgres


:: CLI
@echo Pass is pg-pass
@rem PGPASSWORD=pg-pass
@rem docker run -it --rm --network mars_bridge_postgres postgres psql -h mars-postgres -U postgres
docker run -it --rm --network mars_bridge_postgres postgres bash -c "PGPASSWORD=pg-pass psql -h mars-postgres -U postgres"



PG tutorial :

https://www.tutorialspoint.com/postgresql/postgresql_quick_guide.htm

https://www.postgresqltutorial.com/


PG data types :

https://www.postgresqltutorial.com/postgresql-data-types/


Import CSV to SQL  in Postgre :

https://www.postgresqltutorial.com/import-csv-file-into-posgresql-table/


Export SQL to CSV from Postgre :

https://www.postgresqltutorial.com/export-postgresql-table-to-csv-file/


PG Admin :

https://www.pgadmin.org/

https://www.pgadmin.org/download/