-
Website
http://www.codespatter.com -
Original page
http://codespatter.com/2008/07/02/tips-for-mysql-to-postgresql-switch/ -
Subscribe
All Comments -
Community
-
Top Commenters
-
itjobs1
1 comment · 2 points
-
billymcclure
1 comment · 1 points
-
Ben Bangert
1 comment · 2 points
-
jakubmusil
1 comment · 1 points
-
dobrych
1 comment · 3 points
-
-
Popular Threads
WTF!? This can seriously break many schemas. Don't do this.
Postres supports multiple-row inserts only in latest versions (8.3+ IIRC).
another useful replacement may be CURRENT_TIMESTAMP with NOW() and timestamp with timestamp without time zone, etc.
Thanks for the comment. I updated the post to cross out the NOT NULL part, I think I meant that only for the auto_increment part, but messed it up.
I'll check out the newer versions to see what's been changed. I was on 7.4.7.
- An enum column in MySQL is equal to a varchar with a check constraint in PostgreSQL, for example:
column_name enum('foo', 'bar') is equal to column_name varchar(3) check (column_name IN ('foo', 'bar'))
- The blob datatype in MySQL is equal to a bytea datatype in PostgreSQL
Also, when truncating a table, I do not believe PostgreSQL resets the sequence for serial columns, so it will need to be done manually.
Obviously there are more differences when moving to PostgreSQL, but these are most of the big ones for CREATE TABLE statements.
Just a little FYI. I wrote these all out, but forgot my email. Your blog is very unfriendly to comments without an email, as in I lost my comments.
CREATE TABLE manila_area_tbl
(
area_no tinyint(4) NOT NULL auto_increment,
area_name varchar(65) NOT NULL,
disp_flag char(1) NOT NULL default '1',
disp_no tinyint(4) NOT NULL,
del_flag char(1) NOT NULL default '0',
PRIMARY KEY (`area_no`),
KEY disp_no` (`disp_no`),KEY `del_flag` (`del_flag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=25;
Try running these 4 queries.
CREATE TABLE manila_area_tbl
(
area_no SERIAL,
area_name varchar(65) NOT NULL,
disp_flag char(1) NOT NULL default 1,
disp_no smallint NOT NULL,
del_flag char(1) NOT NULL default 0,
PRIMARY KEY (area_no)
) ;
CREATE INDEX disp_no ON manila_area_tbl (disp_no);
CREATE INDEX del_flag ON manila_area_tbl (del_flag);
ALTER SEQUENCE manila_area_tbl_area_no_seq RESTART WITH 25;
do you know how to do something like this ?
ALTER SEQUENCE univers_id_seq RESTART WITH (SELECT max(id) + 1 FROM univers);
Thanks
Emilien
ALTER SEQUENCE univers_id_seq RESTART WITH (SELECT max(id) FROM univers)+1;