Skip to content
This repository

ActiveRecord should store strings as type 'text' with a length constraint when using Postgresql #5053

Closed
joevandyk opened this Issue · 2 comments

2 participants

Joe Van Dyk Aaron Patterson
Joe Van Dyk

The following code will create create a column name with type character varying(255).

create table :users do |t|
  t.string :name
end

The above generates this SQL:
CREATE TABLE "users" ("id" serial primary key, "name" character varying(255))

Ideally, it would use this SQL:
CREATE TABLE "users" ("id" serial primary key, "name" text, constraint "name_length" check (length("name") < 256));

This would allow you to change the length constraint without having to rewrite the table.

Although, on second thought, why is there a length constraint added automatically? It's not like "character varying" types are stored more effectively than the "text" types.

Aaron Patterson
Owner

Have you tried any performance testing? I'm guessing the length check would be slower on inserts. As for storage effectiveness, I think that depends on the database you're using. Can you specify a varchar with no length constraint on all databases? Does the constraint check work across all databases?

Joe Van Dyk

Good call about checking performance. The length constraint is about 7% slower. https://gist.github.com/1847130 5.3 vs 5.7 seconds for inserting 1,000,000 rows on my laptop.

I have no idea about other databases, and I'm fairly sure mysql doesn't support CHECK constraints.

I think this can be closed. Anyone who cares enough is probably writing their own SQL for DDL changes and not relying on create_table and friends.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.