Search code examples
sqlfirebird

Add column to firebird table only if it not exists and ignore ALTER statement otherwise

I need to add a column if it does not exist in a Firebird table. According to official docs How to add a column only if it does not exist in table:

EXECUTE block as
BEGIN
    if (not exists(
        select 1 from RDB$RELATION_FIELDS rf
        where rf.RDB$RELATION_NAME = 'TABLE_NAME' and rf.RDB$FIELD_NAME = 'COLUMN_NAME'))
then
execute statement 'ALTER TABLE TABLE_NAME ADD COLUMN_NAME DECIMAL(6,6);';
END

This code works perfectly for the first time of execution (if column does not exist - it is being created). But when this code runs for the second time (when a column already exists) - an error is being thrown:

enter image description here

My JavaScript code catches this error and program stops execution. What I need: if a column already exists - just to ignore that block.

Solution

  • I solved this task in another way: first - check if a column exists:

    SELECT RDB$FIELD_NAME AS FIELD_NAME
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='my_table_name'
AND RDB$FIELD_NAME = 'my_field_name';

    As a resut I get an array. If array contains FIELD_NAME - just continue running other code. If array is empty (which means the column 'my_field_name' isn't there) - I run ALTER statement:

    ALTER TABLE my_table_name ADD my_field_name DECIMAL(6,6);