0

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)
EricD
  • 587
  • 6
  • 22

1 Answers1

1

"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