ISBN range table

Started by Thomas Munro7 months ago12 messageshackers
Jump to latest
#1Thomas Munro
thomas.munro@gmail.com

Hi,

A recent-ish thread[1]/messages/by-id/e0a62134-83da-4ba4-8cdb-ceb0111c95ce@eisentraut.org made me wonder what that big table of data
does, and why we have a random update every decade or so, and I came
up with the attached.

It's pretty low stakes stuff: it controls where hyphens are inserted.
This happens when new ranges are carved out for publishers, so to pick
an example ISBN plucked from Wikipedia[2]https://en.wikipedia.org/wiki/List_of_ISBN_registration_groups, here's an ISBN that is
shown differently after the attached:

postgres=# select '9791186178140'::isbn;
isbn
-----------------
979-118617814-0
(1 row)

postgres=# select '9791186178140'::isbn;
isbn
-------------------
979-11-86178-14-0

[1]: /messages/by-id/e0a62134-83da-4ba4-8cdb-ceb0111c95ce@eisentraut.org
[2]: https://en.wikipedia.org/wiki/List_of_ISBN_registration_groups

Attachments:

0001-contrib-isb-Update-ISBN.h-automatically.patchtext/x-patch; charset=UTF-8; name=0001-contrib-isb-Update-ISBN.h-automatically.patchDownload+1613-183
#2Thomas Munro
thomas.munro@gmail.com
In reply to: Thomas Munro (#1)
Re: ISBN range table

On Sun, Apr 12, 2026 at 11:37 PM Thomas Munro <thomas.munro@gmail.com> wrote:

A recent-ish thread[1] made me wonder what that big table of data
does, and why we have a random update every decade or so, and I came
up with the attached.

Fixed a couple of typos and added as commitfest #6676. The idea is
that update_isbn.py would become another periodic update task, similar
to Unicode, contrib/unaccent and the IANA timezone stuff. I suppose
running it only for major releases would be enough for such
non-critical data. There are also some other much smaller tables in
this contrib module, but they don't seem to be moving (?) so I didn't
look at them.

I'm not volunteering today, but a passing thought I had while browsing
this complicated code full of scary pointer arithmetic is that it
might be an idea to try to describe the entire EAN3 number space in a
much simpler single binary-searchable array that maps uint64_t ranges
to TO_CHAR format strings, with entries that look something like:

{9780037000000, 9780638999999, "999-9-99-999999-9"}

Attachments:

v2-0001-contrib-isb-Update-ISBN.h-automatically.patchtext/x-patch; charset=UTF-8; name=v2-0001-contrib-isb-Update-ISBN.h-automatically.patchDownload+1613-183
#3Florents Tselai
florents.tselai@gmail.com
In reply to: Thomas Munro (#2)
Re: ISBN range table

On Tue, Apr 14, 2026 at 6:05 AM Thomas Munro <thomas.munro@gmail.com> wrote:

On Sun, Apr 12, 2026 at 11:37 PM Thomas Munro <thomas.munro@gmail.com>
wrote:

A recent-ish thread[1] made me wonder what that big table of data
does, and why we have a random update every decade or so, and I came
up with the attached.

Fixed a couple of typos and added as commitfest #6676. The idea is
that update_isbn.py would become another periodic update task, similar
to Unicode, contrib/unaccent and the IANA timezone stuff. I suppose
running it only for major releases would be enough for such
non-critical data. There are also some other much smaller tables in
this contrib module, but they don't seem to be moving (?) so I didn't
look at them.

I'm not volunteering today, but a passing thought I had while browsing
this complicated code full of scary pointer arithmetic is that it
might be an idea to try to describe the entire EAN3 number space in a
much simpler single binary-searchable array that maps uint64_t ranges
to TO_CHAR format strings, with entries that look something like:

{9780037000000, 9780638999999, "999-9-99-999999-9"}

Nitpick; I'd just use standard python and avoid import requests .

Attachments:

stdpy.diffapplication/octet-stream; name=stdpy.diffDownload+6-6
#4Jonah Shafran
jonahshafran@gmail.com
In reply to: Florents Tselai (#3)
Re: ISBN range table

Hi Thomas,
I came across this thread when I was looking at updating the ISBN table.
Are you still planning to move this forward? If not would you be open to me
pushing it forward?
Thanks,
Jonah

On Sun, Nov 8, 2026 at 1:37 PM Florents Tselai <florents.tselai@gmail.com>
wrote:

Show quoted text

On Tue, Apr 14, 2026 at 6:05 AM Thomas Munro <thomas.munro@gmail.com>
wrote:

On Sun, Apr 12, 2026 at 11:37 PM Thomas Munro <thomas.munro@gmail.com>
wrote:

A recent-ish thread[1] made me wonder what that big table of data
does, and why we have a random update every decade or so, and I came
up with the attached.

Fixed a couple of typos and added as commitfest #6676. The idea is
that update_isbn.py would become another periodic update task, similar
to Unicode, contrib/unaccent and the IANA timezone stuff. I suppose
running it only for major releases would be enough for such
non-critical data. There are also some other much smaller tables in
this contrib module, but they don't seem to be moving (?) so I didn't
look at them.

I'm not volunteering today, but a passing thought I had while browsing
this complicated code full of scary pointer arithmetic is that it
might be an idea to try to describe the entire EAN3 number space in a
much simpler single binary-searchable array that maps uint64_t ranges
to TO_CHAR format strings, with entries that look something like:

{9780037000000, 9780638999999, "999-9-99-999999-9"}

Nitpick; I'd just use standard python and avoid import requests .

#5Daniel Gustafsson
daniel@yesql.se
In reply to: Jonah Shafran (#4)
Re: ISBN range table

On 8 Nov 2026, at 20:44, Jonah Shafran <jonahshafran@gmail.com> wrote:

I came across this thread when I was looking at updating the ISBN table. Are you still planning to move this forward? If not would you be open to me pushing it forward?

FWIW, I am also very much interested in this getting applied and regularly (per
release) maintained.

--
Daniel Gustafsson

#6Alvaro Herrera
alvherre@kurilemu.de
In reply to: Daniel Gustafsson (#5)
Re: ISBN range table

On 2026-Nov-12, Daniel Gustafsson wrote:

On 8 Nov 2026, at 20:44, Jonah Shafran <jonahshafran@gmail.com> wrote:

I came across this thread when I was looking at updating the ISBN table. Are you still planning to move this forward? If not would you be open to me pushing it forward?

FWIW, I am also very much interested in this getting applied and regularly (per
release) maintained.

One thing I wonder is why are these tables embedded in the C source
code, where users would have a really hard time updating them, instead
of being, like the timezone table, a separate text file or something
that the sysadmin can update more easily.

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/

#7Jonah Shafran
jonahshafran@gmail.com
In reply to: Alvaro Herrera (#6)
Re: ISBN range table

Is ISBN data something that sysadmins would want to be updating out of
band? While it is certainly useful to have ISBNs formatting as expected,
there isn't a real functional impact in the same way as time zone updates.
Additionally, while I didn't look through all of them, I didn't spot other
extensions in contrib shipping data files, which makes me suspect which
makes me suspect that there are more infrastructural changes required to be
able to drive this from a text file.
Given that, my feeling is we should update the existing structure now, so
it is no longer sixteen years out of date, and then look into making the
feature more robust.

I've re-run the script and updated the patch with those changes as well as
those suggested by Florents.

On Thu, Nov 12, 2026 at 7:57 AM Álvaro Herrera <alvherre@kurilemu.de> wrote:

Show quoted text

On 2026-Nov-12, Daniel Gustafsson wrote:

On 8 Nov 2026, at 20:44, Jonah Shafran <jonahshafran@gmail.com> wrote:

I came across this thread when I was looking at updating the ISBN

table. Are you still planning to move this forward? If not would you be
open to me pushing it forward?

FWIW, I am also very much interested in this getting applied and

regularly (per

release) maintained.

One thing I wonder is why are these tables embedded in the C source
code, where users would have a really hard time updating them, instead
of being, like the timezone table, a separate text file or something
that the sysadmin can update more easily.

--
Álvaro Herrera Breisgau, Deutschland —
https://www.EnterpriseDB.com/

Attachments:

t139300_7
v2-0001-Update-ISBN.h-automatically.patchapplication/octet-stream; name=v2-0001-Update-ISBN.h-automatically.patchDownload+1678-188
#8Daniel Gustafsson
daniel@yesql.se
In reply to: Jonah Shafran (#7)
Re: ISBN range table

On 13 Nov 2026, at 00:35, Jonah Shafran <jonahshafran@gmail.com> wrote:

Given that, my feeling is we should update the existing structure now, so it is
no longer sixteen years out of date, and then look into making the feature more
robust.

I agree with that.

--
Daniel Gustafsson

#9Jim Jones
jim.jones@uni-muenster.de
In reply to: Daniel Gustafsson (#8)
Re: ISBN range table

Hi Jonah

I was playing a bit with this patch and found something odd -- most
likely unrelated to this patch:

psql (20devel)
Type "help" for help.

postgres=# create extension isn;
CREATE EXTENSION
postgres=# with j (v1,v2) as (
values ('12345679'::issn, '9771234567898'::issn)
)
select v1,v2,v1=v2 from j;
v1 | v2 | ?column?
-----------+-----------+----------
1234-5679 | 1234-5679 | f
(1 row)

Although casting v1 and v2 display the exact same value, they compare
unequal. I understand that the input values are "different" and
therefore a comparison should return false, but displaying identical
results is a bit confusing. Unless the truncation is correct, but then
the eq operator is wrong :)

Best, Jim

#10Jonah Shafran
jonahshafran@gmail.com
In reply to: Jim Jones (#9)
Re: ISBN range table

Jim,
I agree with your assessment that this is unrelated to the patch- I was
able to reproduce this back to Postgres 17 (the oldest version I had
running on hand). As far as what is going on, it looks like ISSNs are
specified to use the short output format (isn_out, see isn--1.1.sql line
154), however, ISSNs don't seem to get normalized to one canonical form
when being read in by issn_in. I wasn't able to determine how ISBNs are
being handled differently with the time I had today, but it looks like
something in string2ean must be processing the ISBN and ISSN strings
differently.
Thanks,
Jonah

On Mon, Nov 16, 2026 at 2:06 PM Jim Jones <jim.jones@uni-muenster.de> wrote:

Show quoted text

Hi Jonah

I was playing a bit with this patch and found something odd -- most
likely unrelated to this patch:

psql (20devel)
Type "help" for help.

postgres=# create extension isn;
CREATE EXTENSION
postgres=# with j (v1,v2) as (
values ('12345679'::issn, '9771234567898'::issn)
)
select v1,v2,v1=v2 from j;
v1 | v2 | ?column?
-----------+-----------+----------
1234-5679 | 1234-5679 | f
(1 row)

Although casting v1 and v2 display the exact same value, they compare
unequal. I understand that the input values are "different" and
therefore a comparison should return false, but displaying identical
results is a bit confusing. Unless the truncation is correct, but then
the eq operator is wrong :)

Best, Jim

#11Jim Jones
jim.jones@uni-muenster.de
In reply to: Jonah Shafran (#10)
Re: ISBN range table

On 17/11/2026 02:39, Jonah Shafran wrote:

I agree with your assessment that this is unrelated to the patch- I was
able to reproduce this back to Postgres 17 (the oldest version I had
running on hand).

I guess it's been like this forever -- I could reproduce it in PG11:

psql (11.22 (Debian 11.22-1.pgdg110+1))
Geben Sie »help« für Hilfe ein.

postgres=# create extension isn;
CREATE EXTENSION
postgres=# with j (v1,v2) as (
values ('12345679'::issn, '9771234567898'::issn)
)
select v1,v2,v1=v2,v1::text=v2::text from j;
v1 | v2 | ?column? | ?column?
-----------+-----------+----------+----------
1234-5679 | 1234-5679 | f | t
(1 Zeile)

As far as what is going on, it looks like ISSNs are
specified to use the short output format (isn_out, see isn--1.1.sql line
154), however, ISSNs don't seem to get normalized to one canonical form
when being read in by issn_in. I wasn't able to determine how ISBNs are
being handled differently with the time I had today, but it looks like
something in string2ean must be processing the ISBN and ISSN strings
differently.

What about making ean2ISSN return early when the issue pub code is not
00? e.g.

diff --git a/contrib/isn/isn.c b/contrib/isn/isn.c
index a1d2f26158a..141d05d910e 100644
--- a/contrib/isn/isn.c
+++ b/contrib/isn/isn.c
@@ -472,6 +472,11 @@ static inline void
 ean2ISSN(char *isn)
 {
        unsigned        check;
+       char            digits[MAXEAN13LEN + 1];
+
+       dehyphenate(digits, isn);
+       if (strncmp(digits + 10, "00", 2) != 0)
+               return;

/* the number should come in this format: 977-0000-000-00-0 */
/* Strip the first part, crop, and calculate the new check digit */

psql (20devel)
Type "help" for help.

postgres=# with j (v1,v2) as (
values ('12345679'::issn, '9771234567898'::issn)
)
select v1,v2,v1=v2,v1::text=v2::text from j;
v1 | v2 | ?column? | ?column?
-----------+-------------------+----------+----------
1234-5679 | 977-1234-567-89-8 | f | f
(1 row)

Best, Jim

#12Jonah Shafran
jonahshafran@gmail.com
In reply to: Jim Jones (#11)
Re: ISBN range table

That approach makes sense to me, the one nitpick would be adding a comment
to the final patch:

diff --git a/contrib/isn/isn.c b/contrib/isn/isn.c
index a1d2f26158a..0313a8aa838 100644
--- a/contrib/isn/isn.c
+++ b/contrib/isn/isn.c
@@ -472,6 +472,15 @@ static inline void
 ean2ISSN(char *isn)
 {
        unsigned        check;
+       char            digits[MAXEAN13LEN + 1];
+
+       /*
+        * If the issue publication code is non-zero,
+        * we shouldn't perform any truncation
+       */
+       dehyphenate(digits, isn);
+       if(strncmp(digits+10, "00", 2) != 0)
+               return;

/* the number should come in this format: 977-0000-000-00-0 */
/* Strip the first part, crop, and calculate the new check digit */

I imagine this fix would probably make sense as a separate bug fix patch.
Thanks,
Jonah

On Tue, Nov 17, 2026 at 2:09 AM Jim Jones <jim.jones@uni-muenster.de> wrote:

Show quoted text

On 17/11/2026 02:39, Jonah Shafran wrote:

I agree with your assessment that this is unrelated to the patch- I was
able to reproduce this back to Postgres 17 (the oldest version I had
running on hand).

I guess it's been like this forever -- I could reproduce it in PG11:

psql (11.22 (Debian 11.22-1.pgdg110+1))
Geben Sie »help« für Hilfe ein.

postgres=# create extension isn;
CREATE EXTENSION
postgres=# with j (v1,v2) as (
values ('12345679'::issn, '9771234567898'::issn)
)
select v1,v2,v1=v2,v1::text=v2::text from j;
v1 | v2 | ?column? | ?column?
-----------+-----------+----------+----------
1234-5679 | 1234-5679 | f | t
(1 Zeile)

As far as what is going on, it looks like ISSNs are
specified to use the short output format (isn_out, see isn--1.1.sql line
154), however, ISSNs don't seem to get normalized to one canonical form
when being read in by issn_in. I wasn't able to determine how ISBNs are
being handled differently with the time I had today, but it looks like
something in string2ean must be processing the ISBN and ISSN strings
differently.

What about making ean2ISSN return early when the issue pub code is not
00? e.g.

diff --git a/contrib/isn/isn.c b/contrib/isn/isn.c
index a1d2f26158a..141d05d910e 100644
--- a/contrib/isn/isn.c
+++ b/contrib/isn/isn.c
@@ -472,6 +472,11 @@ static inline void
ean2ISSN(char *isn)
{
unsigned        check;
+       char            digits[MAXEAN13LEN + 1];
+
+       dehyphenate(digits, isn);
+       if (strncmp(digits + 10, "00", 2) != 0)
+               return;

/* the number should come in this format: 977-0000-000-00-0 */
/* Strip the first part, crop, and calculate the new check digit */

psql (20devel)
Type "help" for help.

postgres=# with j (v1,v2) as (
values ('12345679'::issn, '9771234567898'::issn)
)
select v1,v2,v1=v2,v1::text=v2::text from j;
v1 | v2 | ?column? | ?column?
-----------+-------------------+----------+----------
1234-5679 | 977-1234-567-89-8 | f | f
(1 row)

Best, Jim