PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID16022
PG Version11.5
OSUbuntu
Opened2019-09-25 12:23:11+00
Reported byA Bergmaier
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      16022
Logged by:          A Bergmaier
Email address:      (redacted)
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

Messages

DateAuthorSubject
2019-09-25 12:23:11+00PG Bug reporting formBUG #16022: to_json on arrays with unusual lower bound is not intuitive
2019-09-25 14:34:16+00Tom LaneRe: BUG #16022: to_json on arrays with unusual lower bound is not intuitive
2019-09-26 13:09:03+00Andreas BergmaierRe: BUG #16022: to_json on arrays with unusual lower bound is not intuitive