Rails : migration for creating a fixed-length char(12) column

Question

What is the best way to define a fixed-length SQL column (CHAR(12) for instance) through a Rails migration ?

Why this should not handled by the model is because of the performance of char() vs varchar(), and I'd like to avoid injecting raw SQL in the database.

Edit : I know the :limit modifier, however the field is still varchar (which is bad for performance) and does not allow a minimum size.

Please note that using `char(n)` column gives no performance boost in many RDBSes (for example in Postgres, refer [their docs](http://www.postgresql.org/docs/8.3/static/datatype-character.html)). What's more, `char(n)` does not ensure minimum length (too short strings will be padded with trailing spaces) – you should use CHECK constraint instead. — skalee, Jan 16 '12 at 11:36

score 56 · Accepted Answer · answered May 06 '11 at 15:01

56

If Rails doesn’t understand the column type, it’ll pass it straight through to the database. So if you want a char instead of varchar, just replace:

t.column :token, :string

With:

t.column :token, "char(12)"

Of course, this may or may not make your migrations non-portable to another database.

(credit to http://laurelfan.com/2010/1/26/special-mysql-types-in-rails-migrations)

answered May 06 '11 at 15:01

KenB

6,587
2
35
31

this generates another column name char(12) for rails 4, not working anymore – JAF Jan 15 '16 at 21:56
1

Works for current Postgres too. Thank you. – Sven R. Jan 22 '18 at 14:09

score 28 · Answer 2 · answered Apr 04 '11 at 09:04

28

 def self.up
    add_column("admin_users", "username", :string, :limit => 25)
 end

 def self.down
    remove_column("admin_users", "username")
 end

answered Apr 04 '11 at 09:04

ceth

44,198
62
180
289

3

I know the :limit modifier, however the field is still varchar (which is bad for performance) and does not allow a minimum size. – manu_v Apr 04 '11 at 09:19
See a section Database Mapping http://www.dizzy.co.uk/ruby_on_rails/cheatsheets/rails-migrations. As I see database field type depends on kind of database server. – ceth Apr 04 '11 at 09:24
2

This is the most advisable solution. `char(n)` does *not* ensure minimum length (at least not in many RDBSes), you should use CHECK constraint instead. – skalee Jan 16 '12 at 11:40

score 9 · Answer 3 · answered Apr 04 '11 at 09:03

9

You can use string type with limit option in your migration file like this:

t.string :name, :limit => 12, :null => false

answered Apr 04 '11 at 09:03

Ashish

5,723
2
24
25

2

this creates a VARCHAR – code-gijoe Jan 05 '17 at 16:32

score 6 · Answer 4 · answered Nov 20 '14 at 20:16

6

For a database specific type, we can now use:

t.column(:column_name, 'char(12)')

And for a complete example:

class Foo < ActiveRecord::Migration
  def change
     create_table :foo do |t|
       t.column(:column_name, 'custom_type')

       t.timestamps
     end
  end
end

answered Nov 20 '14 at 20:16

meoww-

1,892
2
21
29

this was the solution – JAF Jan 15 '16 at 21:55

Rails : migration for creating a fixed-length char(12) column

4 Answers4