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