SQL Generation

Insert a new course version for course_id = 1 and version_number = 3, with title = 'SQL Basics v3', description = 'Added advanced topics',if that (course_id, version_number) already exists, update the title, description, and set created_at to NOW(). Care with unique constraints, if you need to provide a version_id use the next available.

INSERT INTO course_versions (version_id, course_id, version_number, title, description, created_at) SELECT (SELECT COALESCE(MAX(version_id), 0) + 1 FROM course_versions), 1, 3, 'SQL Basics v3', 'Added advanced topics', NOW() ON CONFLICT (course_id, version_number) DO UPDATE SET title = EXCLUDED.title, description = EXCLUDED.description, created_at = NOW() RETURNING *;

WITH next_version_id AS (
  SELECT COALESCE(MAX(version_id), 0) + 1 AS next_id
  FROM course_versions
)
INSERT INTO course_versions (
    version_id,
    course_id,
    version_number,
    title,
    description,
    created_at
)
VALUES (
    (SELECT next_id FROM next_version_id),
    1,
    3,
    'SQL Basics v3',
    'Added advanced topics',
    NOW()
)
ON CONFLICT (course_id, version_number)
DO UPDATE SET
    title = EXCLUDED.title,
    description = EXCLUDED.description,
    created_at = NOW();