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 11:15:27 -0800
Message-ID: <CA+FnnTzR+Nh_DACuc5R_D+sb9165O7EdR8mnk=xXhu0Heu0BHA@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: multipart/alternative; boundary="000000000000b5a29d064c73fefb"
Archived-At: <https://www.postgresql.org/message-id/CA%2BFnnTzR%2BNh_DACuc5R_D%2Bsb9165O7EdR8mnk%3DxXhu0Heu0BHA%40mail.gmail.com>
Precedence: bulk

--000000000000b5a29d064c73fefb
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi, David,

On Fri, Mar 6, 2026 at 11:41=E2=80=AFPM 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 second argument.
>
> Given that error message, an array of text is the correct type.
>
> Array[]::text[]
>

So what is the proper syntax?

Thank you.


> David J.
>
>

--000000000000b5a29d064c73fefb
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"auto">Hi, David,</div><div><br><div class=3D"gmail_quote gmail_=
quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Fri, Mar 6, 2026 =
at 11:41=E2=80=AFPM David G. Johnston &lt;<a href=3D"mailto:david.g.johnsto=
n@gmail.com">david.g.johnston@gmail.com</a>&gt; wrote:<br></div><blockquote=
 class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc soli=
d;padding-left:1ex">On Saturday, March 7, 2026, Igor Korot &lt;<a href=3D"m=
ailto:ikorot01@gmail.com" target=3D"_blank">ikorot01@gmail.com</a>&gt; wrot=
e:<br><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-l=
eft:1px #ccc solid;padding-left:1ex"><br>
included, NULLIF( TRIM( c.reloptions ), &#39;&#39; ) AS storage FROM pg_ind=
ex<br>
idx, pg_class c, pg_namespace n, pg_class t, pg_indexes ixs WHERE<br>
ixs.indexname =3D c.relname AND c.oid =3D idx.indexrelid AND t.oid =3D<br>
idx.indrelid AND n.oid =3D c.relnamespace AND idx.indisprimary AND<br>
n.nspname =3D &#39;public&#39; AND t.relname =3D &#39;leagues&#39;;<br>
ERROR:=C2=A0 function pg_catalog.btrim(text[]) does not exist<br>
LINE 1: ...num) OFFSET idx.indnkeyatts) AS included, NULLIF( TRIM( c.re...<=
br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
=C2=A0</blockquote><div><br></div><div>You are asking the wrong question.=
=C2=A0 The right question is =E2=80=9Chow does one turn an empty array into=
 the null value?=E2=80=9D =C2=A0Nullif is correct, you just need to specify=
 an empty array (of the correct type) for the second argument.</div><div><b=
r></div><div>Given that error message, an array of text is the correct type=
.</div><div><br></div><div>Array[]::text[]</div></blockquote><div dir=3D"au=
to"><br></div><div dir=3D"auto">So what is the proper syntax?</div><div dir=
=3D"auto"><br></div><div dir=3D"auto">Thank you.</div><div dir=3D"auto"><br=
></div><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-=
left:1px #ccc solid;padding-left:1ex"><div dir=3D"auto"></div><div><br></di=
v><div>David J.</div><div><br></div>
</blockquote></div></div>

--000000000000b5a29d064c73fefb--