[PATCH] Generic type subscripting

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
141 messages Options
1 ... 45678
Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Generic type subscripting

Dmitry Dolgov
> On Fri, Feb 1, 2019 at 4:55 PM Alvaro Herrera <[hidden email]> wrote:
>
> On 2019-Feb-01, Dmitry Dolgov wrote:
>
> > The moment was longer than I expected, but here is the rebased version, where
> > all the individual patches can be applied and compiled cleanly (although there
> > is still functional dependency between 0002 and 0003, since the former
> > introduces a new subscripting without any implementation, and the latter
> > introduces an implementation for array data type).
>
> Cool, pushed 0001.  I'm afraid I included some pgindenting, so you'll
> have to rebase again.  Maybe you already know how to do it without
> manually rebasing, but if not, a quick trick to avoid rebasing manually
> over all those whitespace changes might be to un-apply with "git show |
> patch -p1 -R", then apply your original 0001, commit, apply 0002, then
> pgindent; if you now do a git diff to the original commit, you should
> get an almost clean diff.  Or you could just try to apply with -w.

Great, thank you!

Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Generic type subscripting

Dmitry Dolgov
> On Fri, Feb 1, 2019 at 5:02 PM Dmitry Dolgov <[hidden email]> wrote:

>
> > On Fri, Feb 1, 2019 at 4:55 PM Alvaro Herrera <[hidden email]> wrote:
> >
> > On 2019-Feb-01, Dmitry Dolgov wrote:
> >
> > > The moment was longer than I expected, but here is the rebased version, where
> > > all the individual patches can be applied and compiled cleanly (although there
> > > is still functional dependency between 0002 and 0003, since the former
> > > introduces a new subscripting without any implementation, and the latter
> > > introduces an implementation for array data type).
> >
> > Cool, pushed 0001.  I'm afraid I included some pgindenting, so you'll
> > have to rebase again.  Maybe you already know how to do it without
> > manually rebasing, but if not, a quick trick to avoid rebasing manually
> > over all those whitespace changes might be to un-apply with "git show |
> > patch -p1 -R", then apply your original 0001, commit, apply 0002, then
> > pgindent; if you now do a git diff to the original commit, you should
> > get an almost clean diff.  Or you could just try to apply with -w.
>
> Great, thank you!
And here is the rebased version.

