Rails: Get PostgresQL table column type

Question

If I have a table of varying PostgresQL data types:

  CREATE TABLE users (
    name varchar(255),
    age int4
  );

When I get that table's columns through the model, the sql_type returned is different than what I entered:

User.columns.first.name #=> "name"
User.columns.first.sql_type #=> "character varying"
User.columns.last.name #=> "age"
User.columns.last.sql_type #=> "integer"

How can I get the original types? In this case it would be varchar(255) and int4.

http://stackoverflow.com/questions/5575970/activerecord-list-columns-in-table-from-console ... The answer just below the "accepted" answer has a great code block that spits out all the details about your columns that I just ran in my console and it output what I believe would be the data you seek. — craig.kaminsky, Feb 18 '16 at 19:46
@craig.kaminsky That returns what I have above. For example, it returns `integer` rather than `int4` — steel, Feb 18 '16 at 19:48
I do see what you mean, the limit attribute appears to only apply for string/varchar fields and not ints. — craig.kaminsky, Feb 18 '16 at 19:50
`varchar` and `character varying` are exactly the same thing, so are `int4` and `integer`. Connect to your database with the `psql` CLI tool and say `\d users`, you'll see no `varchar` or `int4` in sight. — mu is too short, Feb 18 '16 at 19:56

score 2 · Accepted Answer · answered Feb 18 '16 at 20:03

From the fine manual:

8.3. Character Types
[...]
The notations varchar(n) and char(n) are aliases for character varying(n) and character(n), respectively.

So the real type name is character varying but you can use varchar if you want. Similarly for integer types:

8.1.1. Integer Types
[...]
SQL only specifies the integer types integer (or int), smallint, and bigint. The type names int2, int4, and int8 are extensions, which are also used by some other SQL database systems.

So you can say int4 if you want but internally that's integer.

You can even check what the database really thinks using psql:

=> create table users (name varchar(255), age int4);
CREATE TABLE
=> \d users
            Table "public.users"
 Column |          Type          | Modifiers 
--------+------------------------+-----------
 name   | character varying(255) | 
 age    | integer                |

No varchar or int4 in sight, just character varying and integer. You can use the aliases when creating tables but the database will always talk to you using the standard types.

You'll find similar shenanigans going on if you use decimal(m,n) (an alias for numeric(m,n)), timestamp (really timestamp without time zone), etc.

Rails: Get PostgresQL table column type

1 Answers1