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 1uKG1a-00HSCE-Cc for pgsql-general@arkaria.postgresql.org; Wed, 28 May 2025 12:36:50 +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 1uKG1Z-00HHto-12 for pgsql-general@arkaria.postgresql.org; Wed, 28 May 2025 12:36:49 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uKDU7-00G0iP-7B for pgsql-general@lists.postgresql.org; Wed, 28 May 2025 09:54:07 +0000 Received: from mail-yw1-x1129.google.com ([2607:f8b0:4864:20::1129]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uKDU3-000Wre-24 for pgsql-general@lists.postgresql.org; Wed, 28 May 2025 09:54:06 +0000 Received: by mail-yw1-x1129.google.com with SMTP id 00721157ae682-70de8897628so28645947b3.1 for ; Wed, 28 May 2025 02:54:03 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=googlemail.com; s=20230601; t=1748426042; x=1749030842; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=Y9kMYvbWPTch84rW1ZtjYVspZC1C9or03ddwQtQ4FOM=; b=NJXMCbB5qhP8VcnPtAmt1wyR/lFtRbpoKp3UfISVN2BGFKdHsYJIH11HjYk2jjqhOy sf0sOJHtl1WvePD8IhP0MpKFBeBW613l816liim0kw+8QlTMqoSas1BJlnF43pNor4KA 6uKRKTkZGPGK1/HMzSGDLyMvNUCdaL27CRTxOV14vu5mmWAiDyPbMMpYq+vOiFH6oViS ToFJXkDY9L1xJr9VVm+kGAydCXHre4Qsp8cdb+Hmg1wv0Dk1fHD74PsiMkSthHMD2DP6 Xdb92sUZxRoSwXS9Pm073X/RChIpWE8btYBhfiqbk4E/sQQCPzdp0ooqcYH8ZH8RbqII ozwQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1748426042; x=1749030842; h=cc: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=Y9kMYvbWPTch84rW1ZtjYVspZC1C9or03ddwQtQ4FOM=; b=O5yUVi1L5yhviU8jv21rv4qIJ2pjZr+ks1Of4Y2ofrMwnGSaNRGrcAOQvIYD0uP0Mv 6m6b5IL1V5b61XxR3XncEFNm4/UcH2VQ4iSXK3xdVZ5PyGkgJpOpr5V7+PVlHYrm2+gI Cg6oCaZiFKu9hI3bHXnEcS1Na2fpIhDCZobFsb//ZzKmZDHCp7c7tkBC8cD6380GILSj 2aKUJccXZjDVLDWjmujGvnefeQ+qQ2ZoqlGlIqyexjS93rgnscslToj3gHnIIW0HfUUX X1TKT4rrEeC/eNzOOhJtaj2Np7gDlY//aycLDzNnS81l9UORRB2ZMIuStWi+3fpg8HQz iKqA== X-Gm-Message-State: AOJu0YyNJLRvBFnUB5Jp64qJ/+ziBnfR7lZ78o5ez2tPod7/PXy5cMZs z6alPxKydOO8btoFBcBC2k/BGODK6mvWFjcCS2XztCYOZqQ4NWEPVgivC1HODZuDmhdwx8wJMV9 F6vOdahV6R4T+XVgEXI+nATRCSk6OzpbOktur X-Gm-Gg: ASbGncvMs+qGGyArYZdNsikuPWCpQqOMZmLbwUOcXAzUkuyVkXGwYF4sE9KXU+Wqtx2 1d7ZPaTHR98x2PnnOYR35mcpvP9hMsqMDCvJDaoUKlRp8+7x6oLK0Mp8W1CfTZarSgJcSRZjIJC Pa738k2Dy+5pB79PoOZAH1sSfBr+ybEIZ1 X-Google-Smtp-Source: AGHT+IGHL4sJi7hdiVgbN2cB7R/Zy351rbcBK8DRAgHE0RVbem7Ox3JUtYNhbBVT/LknQJhEdYFbVQ19HuTRbhp2tYE= X-Received: by 2002:a05:690c:4b01:b0:70c:a5c2:ceed with SMTP id 00721157ae682-70e2daac0a5mr193896507b3.25.1748426042245; Wed, 28 May 2025 02:54:02 -0700 (PDT) MIME-Version: 1.0 References: <4c1c0192-e4f1-4e13-8dae-bfd9bb1801c1@aklaver.com> In-Reply-To: <4c1c0192-e4f1-4e13-8dae-bfd9bb1801c1@aklaver.com> From: Richard Zetterberg Date: Wed, 28 May 2025 11:53:51 +0200 X-Gm-Features: AX0GCFvFtUb5HHc5bpGCvZ_aV_h2E970dnyPLY_qUDI9Cs3fQPSopCqOeZ1FDoE Message-ID: Subject: Re: Changing a varchar(7) domain into text directly in pg_type To: Adrian Klaver Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000002a05a706362f29b2" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002a05a706362f29b2 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 track 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 contain 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 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 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 the domain `species_id` from `varchar(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_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 would accept this change and carry on 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 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 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 t= o > > 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 fo= r > > the extra constraint checks on varchar. So, I thought that maybe I coul= d > > 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 > --0000000000002a05a706362f29b2 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thanks for the reply, Adrian!

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

Let me present an imaginary scen= ario and hopefully that clears things up.

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

To make sure that users in your birdwatching club all= enter the specie of the observed bird correctly, you decide to create a ta= ble that will contain all distinct bird species. Since there are over >1= 0 000 bird species you decide to download an existing taxonomy of bird spec= ies. On birdtaxonomies.com you do= wnload edition 5 of the bird species taxonomy as a CSV-file. Each row of th= is 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 cha= racters. 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 s= pecies_id AS varchar(7);
```

Then you create the table that conta= ins the bird species taxonomy:

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

Once all the bird species are imported fro= m the taxonomy file into the `species` table, nothing is changed in this fi= le.

To guarantee 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 TABLE 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 PRIMAR= Y KEY,
=C2=A0 specie_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 geogr= aphy(POINT, 4326) NOT NULL,
=C2=A0 date =C2=A0 =C2=A0 =C2=A0 =C2=A0times= tamptz =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0NOT NULL
);
```
Each year, birdtaxonomies.com r= elease a new edition of the bird species taxonomy, that you update your spe= cies table with. But this year, birdt= axonomies.com comes decides they want a more fine grained way of classi= fying bird species, which results in latest edition of bird species taxonom= y to have more than 1 million species and IDs that are as long as 9 charact= ers.

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 nee= d to change the type of the domain `species_id` from `varchar(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 do= main: `ALTER DOMAIN species_id RENAME TO species_id_depricated;`
2. Crea= te a new domain: `CREATE DOMAIN species_id AS text;`
3. Change all >2= 0 tables, procedures and views to use the new domain
4. Drop the old dom= ain

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. S= o, you decide to manipulate `pg_type` directly, since you read somewhere on= line 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 sid= e effects. I don't have any knowledge of postgres internals, so I don&#= 39;t know if postgres would accept this change and carry on like nothing ha= ppened. Or, if it will break 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?

Tha= nks
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
--0000000000002a05a706362f29b2--