mysql to postgres and auto_increment

The second pitfal in migration from mysql to postgresql is the auto_increment feature for integer types in mysql - the first of course is the password() function.

In other databases you have to create a sequence and use the nextval() to specify that a field is to be auto incremented. While this may appear to be more cumbersome than the mysql way - it certainly gives greater control. In postgresql there is a shortcut; it allows you to define a field as SERIAL and the sequence is automatically created for you.

thus messageId int(11) NOT NULL auto_increment in mysql becomes messageId SERIAL in postgres - and that's quite a bit less typing than what you have to do in mysql.

Another point worth mention about the column definition is that mysql has several types of integers and with any of them you can define the number of bytes to be used. Even though postgress has a smallint and bigint in addition to the standard integer you will rarely need to use anything other than integer and due to a quirk in type casting if you use smallint your query may not make use of the indices as you expect.

Another datatype that will cause you trouble is the blob - blobs are called bytea in postgresql while in mysql you find them in many flavours including mediumblob and blob

follow up: Aug 05 , 2004 - Retrieving the last insert id