isn

mdx: format: md

The isn module provides data types for the following international product numbering standards: EAN13, UPC, ISBN (books), ISMN (music), and ISSN (serials). Input is validated against a hard-coded list of prefixes at the time of entry, and this prefix list is also used to concatenate numbers on output. Since new prefixes appear from time to time, the prefix list may become outdated. A future version of this module is expected to obtain the prefix list from one or more tables, so that users can easily update the prefix list as needed. However, currently the list can only be updated by modifying the source code and recompiling. Alternatively, prefix validation and concatenation support may be removed entirely in a future version of this module. This module is considered "trusted", meaning it can be installed by non-superusers who have CREATE privilege on the current database.

1. Data Types

Table C.11 shows the data types provided by the isn module.

Table C.11. isn Data Types

Data Type	Description
EAN13	European Article Number, always displayed in EAN13 format
ISBN13	International Standard Book Number, displayed in the new EAN13 format
ISMN13	International Standard Music Number, displayed in the new EAN13 format
ISSN13	International Standard Serial Number, displayed in the new EAN13 format
ISBN	International Standard Book Number, displayed in the old short format
ISMN	International Standard Music Number, displayed in the old short format
ISSN	International Standard Serial Number, displayed in the old short format
UPC	Universal Product Code

Some notes:

1. ISBN13, ISMN13, and ISSN13 numbers are all EAN13 numbers.

2. EAN13 numbers are not always ISBN13, ISMN13, or ISSN13 (some are).

3. Some ISBN13 numbers can be displayed as ISBN.

4. Some ISMN13 numbers can be displayed as ISMN.

5. Some ISSN13 numbers can be displayed as ISSN.

6. UPC numbers are a subset of EAN13 numbers (they are essentially EAN13 numbers with the leading 0 removed).

7. All UPC, ISBN, ISMN, and ISSN numbers can be represented as EAN13 numbers.

Internally, all of these types use the same representation (a 64-bit integer), and all internal representations are interchangeable. Multiple types are provided to control display formatting and to perform stricter validity checking on input that is assumed to represent a particular type of number.

Whenever possible, the ISBN, ISMN, and ISSN types will display the short version of the number (ISxN 10), and display the ISxN 13 format when the short version cannot accommodate it. The EAN13, ISBN13, ISMN13, and ISSN13 types always display the long version of the ISxN (EAN13).

2. Casts

The isn module provides casts between the following types:

• ISBN13 <=> EAN13

• ISMN13 <=> EAN13

• ISSN13 <=> EAN13

• ISBN <=> EAN13

• ISMN <=> EAN13

• ISSN <=> EAN13

• UPC <=> EAN13

• ISBN <=> ISBN13

• ISMN <=> ISMN13

• ISSN <=> ISSN13

When casting from EAN13 to another type, there is a runtime check whether the value falls within the domain of the other type, and an error is thrown if it does not. Other casts simply relabel the value, so they always succeed.

3. Functions and Operators

The isn module provides standard comparison operators, plus B-tree and hash index support for all of these data types. In addition, there are some special functions, shown in Table C.12. In this table, isn means any one of the module's data types.

Table C.12. isn Functions

Function/Description
isn_weak ( boolean ) → boolean Sets weak input mode (returns the new setting)
isn_weak () → boolean Returns the current state of weak mode
make_valid ( isn ) → isn Validates an invalid number (clears the invalid flag)
is_valid ( isn ) → boolean Checks for the presence of the invalid flag

Weak mode is used to allow inserting invalid data into a table. Invalid means the check digit is wrong, not that the number is missing.

Why might you want to use weak mode? You might have a large collection of ISBN numbers and for some strange reason they have incorrect check digits. Regardless, the point is that you may want to clean up the mess, but you still want to be able to put those numbers in your database and possibly use an external tool to locate invalid numbers in the database, so that you can verify the information more easily. Therefore, you might want to select all invalid numbers from the table.

When you insert invalid numbers into a table using weak mode, the inserted number will have the corrected check digit, but it will have an exclamation mark (!) at the end, for example 0-11-000322-5!. This invalid flag can be checked with the is_valid function and cleared with the make_valid function. Even when not in weak mode, you can force the insertion of invalid numbers by appending the ! character to the number. Another special feature is that during input, you can write a ? in place of the check digit, and the correct check digit will be automatically inserted.

4. Examples

-- Using the types directly:

test=## SELECT isbn('978-0-393-04002-9');

isbn

---------------

0-393-04002-X

(1 row)


test=## SELECT isbn13('0901690546');

isbn13

-------------------

978-0-901690-54-8

(1 row)


test=## SELECT issn('1436-4522');

issn

-----------

1436-4522

(1 row)

-- Type casting:

-- Note that you can only cast from EAN13 to another type if the number falls within the valid values of the other type

-- Therefore the following will not work:

test=## select isbn(ean13('0220356483481'));

ERROR: cannot cast EAN13(UPC) to ISBN for number: "0220356483481"

-- But the following works:

test=## SELECT upc(ean13('0220356483481'));

upc

--------------

220356483481

(1 row)


test=## SELECT ean13(upc('220356483481'));

ean13

-----------------

022-035648348-1

(1 row)

-- Create a table with a single column to hold ISBN numbers:

CREATE TABLE test (id isbn);

INSERT INTO test VALUES('9780393040029');

-- Auto-calculate the check digit (note the '?'):

INSERT INTO test VALUES('220500896?');
INSERT INTO test VALUES('978055215372?');

test=## SELECT issn('3251231?');

issn

-----------

3251-2317

(1 row)


test=## SELECT ismn('979047213542?');

ismn

---------------

M-47213-542-3

(1 row)

-- Using weak mode:

SELECT isn_weak(true);
INSERT INTO test VALUES('978-0-11-000533-4');
INSERT INTO test VALUES('9780141219307');
INSERT INTO test VALUES('2-205-00876-X');

test=## SELECT isn_weak(false);

isn_weak

----------

f

(1 row)


test=## SELECT id FROM test WHERE NOT is_valid(id);

id

----------------

0-11-000533-3!

0-14-121930-0!

2-205-00876-5!

(3 rows)


test=## UPDATE test SET id = make_valid(id) WHERE id = '2-205-00876-X!';

UPDATE 1

test=## SELECT * FROM test;

id

----------------

0-393-04002-X

2-205-00896-X

0-552-15372-9

0-11-000533-3!

0-14-121930-0!

2-205-00876-5

(6 rows)


test=## SELECT isbn13(id) FROM test;

isbn13

--------------------

978-0-393-04002-9

978-2-205-00896-8

978-0-552-15372-0

978-0-11-000533-1!

978-0-14-121930-1!

978-2-205-00876-0

(6 rows)