# PostgreSQL specific data types in migrations

**URL:** <https://www.ruby-forum.com/t/postgresql-specific-data-types-in-migrations/158280>\
**Category:** Rails\
**Created:** [January 21, 2009, 4:18pm UTC](https://www.ruby-forum.com/t/postgresql-specific-data-types-in-migrations/158280 "2009-01-21T16:18:23Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![mgreenly](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/85f322/32.png) [@mgreenly](https://www.ruby-forum.com/u/mgreenly)\
**Post date:** [January 21, 2009, 4:18pm UTC](https://www.ruby-forum.com/t/postgresql-specific-data-types-in-migrations/158280/1 "2009-01-21T16:18:23Z")

</div>

I am willing to give up the common denominator in database  
compatibility and dive into using some PostgreSQL specific types. In  
particular, I need bigint (int8) id columns and ‘cidr’ data type.

I know I can fake it with a string, but I really want to be able to  
use the cidr type’s features. We’re not talking about a small  
database here; it will likely be huge.

I managed to get a schema created using this:

class CreateProbes \< ActiveRecord::Migration  
def self.up  
create\_table :probes do |t|  
t.column “target”, :cidr  
t.binary :flags  
t.string :status, :limit =\> 1, :default =\> ‘A’  
t.timestamps  
end  
end

def self.down  
drop\_table :probes  
end  
end

which does SOME of it. However, db/schema.db contains:

ActiveRecord::Schema.define(:version =\> 20090121052017) do

create\_table “probes”, :force =\> true do |t|  
t.string “target”, :limit =\> nil  
t.binary “flags”  
t.string “status”, :limit =\> 1  
t.datetime “created\_at”  
t.datetime “updated\_at”  
end

end

which is NOT what I wanted. This makes all my “rake test” tests fail.

I looked into part of activerecord, and man is it tricky. Trying to  
get this to work means major changes all over the place from what I  
can see. There aren’t any database-specific hooks in place to allow  
adding custom types without a lot of monkey patching.

Has anyone already done all this work by chance?

Thanks,  
–Michael

---

<div class="post-metadata">

**Author:** ![mgreenly](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/85f322/32.png) [@mgreenly](https://www.ruby-forum.com/u/mgreenly)\
**Post date:** [January 22, 2009, 2:16am UTC](https://www.ruby-forum.com/t/postgresql-specific-data-types-in-migrations/158280/2 "2009-01-22T02:16:49Z")

</div>

Michael G. wrote:

> I am willing to give up the common denominator in database  
> compatibility and dive into using some PostgreSQL specific types. In  
> particular, I need bigint (int8) id columns and ‘cidr’ data type.

Can’t help you on the cidr, but to change the id to bigint I’ve used

change\_column :probes, :id, :bigint, :limit =\> 8

after the create\_table block.

–  
Rails Wheels - Find Plugins, List & Sell Plugins -  
[http://railswheels.com](http://railswheels.com)

---

<div class="post-metadata">

**Author:** ![mgreenly](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/85f322/32.png) [@mgreenly](https://www.ruby-forum.com/u/mgreenly)\
**Post date:** [January 22, 2009, 3:01am UTC](https://www.ruby-forum.com/t/postgresql-specific-data-types-in-migrations/158280/3 "2009-01-22T03:01:16Z")

</div>

Thanks for the reply!

It sort of turns out that the development (and anything generated from  
a “rake migrate” step) work as intended. However, it seems that the  
test database is created by running db/schema.rb, which has badness.

I know the general work of acriverecord is to maintain as much  
database agnostic types as it can, but this is contrary to my reality.  
I choose to use postgresql because it has these features, and not  
being able to use them in rails makes my coworkers question the use of  
rails, not the use of postgresql.

I’ll have to look into ways to contribute back I suppose, and see what  
happens.

Types like t.reference would have to change to bigint in my world as  
well, or I’ll have to create the othertable\_id columns as bigint  
specially.

–Michael