v19-0001-Base-implementation-of-subscripting-mechanism.patch (66K) Download Attachment
v19-0004-Subscripting-documentation.patch (27K) Download Attachment
v19-0003-Subscripting-for-jsonb.patch (45K) Download Attachment
v19-0002-Subscripting-for-array.patch (33K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Generic type subscripting

Dmitry Dolgov
> On Tue, Feb 19, 2019 at 5:22 PM Dmitry Dolgov <[hidden email]> wrote:
>
> And here is the rebased version.

One more, after jsonpath changes.

v20-0002-Subscripting-for-array.patch (33K) Download Attachment
v20-0001-Base-implementation-of-subscripting-mechanism.patch (66K) Download Attachment
v20-0004-Subscripting-documentation.patch (27K) Download Attachment
v20-0003-Subscripting-for-jsonb.patch (46K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Generic type subscripting

Dmitry Dolgov
> On Tue, Mar 19, 2019 at 2:30 PM Dmitry Dolgov <[hidden email]> wrote:
>
> > On Tue, Feb 19, 2019 at 5:22 PM Dmitry Dolgov <[hidden email]> wrote:
> >
> > And here is the rebased version.
>
> One more, after jsonpath changes.

Oh, I forgot to fix duplicating oids, here is it.

v21-0001-Base-implementation-of-subscripting-mechanism.patch (66K) Download Attachment
v21-0002-Subscripting-for-array.patch (33K) Download Attachment
v21-0003-Subscripting-for-jsonb.patch (46K) Download Attachment
v21-0004-Subscripting-documentation.patch (27K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Generic type subscripting

Dmitry Dolgov
Rebase after pg_indent. Besides, off the list there was a suggestion that this
could be useful to accept more than one data type as a key for subscripting.
E.g. for jsonb it probably makes sense to understand both a simple key name and
jsonpath:

    jsonb['a'] and jsonb['$.a']

While to implement it can be technically relatively straightforward I guess, I
wonder if there is any opinion about how valuable it could be and what it
should looks like from the syntax point of view (since I believe a user needs
to specify which type needs to be used).

v22-0004-Subscripting-documentation.patch (27K) Download Attachment
v22-0002-Subscripting-for-array.patch (33K) Download Attachment
v22-0001-Base-implementation-of-subscripting-mechanism.patch (66K) Download Attachment
v22-0003-Subscripting-for-jsonb.patch (46K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Generic type subscripting

Pavel Stehule


st 29. 5. 2019 v 17:49 odesílatel Dmitry Dolgov <[hidden email]> napsal:
Rebase after pg_indent. Besides, off the list there was a suggestion that this
could be useful to accept more than one data type as a key for subscripting.
E.g. for jsonb it probably makes sense to understand both a simple key name and
jsonpath:

    jsonb['a'] and jsonb['$.a']

While to implement it can be technically relatively straightforward I guess, I
wonder if there is any opinion about how valuable it could be and what it
should looks like from the syntax point of view (since I believe a user needs
to specify which type needs to be used).

It is difficult decision - possibility to use jsonpath looks great, but necessity to cast every time is not friendly.

Probably there can be preferred type if subscripting is of unknown type. There can be similar rules to function's parameters.

so jsonb['a'] -- key
    jsonb['$.a'] -- key
    jsonb['$.a'::jsonpath'] -- json path

but it can be source of bad issues - so I think we don't need this feature in this moment. This feature can be implemented later, I think.

Regards

Pavel


Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Generic type subscripting

Dmitry Dolgov
> On Wed, May 29, 2019 at 6:17 PM Pavel Stehule <[hidden email]> wrote:
>
> st 29. 5. 2019 v 17:49 odesílatel Dmitry Dolgov <[hidden email]> napsal:
>>
>> Rebase after pg_indent. Besides, off the list there was a suggestion that this
>> could be useful to accept more than one data type as a key for subscripting.
>> E.g. for jsonb it probably makes sense to understand both a simple key name and
>> jsonpath:
>>
>>     jsonb['a'] and jsonb['$.a']
>>
>> While to implement it can be technically relatively straightforward I guess, I
>> wonder if there is any opinion about how valuable it could be and what it
>> should looks like from the syntax point of view (since I believe a user needs
>> to specify which type needs to be used).
>
>
> It is difficult decision - possibility to use jsonpath looks great, but
> necessity to cast every time is not friendly.

Thanks. Yes, I also wonder if it's possible to avoid type casting every time,
but other ideas seems syntactically equally not friendly.

> Probably there can be preferred type if subscripting is of unknown type.
> There can be similar rules to function's parameters.
>
> so jsonb['a'] -- key
>     jsonb['$.a'] -- key
>     jsonb['$.a'::jsonpath'] -- json path
>
> but it can be source of bad issues - so I think we don't need this feature in
> this moment. This feature can be implemented later, I think.

Yeah, I agree it's something that looks like a good potential improvement, not
now but in the future.


Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Generic type subscripting

Dmitry Dolgov
> On Thu, May 30, 2019 at 4:17 PM Dmitry Dolgov <[hidden email]> wrote:
>
> > On Wed, May 29, 2019 at 6:17 PM Pavel Stehule <[hidden email]> wrote:
> >
> > st 29. 5. 2019 v 17:49 odesílatel Dmitry Dolgov <[hidden email]> napsal:
> >>
> >> Rebase after pg_indent. Besides, off the list there was a suggestion that this
> >> could be useful to accept more than one data type as a key for subscripting.
> >> E.g. for jsonb it probably makes sense to understand both a simple key name and
> >> jsonpath:

And one more rebase.

v23-0003-Subscripting-for-jsonb.patch (46K) Download Attachment
v23-0002-Subscripting-for-array.patch (33K) Download Attachment
v23-0001-Base-implementation-of-subscripting-mechanism.patch (66K) Download Attachment
v23-0004-Subscripting-documentation.patch (27K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Generic type subscripting

Dmitry Dolgov
> On Thu, Jun 6, 2019 at 3:17 PM Dmitry Dolgov <[hidden email]> wrote:
>
> > On Thu, May 30, 2019 at 4:17 PM Dmitry Dolgov <[hidden email]> wrote:
> >
> > > On Wed, May 29, 2019 at 6:17 PM Pavel Stehule <[hidden email]> wrote:
> > >
> > > st 29. 5. 2019 v 17:49 odesílatel Dmitry Dolgov <[hidden email]> napsal:
> > >>
> > >> Rebase after pg_indent. Besides, off the list there was a suggestion that this
> > >> could be useful to accept more than one data type as a key for subscripting.
> > >> E.g. for jsonb it probably makes sense to understand both a simple key name and
> > >> jsonpath:
>
> And one more rebase.

Oh, looks like I was just confused and it wasn't necessary - for some reason
starting from v22 cfbot tries to apply v6 instead of the latest one.


Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Generic type subscripting

Thomas Munro-5
On Fri, Jun 7, 2019 at 6:22 AM Dmitry Dolgov <[hidden email]> wrote:
> > > >> Rebase after pg_indent. Besides, off the list there was a suggestion that this
> > > >> could be useful to accept more than one data type as a key for subscripting.
> > > >> E.g. for jsonb it probably makes sense to understand both a simple key name and
> > > >> jsonpath:
> >
> > And one more rebase.
>
> Oh, looks like I was just confused and it wasn't necessary - for some reason
> starting from v22 cfbot tries to apply v6 instead of the latest one.

Hi Dmitry,

Sorry about that.  It looks like I broke the cfbot code that picks
which thread to pull patches from when there are several registered in
the CF app, the last time the HTML format changed.  Now it's back to
picking whichever thread has the most recent message on it.  Such are
the joys of web scraping (obviously we need better integration and
that will happen, I just haven't had time yet).

Anyway, I fixed that.  But now you really do need to rebase :-)

--
Thomas Munro
https://enterprisedb.com


Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Generic type subscripting

Dmitry Dolgov
> On Mon, Jul 8, 2019 at 6:46 AM Thomas Munro <[hidden email]> wrote:

>
> On Fri, Jun 7, 2019 at 6:22 AM Dmitry Dolgov <[hidden email]> wrote:
> > > > >> Rebase after pg_indent. Besides, off the list there was a suggestion that this
> > > > >> could be useful to accept more than one data type as a key for subscripting.
> > > > >> E.g. for jsonb it probably makes sense to understand both a simple key name and
> > > > >> jsonpath:
> > >
> > > And one more rebase.
> >
> > Oh, looks like I was just confused and it wasn't necessary - for some reason
> > starting from v22 cfbot tries to apply v6 instead of the latest one.
>
> Hi Dmitry,
>
> Sorry about that.  It looks like I broke the cfbot code that picks
> which thread to pull patches from when there are several registered in
> the CF app, the last time the HTML format changed.  Now it's back to
> picking whichever thread has the most recent message on it.  Such are
> the joys of web scraping (obviously we need better integration and
> that will happen, I just haven't had time yet).
>
> Anyway, I fixed that.  But now you really do need to rebase :-)
Thanks for fixing and for the reminder! Here is the new rebased version. It
contradicts a bit with 44982e7d09, because I'm actually using indexprSlice, but
I guess we can figure this out.

And I must admit, it's a pure fun to maintain such a large patch set in sync
for already several years :)

v24-0004-Subscripting-documentation.patch (27K) Download Attachment
v24-0003-Subscripting-for-jsonb.patch (46K) Download Attachment
v24-0001-Base-implementation-of-subscripting-mechanism.patch (66K) Download Attachment
v24-0002-Subscripting-for-array.patch (33K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Generic type subscripting

David Fetter
On Tue, Jul 09, 2019 at 02:23:57PM +0200, Dmitry Dolgov wrote:

> > On Mon, Jul 8, 2019 at 6:46 AM Thomas Munro <[hidden email]> wrote:
> >
> > On Fri, Jun 7, 2019 at 6:22 AM Dmitry Dolgov <[hidden email]> wrote:
> > > > > >> Rebase after pg_indent. Besides, off the list there was a suggestion that this
> > > > > >> could be useful to accept more than one data type as a key for subscripting.
> > > > > >> E.g. for jsonb it probably makes sense to understand both a simple key name and
> > > > > >> jsonpath:
> > > >
> > > > And one more rebase.
> > >
> > > Oh, looks like I was just confused and it wasn't necessary - for some reason
> > > starting from v22 cfbot tries to apply v6 instead of the latest one.
> >
> > Hi Dmitry,
> >
> > Sorry about that.  It looks like I broke the cfbot code that picks
> > which thread to pull patches from when there are several registered in
> > the CF app, the last time the HTML format changed.  Now it's back to
> > picking whichever thread has the most recent message on it.  Such are
> > the joys of web scraping (obviously we need better integration and
> > that will happen, I just haven't had time yet).
> >
> > Anyway, I fixed that.  But now you really do need to rebase :-)
>
> Thanks for fixing and for the reminder! Here is the new rebased version. It
> contradicts a bit with 44982e7d09, because I'm actually using indexprSlice, but
> I guess we can figure this out.
>
> And I must admit, it's a pure fun to maintain such a large patch set in sync
> for already several years :)
Looks great!

The tutorial piece has bit-rotted slightly. Please find attached a
patch atop yours that fixes it.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

fix_subscripting_tutorial.patch (920 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Generic type subscripting

Dmitry Dolgov
> On Thu, Jul 11, 2019 at 9:47 AM David Fetter <[hidden email]> wrote:
>
> Looks great!
>
> The tutorial piece has bit-rotted slightly. Please find attached a
> patch atop yours that fixes it.

Indeed, I've missed this change, thank you! Although there supposed to be an
upperindex, not numupper (since the latter is just a number of upper indexes).

v25-0001-Base-implementation-of-subscripting-mechanism.patch (66K) Download Attachment
v25-0002-Subscripting-for-array.patch (33K) Download Attachment
v25-0004-Subscripting-documentation.patch (27K) Download Attachment
v25-0003-Subscripting-for-jsonb.patch (46K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Generic type subscripting

David Fetter
On Thu, Jul 11, 2019 at 04:30:46PM +0200, Dmitry Dolgov wrote:
> > On Thu, Jul 11, 2019 at 9:47 AM David Fetter <[hidden email]> wrote:
> >
> > Looks great!
> >
> > The tutorial piece has bit-rotted slightly. Please find attached a
> > patch atop yours that fixes it.
>
> Indeed, I've missed this change, thank you! Although there supposed to be an
> upperindex, not numupper (since the latter is just a number of upper indexes).

Oops! Fooled by a suggestion from the compiler. My bad for not
checking more carefully. Thanks for making this happen.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Generic type subscripting

Alvaro Herrera-9
In reply to this post by Dmitry Dolgov
On 2019-Jul-11, Dmitry Dolgov wrote:

> > On Thu, Jul 11, 2019 at 9:47 AM David Fetter <[hidden email]> wrote:
> >
> > Looks great!
> >
> > The tutorial piece has bit-rotted slightly. Please find attached a
> > patch atop yours that fixes it.
>
> Indeed, I've missed this change, thank you! Although there supposed to be an
> upperindex, not numupper (since the latter is just a number of upper indexes).

Can you please send an updated version?

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Generic type subscripting

Dmitry Dolgov
> On Thu, Sep 12, 2019 at 3:58 AM Alvaro Herrera <[hidden email]> wrote:
> Can you please send an updated version?

Sure, I'll send it in a few days.


Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Generic type subscripting

Dmitry Dolgov
> On Fri, Sep 13, 2019 at 10:29 PM Dmitry Dolgov <[hidden email]> wrote:
>
> > On Thu, Sep 12, 2019 at 3:58 AM Alvaro Herrera <[hidden email]> wrote:
> > Can you please send an updated version?
>
> Sure, I'll send it in a few days.

Here it is.

v26-0001-Base-implementation-of-subscripting-mechanism.patch (65K) Download Attachment
v26-0002-Subscripting-for-array.patch (33K) Download Attachment
v26-0004-Subscripting-documentation.patch (27K) Download Attachment
v26-0003-Subscripting-for-jsonb.patch (46K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Generic type subscripting

Alvaro Herrera-9
This broke recently.  Can you please rebase again?

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Generic type subscripting

Dmitry Dolgov
> On Wed, Sep 25, 2019 at 10:22 PM Alvaro Herrera <[hidden email]> wrote:
>
> This broke recently. Can you please rebase again?

Thanks for noticing! Sure, here it is. We're quite close to the records.

v27-0001-Base-implementation-of-subscripting-mechanism.patch (65K) Download Attachment
v27-0002-Subscripting-for-array.patch (33K) Download Attachment
v27-0003-Subscripting-for-jsonb.patch (45K) Download Attachment
v27-0004-Subscripting-documentation.patch (27K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Generic type subscripting

Nikita Glukhov

On 30.09.2019 14:57, Dmitry Dolgov wrote:

On Wed, Sep 25, 2019 at 10:22 PM Alvaro Herrera [hidden email] wrote:

This broke recently. Can you please rebase again?
Thanks for noticing! Sure, here it is. We're quite close to the records.
Hi. I added new 5th patch to this patch set.


Jsonb subscripting uses text for representing subscript values.  This is Ok for
object keys, but integer arrays indexes should be parsed at runtime.  Another
problem is that floats can't be used as array indexes because integers simply
can't be parsed from a string containing a floating point.

But we can use float indexes in ordinary Postgres arrays:

SELECT ('{1,2,3}'::int[])[2.3];
 int4 
------
    2
(1 row)

Also SQL standard allows to use float indexes in JSON path with implementation-
defined rounding or truncation:

SELECT jsonb_path_query('[1, 2, 3]', '$[1.3]');
 jsonb_path_query 
------------------
 2
(1 row)


So, I decided to fix these two issues introducing polymorphic subscripting,
in which each subscript expression variant interpreted depending on the result
of previous subscripting step. There are two variants of jsonb subscript
expressions -- the first is casted to text and the second is casted to int4.
Executor at each subscripting step selects which variant to execute by calling
callback jsonb_subscript_selectexpr().  To manage the subscripting state,
another callback jsonb_subscript_step() was introduced along with the new field
SubscriptingRefState.privatedata.

Such expression selecting has noticeable overhead, which we can eliminate by
generating only one expression variant when subscript is of int2/int4 or
text type.

After float subscripts start to works as expected:

SELECT ('[1, 2, 3]'::jsonb)[1.2];
 jsonb 
-------
 2
(1 row)

SELECT ('{"1": "a", "1.0": "b", "1.2": "c"}'::jsonb)[i]
FROM unnest('{1,1.0,1.2}'::numeric[]) i;

 jsonb 
-------
 "a"
 "b"
 "c"
(3 rows)




Performance was compared on 4 tables with 10M rows:

-- [ i ]
CREATE TABLE arr_1 AS
SELECT jsonb_build_array(i)::jsonb js
FROM generate_series(1, 10000000) i;

-- { "a": i }
CREATE TABLE obj_1 AS
SELECT jsonb_build_object('a', i)  js
FROM generate_series(1, 10000000) i;

-- [[[[[[[[[[ i ]]]]]]]]]]
CREATE TABLE arr_10 AS
SELECT (repeat('[', 10) || i || repeat(']', 10))::jsonb js
FROM generate_series(1, 10000000) i;

-- {"a": {"a": ... {"a": {"a": i } } ... } }
CREATE TABLE obj_10 AS
SELECT (repeat('{"a":', 10) || i || repeat('}', 10))::jsonb js
FROM generate_series(1, 10000000) i;

Queries were like "SELECT FROM table WHERE expression IS [NOT] NULL".

Compared previous v27 version (4 patches) with v28 version (5 patches).
New patch #5 contains one small but important optimization -- elimination of
unnecessary palloc() in getIthJsonbValueFromContainer() and jsonb_get_element().
It should be posted separately, but for simplicity I included it the patch now.
For the correctness of comparison, it was evaluated separately on top of v27
(v27opt).


 Table  |               Expression             |      Query time, ms
        |                                      |  v27  | v27opt|  v28 
--------+--------------------------------------+-------+-------+-------
 arr_1  | js->0                                |  1811 |  1809 |  1813  
 arr_1  | js[0]                                |  2273 |  2294 |  2028 
 arr_1  | js['0']                              |  2276 |  2286 |  2339
 arr_1  | js->1                                |   808 |   844 |   809
 arr_1  | js[1]                                |  1180 |  1187 |  1008 
 
 obj_1  | js->'a'                              |  1941 |  1935 |  1939
 obj_1  | js['a']                              |  2079 |  2083 |  2102
 obj_1  | js->'b'                              |   917 |   915 |   902
 obj_1  | js['b']                              |   960 |   961 |  1059
        |
 arr_10 | js->0->0 ... ->0->0                  |  4530 |  4068 |  4052
 arr_10 | js[0][0] ... [0][0]                  |  6197 |  5513 |  3766
 arr_10 | js['0']['0'] ... ['0']['0']          |  6202 |  5519 |  5983
 arr_10 | js #>  '{0,0,0,0,0,0,0,0,0,0}'       |  6412 |  5850 |  5835
 arr_10 | js #>> '{0,0,0,0,0,0,0,0,0,0}'       |  5904 |  5181 |  5192

 obj_10 | js->'a'->'a' ... ->'a'->'a'          |  4970 |  4717 |  4704
 obj_10 | js['a']['a'] ... ['a']['a']          |  4331 |  3698 |  4032
 obj_10 | js #>  '{a,a,a,a,a,a,a,a,a,a}'       |  4570 |  3941 |  3949         
 obj_10 | js #>> '{a,a,a,a,a,a,a,a,a,a}'       |  4055 |  3395 |  3392       

As it can be seen, array access time reduced from 10% in single subscripts
to 40% in 10-subscript chains, and subscripting event started to overtake
chained "->" operators.  But there is 10% slowdown of object key access that
needs further investigation.  The elimination of unnecessary palloc()s also
gives good results.



I had to write new assignment logic reusing only some parts of setPath(),
because the loop in setPath() should be broken on every level.  During this
process, I decided to implement assignment behavior similar to PostgreSQL's
array behavior and added two new features:
 - creation of jsonb arrays/objects container from NULL values
 - appending/prepending array elements on the specified position, gaps filled
   with nulls (JavaScript has similar behavior)
These features are not so easy to extract into a separate patch on top of the 
first 4 patches, but I can try if necessary.

Here is examples of new features:

CREATE TABLE t AS SELECT NULL::jsonb js, NULL::int[] a;

-- create array from NULL
UPDATE t SET js[0] = 1, a[1] = 1;
SELECT * FROM t;
 js  |  a  
-----+-----
 [1] | {1}
(1 row)

-- append 4th element
UPDATE t SET js[3] = 4, a[4] = 4;
SELECT * FROM t;
         js         |        a        
--------------------+-----------------
 [1, null, null, 4] | {1,NULL,NULL,4}
(1 row)

-- prepend element when index is negative (position = size + index)
UPDATE t SET js[-6] = -2;
SELECT js FROM t;
              js              
------------------------------
 [-2, null, 1, null, null, 4]
(1 row)

--
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

v28-0001-Base-implementation-of-subscripting-mechanism.patch (49K) Download Attachment
v28-0002-Subscripting-for-array.patch (25K) Download Attachment
v28-0003-Subscripting-for-jsonb.patch (34K) Download Attachment
v28-0004-Subscripting-documentation.patch (20K) Download Attachment
v28-0005-Polymorphic-subscripting.patch (64K) Download Attachment
1 ... 45678