postgresqlselectlateral

Postgres - How to use a LATERAL SELECT CASE to return a unique value instead of a list?


Considering the following table signatures referencing the signature date of a document by 2 persons

id p1_signed_at p2_signed_at
1 NULL NULL
2 01/01/2022 NULL
3 NULL 07/08/2022
4 03/04/2022 04/04/2022

I want to identify the next signatory of each document.

I tried to use a FROM LATERAL to be able to filter non-null rows, it's working, but the result is a list.

How can i make postgres understand that the identity comlumn is a single value ?

SELECT 
    "id",
    "identity"

FROM 
  "signatures",
  LATERAL (
    SELECT CASE 
        WHEN "p1_signed_at" IS NULL THEN 'p1'
        WHEN "p2_signed_at" IS NULL THEN 'p2'
    END) AS "identity"

WHERE 
    "identity" IS NOT NULL
id identity
1 (p1)
2 (p2)
3 (p1)

Solution

  • "identity" is a table alias, not a column alias. If you use that in the SELECT list, it will be shown as an anonymous record. You need to give your CASE expression a proper alias to refer to the column:

    SELECT 
        id,
        p."identity"
    FROM 
      signatures,
      LATERAL (
        SELECT CASE 
            WHEN p1_signed_at IS NULL THEN 'p1'
            WHEN p2_signed_at IS NULL THEN 'p2'
        END) AS p("identity")
    WHERE 
        p."identity" IS NOT NULL
    

    p("identity") defines a table alias with the name p and a column with the name "identity"


    The lateral cross join seems unnecessary, a simple CASE expression in the SELECT list would achieve the same.

    SELECT 
        id,
        CASE 
            WHEN p1_signed_at IS NULL THEN 'p1'
            WHEN p2_signed_at IS NULL THEN 'p2'
        END as "identity"
    FROM 
      signatures
    WHERE p1_signed_at is null 
       OR p2_signed_at is null;
    

    If you want to access the column alias of the CASE expression by name, you need to wrap this in a derived table:

    select *
    from (        
      SELECT 
          id,
          CASE 
              WHEN p1_signed_at IS NULL THEN 'p1'
              WHEN p2_signed_at IS NULL THEN 'p2'
          END as "identity"
      FROM 
        signatures
    ) x
    where "identity" is not null