PostgreSQL — Tables

Create Table

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

List Tables

SELECT table_name FROM information_schema.tables 
WHERE table_schema = 'public';

Or psql: \dt

Describe Table

\d users

Or:

SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_name = 'users';

Alter Table

-- Add column
ALTER TABLE users ADD COLUMN age INTEGER;

-- Drop column
ALTER TABLE users DROP COLUMN age;

-- Rename column
ALTER TABLE users RENAME COLUMN name TO full_name;

-- Change type
ALTER TABLE users ALTER COLUMN age TYPE SMALLINT;

Drop Table

DROP TABLE users;
DROP TABLE IF EXISTS users;

Mini Practice

1. Create a table
2. List all tables
3. Describe table structure
4. Alter table

Up Next

Continue with Create Table — creating tables.