Feedback-ID: i76984098:Fastmail
Message-ID: <4c1c0192-e4f1-4e13-8dae-bfd9bb1801c1@aklaver.com>
Date: Tue, 27 May 2025 11:09:40 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Changing a varchar(7) domain into text directly in pg_type
To: Richard Zetterberg <richard.zetterberg@googlemail.com>,
 pgsql-general@lists.postgresql.org
References: <CA+j-KtY71coW3-fn5j87R4eKVoPfaA_98brZskWb3qWi=23Buw@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CA+j-KtY71coW3-fn5j87R4eKVoPfaA_98brZskWb3qWi=23Buw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/4c1c0192-e4f1-4e13-8dae-bfd9bb1801c1%40aklaver.com>
Precedence: bulk


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