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) |
"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