Modifying Arrays

2023-02-27 Thread PG Doc comments form 
The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/15/arrays.html
Description:

According to the official document of PostgreSQL 15, in the section 8.15.4.
Modifying Arrays there is a statement like:

The slice syntaxes with omitted lower-bound and/or upper-bound can be used
too, but only when updating an array value that is not NULL or
zero-dimensional (otherwise, there is no existing subscript limit to
substitute).

This statement is not true for the following statements or I am missing
something?

CREATE TABLE sal_emp (
nametext,
pay_by_quarter  integer[],
scheduletext[][]
);

INSERT INTO sal_emp
VALUES ('Bill',
'{1, 1, 1, 1}',
'{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT INTO sal_emp
VALUES ('Carol',
'{2, 25000, 25000, 25000}',
'{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

INSERT INTO sal_emp
VALUES ('Carolx',
'{2, 25001, 25002, 25003}',
'{{"breakfastx", "consultingx"}, {"meetingx", "lunchx"}, {"meetingy",
"lunchy"}}');

INSERT INTO sal_emp
VALUES ('Sam',
'{2, 25001, 25002, 25003}',
'{}');

INSERT INTO sal_emp
VALUES ('Pam',
'{2, 25001, 25002, 25003}');

SELECT * FROM sal_emp;

UPDATE sal_emp SET schedule[1:2] = '{"asd", "asa"}'
WHERE name = 'Sam';

UPDATE sal_emp SET schedule[1:2] = '{"x", "y"}'
WHERE name = 'Pam';

SELECT * FROM sal_emp;

In order to test it: https://dbfiddle.uk/pHKY32u0

Thanks!

Re: Modifying Arrays

2023-02-27 Thread Tom Lane 
PG Doc comments form  writes:
> According to the official document of PostgreSQL 15, in the section 8.15.4.
> Modifying Arrays there is a statement like:

> The slice syntaxes with omitted lower-bound and/or upper-bound can be used
> too, but only when updating an array value that is not NULL or
> zero-dimensional (otherwise, there is no existing subscript limit to
> substitute).

> This statement is not true for the following statements or I am missing
> something?

Your example doesn't use a slice with omitted bound, so I'm not quite sure
what you are trying to show?  Using your test data, a slice with omitted
bound does fail with Pam's null schedule:

=> UPDATE sal_emp SET schedule[:2] = '{"w", "x", "y", "z"}' WHERE name = 'Pam';
ERROR:  array slice subscript must provide both boundaries
DETAIL:  When assigning to a slice of an empty array value, slice boundaries 
must be fully specified.

but it works for the other entries:

=> UPDATE sal_emp SET schedule[:2] = '{"w", "x", "y", "z"}' WHERE name != 'Pam';
UPDATE 3
=> table sal_emp;
  name  |  pay_by_quarter   |schedule 
+---+-
 Pam| {2,25001,25002,25003} | 
 Bill   | {1,1,1,1} | {{w,x},{y,z}}
 Carol  | {2,25000,25000,25000} | {{w,x},{y,z}}
 Carolx | {2,25001,25002,25003} | {{w,x},{y,z},{meetingy,lunchy}}
(4 rows)

regards, tom lane

Re: Modifying Arrays

2023-02-27 Thread Umut TEKİN 
Hi Tom,

Thanks for the e - mail. I did interpret it exactly in a different manner,
it was my mistake. I am sorry.

Thanks!

On Mon, Feb 27, 2023 at 4:41 PM Tom Lane  wrote:

> PG Doc comments form  writes:
> > According to the official document of PostgreSQL 15, in the section
> 8.15.4.
> > Modifying Arrays there is a statement like:
>
> > The slice syntaxes with omitted lower-bound and/or upper-bound can be
> used
> > too, but only when updating an array value that is not NULL or
> > zero-dimensional (otherwise, there is no existing subscript limit to
> > substitute).
>
> > This statement is not true for the following statements or I am missing
> > something?
>
> Your example doesn't use a slice with omitted bound, so I'm not quite sure
> what you are trying to show?  Using your test data, a slice with omitted
> bound does fail with Pam's null schedule:
>
> => UPDATE sal_emp SET schedule[:2] = '{"w", "x", "y", "z"}' WHERE name =
> 'Pam';
> ERROR:  array slice subscript must provide both boundaries
> DETAIL:  When assigning to a slice of an empty array value, slice
> boundaries must be fully specified.
>
> but it works for the other entries:
>
> => UPDATE sal_emp SET schedule[:2] = '{"w", "x", "y", "z"}' WHERE name !=
> 'Pam';
> UPDATE 3
> => table sal_emp;
>   name  |  pay_by_quarter   |schedule
> +---+-
>  Pam| {2,25001,25002,25003} |
>  Bill   | {1,1,1,1} | {{w,x},{y,z}}
>  Carol  | {2,25000,25000,25000} | {{w,x},{y,z}}
>  Carolx | {2,25001,25002,25003} | {{w,x},{y,z},{meetingy,lunchy}}
> (4 rows)
>
> regards, tom lane
>