Cannot Alter Column in Squirell SQL Client

Question

I am trying to alter a column in my ingres DB to expand the size of the column.

The query i'm running is

ALTER TABLE test_table ALTER COLUMN address varchar(100) NOT NULL

Which gives error

Error: ALTER TABLE: invalid change of attributes on an ALTER COLUMN

SQLState: 42000 ErrorCode: 3859

Anyone any idea why I'm getting this error? I've checked the syntax for altering tables.

Try: `ALTER TABLE test_table MODIFY address varchar(100) NOT NULL` — Lukasz Szozda, Oct 14 '15 at 12:36
I'm guessing you have a varchar(<100) right now? is it nullable? If it isn't nullable you will probably have to mention default value for the empty rows. — Julien Blanchard, Oct 14 '15 at 12:46
Yeah it's varchar 50 now, the "is_nullable" column is set to YES — gio10245, Oct 14 '15 at 13:40
Simply the point is you have null in data. `UPDATE test_table SET address='' WHERE address IS NULL`; and then try changing to `NOT NULL` — Lukasz Szozda, Oct 14 '15 at 13:43
I just deleted the NOT NULL from my originally query and it worked? — gio10245, Oct 14 '15 at 13:46

Lukasz Szozda · Answer 1 · 2015-10-14T13:58:05.483

0

Probably you have NULL in data. Update your table first (set to empty string or any value you want):

UPDATE test_table
SET address = ''         -- or another value indicating missing addres like 'none'
WHERE address IS NULL;

And then try:

ALTER TABLE test_table ALTER COLUMN address varchar(100) NOT NULL;

EDIT:

If you don't want to enforce NOT NULL use just:

ALTER TABLE test_table ALTER COLUMN address varchar(100);

edited Oct 14 '15 at 13:58

answered Oct 14 '15 at 13:45

Lukasz Szozda

I just deleted the NOT NULL from my original query and it worked? – gio10245 Oct 14 '15 at 13:47
@gio10245 The point is you have NULL in data. When you ALTER TABLE and set NOT NULL it can't be enforced because of NULL. Update table then alter it and it should work – Lukasz Szozda Oct 14 '15 at 13:48
But it's working now by simply just removing the NOT NULL from my original query so why do I need to do this? – gio10245 Oct 14 '15 at 13:50
@gio10245 Question is do you want address that contain `NULL` or not? See demo I provided. – Lukasz Szozda Oct 14 '15 at 13:56
All i want to do is expand size, I wasn't sure if I needed "NOT NULL". The value in this column can be NULL if it wants to be so I removed the NOT NULL and it works? – gio10245 Oct 14 '15 at 13:57
@gio10245 Then yes use just `ALTER TABLE test_table ALTER COLUMN address varchar(100);` and allow to be NULL. – Lukasz Szozda Oct 14 '15 at 13:58

1 Answers1