SERIAL and javascript

General
#1

hello -

using SERIAL PRIMARY KEY is totally awesome.

however, when we try to access them using jScript, the 18 digit number is causing jScript issues.

is it possible to use myPrimaryKey SERIAL PRIMARY KEY but somehow end up with something that is a little more javascript friendly? is there any way to trick javascript into thinking this is really a string and not an integer?

#2

The easiest way (but not the most efficient) to get an auto-generated primary key that won’t cause any trouble with javascript is STRING PRIMARY KEY DEFAULT from_uuid(uuid_v4()).

There’s probably also a way to work around javascript’s limitations here but I don’t know what it is.

#3

excerpt from https://www.cockroachlabs.com/docs/serial.html:

We believe this data type is a better solution than PostgeSQL’s SERIAL and MySQL’s AUTO_INCREMENT types, both of which auto-increment integers but not necessarily in a strictly sequential fashion (see the Auto-Incrementing Is Not Always Sequential example below). However, if you find that this feature is incompatible with your application, please open an issue or chat with us on Gitter.

i have been using auto-generated keys for too long to turn back now. and cockroach SERIAL PRIMARY KEY approach is the best i have ever seen.

is it possible to somehow define the SERIAL PRIMARY KEY as a string rather than as an integer somehow? i did see a SMALLSERIAL option, but according to the documentation (and yes i actually looked at it) but it appears to be just a mere alias for SERIAL.

EDIT: is it possible to somehow shrink the size of the primary key by a mere one digit, from 18 digits to 17 digits? that would get me around the jS issue.

var xx = '23576496766323916';
parseInt(xx)  // 23576496766323916
#4

this seems to have fixed it: Casting a primary key as a VARCHAR/STRING

#5

That would be doable - STRING PRIMARY KEY DEFAULT to_hex(unique_rowid()).

#6

hey ben - do you see any advantage or disadvantage to these two different approaches?

the original way using SERIAL PRIMARY KEY might a minor advantage in that most primary keys are already numeric, which would make a conversion much easier.

#7

UUIDs and unique_rowid() have different ordering properties - the UUID version will scatter records randomly throughout the table, while the unique_rowid() version will group records created about the same time onto the same range. Which one is better depends on your query pattern (UUIDs ensure that every node shares in the load, but unique_rowid() may be able to group “related” data together so it can be queried without a distributed transaction). My bias is to prefer the scattering of a UUID primary key unless I have reason to believe that my query pattern will benefit from time-ordered storage (and this benefit is large enough to overcome the less balanced load).

Best Practice for Primary Keys