Using bigint in PostgreSQL and SQLite

Question

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).

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
– mu is too short, Commented Jun 19, 2013 at 18:51
@muistooshort I just want something that will work on either platform, but I need all 64 bits for my column. — RileyE
– RileyE, Commented Jun 19, 2013 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
– mu is too short, Commented Jun 19, 2013 at 19:00
@muistooshort It's just an "opaque blob" of data. I will be doing all bit operations outside of the queries. — RileyE
– RileyE, Commented Jun 19, 2013 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
– RileyE, Commented Jun 19, 2013 at 19:02

user80168 · Accepted Answer · 2013-06-19 18:55:44Z

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)

answered Jun 19, 2013 at 18:55

user80168

Sign up to request clarification or add additional context in comments.

5 Comments

RileyE Over a year ago

But would bit be possible in SQLite as well? Also, would that work in a rails schema?

mvp Over a year ago

@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.

CL. Over a year ago

@RileyE This will also work in SQLite due to its dynamic type system.

RileyE Over a year ago

@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 Over a year ago

@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?

Collectives™ on Stack Overflow

Using bigint in PostgreSQL and SQLite

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related