Syntax error on ALTER COLUMN - Ingres

0
votes

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.

1
sqlingres
Ingres doesn't appear to support modifying a column. You can drop the column and add it again. - Gordon Linoff
Does this mean I would lose all the data in the column? There is thousands of data entries. - gio10245
Rename it first then create a new column and copy the old data,then drop it - Mihai
Ingres doesn't support rename either Error: line 1, Syntax error on 'RENAME'. The correct syntax is: bla bla ADD DROP - gio10245

1 Answers

0
votes

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.