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.

stackoverflow.com/questions/5575970/… ... 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
– craig.kaminsky, Commented Feb 18, 2016 at 19:46
@craig.kaminsky That returns what I have above. For example, it returns integer rather than int4 — steel
– steel, Commented Feb 18, 2016 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
– craig.kaminsky, Commented Feb 18, 2016 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
– mu is too short, Commented Feb 18, 2016 at 19:56

mu is too short · Accepted Answer · 2016-02-18 20:03:54Z

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.

Collectives™ on Stack Overflow

Rails: Get PostgresQL table column type

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related