Skip to main content

Useful PostgreSQL queries or commands

· 4 min read
Hreniuc Cristian-Alexandru

Useful documentation with examples:

Connect to the postgresql

psql -p 5432 -h localhost -U postgres -W

Database and Schema

Show all databases

\l
# Or
SELECT datname FROM pg_database;

Switch to another database

Similar to USE from mysql.

\c databasename

Create database

Documentation

CREATE DATABASE test_db;

Drop/Delete database

Documentation

DROP DATABASE IF EXISTS test_db;

A database may contain multiple schemas, which groups tables from a database. By default public schema is created for every database. And also you don't have to specify it in your queries, eg: public.staff is the same as staff. More examples and documentation can be checked here

User/Role

Show all users

\du
# With description
\du+
# Or sql
SELECT rolname FROM pg_roles;

Create a user

Documentation

CREATE USER test_user WITH PASSWORD 'pass';

Grant user rights to connect to db

GRANT CONNECT ON DATABASE test_db TO test_user;

Try to connect afterwards:

psql -p 5432 -h localhost -U test_user -W -d test_db

Create superuser

Documentation

CREATE ROLE test_user2 WITH LOGIN SUPERUSER CREATEDB PASSWORD 'pass';
# Connect afterwards
psql -p 5432 -h localhost -U test_user2 -W -d postgres

Alter user, add createdb rights

ALTER ROLE test_user2 WITH CREATEDB;

Grant all privileges on the database

Documentation

GRANT ALL PRIVILEGES ON DATABASE test_db TO test_user2;

Common users

You ussually need the following users:

Reader:

CREATE USER reader_user WITH PASSWORD 'reader_user';
GRANT CONNECT ON DATABASE test_db TO reader_user;

\c databsename
ALTER DEFAULT PRIVILEGES
FOR USER reader_user
IN SCHEMA public
GRANT SELECT ON TABLES TO reader_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO reader_user;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO reader_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO reader_user;

Writer:

CREATE USER reader_writer WITH PASSWORD 'reader_writer';
GRANT CONNECT ON DATABASE test_db TO reader_writer;

\c databsename
ALTER DEFAULT PRIVILEGES
FOR USER reader_writer
IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO reader_writer;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO reader_writer;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO reader_writer;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO reader_writer;

Admin

CREATE USER admin WITH PASSWORD 'admin';
GRANT CONNECT ON DATABASE test_db TO admin;

\c test_db
ALTER DEFAULT PRIVILEGES
FOR USER admin
IN SCHEMA public
GRANT ALL ON TABLES TO admin;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON SEQUENCES TO admin;

GRANT ALL ON ALL TABLES IN SCHEMA public TO admin;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO admin;

Tables

Create table

Documentation, official documentation

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

CREATE TABLE address (
id serial PRIMARY KEY,
name VARCHAR ( 50 ) NOT NULL
);

CREATE TABLE people (
id serial PRIMARY KEY,
name VARCHAR ( 50 ) NOT NULL
);

Describe table

Documentation

\d accounts

Show tables

Documentation

\dt+

Grant SELECT on table

Documentation, Official docs

First you need to connect with an user that has grant privileges and then switch to the database that contains that table:

\c test_db
GRANT SELECT ON accounts TO test_user;

Grant select on all tables even when new tables are added*

Documentation, official documentation

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO test_user;

See rights for specific table

Docs

\dp table
# Default priv
\ddp table