How to fix "relation does not exist" ERROR even when using 'IF EXISTS-THEN' pgsql block?

Question

We are using Postgres 13.0 version with Spring-Boot .sql file as an initial step.

I need to run an UPDATE script but only if the table itself already exists.

After some effort to understand what is the correct syntax I came with the following script:

ALTER TABLE IF EXISTS ONLY scm_repos ADD COLUMN IF NOT EXISTS token_id BIGINT;
DO '
    BEGIN
        IF EXISTS
            (SELECT 1 FROM scm_repos WHERE id = 1)
        THEN
            UPDATE scm_repos repos SET token_id=(SELECT token_id FROM scm_orgs orgs WHERE repos.org_id=orgs.id);
        END IF ;
    END;
' ;

My intention is simple - to run the UPDATE script only if the scm_repos table does exists, but whatever I tried, I'm still getting the following error:

Failed to execute SQL script statement #5 of URL [jar:file:/app/cx-integrations-datastore.jar!/BOOT-INF/classes!/schema.sql]: DO '
    BEGIN
        IF EXISTS
            (SELECT 1 FROM scm_repos WHERE id = 1)
        THEN
            UPDATE scm_repos repos SET token_id=(SELECT token_id FROM scm_orgs orgs WHERE repos.org_id=orgs.id);
        END IF ;
    END;
' ; nested exception is org.********ql.util.PSQLException: ERROR: relation "scm_repos" does not exist
  Where: PL/pgSQL function inline_code_block line 3 at IF

What am I missing here?

score 3 · Accepted Answer · answered Oct 03 '21 at 16:08

13.0 has known unfixed bugs. 13.4 is the latest release of 13. There is almost never a good reason to run an old minor release version. Not that that seems to be relevant here.

But what you are missing here is that at the top level, EXISTS checks to see if a SELECT returns any rows. It does not check to see if tables mentioned in the FROM list of the SELECT exist or not, it assumes they do.

You could change your query so that it queries the catalog to see if the table exists, something like:

IF EXISTS 
    (SELECT 1 FROM pg_class where relname=$J$scm_repos$J$)
...

How to fix "relation does not exist" ERROR even when using 'IF EXISTS-THEN' pgsql block?

1 Answers1