Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uKHXm-000P4o-OP for pgsql-general@arkaria.postgresql.org; Wed, 28 May 2025 14:14:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uKHXl-000Cye-C6 for pgsql-general@arkaria.postgresql.org; Wed, 28 May 2025 14:14:09 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uKHXk-000CyW-Pb for pgsql-general@lists.postgresql.org; Wed, 28 May 2025 14:14:09 +0000 Received: from mail-oa1-x2e.google.com ([2001:4860:4864:20::2e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uKHXi-000O5h-1e for pgsql-general@lists.postgresql.org; Wed, 28 May 2025 14:14:07 +0000 Received: by mail-oa1-x2e.google.com with SMTP id 586e51a60fabf-2d060c62b61so3754120fac.0 for ; Wed, 28 May 2025 07:14:06 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1748441645; x=1749046445; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=NLQpbEv5Wx3CPIFsr1ZmbZrEdvv+FHoDd6b6ysmYgMw=; b=cq18iiWziUJs1hesvDAGhSNDC2AlTRpl7DAIfqJx8hfstViNsZqVXKu1xTgAj8cMsp Oh2WOh5E376VuMVU/PUWKjC1asy5rbNeQxxsECx6Dp2TUYm2ugw2dK/r9ZjSCXIi1xCX 3GPzxR87W9FqL2BRsgygmfocr4ZnIoqCU3vReufyMYlONbIr7LZr+fBHCM+P3NHlY3u/ 40Og8kaiEg6dp2CLpX65sRN1EItZhcHg2XU7TtIjfb6aUGU7uyJ27TP/TCVyd6+d/ybU sxC25uqzuZ05JNtOA/8dkelQnY5kjnoZ50zWnwNVL0gB7zzwKsdMjaIarUjRHt03yrNl IxbA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1748441645; x=1749046445; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=NLQpbEv5Wx3CPIFsr1ZmbZrEdvv+FHoDd6b6ysmYgMw=; b=A1Iziwzh4XpvRa/ktEkr9ejNDrzlj8p8YrPQ6JQQXsgKzLDUAM76lPXctekrzoxKGE cueO9r9XACH+XGFHb9ihDnVaO4iKCgCeh93QPBz9nMQ4ry5nke7YiwEOqpqBGakKmH0d VyRc0fCUd+ZymXCLYJdgCbrpPc+ryVwuErCOnYjwhnUojBZo0zv8xA5CDleObIckNhNt f2zqyCmta0JqSYoKVnf1Z89JMHujlLpgYB6uYtUuSDFGPx2XjnHPgbFmHpiHX3k/yjEM SEDx9NonQZ92v+JqAMcBS1H7X9qNBRz/IjQHrxgBFn4o3Is7wwAjSb44aeZ28svevTd3 pVsA== X-Gm-Message-State: AOJu0YxstT/nNzMzAG9kjYOMQoMyfcWcZuOsO6bQ00fifmFqASX59x3Q rs+OejT+isi+zNr93zLinpHs0EBRMORDSYQH8KOvo3FkpjMLp2+dzXb6Snz3uEMeNaUINWjqMjA xd3tR6q6+Ispfpk7NgeZxsB8Ijxl/XVrsjw== X-Gm-Gg: ASbGncv/wVvNIbTjPInOn0Wn9jQKIAkJ/JtFwJtDN7sVFcAEdeODpFN2p7nSim3BAZE 42gxilwnVJmEWeXqhPqiv8CjqF1p38cIUVelMQqYjx3vduFk3EnPMX3DdEFXvd0EcRbb7csMpz9 QX1GG5cjJoxQvBUO+DV4XQy8cLwo5c0itmjw== X-Google-Smtp-Source: AGHT+IEqYPh7hS/17pymXQUCWif4wjpvyzOdfBKkD1jUzUBwgfA4cxacIls0Nls0SLb6xNbg4enKrBfdvbITelMbzvk= X-Received: by 2002:a05:6871:71c5:b0:2d5:ba2d:80df with SMTP id 586e51a60fabf-2e861d62e22mr8389306fac.8.1748441645073; Wed, 28 May 2025 07:14:05 -0700 (PDT) MIME-Version: 1.0 References: <4c1c0192-e4f1-4e13-8dae-bfd9bb1801c1@aklaver.com> In-Reply-To: From: Ron Johnson Date: Wed, 28 May 2025 10:13:54 -0400 X-Gm-Features: AX0GCFs_PFqg8qtjQgv-8UsHhJgeEFU8r65mK2zK_hCcfPdlfXTPolOzw1uP3uo Message-ID: Subject: Re: Changing a varchar(7) domain into text directly in pg_type To: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000002a4b0a063632cbeb" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002a4b0a063632cbeb Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Foreign key "code tables" are ubiquitous in good database design; another example is zip/postal codes and cities. Statuses are another standard usage. Thus, what you're doing is absolutely bog-standard. (In your case, I would add "taxonomy_edition" to the species table. You get more information, and don't have to update the species table; just add more to it. New sightings can use the new taxonomy.) As to whether it would work... I absolutely _would not_ go mucking around in the catalog. Bite the bullet and ALTER the twenty-odd columns to TEXT. (Why TEXT and not a DOMAIN? Simplicity.) On Wed, May 28, 2025 at 8:33=E2=80=AFAM Richard Zetterberg < richard.zetterberg@googlemail.com> wrote: > Thanks for the reply, Adrian! > > I must have explained my problem poorly, sorry for that. > > Let me present an imaginary scenario and hopefully that clears things up. > > It's 2015 and you want to start a birdwatching club and want to keep trac= k > of sightings in a postgres database. > > To make sure that users in your birdwatching club all enter the specie of > the observed bird correctly, you decide to create a table that will conta= in > all distinct bird species. Since there are over >10 000 bird species you > decide to download an existing taxonomy of bird species. On > birdtaxonomies.com you download edition 5 of the bird species taxonomy as > a CSV-file. Each row of this file represents a bird specie and it has two > columns: unique ID and name. > > You observe that the IDs in the downloaded file have at most 6 characters= . > To make room for additional bird species in coming editions of the > taxonomy, you decide to use `varchar(7)` as the type of the ID column. To > improve readability, you create a type alias: > > ``` > CREATE DOMAIN species_id AS varchar(7); > ``` > > Then you create the table that contains the bird species taxonomy: > > ``` > CREATE TABLE species ( > id species_id PRIMARY KEY, > name text NOT NULL > ); > ``` > > Once all the bird species are imported from the taxonomy file into the > `species` table, nothing is changed in this file. > > To guarantee that people enter the specie correctly, you now add a foreig= n > key to all of your observation tables referencing the `species` table. > Here's an example of what one of those tables look like: > > ``` > CREATE TABLE sightings ( > id serial PRIMARY KEY, > specie_id species_id NOT NULL REFERENCES species (id), > coordinates geography(POINT, 4326) NOT NULL, > date timestamptz NOT NULL > ); > ``` > > Each year, birdtaxonomies.com release a new edition of the bird species > taxonomy, that you update your species table with. But this year, > birdtaxonomies.com comes decides they want a more fine grained way of > classifying bird species, which results in latest edition of bird species > taxonomy to have more than 1 million species and IDs that are as long as = 9 > characters. > > After these 10 years your club has more than 1 million members worldwide, > more than 20 different kinds of observations tables, procedures and views= , > billions of rows of different kinds of observations and a total size of > over 100 GB. > > In order to import the latest taxonomy, you need to change the type of th= e > domain `species_id` from `varchar(7)` to `text`. But postgres doesn't all= ow > you to change the type of a domain with a single command. Instead what yo= u > need to do is: > > 1. Rename current domain: `ALTER DOMAIN species_id RENAME TO > species_id_depricated;` > 2. Create a new domain: `CREATE DOMAIN species_id AS text;` > 3. Change all >20 tables, procedures and views to use the new domain > 4. Drop the old domain > > You had done a similar but smaller change a couple of years ago, and that > meant days of downtime. Also, you are a lazy person and it's tedious to > write the SQL code to change all tables, procedures and views. So, you > decide to manipulate `pg_type` directly, since you read somewhere online > that `varchar` and `text` are basically the same under the hood. > > End of story. > > Basically, I was hoping that my shortcut would save time and that this > small `pg_type` manipulation can be done without any side effects. I don'= t > have any knowledge of postgres internals, so I don't know if postgres wou= ld > accept this change and carry on like nothing happened. Or, if it will bre= ak > my indices or how queries are executed. > > In my head, this change would be instant and postgres would carry on like > nothing happened and that the domain always had the type `text`. Is this = a > fools errand? Or could it actually make sense in this situation? > > Thanks > Richard > > On Tue, May 27, 2025 at 8:10=E2=80=AFPM Adrian Klaver > wrote: > >> >> >> 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 descriptio= n >> > 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 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 tha= t >> > should solve my problems >> >> Per my comment above, how? >> >> >> > Thanks for any insight, >> > Richard Zetterberg >> >> -- >> Adrian Klaver >> adrian.klaver@aklaver.com >> > --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --0000000000002a4b0a063632cbeb Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Foreign key "code tables" are u= biquitous in good database design; another example is zip/postal codes and = cities.=C2=A0 Statuses are another standard usage.=C2=A0 Thus, what you'= ;re doing is absolutely bog-standard.=C2=A0 (In your case, I would add &quo= t;taxonomy_edition" to the species table.=C2=A0 You get more informati= on, and don't have to update the species table; just add more to it.=C2= =A0 New sightings can use the new taxonomy.)

As to whether it would work... I absolutely _would not_ go mucking a= round in the catalog.=C2=A0 Bite the bullet and ALTER the twenty-odd column= s to TEXT.=C2=A0=C2=A0

(Why TEXT and not a DOMAIN?= =C2=A0 Simplicity.)

On Wed, May 28, 2025 at 8:33=E2=80= =AFAM Richard Zetterberg <richard.zetterberg@googlemail.com> wrote:
Thanks for the rep= ly, Adrian!

I must have explained my problem poorly, sorry for that.=

Let me present an imaginary scenario and hopefully that clears thin= gs up.

It's 2015 and you want to start a birdwatching club and w= ant to keep track of sightings in a postgres database.

To make sure = that users in your birdwatching club all enter the specie of the observed b= ird correctly, you decide to create a table that will contain all distinct = bird species. Since there are over >10 000 bird species you decide to do= wnload an existing taxonomy of bird species. On birdtaxonomies.com you download edition 5 = of the bird species taxonomy as a CSV-file. Each row of this file represent= s a bird specie and it has two columns: unique ID and name.

You obse= rve that the IDs in the downloaded file have at most 6 characters. To make = room for additional bird species in coming editions of the taxonomy, you de= cide to use `varchar(7)` as the type of the ID column. To improve readabili= ty, you create a type alias:

```
CREATE DOMAIN species_id AS varc= har(7);
```

Then you create the table that contains the bird spec= ies taxonomy:

```
CREATE TABLE species (
=C2=A0 id =C2=A0 spec= ies_id PRIMARY KEY,
=C2=A0 name text =C2=A0 =C2=A0 =C2=A0 NOT NULL
);=
```

Once all the bird species are imported from the taxonomy fil= e into the `species` table, nothing is changed in this file.

To guar= antee that people enter the specie correctly, you now add a foreign key to = all of your observation tables referencing the `species` table. Here's = an example of what one of those tables look like:

```
CREATE TABL= E sightings (
=C2=A0 id =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0serial =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 PRIMARY KEY,
=C2=A0 spe= cie_id =C2=A0 species_id =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 NOT NULL= REFERENCES species (id),
=C2=A0 coordinates geography(POINT, 4326) NOT = NULL,
=C2=A0 date =C2=A0 =C2=A0 =C2=A0 =C2=A0timestamptz =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0NOT NULL
);
```

Each year, birdtaxonomies.com rel= ease a new edition of the bird species taxonomy, that you update your speci= es table with. But this year, birdtaxonomies.com comes decides they want a more fine grai= ned way of classifying bird species, which results in latest edition of bir= d species taxonomy to have more than 1 million species and IDs that are as = long as 9 characters.

After these 10 years your club has more than 1= million members worldwide, more than 20 different kinds of observations ta= bles, procedures and views, billions of rows of different kinds of observat= ions and a total size of over 100 GB.

In order to import the latest = taxonomy, you need to change the type of the domain `species_id` from `varc= har(7)` to `text`. But postgres doesn't allow you to change the type of= a domain with a single command. Instead what you need to do is:

1. = Rename current domain: `ALTER DOMAIN species_id RENAME TO species_id_depric= ated;`
2. Create a new domain: `CREATE DOMAIN species_id AS text;`
3.= Change all >20 tables, procedures and views to use the new domain
4.= Drop the old domain

You had done a similar but smaller change a cou= ple of years ago, and that meant days of downtime. Also, you are a lazy per= son and it's tedious to write the SQL code to change all tables, proced= ures and views. So, you decide to manipulate `pg_type` directly, since you = read somewhere online that `varchar` and `text` are basically the same unde= r the hood.

End of story.

Basically, I was hoping that my sho= rtcut would save time and that this small `pg_type` manipulation can be don= e without any side effects. I don't have any knowledge of postgres inte= rnals, so I don't know if postgres would accept this change and carry o= n like nothing happened. Or, if it will break my indices or how queries are= executed.

In my head, this change would be instant and postgres wou= ld carry on like nothing happened and that the domain always had the type `= text`. Is this a fools errand? Or could it actually make sense in this situ= ation?

Thanks
Richard

On Tue, May 27, 2025 at 8:10=E2=80=AFPM = Adrian Klaver <adrian.klaver@aklaver.com> wrote:


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 conta= in
> invalid/unknown categories and so that users can lookup the descriptio= n
> 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 avo= id having to
> type "varchar(7)" in all the tables that references the cate= gory 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 lon= ger
> 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 representatio= n 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 cou= ld
> just change the type of my domain to text, directly in pg_type and tha= t
> should solve my problems

Per my comment above, how?


> Thanks for any insight,
> Richard Zetterberg

--
Adrian Klaver
adrian.klave= r@aklaver.com


--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lobs= ter!
--0000000000002a4b0a063632cbeb--