Question 247043 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – CASE tries to evaluate subexpressions before checking the expression

PostgreSQL version: 15

I have dynamic tables with dynamic columns and I need to calculate some abstract property length. If column is array then I need to use array_length function, if column is string then length.

I tried to use CASE WHEN THEN:

SELECT system_id
FROM models."model_e8467f0e-780d-45d0-b665-290452d0eae1"
WHERE (
    (
        SELECT CASE (SELECT element_type FROM elements WHERE id = '80bbc0ee-9f0b-4b16-9188-7d0b63c7c45d'::UUID)
            WHEN 4 THEN length("element_80bbc0ee-9f0b-4b16-9188-7d0b63c7c45d")
            WHEN 5 THEN array_length("element_80bbc0ee-9f0b-4b16-9188-7d0b63c7c45d", 1)
            WHEN 6 THEN (
                SELECT COUNT(child_id)
                FROM m2m_entry_n_entry_element_values
                WHERE parent_id = system_id
            )
        END
    ) >= 1
)

In this example, the column in the elements table has element_type = 6, so last branch should trigger. But function length(uuid[]) does not exist error was raised, which means that postgres tried to calculate the first subexpression, although the documentation says the following:

A CASE expression does not evaluate any subexpressions that are not needed to determine the result.

Tags:

2

Answers


  1. 0

    Postgres goes through a multi-stage process before running the query. It may determine what each subexpressions is, even if it doesn’t call the functions in question. Postgres has to plan how it’s going to execute the query and what optimisations it can apply.

    It’s probably a nicer strategy to notify you earlier, rather than forcing you to wait for a long running query before failing.

    For details of what Postgres is doing, see it’s documentation here: https://www.postgresql.org/docs/current/overview.html

    Login or Signup to reply.
  2. 0

    A statement is parsed before it is executed. Since the column "element_80bbc0ee-9f0b-4b16-9188-7d0b63c7c45d" seemd to be of type uuid[] (array of uuid), the parser complains that no length() function for that data type exists.

    This happens before the query even starts executing.

    Your SQL statement has some fundamental problems and you get alerted to them early on.

    Avoid using array types in column definitions, that usually leads to trouble.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search