How do I perform a dynamic WHERE clause in QuestDB?

I’m looking to create a dynamic WHERE clause in QuestDB for something like the following example SQL:

DECLARE @ColumnA VARCHAR(20)
DECLARE @ColumnB VARCHAR(25)
DECLARE @SQL NVARCHAR(1000)

SET @SQL = 'SELECT
    ColumnA,
    ColumnB
FROM TestData
WHERE 1=1'

IF(@ColumnA IS NULL)
BEGIN
    SET @SQL = @SQL + '
               AND ColumnA = ''' + @ColumnA + ''''
END

IF(@ColumnB IS NULL)
BEGIN
    SET @SQL = @SQL + ' 
                AND ColumnB = ''' + @ColumnB + ''''
END

EXEC(@SQL)

What’s the nicest way to do this? I can’t find guidance in the WHERE keyword documentation

Answer

This depends on what you’re trying to achieve, but what might be useful is using coalesce() which was added in release 5.0.6.1. The function is designed to work like the Postgres COALESCE keyword and returns the first non-null argument. There are ways that this could be included in your statement, but it’s useful if you want to set a default value for a column if it returns null:

SELECT
    coalesce(ColumnA, 0.12) as column_a_not_null
    coalesce(ColumnB, 2.34) as column_b_not_null
FROM TestData

if you want to omit rows where both columns are null, you can use:

SELECT
    ColumnA,
    ColumnB
FROM TestData
WHERE ColumnA != null OR ColumnB != null

and if you want to omit rows where null is in any column, you could use

SELECT
    ColumnA,
    ColumnB
FROM TestData
WHERE ColumnA != null AND ColumnB != null

If you want to see more examples on coalesce(), you can have a look at the conditional functions documentation.