PostgreSQL Handbook (🚧Continuous Update)

1. Init a database

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

2. Duplicate a table

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

3. Change the owner of a table and view

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

4. Drop a column

ALTER TABLE tablename DROP COLUMN category_id;

5. Install “uuid-ossp”

apt-get install postgresql-contrib-9.3

6. Check index

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

7. Connect to the remote database by CLI

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

8. Change a column name

ALTER TABLE client_profiles RENAME COLUMN user_id TO profile_id

9. Install Postgres

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

10. Set the access permission of other machines

/etc/postgresql/9.1/main/pg_hba.conf
# ipv4
host all all 0.0.0.0/0 md5 (e.g.  192.168.0.0/16)
host all all 192.168.239.133/16 md5 
/etc/postgresql/9.1/main/postgresql.conf
listen_address='*'

Restart database

sudo service postgresql restart

11. Change user password

\password osmapi

12. List all users

\list

13. Set UTF8 to WIN1252

set client_encoding to 'WIN1252'

14. Check the privileges inside psql

\dn+ public

15. Rebuild schema

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

16. Copy one column to another column

update table set columnB = columnA

17. How to find the data directory

Connect to a database

SHOW data_directory;

18. Uninstall PostgreSQL

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

19. Change a table name

ALTER TABLE table_name RENAME TO new_table_name

20. Add auto incremental primary key

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

21. Reset sequence

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

22. Grant read for all users

GRANT SELECT ON ALL TABLES IN SCHEMA public TO dataantapi

23. Change a contranit from Not Null to Nullable

ALTER TABLE users ALTER COLUMN password DROP NOT NULL

24. Get and set timezone

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

SET TIME ZONE 'UTC';

25. List all databases

SELECT datname FROM pg_database WHERE datistemplate = false;

26. Dump a specific table

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

27. Query db connection states

SELECT * FROM pg_stat_activity;

28. Only export the specific database’s tables’ schema

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

29. Export the database’s tables’ data

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

30. Import the database’s tables’ data

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

If this post helped you to solve a problem or provided you with new insights, please upvote it and share your experience in the comments below. Your comments can help others who may be facing similar challenges. Thank you!
Buy Me A Coffee
DigitalOcean Referral Badge
Sign up to get $200, 60-day account credit !