BUG #7903: EAN13s are shown ISBN values

[k***@roeckx.be]

The following bug has been logged on the website:

Bug reference: 7903
Logged by: Kurt Roeckx
Email address: ***@roeckx.be
PostgreSQL version: 9.2.3
Operating system: all
Description:

Hi,

I was looking at the isn extention to store EAN13 value. A lot of those
that I want to store are about books so they are also an ISBN13.

My problem now is that I it's always displayed with the dashes in between,
even when I want to show the EAN13. As far as I know EANs are never shown
with the dashes.

For EANs that aren't books it's also doing XXX-XXXXXXXXX-X, which I also
don't want. For UPC it seems to do what I want.

I think the output should depend on wheter I want to show an ISBN or EAN.


Kurt

[Peter Geoghegan]

Post by k***@roeckx.be
My problem now is that I it's always displayed with the dashes in between,
even when I want to show the EAN13. As far as I know EANs are never shown
with the dashes.

Right, they're not. But then, contrib/isn also sanitises both ISBN
ranges and EAN country codes using its own internal database, which
ought to be kept in lockstep with an external database whose
maintenance is highly decentralised - regional authorities make
certain decisions that can create a need to update the database. I'm
pretty sure that that database doesn't actually exist, at least all in
one place, in the case of ISBN. So contrib/isn is fundamentally
wrong-headed, and I would be quite happy to see its removal from
contrib.

In my experience of supply chain type applications, there is generally
a need to support fairly complex custom rules for sanitising EANs,
which generally makes a custom bigint domain a compelling choice. For
example, sometimes (typically in situations where products are sold by
weight), a price will be baked into the barcode that is affixed to the
product after it is weighed on a digital scales. The last 5 digits of
a barcode before the check digit is often a price (a number of cents,
usually) that a scales assigns based on a known price per kilogram,
plus the item's weight. This may present you with a need to store the
"normalised, base barcode" (basically, just the pseudo "custom"
country code and SKU) without any price, and without a check digit
(the check digit is of course a function of the dynamically assigned
price).

There are examples for Postgres on the internet that you can
generalise from for this sort of thing. These show how to enforce that
a check digit is correct using SQL, using this simple method:

https://en.wikipedia.org/wiki/EAN-13#Calculation_of_checksum_digit

We actually discussed changing the formatting of isn along the lines
you've discussed, and it was shot down. I'd just like to see it go.

[Peter Eisentraut]

Post by Peter Geoghegan
But then, contrib/isn also sanitises both ISBN
ranges and EAN country codes using its own internal database, which
ought to be kept in lockstep with an external database whose
maintenance is highly decentralised - regional authorities make
certain decisions that can create a need to update the database. I'm
pretty sure that that database doesn't actually exist, at least all in
one place, in the case of ISBN. So contrib/isn is fundamentally
wrong-headed, and I would be quite happy to see its removal from
contrib.

What's the alternative?

Have each user create their custom domain?

Is there a stable subset that we could maintain with minimal effort?

Would it be better if the module were removed from PostgreSQL core but
maintained externally where it can iterate faster and keep the database
up to date?

Is there a third-party library that does maintain such a database so
that this module could be based upon that instead of having to maintain
the database itself?

[Peter Geoghegan]

Post by Peter Eisentraut
Have each user create their custom domain?

That's likely to be the most effective solution, yes. I'd take the
fact that people haven't been complaining about contrib/isn more as
suggestive of people figuring this out for themselves than suggestive
of contrib/isn being of acceptable quality.

Post by Peter Eisentraut
Is there a stable subset that we could maintain with minimal effort?

Well, at the very least I'd rip out the over-zealous sanitisation
(everything but the check digit). I guess that enforcing the GS1
country codes within EAN-*s isn't completely crazy, if only because
new countries don't come along that often, and when they do that
doesn't tend to have anything to do with the dissolution of a GS1
member state.

Note that ISBN13 is just an EAN-13 from the fictional country of
bookland (that's a GS1 code). So for that reason, there arguably
doesn't need to be and shouldn't be a separate ISBN type. I guess
having a separate ISBN type was motivated solely by that enabling the
ill-advised additional sanitisation of ISBNs, though there is no
reason why you can't do something special with the bookland GS1 code
instead (nothing other than the fact that, as I've said, sanitising
what the module calls "ISBN_range" is generally quite a bad idea).

Post by Peter Eisentraut
Would it be better if the module were removed from PostgreSQL core but
maintained externally where it can iterate faster and keep the database
up to date?

I don't think so. To my mind, the whole idea of sanitising ISBN_range
stinks. GS1 country code sanitisation works out a lot better in
practice, but feels just as wrong to me. Check digit enforcement is
fine, even if that approach is a little bit limiting compared to a
custom domain.

Enforcing that a check digit must be correct gives you 99% of the
value that you're likely to get here, because it protects against
transposition errors, which are by far the most likely type of error
made by data entry clerks.

Post by Peter Eisentraut
Is there a third-party library that does maintain such a database so
that this module could be based upon that instead of having to maintain
the database itself?

No, there is not.

The textual representation of the types - the dashes - are fairly odd,
but it's too late to fix that.