Thread

1. Re: Re: BUG #18632: Whether you need to consider modifying the array's handling of delimiters？

   zengman <zengman@halodbtech.com> — 2024-09-25T09:16:51Z

   （Sorry, there was a garbled situation in the last email）
   Thank you. I know that the way you said can be handled normally.
   
   
   postgres=# SELECT ('{''a-3'',''a,3''}'::varchar[]);
       varchar    
   ---------------
    {'a-3','a,3'}
   (1 row)
   
   
   I wonder if we need to modify array_in so that ''a,3'' and ''a-3'' behave the same and have a uniform style.
   Would it be better?
   
   
   postgres=# SELECT ('{''a-3'',''a,3''}'::varchar[])[1];
    varchar 
   ---------
    'a-3'
   (1 row)
   
   
   postgres=# SELECT ('{''a-3'',''a,3''}'::varchar[])[2];
    varchar 
   ---------
    'a
   (1 row)
   
   
   
   
                       Erik Wienhold<ewie@ewie.name&gt;&nbsp;在 2024年9月25日 周三 16:32 写道：
   
   On 2024-09-25 09:57 +0200, PG Bug reporting form wrote:
   &gt; The following bug has been logged on the website:
   &gt; 
   &gt; Bug reference:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 18632
   &gt; Logged by:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Man Zeng
   &gt; Email address:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; zengman@halodbtech.com
   &gt; PostgreSQL version: 14.10
   &gt; Operating system:&nbsp;&nbsp; centos-8
   &gt; Description:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
   &gt; 
   &gt; Hi, I found a problem with array separator handling.
   &gt; The current handling of delimiters is not quite as expected (not very
   &gt; flexible).
   &gt; The test SQL and results are shown below.
   &gt; 
   &gt; [postgres@halo-centos-8-release ~]$ psql
   &gt; psql (14.10)
   &gt; Type "help" for help.
   &gt; 
   &gt; postgres=# CREATE OR REPLACE FUNCTION arrayfunc() 
   &gt; postgres-# RETURNS _varchar 
   &gt; postgres-# AS $$ 
   &gt; postgres$#&nbsp;&nbsp; SELECT '{''a,3'',''b'',''c''}'::_varchar; 
   &gt; postgres$# $$ LANGUAGE SQL;
   &gt; CREATE FUNCTION
   &gt; postgres=# -- array cstring
   &gt; postgres=# SELECT arrayfunc();
   &gt;&nbsp;&nbsp;&nbsp;&nbsp; arrayfunc&nbsp;&nbsp;&nbsp; 
   &gt; -----------------
   &gt;&nbsp; {'a,3','b','c'}
   &gt; (1 row)
   &gt; 
   &gt; postgres=# -- length is 4
   &gt; postgres=# SELECT array_length(arrayfunc(), 1); 
   &gt;&nbsp; array_length 
   &gt; --------------
   &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4
   &gt; (1 row)
   &gt; 
   &gt; postgres=# -- first element
   &gt; postgres=# SELECT (arrayfunc())[1];
   &gt;&nbsp; arrayfunc 
   &gt; -----------
   &gt;&nbsp; 'a
   &gt; (1 row)
   &gt; 
   &gt; postgres=# -- second element
   &gt; postgres=# SELECT (arrayfunc())[2];
   &gt;&nbsp; arrayfunc 
   &gt; -----------
   &gt;&nbsp; 3'
   &gt; (1 row)
   
   You need to double-quote elements that contain the separator:
   
   &nbsp;&nbsp;&nbsp; SELECT '{"''a,3''",''b'',''c''}'::varchar[];
   
   That's also documented in the first paragraph of
   https://www.postgresql.org/docs/current/arrays.html#ARRAYS-INPUT
   
   So, not a bug.
   
   &gt; postgres=# -- other
   &gt; postgres=# SELECT (arrayfunc())[3];
   &gt;&nbsp; arrayfunc 
   &gt; -----------
   &gt;&nbsp; 'b'
   &gt; (1 row)
   &gt; 
   &gt; postgres=# SELECT (arrayfunc())[4];
   &gt;&nbsp; arrayfunc 
   &gt; -----------
   &gt;&nbsp; 'c'
   &gt; (1 row)
   &gt; 
   &gt; postgres=# -- The following SQL tests are as expected
   &gt; postgres=# CREATE OR REPLACE FUNCTION arrayfunc2() 
   &gt; postgres-# RETURNS _varchar 
   &gt; postgres-# AS $$ 
   &gt; postgres$#&nbsp;&nbsp; SELECT '{''a-3'',''b'',''c''}'::_varchar; 
   &gt; postgres$# $$ LANGUAGE SQL;
   &gt; CREATE FUNCTION
   &gt; postgres=# -- array cstring
   &gt; postgres=# SELECT arrayfunc2();
   &gt;&nbsp;&nbsp;&nbsp; arrayfunc2&nbsp;&nbsp;&nbsp; 
   &gt; -----------------
   &gt;&nbsp; {'a-3','b','c'}
   &gt; (1 row)
   &gt; 
   &gt; postgres=# -- length is 3
   &gt; postgres=# SELECT array_length(arrayfunc2(), 1); 
   &gt;&nbsp; array_length 
   &gt; --------------
   &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3
   &gt; (1 row)
   &gt; 
   &gt; postgres=# -- first element
   &gt; postgres=# SELECT (arrayfunc2())[1];
   &gt;&nbsp; arrayfunc2 
   &gt; ------------
   &gt;&nbsp; 'a-3'
   &gt; (1 row)
   &gt; 
   &gt; So should we consider modifying "array_in" to enhance the handling of
   &gt; separators to be more consistent with people's expectations?
   &gt; 
   
   -- 
   Erik

2. Re: BUG #18632: Whether you need to consider modifying the array's handling of delimiters？

   Wolfgang Walther <walther@technowledgy.de> — 2024-09-25T10:13:44Z

   曾满:
   > I wonder if we need to modify array_in so that ''a,3'' and ''a-3'' 
   > behave the same and have a uniform style.
   
   You are still using single quotes, but two of them. You need to use 
   **double** quotes, not two single quotes.
   
   ''a,3'' is different from "a,3".
   
   With true double quotes:
   
   postgres=# SELECT unnest('{"a-3","a,3"}'::varchar[]);
     unnest
   --------
     a-3
     a,3
   (2 rows)
   
   Best,
   
   Wolfgang