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 existing_version AS (
    SELECT version_id 
    FROM course_versions 
    WHERE course_id = 1 AND version_number = 3
), next_version_id AS (
    SELECT COALESCE(
        (SELECT version_id FROM existing_version),
        (SELECT COALESCE(MAX(version_id), 0) + 1 FROM course_versions)
    ) AS new_version_id
)
INSERT INTO course_versions (version_id, course_id, version_number, title, description, created_at)
VALUES (
    (SELECT new_version_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();