postgresql bigint to string

[jackshang]

1. database table:

CREATE TABLE IF NOT EXISTS public.user
(
ti_id bigint NOT NULL,
ti_username character varying(16) NOT NULL,
ti_created_on timestamp with time zone NOT NULL,
ti_updated_on timestamp with time zone NOT NULL,
);

---

2. snippet code

db = ... ...
db.extension :pg_extended_integer_support
dataset = db[:user].where(:ti_id=>4165893557190656012)
dataset = dataset.extension(:pg_extended_integer_support)
dataset.first

Actual result：
{:ti_id=>4165893557190656012, :ti_username=>"1", :ti_created_on=>2024-01-11 10:43:02.092 +0800, :ti_updated_on=>2024-01-11 10:43:02.092 +0800}

Expected result：
{:ti_id=>”4165893557190656012“, :ti_username=>"1", :ti_created_on=>2024-01-11 10:43:02.092 +0800, :ti_updated_on=>2024-01-11 10:43:02.092 +0800}

Is there a way convert bigint to string automaticly？

[jeremyevans]

I'm not sure why you would expect a string to be returned instead of an integer, when the column is a bigint column. The pg_extended_integer_support extension handles how Ruby integers outside the PostgreSQL bigint range are literalized into SQL, it does not affect how integers returned from the database (which must be inside the bigint range) are treated. If you would like a string returned, it's best to cast:

dataset.select(Sequel.cast(:ti_id, String), ...).first

There isn't way to do this automatically, though if you are using models, you could make the model's dataset use this cast, and then all datasets based on it will also use the cast.

[jackshang]

Let me first explain that this is not a Sequel problem.

In the Postgresql database, the primary key uses bigint rather than character varying for better performance, so I used the Twitter snowflake id as the primary key.

In Javascript, there is a issue in JSON.parse() when the integer exceeds 9007199254740991 (Number.MAX_SAFE_INTEGER), and the ID will be wrong. Although JS has the "json-bigint" extension library to solve this problem, it is not a good way. Maybe this is not a bug, but it does cause trouble.

I think maybe the Sequel plugin supports converting bigint to string in dataset to end this trouble. Thank you for your answer.