Re: Changing a varchar(7) domain into text directly in pg_type

On 5/27/25 7:27 AM, Richard Zetterberg wrote:
> 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.
Define 'read-only'. In other words can you temporarily make it not
read-only and change the type to text(or just varchar (no length specifier)?
This would be the easiest fix.

>
> 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 <http://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.

If the read-only table field has a maximum length of 7 and you have
incoming data that is coming in longer then 7 characters, how are they
going to reference the read-only table?

>
> 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

Per my comment above, how?

> Thanks for any insight,
> Richard Zetterberg

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com