converting mysql scripts to postgresql script

Question

I have the following line in a .sql file from a mysql db:

  ALTER TABLE lcr_gw ALTER COLUMN ip_addr TYPE VARCHAR(50) DEFAULT NULL;

I would like to convert it into syntax that postgresql would understand. In my personal tests, I was only able to get it to work by breaking it down into two separate statements, like so:

ALTER TABLE lcr_gw ALTER COLUMN ip_addr TYPE VARCHAR(50);
ALTER TABLE lcr_gw ALTER COLUMN ip_addr SET DEFAULT NULL;

Just wondering if there's a way to consolidate the two statements back into one, but one that postgresql will be happy with?

Thanks!

Answer 1

The statement you posted is not valid syntax at all:
SQL Fiddle

To change the type in MySQL, you would use CHANGE or MODIFY.
To change the default you would use DROP DEFAULT or SET DEFAULT NULL.

If the intention was to change the type and reset the column default:

Like in MySQL, you can pack multiple actions into a single ALTER TABLEstatement in Postgres .

ALTER TABLE lcr_gw ALTER COLUMN ip_addr SET DEFAULT NULL
                  ,ALTER COLUMN ip_addr TYPE VARCHAR(50);

Per documentation:

The main reason for providing the option to specify multiple changes in a single ALTER TABLE is that multiple table scans or rewrites can thereby be combined into a single pass over the table.

But if there was a DEFAULT on the column that is incompatible with the new type, you have to run two separate statements:

ALTER TABLE lcr_gw ALTER COLUMN ip_addr SET DEFAULT NULL;
ALTER TABLE lcr_gw ALTER COLUMN ip_addr TYPE VARCHAR(50);

Doesn't matter in this case anyway.

Answer 2

As @Gordon Linoff states in the comments, postgreSQL by default sets a value to null unless a value is given or the default is changed to something else;

therefore, all you'll need is:

ALTER TABLE lcr_gw ALTER COLUMN ip_addr TYPE VARCHAR(50);

Answer 3

The PostgreSQL ALTER TABLE syntax diagram doesn't show any way to combine changing a data type and changing a default value in a single SQL statement. You can't simply omit set default null in the general case. For example,

create table test (
  column_1 char(10) not null default 'a'
);
alter table test alter column column_1 type varchar(50);
insert into test values (default);
select * from test;

column_1
--
a

Instead, either rewrite as two independent statements (which you already know how to do), or as two statements in a single transaction.