MIME-Version: 1.0
From: Richard Zetterberg <richard.zetterberg@googlemail.com>
Date: Tue, 27 May 2025 16:27:33 +0200
Message-ID: <CA+j-KtY71coW3-fn5j87R4eKVoPfaA_98brZskWb3qWi=23Buw@mail.gmail.com>
Subject: Changing a varchar(7) domain into text directly in pg_type
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000002c8e0e06361ede99"
Archived-At: <https://www.postgresql.org/message-id/CA%2Bj-KtY71coW3-fn5j87R4eKVoPfaA_98brZskWb3qWi%3D23Buw%40mail.gmail.com>
Precedence: bulk

--0000000000002c8e0e06361ede99
Content-Type: text/plain; charset="UTF-8"

Hello,

I have a read-only table that contains a set of never changing categories.
Each category has a unique alpha numerical ID and a description. The
purpose of this table is so that other tables can reference the ID of this
table, to make sure that they don't contain invalid/unknown categories and
so that users can lookup the description of each category.

This category table has the following type on the ID column: "varchar(7)"
(yes, I should have used text). In order to avoid having to type
"varchar(7)" in all the tables that references the category table, I
created this domain that I used as type for all referencing columns:
"CREATE DOMAIN cat.id AS varchar(7);".

During some data archeology, I found a bunch of new categories that haven't
been imported into the database yet, and they have IDs longer than 7.

I've seen claims that varchar and text have the same representation on disk
and that they are treated the same way "under the hood", except for the
extra constraint checks on varchar. So, I thought that maybe I could just
change the type of my domain to text, directly in pg_type and that should
solve my problems.

After some thinkering, and looking at how "CREATE DOMAIN cat.id AS
varchar(7);" and "CREATE DOMAIN cat.id AS text;" appeared in pg_type and
information_schema.domains, I came up with this query:

```
WITH text_oid AS (
  SELECT typ.oid AS text_oid
    FROM pg_type AS typ
         INNER JOIN pg_namespace AS ns
                 ON typ.typnamespace = ns.oid
                AND typ.typname      = 'text'
), target_oid AS (
  SELECT typ.oid AS target_oid
    FROM pg_type AS typ
         INNER JOIN pg_namespace AS ns
                 ON typ.typnamespace = ns.oid
                AND ns.nspname       = 'cat
                AND typ.typname      = 'id'
), oids AS (
  SELECT *
    FROM text_oid
         CROSS JOIN target_oid
) UPDATE pg_type AS styp
   SET typoutput   = 'textout'
     , typsend     = 'textsend'
     , typbasetype = o.text_oid
     , typtypmod   = -1
  FROM oids AS o
 WHERE oid = o.target_oid;
```

After running that query, my domain had the type text instead of varchar(7)
and all the
fkeys to my category table seems to be working.

Could this be a viable option to solve my problem? Or will I face serious
problems later down the line that I haven't discovered/considered yet?

Thanks for any insight,
Richard Zetterberg

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

<div dir=3D"ltr">Hello,<br><br>I have a read-only table that contains a set=
 of never changing categories. Each category has a unique alpha numerical I=
D and a description. The purpose of this table is so that other tables can =
reference the ID of this table, to make sure that they don&#39;t contain in=
valid/unknown categories and so that users can lookup the description of ea=
ch category.<br><br>This category table has the following type on the ID co=
lumn: &quot;varchar(7)&quot; (yes, I should have used text). In order to av=
oid having to type &quot;varchar(7)&quot; in all the tables that references=
 the category table, I created this domain that I used as type for all refe=
rencing columns: &quot;CREATE DOMAIN <a href=3D"http://cat.id">cat.id</a> A=
S varchar(7);&quot;.<br><br>During some data archeology, I found a bunch of=
 new categories that haven&#39;t been imported into the database yet, and t=
hey have IDs longer than 7.<br><br>I&#39;ve seen claims that varchar and te=
xt have the same representation on disk and that they are treated the same =
way &quot;under the hood&quot;, except for the extra constraint checks on v=
archar. So, I thought that maybe I could just change the type of my domain =
to text, directly in pg_type and that should solve my problems.<br><br>Afte=
r some thinkering, and looking at how &quot;CREATE DOMAIN <a href=3D"http:/=
/cat.id">cat.id</a> AS varchar(7);&quot; and &quot;CREATE DOMAIN <a href=3D=
"http://cat.id">cat.id</a> AS text;&quot; appeared in pg_type and informati=
on_schema.domains, I came up with this query:<br><br>```<br>WITH text_oid A=
S (<br>=C2=A0 SELECT typ.oid AS text_oid<br>=C2=A0 =C2=A0 FROM pg_type AS t=
yp<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0INNER JOIN pg_namespace AS ns<br>=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0ON typ.typnam=
espace =3D ns.oid<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 AND typ.typname =C2=A0 =C2=A0 =C2=A0=3D &#39;text&#39;<br>), target_oid=
 AS (<br>=C2=A0 SELECT typ.oid AS target_oid<br>=C2=A0 =C2=A0 FROM pg_type =
AS typ<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0INNER JOIN pg_namespace AS ns<b=
r>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0ON typ.typn=
amespace =3D ns.oid<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 AND ns.nspname =C2=A0 =C2=A0 =C2=A0 =3D &#39;cat<br>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 AND typ.typname =C2=A0 =C2=A0 =C2=A0=
=3D &#39;id&#39;<br>), oids AS (<br>=C2=A0 SELECT *<br>=C2=A0 =C2=A0 FROM t=
ext_oid<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0CROSS JOIN target_oid<br>) UPD=
ATE pg_type AS styp<br>=C2=A0 =C2=A0SET typoutput =C2=A0 =3D &#39;textout&#=
39;<br>=C2=A0 =C2=A0 =C2=A0, typsend =C2=A0 =C2=A0 =3D &#39;textsend&#39;<b=
r>=C2=A0 =C2=A0 =C2=A0, typbasetype =3D o.text_oid<br>=C2=A0 =C2=A0 =C2=A0,=
 typtypmod =C2=A0 =3D -1<br>=C2=A0 FROM oids AS o<br>=C2=A0WHERE oid =3D o.=
target_oid;<br>```<br><br>After running that query, my domain had the type =
text instead of varchar(7) and all the<br>fkeys to my category table seems =
to be working.<br><br>Could this be a viable option to solve my problem? Or=
 will I face serious problems later down the line that I haven&#39;t discov=
ered/considered yet? <br><br>Thanks for any insight,<br>Richard Zetterberg<=
br></div>

--0000000000002c8e0e06361ede99--