Limitless Strings for PostgreSQL

— June 1, 2011 at 22:45 PDT


We all love ActiveRecord migrations and the sexy DSL for declaring fields. OK, I don't know if you do, but I sure do. But life isn't perfect (don't get me started), and there's various details that make using the migration DSL a bit of a pain.

The one thing that has annoyed me for ages is how string (VARCHAR) fields are handled for PostgreSQL. While the standard for SQL requires you specify a limit for the length, PostgreSQL is more flexible. From the PostgreSQL docs at http://www.postgresql.org/docs/current/static/datatype-character.html:

If character varying is used without length specifier, the type accepts strings of any size. The latter is a PostgreSQL extension.

Unless your string field needs a limited length for semantics (like a SSN or ZIP code), it's better not to specify a limit for VARCHAR fields.

The problem is that ActiveRecord follows the SQL standard and insists on a limit of 255 if none is specified. So if you define a field thusly:

create_table "users" do |t|
  t.string "name"
end

That is equivalent to

CREATE TABLE users (
  name character varying(255)
);

OK, you think, I know what to do. I'll specify a limit of nil!

create_table "users" do |t|
  t.string "name", :limit => nil
end

But no, that doesn't work. A nil limit is ignored and the default 255 is used anyway. There's a tiny patch for that I might pursue, but in the mean time there is a pretty simple workaround.

Drop these lines into application.rb (assuming you are on Rails 3.0 or greater) within the Application class definition:

initializer "postgresql.no_default_string_limit" do
  ActiveSupport.on_load(:active_record) do
    ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::NATIVE_DATABASE_TYPES[:string].delete(:limit)
  end
end

That removes the default limit for string fields. Problem solved. And that's even nicer than saying :limit => nil anyway.

And also, this is pretty cool. I got to use one of those fancy ActiveSupport initializer hooks. Let me explain in case you haven't used one before. What that's doing is waiting until the active_record library is done loading, then running some code to act on the initialized library. That ensures that the default value is removed after it's been created, but before anyone gets a chance to use it.

12 commentsactiverecord, migrations, postgres, postgresql, rails

Comments
  1. Robby Russell2011-06-01 23:05:55

    In PostgreSQL did you know that if you use a TEXT data type that there is no limit? (and there is no performance penalty vs using VARCHAR)

    create_table "users" do |t|
      t.text "name"
    end
    

    For more info, read...

    • http://archives.postgresql.org/pgsql-novice/2002-02/msg00098.php
  2. Josh Susser2011-06-01 23:16:53

    Hey Robby. Yep, you can use text for almost everything in PostgreSQL. But I think using the string/varchar type is better this way. It's more portable, for one thing. But more pragmatically, things like simple_form work better. simple_form is clever enough to render a form input for a string using a text_field input, but uses a textarea for a text field. Other than that, I'd consider using text everywhere.

  3. Ryan Bigg2011-06-01 23:20:46

    Thank you Josh for getting back into blogging again. Well, for at least one entry ;)

    I would advise people to put the code in an initializer rather than application.rb, as application.rb should not be crowded with what is effectively hacks around things. config/initializers is a better place for this.

    Think about it like this: application.rb is the club where all the cool kids go (like you and I), and config/initializers is where the not so cool kids are shuffled off to.

  4. rwz2011-06-01 23:44:14

    why don't you write a patch to AR removing limit on pgsql depending for example on some config variable like disable_pgsql_default_limit = true?

  5. Benjamin2011-06-02 01:14:42

    if you want to input a lot of text why not using a textarea? more than 255 characters in an input text raises the question of usability

    and you can specify things like :as => :string for things like simple form to behave different from their default

  6. Samuel2011-06-02 05:16:52

    The :limit is set to 255 by default because MySQL can only index varchar fields up to that value (approx. 255 * 3 bytes).

    That :limit attribute has this funny way of putting us in a box and making us think that's really the limit, but it's not. If you need larger varchar columns, just define the :limit upwards (e.g. :limit => 1024).

  7. skrat2011-06-02 05:25:28

    Wonderful, so you propose hacking a constant in an external dependency, instead of using a patch? Way to go :D like using a screwdriver to open a can of beans.

  8. Wade Winningham2011-06-02 05:41:22

    With MySQL, although you are using a varchar, if that varchar field is indexed, the index created uses the full limit of the field. So a 20-character string in a field with a 255 limit may only take up 20-characters of space in the data, but in the index, it's taking 255 characters, which makes for very bloated, slower indexes.

    I'm not sure if that's the same case with PostgreSQL. Anyone know?

  9. Josh Susser2011-06-02 11:12:22

    Ryan, I put it in application.rb instead of config/initializers because I wanted to ensure that the default was removed before any of the initializers ran. That might not be an issue for running migrations, but in general I think using load hook initialization blocks needs to be done in application.rb. I guess I should look into that.

    (And I'm not making a big deal about blogging again, but it's something I've missed and am making a priority again. Thanks for the encouragement!)

  10. Josh Susser2011-06-02 11:13:58

    Benjamin, the point is that I don't want a lot of text. I just want a short string, but don't want to impose the 255 char limit. I might only be using 20 chars, but it's unnecessary to set that limit.

  11. Josh Susser2011-06-02 11:15:55

    skrat, I'd rather patch AR to remove that default limit for PostgreSQL, but that doesn't help me or anyone else right now. Rails has a very nice API for doing this sort of thing, so why is it bad to take advantage of it?

  12. Jim2011-06-20 22:35:05

    Cool, you're back! I thought you were dead or something.

Sorry, comments for this article are closed.