public inbox for [email protected]  

help / color / mirror / Atom feed

From: Richard Zetterberg <[email protected]>
To: [email protected]
Subject: Changing a varchar(7) domain into text directly in pg_type
Date: Tue, 27 May 2025 16:27:33 +0200
Message-ID: <CA+j-KtY71coW3-fn5j87R4eKVoPfaA_98brZskWb3qWi=23Buw@mail.gmail.com> (raw)

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

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Changing a varchar(7) domain into text directly in pg_type
  In-Reply-To: <CA+j-KtY71coW3-fn5j87R4eKVoPfaA_98brZskWb3qWi=23Buw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox