tag:blog.hasmanythrough.com,2006-02-27:/tag/postgreshas_many :through - postgres2011-06-01T22:45:25-07:00tag:blog.hasmanythrough.com,2006-02-27:Article/1322011-06-01T22:45:25-07:002011-06-01T22:45:25-07:00Limitless Strings for PostgreSQLJosh Susser<p>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.</p>
<p>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 <a href="http://www.postgresql.org/docs/current/static/datatype-character.html">http://www.postgresql.org/docs/current/static/datatype-character.html</a>:</p>
<blockquote>
<p>If character varying is used without length specifier, the type accepts strings of any size. The latter is a PostgreSQL extension.</p>
</blockquote>
<p>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.</p>
<p>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:</p>
<pre><code>create_table "users" do |t|
t.string "name"
end
</code></pre>
<p>That is equivalent to</p>
<pre><code>CREATE TABLE users (
name character varying(255)
);
</code></pre>
<p>OK, you think, I know what to do. I'll specify a limit of nil!</p>
<pre><code>create_table "users" do |t|
t.string "name", :limit => nil
end
</code></pre>
<p>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.</p>
<p>Drop these lines into application.rb (assuming you are on Rails 3.0 or greater) within the Application class definition:</p>
<pre><code>initializer "postgresql.no_default_string_limit" do
ActiveSupport.on_load(:active_record) do
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::NATIVE_DATABASE_TYPES[:string].delete(:limit)
end
end
</code></pre>
<p>That removes the default limit for string fields. Problem solved. And that's even nicer than saying <code>:limit => nil</code> anyway.</p>
<p>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 <code>active_record</code> 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.</p><p>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.</p>
<p>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 <a href="http://www.postgresql.org/docs/current/static/datatype-character.html">http://www.postgresql.org/docs/current/static/datatype-character.html</a>:</p>
<blockquote>
<p>If character varying is used without length specifier, the type accepts strings of any size. The latter is a PostgreSQL extension.</p>
</blockquote>
<p>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.</p>
<p>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:</p>
<pre><code>create_table "users" do |t|
t.string "name"
end
</code></pre>
<p>That is equivalent to</p>
<pre><code>CREATE TABLE users (
name character varying(255)
);
</code></pre>
<p>OK, you think, I know what to do. I'll specify a limit of nil!</p>
<pre><code>create_table "users" do |t|
t.string "name", :limit => nil
end
</code></pre>
<p>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.</p>
<p>Drop these lines into application.rb (assuming you are on Rails 3.0 or greater) within the Application class definition:</p>
<pre><code>initializer "postgresql.no_default_string_limit" do
ActiveSupport.on_load(:active_record) do
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::NATIVE_DATABASE_TYPES[:string].delete(:limit)
end
end
</code></pre>
<p>That removes the default limit for string fields. Problem solved. And that's even nicer than saying <code>:limit => nil</code> anyway.</p>
<p>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 <code>active_record</code> 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.</p>