0

How would I specify bigint in a create_table for PostgreSQL and SQLite in a rails schema file?

I've looked at the data types to see that there is a carry over between the two and checked their capacities here and here.

I then saw this question and I was wondering if there really wasn't any way to create the bigint inside the create_table schema.

Also, I am not necessarily looking for bigint, but rather an 64-bit datatype; signing doesn't matter (I will only be accessing the bits).

Community
  • 1
  • 1
RileyE
  • 10,874
  • 13
  • 63
  • 106
  • The first question is *why are you trying to use a bitmap inside a relational database?* You can always hammer a square peg into a round hole but it will be messy. – mu is too short Jun 19 '13 at 18:51
  • @muistooshort I just want something that will work on either platform, but I need all 64 bits for my column. – RileyE Jun 19 '13 at 18:52
  • You misunderstand. I'm questioning your "I will only be accessing the bits" design decision. Is this just an opaque blob of data that the database is just storing or are you expecting to do bit operations inside queries? – mu is too short Jun 19 '13 at 19:00
  • @muistooshort It's just an "opaque blob" of data. I will be doing all bit operations outside of the queries. – RileyE Jun 19 '13 at 19:01
  • @muistooshort The reason I chose `bigint` is because it seemed to be the only thing that was 8 bytes and was on both platforms. – RileyE Jun 19 '13 at 19:02

1 Answers1

1

In PostgreSQL:

create table whatever ( some_column bigint );

That's all. But if you want bit operations, it's better to use dedicated data type - namely bit(n)

  • But would bit be possible in SQLite as well? Also, would that work in a rails schema? – RileyE Jun 19 '13 at 18:58
  • 1
    @RileyE: Not as easy as in Postgres. Postgres `BIT` or `VARBIT` type can be of unlimited length (like in megabytes) and natively supports operations like `&`, `|` and their aggregate equivalents `bit_and`, `bit_or`. Nothing like that exists for SQLite - but you can re-implement it, I guess. – mvp Jun 20 '13 at 01:36
  • 1
    @RileyE This will also work in SQLite due to its [dynamic type system](http://www.sqlite.org/datatype3.html). – CL. Jun 20 '13 at 06:53
  • @mvp Okay. That's good to know, but I guess I may have to stick with the cross-platform types, right? Thank you for that tasty tid `BIT` of information :) – RileyE Jun 20 '13 at 14:43
  • @CL. So, if I stay with a regular implementation of `bigint`, then I should be fine? But what about the issues with it not working in [this question](http://stackoverflow.com/questions/7061239/rails-migration-bigint-on-postgresql-seems-to-be-failing?lq=1)? – RileyE Jun 20 '13 at 14:45