PostgreSQL commands cheat sheet

Posted on In Views: Word count in article: 584 Reading time ≈ 1 mins.

Create a new user

1
CREATE USER username WITH PASSWORD 'password';

Create a new database belong to specified user and grant all privileges to the user

1
2
3
4
CREATE DATABASE dbname OWNER username;
GRANT ALL PRIVILEGES ON DATABASE dbname TO username;
\c dbname
ALTER SCHEMA public OWNER TO username;

Check current tables in the database

1
\dt

Delete everything in the database

1
2
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

Migragte data from one database to another

1
2
3
4
# psql dump data from source database to a sql file
pg_dump -U username -h localhost sourcedb > dump.sql
# psql import data from sql file to target database
psql -U username -h localhost targetdb < dump.sql