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'.
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 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
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 :
