Postgresql unable to change the data type of a column

Question

I am new to Postgresql, and I am trying to change the data type of a column from Integer to Varchar(20), but I get strange error:

 ERROR: operator does not exist: character varying <> integer : No operator matches the given name and argument type(s). You might need to add explicit type casts.********** Error **********

The script I wrote to create the table is:

CREATE TABLE LOGIN( USERNAME INTEGER NOT NULL CHECK(USERNAME != NULL), PASSWORD VARCHAR(10) NOT NULL CHECK(PASSWORD <>'' AND USERNAME != NULL) );

This is the script I used to modify the column from Integer to Varchar:

ALTER TABLE LOGIN ALTER COLUMN USERNAME TYPE varchar(20);

I appreciate any help. Thanks.

NOT NULL does the trick, no need to add another not null attempt. — jarlh
– jarlh, Commented May 20, 2016 at 10:28

Adam · Accepted Answer · 2016-05-20 10:59:49Z

Use USING expression. It allows you to define value conversion:

ALTER TABLE LOGIN ALTER COLUMN USERNAME TYPE varchar(20) USING ...expression...;

From PostgreSQL documentation:

The optional USING clause specifies how to compute the new column value from the old; if omitted, the default conversion is the same as an assignment cast from old data type to new. A USING clause must be provided if there is no implicit or assignment cast from old to new type.

Won't work because of the (useless) check constraint USERNAME != NULL. The error message actually "points" to that constraint because of the <> operator in it.

user330315 · Accepted Answer · 2016-05-20 11:13:23Z

The cause of the error is the useless additional check constraint (<> null) that you have:

operator does not exist: character varying <> integer :

refers to the condition USERNAME != NULL in both of your check constraints.

(the "not equals" operator in SQL is <> and != gets re-written into that)

So you first need to get rid of those check constraints. The default generated name for that check would be login_username_check, so the following will most probably work:

alter table login drop constraint login_username_check;

The other check is most probably login_check:

alter table login drop constraint login_check;

Once those check constraints are dropped you can alter the data type:

ALTER TABLE LOGIN ALTER COLUMN USERNAME set data TYPE varchar(20);

Now you need to re-add the constraint for the password:

alter table login add constraint check_password check (password <> '');

If for some reason the generated constraint names are different then the ones I assumes, you can find the names using:

select c.conname, c.consrc from pg_constraint c join pg_class t on c.conrelid = t.oid join pg_namespace n on t.relnamespace = n.oid where t.relname = 'login' and n.nspname = 'public'; --<< change here for the correct schema name

As jarlh has already commented, defining a column as NOT NULL is enough. There is no need to add another "not null" check. Plus: the check is wrong anyway. You can't compare a value against null using = or <>. To test for a not null value you need to use IS NOT NULL. The correct way to write an explicit check constraint would be

 username check (username is not null)

NLink · Accepted Answer · 2016-05-20 11:12:45Z

The problem is in USERNAME's CHECK constraint. You may get a way out of it using catalog pg_constraint somehow like that:

SELECT conname, consrc FROM pg_constraint WHERE conrelid = (SELECT oid FROM pg_class WHERE relname LIKE 'login');

In a field 'consrc' you may notice that constraints actually look like "(username <> NULL::integer)", and it brings an error when you try to change column type from integer to varchar.

To remove constraints use

ALTER TABLE LOGIN DROP CONSTRAINT login_username_check; ALTER TABLE LOGIN DROP CONSTRAINT login_check;

where 'login_username_check' and 'login_check' are connames you got from query above (be careful and doublecheck that names).

After that you should try to

ALTER TABLE LOGIN ALTER COLUMN USERNAME TYPE varchar(20);

This should work if your table has no data in it. If it doesn't, add a

ALTER TABLE dev.tests_LOGIN ALTER COLUMN USERNAME TYPE varchar(20) USING USERNAME::VARCHAR;

Than you probably want to restore PASSWORD<>'' check, so you'll have to do one more alter to bring it back. Good luck!

Some PostgreSQL documentation you may find handy:

pg_constraint doc

alter doc

PS: As mentioned above, you don't need to get a constraint (!=NULL) check when you already have NOT NULL in column description. Also, NULL checks are a bit different. Quick illustration:

SELECT (NULL = NULL); SELECT (NULL != NULL); SELECT (NULL IS NULL); SELECT (NULL IS NOT NULL);

Collectives™ on Stack Overflow

Postgresql unable to change the data type of a column

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related