sudo -u postgres psql
Or:
 
psql -U postgres
Main psql Commands
\?              # List psql commands
\h              # List SQL commands
\i <file>       # Execute SQL file
\l              # List databases
\conninfo       # Show connection info
\connect <db>   # Connect to database
\dt             # List tables
\q              # Quit
2. Managing Databases
Create a User (Role)
CREATE ROLE username;
Example:
 
CREATE ROLE thomas;
Create a Database
CREATE DATABASE database_name OWNER role;
Example:
 
CREATE DATABASE starwars OWNER glucas;
Create a Table
CREATE TABLE table_name (
  column1 type,
  column2 type,
  column3 type
);
Example:
 
CREATE TABLE players (
  fullname VARCHAR(200),
  age INTEGER
);
Modify a Table
ALTER TABLE table_name
ADD COLUMN column_name data_type;
Example:
 
ALTER TABLE players
ADD COLUMN birthday DATE DEFAULT NULL;
Other possible actions:
 
DROP COLUMN
 
ALTER COLUMN SET DEFAULT
 
ALTER COLUMN SET NOT NULL
 
RENAME COLUMN
 
RENAME TO
 
Drop a Table
DROP TABLE table_name;
Example:
 
DROP TABLE players;
Table Constraints
Primary Key
PRIMARY KEY (id)
Foreign Key
FOREIGN KEY (player_id)
REFERENCES player(id)
NOT NULL
name VARCHAR(100) NOT NULL
DEFAULT
DEFAULT 'Unknown Gamer'
UNIQUE
UNIQUE (email)
CHECK
CONSTRAINT positive_price CHECK (price > 0)
3. Querying Data
Select All Columns
SELECT * FROM table_name;
Example:
 
SELECT * FROM players;
Select Specific Columns
SELECT column1, column2 AS alias
FROM table_name;
Example:
 
SELECT name, experience AS exp
FROM players;
Filtering Results
SELECT column
FROM table
WHERE condition;
Example:
 
SELECT name
FROM players
WHERE level > 50 AND premium = true;
Sorting Results
SELECT *
FROM table
ORDER BY column1 ASC, column2 DESC;
Example:
 
SELECT *
FROM players
ORDER BY lastname, firstname;
Limiting Results
SELECT *
FROM table
LIMIT 10 OFFSET 5;
4. Managing Data
Insert Data
INSERT INTO table_name (columns)
VALUES (values);
Example:
 
INSERT INTO players (name, level, premium)
VALUES ('Thomas', 10, true);
Notes on INSERT
SERIAL auto-increments automatically
 
Missing values use DEFAULT
 
If a column is NOT NULL and no value is provided → insertion fails
 
Multiple rows can be inserted in one query:
 
INSERT INTO table (col1, col2)
VALUES
  ('a', 1),
  ('b', 2);
Update Data
UPDATE table_name
SET column = value
WHERE condition;
Example:
 
UPDATE players
SET premium = true
WHERE monthly_sub_amount >= 20;
⚠️ Always use WHERE to avoid updating every row.
 
Delete Data
DELETE FROM table_name
WHERE condition;
Example:
 
DELETE FROM players
WHERE last_login < NOW() - INTERVAL '60 days';