Solution & Challenge: Advanced Data Types · PostgreSQL

Ch 2 · PostgreSQL Data Types — card 4 of 4.

Compare your table and queries with the worked solution, which stores tags in an array and view counts in JSONB. The challenge then asks you to design a product catalogue where different products can have different attributes. A good answer uses JSONB for those flexible attributes, a text array for tags and a UUID for the public-facing ID, and queries all of them.

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  tags TEXT[],
  metadata JSONB DEFAULT '{}'
);
INSERT INTO posts (title, tags, metadata) VALUES
  ('Post 1', ARRAY['tech','pg'], '{"views": 100}'::jsonb);
SELECT * FROM posts WHERE 'tech' = ANY(tags);
SELECT title, metadata->>'views' AS views FROM posts;