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.klaver@aklaver.com