PostgreSQL sequence naming - how it works
I've been dabbling with PostgreSQL for a little while now. There are a lot of differences when compared to MySQL - but I think I like it. One of the big differences is the way auto incrementing fields are handled. In MySQL you can simply use the 'auto_increment' attribute - but PostgreSQL uses sequences instead.
The easiest way to define a sequence is to create a table with a field that uses the 'SERIAL' pseudo-datatype. Such fields are actually of the integer datatype, but let PostgreSQL know to create a sequence associated with that field, named thusly: tablename_fieldname_seq
Having migrated a 250-ish table database from MySQL to PostgreSQL, I found myself in need of a way to update all the sequences that had been automatically created. It was then that I discovered that there is a maximum length of 64 bytes for a sequence name, and that some of my table and field names were long enough to exceed this limit (bad, I know. But I didn't name them :-P )
It took a fair bit of analysis to figure out what PostgreSQL does when such a situation arises, so allow me to save you the leg work of ever having to figure it out. Writing it out in pseudo-code would be way too verbose and look hiddious (with my pseudo-code it would, anyway) - so I've just written it as an AWK script for processing a file in the format tablename fieldname.
#!/bin/awk -f
{
tablename = $1;
fieldname = $2;
# Default to using all of tablename and
# all of fieldname
seqname = tablename "_" fieldname "_seq";
# If the length exceeds 64 bytes, we need to fix it!
if (length(seqname) > 64)
{
# If tablename AND fieldname are longer than 29 bytes,
# use the first 29 bytes of each
if ((length(tablename > 29)) && (length(fieldname) > 29))
{
seqname = substr(tablename,1,29) "_" substr(fieldname,1,29) "_seq";
}
# If only tablename is longer than 29 bytes, use the first
# 29 bytes of it and all of fieldname
if ((length(tablename) > 29) && (length(fieldname) <= 29))
{
seqname = substr(tablename,1,(58 - length(fieldname))) "_" substr(fieldname,1,29) "_seq";
}
# If only fieldname is longer than 29 bytes, use the first
# 29 bytes of it and all of tablename
if ((length(tablename) <= 29) && (length(fieldname) > 29))
{
seqname = substr(tablename,1,29) "_" substr(fieldname,1,(58 - length(tablename))) "_seq";
}
}
# Generate SQL to update the sequences
print "SELECT setval('" seqname "', (SELECT max(" $2 ") + 1 from " $1 "));"
}
I've tried to comment it as best I can - I really need syntax highlighting on my code blocks :-P
You may notice that this particular script generates SQL to update sequences for all given tablename / fieldname pairs - that was the whole reason I wrote it in the first place: after inserting data from my old MySQL tables, the sequences needed to be updated so that any new data would get the correct value from the sequence.
A typical output from the script might be:
SELECT setval('users_uid_seq', (SELECT max(uid) + 1 from users));
SELECT setval('customers_cid_seq', (SELECT max(cid) + 1 from customers));
SELECT setval('products_product_id_seq', (SELECT max(product_id) + 1 from products));
If that's confused the hell out of you, or I've done something stupid: please feel free to email me on mail at tomnomnom dot com.
First posted: Thu, 08 Jan 2009 00:51:02 +0000