Postgresql
Basic Operations
Create Table
CREATE TABLE users( id INTEGER NOT NULL PRIMARY KEY, username TEXT NOT NULL, created_at TIMESTAMP DEFAULT current_timestamp, updated_at TIMESTAMP)
Alter Table
-- add new columnALTER TABLE usersADD COLUMN email TEXT NOT NULL;
-- rename columnALTER TABLE usersRENAME COLUMN email TO mail;
-- delete columnALTER TABLE usersDROP COLUMN mail;
Add Index
CREATE INDEX IF NOT EXISTS user_username_index ON users (username);
Add Check
ALTER TABLE usersADD CONSTRAINT username CHECK (LENGTH(username) >= 3); -- only allow username length >= 3
Add Unique
ALTER TABLE usersADD CONSTRAINT users_username_unique UNIQUE (username);
Add Foreign Key
We have 2 options to add foreign key
-- add foreign key using createCREATE TABLE IF NOT EXISTS posts( id INTEGER NOT NULL PRIMARY KEY, title TEXT NOT NULL, user_id INTEGER NOT NULL REFERENCES users(id) -- add foreign key)
-- add foreign key using alterALTER TABLE postsADD CONSTRAINT posts_user_id_foreign FOREIGN KEY (user_id) REFERENCES users(id);
Delete Table
DROP TABLE users;
Truncate Table
TRUNCATE TABLE users;
Tips and Tricks
Automate created_at and update_at column
Create function and trigger to automatically update created_at
and updated_at
column.
-- create set_timestamp function that will set updated_at columnCREATE OR REPLACE FUNCTION set_timestamp()RETURNS TRIGGER AS $$BEGIN NEW.updated_at = NOW(); RETURN NEW;END;$$ LANGUAGE plpgsql;
-- create trigger for our users table for UPDATE queryCREATE TRIGGER set_timestampBEFORE UPDATE ON usersFOR EACH ROWEXECUTE PROCEDURE set_timestamp();