Re: Changing a varchar(7) domain into text directly in pg_type - Mailing list pgsql-general

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