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
