# Replacing MySQL's AUTO_INCREMENT with Postgres' SERIAL

I am making changes to a Python script that converts MySQL scripts to PostgreSQL, and I want to replace strings such as id INTEGER NOT NULL AUTO_INCREMENT with id SERIAL NOT NULL.

This is the code I got to work:

import re

line = 'id INTEGER(11) NOT NULL AUTO_INCREMENT,'

numeric_types = ['(BIG|MEDIUM|SMALL|TINY)*INT(EGER)*($$.*?$$)*',
'DEC(IMAL)*($$.*?$$)*', 'NUMERIC($$.*$$)*', 'FIXED($$.*$$)*',
'FLOAT($$.*$$)*', 'DOUBLE( PRECISION)*($$.*?$$)*', 'REAL($$.*$$)*',
'BIT', 'BOOL(EAN)*']

for i in range(len(numeric_types)):
type = numeric_types[i]
if (re.search(type, line)):
line = re.sub(type, "SERIAL", line).replace(" AUTO_INCREMENT", "")
print line
break


Notes:

• line will be a column from a CREATE TABLE statement inputted by the user
• I could probably join all regular expressions into one using ORs, but I do not know if that would be a good practice or not

For reference, all of the code I used is at the bottom of this post.

I will first discuss a few python style points, and then we'll look at performance.

Pep8:

First thing to do is get a style/lint checker. I use the pycharm ide which will show you style and compile issues right in the editor. When you get a chance you should also read through pep8 which is the official python style guide.

One item per line is quite a bit easier to read.

numeric_types = [
'(BIG|MEDIUM|SMALL|TINY)*INT(EGER)*($$.*?$$)*',
'DEC(IMAL)*($$.*?$$)*',
...
'BIT',
'BOOL(EAN)*'
]


Use pythons iterators:

Most of the time, you will not need to use an index variable (eg: the i in the original code). Python can just iterate over an amazing number of things.

for line in lines:
for regex_str in numeric_types:
if re.search(regex_str, line):
line = re.sub(regex_str, "SERIAL", line).replace(
" AUTO_INCREMENT", "")
break


Performance investigation:

So when investigating these sorts of things, timeit is your friend. I timed the things I tried as:

def method1():
...

def method2():
...

from timeit import timeit
count = 3
print('original:', [timeit(method1, number=200) for i in range(count)])
print('compiled:', [timeit(method2, number=200) for i in range(count)])


Method1:

method1 is functionally equivalent to the original code.

def method1():
for line in lines:
for regex_str in numeric_types:
if re.search(regex_str, line):
line = re.sub(regex_str, "SERIAL", line).replace(
" AUTO_INCREMENT", "")
break


Method2:

method2 compiles all of regular expression strings into regular expression objects, and then uses the objects:

num_type_regex = [re.compile(x) for x in numeric_types]
def method2():
for line in lines:
for regex in num_type_regex:
if regex.search(line):
line = regex.sub("SERIAL", line).replace(
" AUTO_INCREMENT", "")
break


Timing Results:

Not surprisingly, the compiled expressions were faster.

original: [1.3694926374633507, 1.2469248480337616, 1.2260409178909049]
compiled: [0.9859922309149369, 1.0048337256902489, 1.0676349069804303]


All of the Code:

import re

line = 'id INTEGER(11) NOT NULL AUTO_INCREMENT,'
lines = [line + str(i) for i in range(1000)]

numeric_types = [
'(BIG|MEDIUM|SMALL|TINY)*INT(EGER)*($$.*?$$)*',
'DEC(IMAL)*($$.*?$$)*',
'NUMERIC($$.*$$)*',
'FIXED($$.*$$)*',
'FLOAT($$.*$$)*',
'DOUBLE( PRECISION)*($$.*?$$)*',
'REAL($$.*$$)*',
'BIT',
'BOOL(EAN)*']

num_type_regex = [re.compile(x) for x in numeric_types]
num_type_joined = re.compile('|'.join(numeric_types))

def method1():
for line in lines:
for regex_str in numeric_types:
if re.search(regex_str, line):
line = re.sub(regex_str, "SERIAL", line).replace(
" AUTO_INCREMENT", "")
break

