I think, therefore I blog

Converting Unsigned Bigints to Signed in PostgreSQL

By , 24 May 2008

Converting Unsigned Bigints to Signed in PostgreSQL

Just upgrading Dspam and discovered that the new version uses (or recommends) a BIGINT column for tokens instead of NUMERIC(20) on postgresql for better performance. Dspam tokens are unsigned 64 bit values, but postgresql's BIGINTs are signed 64 bit values. The new dspam just marshals back and forth between signed and unsigned values which avoids the costly NUMERIC data type.

The problem is, of course that all our data is stored as unsigned NUMERIC values. Dspam has a program to convert the database, dspam_pg2int8, but it gave me a segmentation fault :( . Anyway, I figured out that it's not too hard to do yourself in SQL and came up with this script, which presumes postgres is using two's complement for negative values:

alter table dspam_token_data rename token to token2;
alter table dspam_token_data add column token bigint;
update dspam_token_data set token = 
       case when token2 < (2 ^ 63)
            then token2
            else token2 - (2 ^ 64) 
alter table dspam_token_data drop column token2;

Hope you find it helpful.

Converting Unsigned Bigints to Signed in PostgreSQL

About Roger Keays

Converting Unsigned Bigints to Signed in PostgreSQL

Roger Keays is an artist, an engineer, and a student of life. Since he left Australia in 2009, he has been living as a digital nomad in over 40 different countries around the world. Roger is addicted to surfing. His other interests are music, psychology, languages, and finding good food. Click here to subscribe to his weekly blog, or stalk him on Facebook and Twitter.

Leave a Comment

Please visit https://RogerKeays.com/blog/converting-unsigned-to-signed-bigints to add your comments.

Comment posted by: , 8 years ago

Oh yah, I had to dump and restore the database to fix the column order too. Dspam is fussy about that. Presumably it's faster if the columns are in a known order.

Join Over 1000 Subscribers

I write every Sunday about travel, psychology, and technology. Thousands of people just like you have already subscribed—and for good reason. It'll change your life. And it's free :)

Read a Good Book

“A spellbinding true story of love, passion and adventure. One can’t help but be swept away by 100% Love Guaranteed.” —Dr Tammie Matson, author of Elephant Dance.

Chat For A While

Your Vote Matters

Which animal will take over when humans go extinct?