PostgreSQL Handbook (🚧Continuous Update)

# List all running containers to find your Postgres container name or ID
docker ps 

# Open an interactive shell inside the Postgres container
docker exec -it postgres_db bash  

# Log in to PostgreSQL as the 'postgres' user
psql -U postgres  

# List all databases in the PostgreSQL server
\l  

# Connect to a specific database
\c your_database_name

# List all tables in the connected database
\dt  

# Display all records from the specified table
SELECT * FROM your_table_name;  

# Display the first 10 records from the table (optional)
SELECT * FROM your_table_name LIMIT 10;  

# Show the structure (columns, types, indexes) of the table
\d your_table_name  

# Toggle expanded view for better readability of wide rows
\x  

# Exit the PostgreSQL prompt
\q  

# Exit the container shell
exit  

# (Alternative: run a one-liner query directly from your host)
# Run SQL query without entering the container
docker exec -it postgres_db psql -U postgres -d your_database_name -c "SELECT * FROM your_table_name LIMIT 10;"  

sudo su postgres
psql

create database osmapi
create user osmapi
\password osmapi
grant all privileges on database "osmapi" to osmapi
\c osmapi
create extension "uuid-ossp"
create extension cube
create extension earthdistance

create table dupe_users as (select * from users);
create table dupe_users as (select * from users) with no data;

ALTER TABLE public.tablename OWNER TO my_user;
ALTER VIEW latest_status_logs OWNER TO my_user;

ALTER TABLE tablename DROP COLUMN category_id;

apt-get install postgresql-contrib-9.3

SELECT * FROM pg_indexes WHERE TABLENAME NOT LIKE 'pg%'
DROP INDEX xxx
alter table activity_auditings drop constraint activity_auditings_extra_curriculum_no_key

psql -d mydb -U myuser 
psql -h myhost -d mydb -U myuser

psql --host=dataant-test.cpkuy5lqfq7v.ap-southeast-2.rds.amazonaws.com \ 
  --port=5432 \ 
  --username=dataanttest \ 
  --password \ 
  --dbname=dataantapi

ALTER TABLE client_profiles RENAME COLUMN user_id TO profile_id

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

/etc/postgresql/9.1/main/pg_hba.conf

/etc/postgresql/9.1/main/postgresql.conf

sudo service postgresql restart

\password osmapi

\list

set client_encoding to 'WIN1252'

\dn+ public

drop schema public cascade
create schema public
grant usage on schema public to public
grant create on schema public to public

update table set columnB = columnA

SHOW data_directory;

sudo dpkg -l | grep postgres
sudo apt-get --purge remove postgresql postgresql-doc postgresql-common
sudo rm -Rf /etc/postgresql /var/lib/postgresql
sudo apt-get install postgresql

ALTER TABLE table_name RENAME TO new_table_name

ALTER TABLE public.extra_curriculums
    ALTER COLUMN id TYPE integer;
ALTER TABLE public.extra_curriculums
    ADD PRIMARY KEY (id);
ALTER TABLE extra_curriculums 
    ALTER COLUMN id SET DEFAULT nextval('extra_curriculums_pkey'::regclass);

SELECT * FROM information_schema.sequences;
ALTER SEQUENCE extra_curriculums_id_seq RESTART WITH 1231;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO dataantapi

ALTER TABLE users ALTER COLUMN password DROP NOT NULL

SELECT EXTRACT(TIMEZONE FROM now())/3600.0;

SET TIME ZONE 'UTC';

SELECT datname FROM pg_database WHERE datistemplate = false;

pg_dump -t <table> <database> > xxx.sql
pg_dump -h wisydb.cpkuy5lqfq7v.ap-southeast-2.rds.amazonaws.com \ 
  -U dataantapi \ 
  -t profiles timwlxcss > timwlxapi-profiles1609.sql

SELECT * FROM pg_stat_activity;

pg_dump -h prerelease.cpkuy5lqfq7v.ap-southeast-2.rds.amazonaws.com \ 
  -U dataantapi \ 
  -s wisydb > wisydb1611.sql

pg_dump -h prerelease.cpkuy5lqfq7v.ap-southeast-2.rds.amazonaws.com \ 
  -U dataantapi timwlx \ 
  -f timwlxapi2805.sql

psql -h 192.168.226.129 -U <table> <database> < xxx.sql