def method2():
for line in lines:
for regex in num_type_regex:
if regex.search(line):
line = regex.sub("SERIAL", line).replace(
" AUTO_INCREMENT", "")
break

from timeit import timeit
count = 3
print('original:', [timeit(method1, number=200) for i in range(count)])
print('compiled:', [timeit(method2, number=200) for i in range(count)])

• Thakn you for the detailed answer! I'm going to play with timeit later when I have free time, but shouldn't you include num_type_regex = [re.compile(x) for x in numeric_types] into method2? I feel like compiling those patterns would add some overhead to the processing. – Gus Feb 24 '17 at 10:39
• Assumption was there were multiple files to process, so regex compile was out side of that loop. But really it was more for illustration, and by all means, whatever is appropriate for your situation. Cheers. – Stephen Rauch Feb 24 '17 at 14:22
• I saw that in your answer you made a joined regex version, I tested it and got even greater results! From around 0.8766 usecs with method2() I went to about 0.0036 usecs with the single regex method. Here's the source for the test case – Gus Feb 25 '17 at 2:18
• Also I replaced the line string with an array for some more elaborate test cases to make sure it wouldn't match fast just because INTEGER is one of the first types on the list. – Gus Feb 25 '17 at 2:21

The MySQL syntax for a CREATE TABLE command is, in part:

column_definition:
data_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string']
[COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
[STORAGE {DISK|MEMORY|DEFAULT}]
[reference_definition]
| data_type [GENERATED ALWAYS] AS (expression)
[VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment]
[NOT NULL | NULL] [[PRIMARY] KEY]

data_type:
BIT[(length)]
| TINYINT[(length)] [UNSIGNED] [ZEROFILL]
| SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
| MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
| INT[(length)] [UNSIGNED] [ZEROFILL]
| INTEGER[(length)] [UNSIGNED] [ZEROFILL]
| BIGINT[(length)] [UNSIGNED] [ZEROFILL]
| REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
| FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]
| NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]
| DATE
| TIME[(fsp)]
| TIMESTAMP[(fsp)]
| DATETIME[(fsp)]
| YEAR
| CHAR[(length)] [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| VARCHAR(length) [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| BINARY[(length)]
| VARBINARY(length)
| TINYBLOB
| BLOB
| MEDIUMBLOB
| LONGBLOB
| TINYTEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| TEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| MEDIUMTEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| LONGTEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| ENUM(value1,value2,value3,...)
[CHARACTER SET charset_name] [COLLATE collation_name]
| SET(value1,value2,value3,...)
[CHARACTER SET charset_name] [COLLATE collation_name]
| JSON
| spatial_type


To handle the general case is a non-trivial task! I highly recommend writing your parsing regexes such that they are recognizably based on the documentation. (One question I have is why you look for BOOL(EAN)* when MySQL has no boolean type.)

According to the PostgreSQL documentation:

The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying:

CREATE TABLE tablename (
colname SERIAL
);


is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;


Note that the equivalent MySQL syntax for that would be

CREATE TABLE tablename (
colname integer NOT NULL AUTO_INCREMENT
);


If I were trying to machine-translate a database schema specification, I would prefer to independently translate the size-and-range aspect of the numeric type and the auto-incrementing nature of the column. Otherwise, trying to handle the column name, the data type, any NOT NULL constraints, primary and foreign keys, etc. all at once will result in a mess.

Specifically, I would say that MySQL's AUTO_INCREMENT keyword translates directly to PostgreSQL's DEFAULT nextval(…), with a CREATE SEQUENCE … preamble and an ALTER SEQUENCE … OWNED BY … postamble.

Such a translation might not result in pretty and idiomatic PostgreSQL code, but the translation code would be more robust and maintainable.

• I am working on an already existing script that does the translation. It creates the CREATE SEQUENCE statements, but not the DEFAULT nexval ones. I thought it would be easier to drop the CREATE SEQUENCE and just change the datatype to SERIAL – Gus Feb 24 '17 at 10:46
• Also BOOL and BOOLEAN are aliases to TINYINT(1). I now realized I got these types from non-official documentation (link here) but they can be used in MySQL Workbench. – Gus Feb 24 '17 at 12:19