MIME-Version: 1.0
References: 
 <DM4PR12MB603953767048EE1B8A39283ADDB1A@DM4PR12MB6039.namprd12.prod.outlook.com>
 <CAKAnmmL1UZcP_SUotSRpDD2a6+k4qCRWvFGawDb6-JkXY0Rmbw@mail.gmail.com>
 <DM4PR12MB603913E485D34B4ED790FDAFDDBAA@DM4PR12MB6039.namprd12.prod.outlook.com>
In-Reply-To: 
 <DM4PR12MB603913E485D34B4ED790FDAFDDBAA@DM4PR12MB6039.namprd12.prod.outlook.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Thu, 1 Jan 2026 09:26:12 -0500
Message-ID: 
 <CAKAnmmJ5A-5wvdqxyZCAigp5_Tpo=w=3oR0r8aP4LH2JqRHYsQ@mail.gmail.com>
Subject: Re: is there a way to automate deduplication of strings?
To: Chris Papademetrious <Christopher.Papademetrious@synopsys.com>
Cc: "pgsql-novice@lists.postgresql.org" <pgsql-novice@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000000be1ae064754628b"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKAnmmJ5A-5wvdqxyZCAigp5_Tpo%3Dw%3D3oR0r8aP4LH2JqRHYsQ%40mail.gmail.com>
Precedence: bulk

--0000000000000be1ae064754628b
Content-Type: text/plain; charset="UTF-8"

Ah, okay, so what you want is to normalize the data. Turns out, that's
something database purists strive for, so you are in good company. Yes,
there is a little more overhead, but that's kind of the cost of using a
relational model. You can smooth things out a great bit with views,
triggers, and functions. For example, you could create a view that joins
the two tables together under the hood but an application or user would
simply select from it as if it were the original non-duplicated form:

create view txn as select t.id /* plus other fields from "t" here */ ,
u.user_agent from transaction t join user_agent u on (u.id =
t.user_agent_id);

You can also make the view ("txn" in this case) updateable by adding
INSTEAD OF triggers on the view, which then call some functions that do the
necessary maintenance work behind the scenes. Then your application simply
calls insert/update/delete on the view as if it were the original table.

https://www.postgresql.org/docs/current/sql-createtrigger.html

We can help with those on this list, of course, but there are some good
examples on that page to get you started.

Minor notes:

* Since the user_agent contains a small and finite number of variants, I
would not bother with a uuid and just use an int:

create table user_agent (id int primary key generated always as identity,
user_agent text not null unique);

* You ought to look at the new uuidv7() function, as it improves a lot on
regular uuids. See for example:

https://www.thenile.dev/blog/uuidv7

* Rather than ON DELETE CASCADE, you probably want ON DELETE RESTRICT for
this use case

* You mentioned "cleanup of no-longer-referenced values over time"

I would not sweat this, at least for this particular example. If those user
agents were used once, they may get used again. For different types of data
and larger tables, you could write a small sql function that cleans up
stray entries and make sure to call that function after a bulk delete, or
just cron it to run weekly.

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

--0000000000000be1ae064754628b
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Ah, okay, so what you want is to normalize the data. Turns=
 out, that&#39;s something database purists strive for, so you are in good =
company. Yes, there is a little more overhead, but that&#39;s kind of the c=
ost of using a relational model. You can smooth things out a great bit with=
 views, triggers, and functions. For example, you could create a view that =
joins the two tables together under the hood but an application or user wou=
ld simply select from it as if it were the original non-duplicated form:<br=
><br>create view txn as select <a href=3D"http://t.id">t.id</a> /* plus oth=
er fields from &quot;t&quot; here */ , u.user_agent from transaction t join=
 user_agent u on (<a href=3D"http://u.id">u.id</a> =3D t.user_agent_id);<br=
><br>You can also make the view (&quot;txn&quot; in this case) updateable b=
y adding INSTEAD OF triggers on the view, which then call some functions th=
at do the necessary maintenance work behind the scenes. Then your applicati=
on simply calls insert/update/delete on the view as if it were the original=
 table.<br><br><a href=3D"https://www.postgresql.org/docs/current/sql-creat=
etrigger.html">https://www.postgresql.org/docs/current/sql-createtrigger.ht=
ml</a><br><br>We can help with those on this list, of course, but there are=
 some good examples on that page to get you started.<br><br>Minor notes:<br=
><br>* Since the user_agent contains a small and finite number of variants,=
 I would not bother with a uuid and just use an int:<br><br>create table us=
er_agent (id int primary key generated always as identity, user_agent text =
not null unique);<br><br>* You ought to look at the new uuidv7() function, =
as it improves a lot on regular uuids. See for example:<br><br><a href=3D"h=
ttps://www.thenile.dev/blog/uuidv7">https://www.thenile.dev/blog/uuidv7</a>=
<br><br>* Rather than ON DELETE CASCADE, you probably want ON DELETE RESTRI=
CT for this use case<br><br>* You mentioned &quot;cleanup of no-longer-refe=
renced values over time&quot;<br><br>I would not sweat this, at least for t=
his particular example. If those user agents were used once, they may get u=
sed again. For different types of data and larger tables, you could write a=
 small sql function that cleans up stray entries and make sure to call that=
 function after a bulk delete, or just cron it to run weekly.<br><div dir=
=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr"><div><br></div><div>Che=
ers,</div><div>Greg</div><div><br></div><div>--</div><div>Crunchy Data - <a=
 href=3D"https://www.crunchydata.com" target=3D"_blank">https://www.crunchy=
data.com</a></div><div>Enterprise Postgres Software Products &amp; Tech Sup=
port</div><div><br></div></div></div></div>

--0000000000000be1ae064754628b--