ISBN range table
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
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
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
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 .
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
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/
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/
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
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
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
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
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