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

PostgreSQL code reaching beyond EXISTS operator even for negation condition

for the below code, I have deleted the hist.game_hist table from db.
Now, according to the code below, I want my code to pickup the else condition and avoid going into the then condition (as game_hist does not exists, it will throw error).

But Till now, when I try to run this code, I get the error that, from then condition "hist.game_hist does not exists".

SELECT
    pi.*
FROM stg.stg_game pi

WHERE pi.status != '9'

AND pi.hvr_capture_timestamp::timestamp >
    (CASE
        WHEN EXISTS (
            SELECT 1
            FROM information_schema.tables
            WHERE table_schema = 'hist' AND table_name = 'game_hist'
        )
        THEN COALESCE(
            (SELECT max(dbt_updated_at) - INTERVAL '120 minutes' FROM hist.game_hist),
            '1900-01-01'::timestamp
        )
        ELSE '1900-01-01'::timestamp
    end)

for the above code, I have deleted the hist.game_hist table from db.
Now, according to the code below, I want my code to pickup the else condition and avoid going into the then condition (as game_hist does not exists, it will throw error).

But Till now, when I try to run this code, I get the error that, from then condition "hist.game_hist does not exists".

Tags:

2

Answers


  1. 0

    The problem you are facing comes from the database’s attempt to assess the subquery in the THEN condition even when table doesn’t exist. You can conditionally run the subquery only if the table exists by using dynamic SQL to prevent this.
    Try this updated code. This code conditionally executes the subquery only if the table exists using a PL/pgSQL block with dynamic SQL. It immediately sets max_dbt_updated_at to the default value if the table is not found, preventing the issue you were experiencing.

    DO $$ 
DECLARE
    table_exists BOOLEAN;
    max_dbt_updated_at TIMESTAMP;
BEGIN
    -- Check if the table exists
    SELECT EXISTS (
        SELECT 1
        FROM information_schema.tables
        WHERE table_schema = 'hist' AND table_name = 'game_hist'
    ) INTO table_exists;

    -- Use dynamic SQL to conditionally execute the subquery
    IF table_exists THEN
        EXECUTE 'SELECT COALESCE(max(dbt_updated_at) - INTERVAL ''120 minutes'', ''1900-01-01''::timestamp) FROM hist.game_hist'
        INTO max_dbt_updated_at;
    ELSE
        max_dbt_updated_at := '1900-01-01'::timestamp;
    END IF;

    -- Your main query
    SELECT
        pi.*
    FROM stg.stg_game pi
    WHERE pi.status != '9'
    AND pi.hvr_capture_timestamp::timestamp > max_dbt_updated_at;

END $$;

    Hope it’s helpful 🙂

    Login or Signup to reply.
  2. 0

    The following query produces the desired results the OP described:

    SELECT pi.*
  FROM stg.stg_game pi
  WHERE pi.status != '9'
    AND pi.hvr_capture_timestamp::timestamp > 
        COALESCE((SELECT (XPATH('/table/row/earliest_ts/text()',
                                QUERY_TO_XML(FORMAT($$SELECT MAX(dbt_update_at) - INTERVAL '120' MINUTE AS earliest_ts FROM %1$I.%2$I$$,
                                                    ist.table_schema,
                                                    ist.table_name),
                                             FALSE,
                                             FALSE,
                                             '')))[1]::text::timestamp
                    FROM information_schema.tables ist
                   WHERE ist.table_schema = 'hist'
                     AND ist.table_name = 'game_hist'), '1900-01-01'::timestamp);

    This approach takes advantage of QUERY_TO_XML‘s ability to execute dynamic SQL. Because it is a SQL query instead of a PL/pgSQL anonymous block, it can be used directly as part of a larger SQL query. If hist.game_hist doesn’t exist, then the SELECT list in the infromation_schema.tables subquery is not evaluated, and the subquery returns NULL. This query also avoids repeating the default value for the capture timestamp comparison.

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

Back To Top
Search