public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: Richard Zetterberg <[email protected]>
To: [email protected]
Subject: Re: Changing a varchar(7) domain into text directly in pg_type
Date: Tue, 27 May 2025 11:09:40 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CA+j-KtY71coW3-fn5j87R4eKVoPfaA_98brZskWb3qWi=23Buw@mail.gmail.com>
References: <CA+j-KtY71coW3-fn5j87R4eKVoPfaA_98brZskWb3qWi=23Buw@mail.gmail.com>



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
[email protected]

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], [email protected]
  Subject: Re: Changing a varchar(7) domain into text directly in pg_type
  In-Reply-To: <[email protected]>

* 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