Skip to content

Instantly share code, notes, and snippets.

@Pxtl

Pxtl/ExistsSelectStarFromGrouped_MSSql-vs-Postgres.sql

Created August 7, 2024 16:02
Download ZIP

Revisions

  1. Pxtl created this gist Aug 7, 2024.
    24 changes: 24 additions & 0 deletions ExistsSelectStarFromGrouped_MSSql-vs-Postgres.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,24 @@
    -- The following code works in MS SQL 2019
    -- But fails in Postgres 14.12 (PG 14 was released in 2021)

    DROP TABLE IF EXISTS EMPLOYEE;

    -- create
    CREATE TABLE EMPLOYEE (
    empId INTEGER PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    dept VARCHAR(50) NOT NULL
    );

    -- insert
    INSERT INTO EMPLOYEE VALUES (0001, 'Clark', 'Sales');
    INSERT INTO EMPLOYEE VALUES (0002, 'Dave', 'Accounting');
    INSERT INTO EMPLOYEE VALUES (0003, 'Ava', 'Sales');

    -- fetch
    SELECT CASE
    WHEN EXISTS (
    SELECT * FROM EMPLOYEE GROUP BY dept HAVING Count(*) > 1
    ) THEN 'there exists a dept with multiple employees'
    ELSE 'there are no depts with multiple employees'
    END AS Result;