Back

Relationships (one-to-many)

Tables can be related to each other. A user can have many posts. A post belongs to one user. This is called a one-to-many relationship.

-- Users table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

-- Posts table (belongs to a user)
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT,
  user_id INTEGER REFERENCES users(id),  -- foreign key
  created_at TIMESTAMP DEFAULT NOW()
);

-- Get all posts with author name
SELECT posts.title, users.name AS author
FROM posts
INNER JOIN users ON posts.user_id = users.id;

Roadmap