From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: ab@principiamentis.com
Subject: BUG #16022: to_json on arrays with unusual lower bound is not intuitive
Date: Wed, 25 Sep 2019 12:23:11 +0000
Message-ID: <16022-5c864f8a572d5b5b@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 16022
Logged by: A Bergmaier
Email address: ab@principiamentis.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, ab@principiamentis.com
Subject: Re: BUG #16022: to_json on arrays with unusual lower bound is not intuitive
In-Reply-To: <16022-5c864f8a572d5b5b@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox