From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | ab(at)principiamentis(dot)com |
Subject: | BUG #16022: to_json on arrays with unusual lower bound is not intuitive |
Date: | 2019-09-25 12:23:11 |
Message-ID: | 16022-5c864f8a572d5b5b@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-docs |
The following bug has been logged on the website:
Bug reference: 16022
Logged by: A Bergmaier
Email address: ab(at)principiamentis(dot)com
PostgreSQL version: 11.5
Operating system: Ubuntu
Description:
Hi!
I would expect the array indices of a JSON array to match with the
subscripts of a postgres array when converting them back and forth.
However, Postgres lets the JSON array begin at the `lower_bound` of the
array, not at subscript 1 (= index 0).
For example, `to_json('[-2:5]={-2,-1,0,1,2,3,4,5}'::int[])` results in
`[-2,-1,0,1,2,3,4,5]` where I would have expected `[1,2,3,4,5]` (or an
error, since JSON arrays must not have negative indices).
And more annoyingly, `to_json('[3:8]={3,4,5,6,7,8}'::int[])` results in
`[3,4,5,6,7,8]` where I would have needed `[null,null,3,4,5,6,7,8]`.
(See also some demo
https://dbfiddle.uk/?rdbms=postgres_11&fiddle=2d359608815f7059f3120fa17da1bdef)
Just for reference, I'm on PostgreSQL 11.4 (Debian 11.4-1.pgdg90+1) on
x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0
20170516, 64-bit, but I doubt it matters.
I can circumvent this behaviour by using a function like
CREATE FUNCTION array_fillup(arr anyarray) RETURNS anyarray
AS $$
BEGIN
IF array_lower(arr, 1) > 1 THEN
arr[1] = NULL;
END IF;
RETURN arr;
END
$$ LANGUAGE plpgsql STRICT;
before passing my sparse array into `to_json`, but I would like to know
whether there's a better solution or whether the issue could be fixed at the
core. (Presumable, breaking backwards-compatibility is a problem?)
Kind regards,
Andreas Bergmaier
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2019-09-25 14:34:16 | Re: BUG #16022: to_json on arrays with unusual lower bound is not intuitive |
Previous Message | movead.li@highgo.ca | 2019-09-25 08:09:46 | Re: BUG #15929: logical decoding can not write down the analyse result when the output file is touched. |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2019-09-25 14:34:16 | Re: BUG #16022: to_json on arrays with unusual lower bound is not intuitive |
Previous Message | Peter Eisentraut | 2019-09-25 08:25:59 | Re: I'm surprised to see the word master here |