Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

postgresql - JSONb dates: actual dates internally?

I'm using the postgresql jdbc adapter to migrate a bunch of data into a jsonb field (postgres 9.4).

After importing, date fields look correct but are displayed surrounded with doublequotes. Is there a way to tell if they are actually stored internally as date values ? If they're strings, I don't think range lookups will be very efficient.

For example, an entry in a properties jsonb field looks like this:

"founded_on": "Sep 1, 2012 12:00:00 AM",

I can now search on, say,

SELECT CAST(properties->>'founded_on' AS DATE

and

SELECT extract('year' from cast(properties->>'founded_on' as timestamp))

and both work fine, but don't tell me whether Postgres is reparsing a string value in the jsonb field each time, as a date.

I could create an index where I cast these values to dates, and use that to search, but it seems somewhat inelegant. I'd really prefer to know that the value stored is a date. Integers and floats appear to be their actual native values, not strings, as in these:

shares_sold": 5900000,

"latitude": 33.561467,

Any feedback highly appreciated.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

JSON does not have a "date" type. The JSONB type (added in Pg 9.4) maps but does not extend upon JSON primitive types. The value seen is what it is, text.

While an index can be added over string properties the current "English prose" format will not be able to participate in range queries because such values are not well-ordered over dates.

Different ways of storing well-ordered dates, given the primitive type restrictions.

(Equality index probes can be used even if ranges cannot .. provided the values match exactly, which is still subject to using a consistent data representation.)

When applying a range query over the (JSONB / GIN) index convert the DATE values to the appropriate JSON data type (mapped to integer, numeric, text in Pg) used for the "date" property; not the other way.

When fetching the values convert the chosen format to a DATE - it's 'okay' since this is done after the range query and 'required' since JSONB does not natively support dates or times.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...