2

https://developer.twitter.com/en/docs/twitter-ids I would like to store such Snowflake ID's in my PostgresQL database, what would be the accurate constraint and datatype for this?

At first I thought "id" VARCHAR(19) NOT NULL, but then I started wondering if there is something more accruate

1 Answers1

2

From the page you linked in the question it says that Twitter ids are 64 bit integers. So you can use bigint for the column type in postgres.

Stuck
  • 11,225
  • 11
  • 59
  • 104
  • I edited the question since I was specifically wondering about string representation of those ID's –  Nov 27 '21 at 15:56
  • @Daniell: according to the link in your question, Twitter only chose to also return them as Strings because of the way JavaScript can't handle 64 bit ingegers. Postgres' bigint does not have such a problem. So use a `bigint` it will be a lot more efficient than a VARCHAR –  Nov 27 '21 at 16:14
  • I should have mentioned that I am consuming an external API in JavaScript so I am getting those ID's in string format to then store, it would be more convenient to store in string format as I do not need to expose those ID's outside of my JS code –  Nov 27 '21 at 16:20
  • You should store it as bigint because it is much more efficient and you can just cast the string to bigint for this purpose – Stuck Nov 28 '21 at 18:26
  • BigInt in Postgres is signed integer, so there is a chance your ID will be larger than the maximum big integer field due to the mismatch between signed and unsigned integer sizes. So this answer should really be downvoted or fixed. – Techdragon Jul 31 '23 at 15:05