PostgreSQL Handbook (🚧Continuous Update)
This is a collection of useful PostgreSQL commands and snippets that I have used in the past. It will be continuously updated. ❗ How to execute psql commands in PostgreSQL docker container # 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;" 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 ...
