Syntax error on if statement with exists

CrateDB SQL
1

I am new to CrateDB, new to postgresql, and so so with sql in general.

I am trying to execute the following sql statements but keep getting an error and cannot figure out what is going on:

IF EXISTS(SELECT 1 FROM crate.doc.schema_versions WHERE script_name = ‘DbUpDemo.Scripts.Script0001-InitialCreate.sql’) THEN

select 'if was true';

else
select ‘if was NOT true’;
end if;

I keep getting this error:
SQL Error [XX000]: ERROR: line 1:7: mismatched input ‘IF’ expecting {‘SELECT’, ‘DEALLOCATE’, ‘FETCH’, ‘END’, ‘WITH’, ‘CREATE’, ‘ALTER’, ‘KILL’, ‘CLOSE’, ‘BEGIN’, ‘START’, ‘COMMIT’, ‘ANALYZE’, ‘DISCARD’, ‘EXPLAIN’, ‘SHOW’, ‘OPTIMIZE’, ‘REFRESH’, ‘RESTORE’, ‘DROP’, ‘INSERT’, ‘VALUES’, ‘DELETE’, ‘UPDATE’, ‘SET’, ‘RESET’, ‘COPY’, ‘GRANT’, ‘DENY’, ‘REVOKE’, ‘DECLARE’}
Where: io.crate.exceptions.SQLExceptions.esToCrateException(SQLExceptions.java:211)…

However the following query returns 1 record with a ‘t’:
select EXISTS(SELECT 1 FROM crate.doc.schema_versions WHERE script_name = ‘DbUpDemo.Scripts.Script0001-InitialCreate.sql’)

2

What you want are case when ... then ... end Scalar functions — CrateDB: Reference

Something like

SELECT
  CASE
    WHEN EXISTS(
      SELECT
        1
      FROM
        crate.doc.schema_versions
      WHERE
        script_name = 'DbUpDemo.Scripts.Script0001-InitialCreate.sql'
    ) THEN 'if was true'
    ELSE 'if was NOT true'
  END
3

Also using the if

SELECT
  IF(
    EXISTS(
      SELECT
        1
      FROM
        crate.doc.schema_versions
      WHERE
        script_name = 'DbUpDemo.Scripts.Script0001-InitialCreate.sql'
    ),
    'EXISTS',
    'DOESN EXISTS'
  )
4

thank you for the quick response…maybe my example with the select was misleading. I was trying to simplify the problem but maybe i changed the problem in an effort to simplify it. i dont really want to select anything but run some other sql statements

IF EXISTS(SELECT 1 FROM crate.doc.schema_versions WHERE script_name = ‘DbUpDemo.Scripts.Script0001-InitialCreate.sql’) THEN

–run some update statements

else
–run some insert statements
end if;

5

CrateDB currently does not support a procedural language like PL/pgSQL were one could do something like:

DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM crate.doc.schema_versions WHERE script_name = 'DbUpDemo.Scripts.Script0001-InitialCreate.sql') THEN
        -- Run some update statements
        UPDATE your_table
        SET column1 = value1
        WHERE some_condition;
    ELSE
        -- Run some insert statements
        INSERT INTO your_table (column1, column2)
        VALUES (value1, value2);
    END IF;
END $$;
6

You may also want to take a look at INSERT … ON CONFLICT … DO UPDATE SET …, it seems you may be trying to maintain reference data as part of some db migrations/release scripts, and if rows do not need to be deleted this may be just what you need.