MIME-Version: 1.0
References: <CA+FnnTxGOqF1DN4e70KXRJakuPV_V7_vGonQTX4eEOEE92PCKw@mail.gmail.com>
 <CAKFQuwakY0c3x=2o=gQ6Cr7jQ=DgE_snL1cHf8Oj+irO1fmjEQ@mail.gmail.com>
In-Reply-To: <CAKFQuwakY0c3x=2o=gQ6Cr7jQ=DgE_snL1cHf8Oj+irO1fmjEQ@mail.gmail.com>
From: Igor Korot <ikorot01@gmail.com>
Date: Sat, 7 Mar 2026 02:11:18 -0600
Message-ID: <CA+FnnTzq6DOL6WWoSsK3M_uzQu0HOisbJ4W7J69Gjky1Xo1rGg@mail.gmail.com>
Subject: Re: How to properly use TRIM()?
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CA%2BFnnTzq6DOL6WWoSsK3M_uzQu0HOisbJ4W7J69Gjky1Xo1rGg%40mail.gmail.com>
Precedence: bulk

Hi, David,

On Sat, Mar 7, 2026 at 1:41=E2=80=AFAM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Saturday, March 7, 2026, Igor Korot <ikorot01@gmail.com> wrote:
>>
>>
>> included, NULLIF( TRIM( c.reloptions ), '' ) AS storage FROM pg_index
>> idx, pg_class c, pg_namespace n, pg_class t, pg_indexes ixs WHERE
>> ixs.indexname =3D c.relname AND c.oid =3D idx.indexrelid AND t.oid =3D
>> idx.indrelid AND n.oid =3D c.relnamespace AND idx.indisprimary AND
>> n.nspname =3D 'public' AND t.relname =3D 'leagues';
>> ERROR:  function pg_catalog.btrim(text[]) does not exist
>> LINE 1: ...num) OFFSET idx.indnkeyatts) AS included, NULLIF( TRIM( c.re.=
..
>>
>
>
> You are asking the wrong question.  The right question is =E2=80=9Chow do=
es one turn an empty array into the null value?=E2=80=9D  Nullif is correct=
, you just need to specify an empty array (of the correct type) for the sec=
ond argument.

I don't think pg_class.reloptions is an ARRAY...

draft=3D# SELECT c.relname AS name, ixs.tablespace, ARRAY(SELECT
a.attname FROM pg_attribute a WHERE a.attrelid =3D idx.indrelid AND
a.attnum =3D ANY(idx.indkey) AND a.attnum > 0 ORDER BY
array_position(idx.indkey, a.attnum) OFFSET idx.indnkeyatts) AS
included, c.reloptions AS storage FROM pg_index idx, pg_class c,
pg_namespace n, pg_class t, pg_indexes ixs WHERE ixs.indexname =3D
c.relname AND c.oid =3D idx.indexrelid AND t.oid =3D idx.indrelid AND
n.oid =3D c.relnamespace AND idx.indisprimary AND n.nspname =3D 'public'
AND t.relname =3D 'leagues';
     name     | tablespace | included | storage
--------------+------------+----------+---------
 leagues_pkey |            | {}       |
(1 row)

Included is one, storage is not.

Thank you.

>
> Given that error message, an array of text is the correct type.
>
> Array[]::text[]
>
> David J.
>