Postgresql with aggregatin data

Postgresql with aggregatin data

Postgresql with aggregate data using subquery and join.

CREATE TABLE
  posts (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    is_published boolean default true
  );

-- Create the comments table
CREATE TABLE
  COMMENTS (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    post_id BIGINT NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts (id) ON DELETE CASCADE
  );

-- Insert sample data into posts
INSERT INTO
  posts (title, content, is_published)
VALUES
  (
    'First Post',
    'This is the content of the first post.',
    true
  ),
  (
    'Second Post',
    'This is the content of the second post.',
    false
  ),
  (
    'Third Post',
    'This is the content of the third post.',
    false
  );

-- Insert sample data into comments
INSERT INTO
  COMMENTS (post_id, CONTENT)
VALUES
  (1, 'This is a comment on the first post.'),
  (1, 'Another comment on the first post.'),
  (2, 'This is a comment on the second post.');

-- using sub-query
select
  id,
  title,
  content,
  is_published,
  (
    select
      string_agg(content, ', ')
    from
      comments
    where
      comments.post_id = posts.id
  ) as comments
from
  posts

-- using left join
select
  p.id,
  p.title,
  p.content,
  p.is_published,
  json_agg(c) as comments
from
  posts as p
  left join comments as c on p.id = c.post_id
group by
  p.id,
  p.title,
  p.content,
  p.is_published