PostgreSQL User Guide¶

This page contains common PostgreSQL tasks and commands for daily use.

Connect to PostgreSQL¶

psql -h 127.0.0.1 -U postgres -d postgres

List databases¶

\l

List roles¶

\du

List schemas¶

\dn

List tables¶

\dt

Create table¶

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Insert data¶

INSERT INTO users (name, email)
VALUES ('Arun', 'arun@example.com');

Read data¶

SELECT * FROM users;

Update data¶

UPDATE users
SET name = 'Arun Prasher'
WHERE id = 1;

Delete data¶

DELETE FROM users
WHERE id = 1;

Grant permissions¶

GRANT CONNECT ON DATABASE mydb TO tenant_user;
GRANT USAGE ON SCHEMA public TO tenant_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO tenant_user;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO tenant_user;

Helpful tips¶

• Always specify the database using -d
• Use roles instead of sharing one admin account
• Grant only required permissions
• Separate app users from admin users