Search code examples
sqlingres

Syntax error on ALTER COLUMN - Ingres

I have an sql script that I am running in via PUTTY to update an ingres DB

It is having errors with the following query (which work fine in Squirell SQL Client)

ALTER TABLE address 
ALTER COLUMN address2 VARCHAR(100); \p\g

Where I am trying to alter the address 2 column to allow for more characters.

It displays

E_US0F0A line 1, Syntax error on 'ALTER COLUMN'. The correct syntax is:

ALTER TABLE tablename ADD [CONSTRAINT constraint_name] constraint_clause | DROP CONSTRAINT constraint_name RESTRICT | CASCADE | ADD [COLUMN] columnname format [default_clause] [null_clause] [column_constraint] | DROP [COLUMN] column_name RESTRICT | CASCADE

It seems ingres only allows for add or drop.

Solution

  • This is too long for a comment. You need to:

    1. Add a new temp column with the right type.
    2. Update the temp column with the old value.
    3. Drop the old column.
    4. Add a new column with the right name and type.
    5. Update the new column with the value from the temp column.
    6. Drop the temp column.

    A bit cumbersome.