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!

You can leave out the `default NULL` in both cases. That is the "default" default in both databases. — Gordon Linoff, Jul 14 '14 at 17:59
@GordonLinoff: OP is changing the data type. The column might already have a different default. If OP were just *adding* a column, you could drop the `default null`. — Mike Sherrill 'Cat Recall', Jul 14 '14 at 18:02

score 0 · Answer 1 · answered Jul 14 '14 at 18:02

0

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);

answered Jul 14 '14 at 18:02

Othya

390
1
3
18

Just that Gordon's comment *assumes* the column already has a default `DEFAULT`, which is unfounded AFAICS. – Erwin Brandstetter Jul 14 '14 at 18:29

score 0 · Answer 2 · answered Jul 14 '14 at 18:13

0

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.

answered Jul 14 '14 at 18:13

Mike Sherrill 'Cat Recall'

91,602
17
122
185

@ErwinBrandstetter: Indeed. `[, . . .]` makes all the difference. More than once, I've read `where action is *one* of:` (emphasis added), and been caught by that. If I joined the PostgreSQL documentation project, maybe I could get that changed to "where action is one, or possibly more, of:". I'll delete this tomorrow. :-) – Mike Sherrill 'Cat Recall' Jul 14 '14 at 18:28

Erwin Brandstetter · Accepted Answer · 2014-07-14T19:28:47.063

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.

Thanks for the comments. I'm just trying to convert the sql shown here: http://www.kamailio.org/wiki/install/upgrade/3.3.x-to-4.0.0 — dot, Jul 15 '14 at 12:27
ah.. i guess i slightly modified it. it's the fourth one down on the page. But the bottom line is that I have to convert all that stuff into postgresql friendly syntax. — dot, Jul 15 '14 at 13:53

converting mysql scripts to postgresql script

3 Answers3

Linked