codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>F.21. isn — data types for international standard numbers (ISBN, EAN, UPC, etc.)</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="intarray.html" title="F.20. intarray — manipulate arrays of integers" /><link rel="next" href="lo.html" title="F.22. lo — manage large objects" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.21. isn — data types for international standard numbers (ISBN, EAN, UPC, etc.)</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="intarray.html" title="F.20. intarray — manipulate arrays of integers">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules and Extensions</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="lo.html" title="F.22. lo — manage large objects">Next</a></td></tr></table><hr /></div><div class="sect1" id="ISN"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.21. isn — data types for international standard numbers (ISBN, EAN, UPC, etc.) <a href="#ISN" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="isn.html#ISN-DATA-TYPES">F.21.1. Data Types</a></span></dt><dt><span class="sect2"><a href="isn.html#ISN-CASTS">F.21.2. Casts</a></span></dt><dt><span class="sect2"><a href="isn.html#ISN-FUNCS-OPS">F.21.3. Functions and Operators</a></span></dt><dt><span class="sect2"><a href="isn.html#ISN-EXAMPLES">F.21.4. Examples</a></span></dt><dt><span class="sect2"><a href="isn.html#ISN-BIBLIOGRAPHY">F.21.5. Bibliography</a></span></dt><dt><span class="sect2"><a href="isn.html#ISN-AUTHOR">F.21.6. Author</a></span></dt></dl></div><a id="id-1.11.7.31.2" class="indexterm"></a><p>3 The <code class="filename">isn</code> module provides data types for the following4 international product numbering standards: EAN13, UPC, ISBN (books), ISMN5 (music), and ISSN (serials). Numbers are validated on input according to a6 hard-coded list of prefixes; this list of prefixes is also used to hyphenate7 numbers on output. Since new prefixes are assigned from time to time, the8 list of prefixes may be out of date. It is hoped that a future version of9 this module will obtain the prefix list from one or more tables that10 can be easily updated by users as needed; however, at present, the11 list can only be updated by modifying the source code and recompiling.12 Alternatively, prefix validation and hyphenation support may be13 dropped from a future version of this module.14 </p><p>15 This module is considered <span class="quote">“<span class="quote">trusted</span>”</span>, that is, it can be16 installed by non-superusers who have <code class="literal">CREATE</code> privilege17 on the current database.18 </p><div class="sect2" id="ISN-DATA-TYPES"><div class="titlepage"><div><div><h3 class="title">F.21.1. Data Types <a href="#ISN-DATA-TYPES" class="id_link">#</a></h3></div></div></div><p>19 <a class="xref" href="isn.html#ISN-DATATYPES" title="Table F.11. isn Data Types">Table F.11</a> shows the data types provided by20 the <code class="filename">isn</code> module.21 </p><div class="table" id="ISN-DATATYPES"><p class="title"><strong>Table F.11. <code class="filename">isn</code> Data Types</strong></p><div class="table-contents"><table class="table" summary="isn Data Types" border="1"><colgroup><col class="col1" /><col class="col2" /></colgroup><thead><tr><th>Data Type</th><th>Description</th></tr></thead><tbody><tr><td><code class="type">EAN13</code></td><td>22 European Article Numbers, always displayed in the EAN13 display format23 </td></tr><tr><td><code class="type">ISBN13</code></td><td>24 International Standard Book Numbers to be displayed in25 the new EAN13 display format26 </td></tr><tr><td><code class="type">ISMN13</code></td><td>27 International Standard Music Numbers to be displayed in28 the new EAN13 display format29 </td></tr><tr><td><code class="type">ISSN13</code></td><td>30 International Standard Serial Numbers to be displayed in the new31 EAN13 display format32 </td></tr><tr><td><code class="type">ISBN</code></td><td>33 International Standard Book Numbers to be displayed in the old34 short display format35 </td></tr><tr><td><code class="type">ISMN</code></td><td>36 International Standard Music Numbers to be displayed in the37 old short display format38 </td></tr><tr><td><code class="type">ISSN</code></td><td>39 International Standard Serial Numbers to be displayed in the40 old short display format41 </td></tr><tr><td><code class="type">UPC</code></td><td>42 Universal Product Codes43 </td></tr></tbody></table></div></div><br class="table-break" /><p>44 Some notes:45 </p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem"><p>ISBN13, ISMN13, ISSN13 numbers are all EAN13 numbers.</p></li><li class="listitem"><p>EAN13 numbers aren't always ISBN13, ISMN13 or ISSN13 (some46 are).</p></li><li class="listitem"><p>Some ISBN13 numbers can be displayed as ISBN.</p></li><li class="listitem"><p>Some ISMN13 numbers can be displayed as ISMN.</p></li><li class="listitem"><p>Some ISSN13 numbers can be displayed as ISSN.</p></li><li class="listitem"><p>UPC numbers are a subset of the EAN13 numbers (they are basically47 EAN13 without the first <code class="literal">0</code> digit).</p></li><li class="listitem"><p>All UPC, ISBN, ISMN and ISSN numbers can be represented as EAN1348 numbers.</p></li></ol></div><p>49 Internally, all these types use the same representation (a 64-bit50 integer), and all are interchangeable. Multiple types are provided51 to control display formatting and to permit tighter validity checking52 of input that is supposed to denote one particular type of number.53 </p><p>54 The <code class="type">ISBN</code>, <code class="type">ISMN</code>, and <code class="type">ISSN</code> types will display the55 short version of the number (ISxN 10) whenever it's possible, and will show56 ISxN 13 format for numbers that do not fit in the short version.57 The <code class="type">EAN13</code>, <code class="type">ISBN13</code>, <code class="type">ISMN13</code> and58 <code class="type">ISSN13</code> types will always display the long version of the ISxN59 (EAN13).60 </p></div><div class="sect2" id="ISN-CASTS"><div class="titlepage"><div><div><h3 class="title">F.21.2. Casts <a href="#ISN-CASTS" class="id_link">#</a></h3></div></div></div><p>61 The <code class="filename">isn</code> module provides the following pairs of type casts:62 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>63 ISBN13 <=> EAN1364 </p></li><li class="listitem"><p>65 ISMN13 <=> EAN1366 </p></li><li class="listitem"><p>67 ISSN13 <=> EAN1368 </p></li><li class="listitem"><p>69 ISBN <=> EAN1370 </p></li><li class="listitem"><p>71 ISMN <=> EAN1372 </p></li><li class="listitem"><p>73 ISSN <=> EAN1374 </p></li><li class="listitem"><p>75 UPC <=> EAN1376 </p></li><li class="listitem"><p>77 ISBN <=> ISBN1378 </p></li><li class="listitem"><p>79 ISMN <=> ISMN1380 </p></li><li class="listitem"><p>81 ISSN <=> ISSN1382 </p></li></ul></div><p>83 When casting from <code class="type">EAN13</code> to another type, there is a run-time84 check that the value is within the domain of the other type, and an error85 is thrown if not. The other casts are simply relabelings that will86 always succeed.87 </p></div><div class="sect2" id="ISN-FUNCS-OPS"><div class="titlepage"><div><div><h3 class="title">F.21.3. Functions and Operators <a href="#ISN-FUNCS-OPS" class="id_link">#</a></h3></div></div></div><p>88 The <code class="filename">isn</code> module provides the standard comparison operators,89 plus B-tree and hash indexing support for all these data types. In90 addition there are several specialized functions; shown in <a class="xref" href="isn.html#ISN-FUNCTIONS" title="Table F.12. isn Functions">Table F.12</a>.91 In this table,92 <code class="type">isn</code> means any one of the module's data types.93 </p><div class="table" id="ISN-FUNCTIONS"><p class="title"><strong>Table F.12. <code class="filename">isn</code> Functions</strong></p><div class="table-contents"><table class="table" summary="isn Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">94 Function95 </p>96 <p>97 Description98 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">99 <a id="id-1.11.7.31.7.3.2.2.1.1.1.1" class="indexterm"></a>100 <code class="function">isn_weak</code> ( <code class="type">boolean</code> )101 → <code class="returnvalue">boolean</code>102 </p>103 <p>104 Sets the weak input mode, and returns new setting.105 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">106 <code class="function">isn_weak</code> ()107 → <code class="returnvalue">boolean</code>108 </p>109 <p>110 Returns the current status of the weak mode.111 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">112 <a id="id-1.11.7.31.7.3.2.2.3.1.1.1" class="indexterm"></a>113 <code class="function">make_valid</code> ( <code class="type">isn</code> )114 → <code class="returnvalue">isn</code>115 </p>116 <p>117 Validates an invalid number (clears the invalid flag).118 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">119 <a id="id-1.11.7.31.7.3.2.2.4.1.1.1" class="indexterm"></a>120 <code class="function">is_valid</code> ( <code class="type">isn</code> )121 → <code class="returnvalue">boolean</code>122 </p>123 <p>124 Checks for the presence of the invalid flag.125 </p></td></tr></tbody></table></div></div><br class="table-break" /><p>126 <em class="firstterm">Weak</em> mode is used to be able to insert invalid data127 into a table. Invalid means the check digit is wrong, not that there are128 missing numbers.129 </p><p>130 Why would you want to use the weak mode? Well, it could be that131 you have a huge collection of ISBN numbers, and that there are so many of132 them that for weird reasons some have the wrong check digit (perhaps the133 numbers were scanned from a printed list and the OCR got the numbers wrong,134 perhaps the numbers were manually captured... who knows). Anyway, the point135 is you might want to clean the mess up, but you still want to be able to136 have all the numbers in your database and maybe use an external tool to137 locate the invalid numbers in the database so you can verify the138 information and validate it more easily; so for example you'd want to139 select all the invalid numbers in the table.140 </p><p>141 When you insert invalid numbers in a table using the weak mode, the number142 will be inserted with the corrected check digit, but it will be displayed143 with an exclamation mark (<code class="literal">!</code>) at the end, for example144 <code class="literal">0-11-000322-5!</code>. This invalid marker can be checked with145 the <code class="function">is_valid</code> function and cleared with the146 <code class="function">make_valid</code> function.147 </p><p>148 You can also force the insertion of invalid numbers even when not in the149 weak mode, by appending the <code class="literal">!</code> character at the end of the150 number.151 </p><p>152 Another special feature is that during input, you can write153 <code class="literal">?</code> in place of the check digit, and the correct check digit154 will be inserted automatically.155 </p></div><div class="sect2" id="ISN-EXAMPLES"><div class="titlepage"><div><div><h3 class="title">F.21.4. Examples <a href="#ISN-EXAMPLES" class="id_link">#</a></h3></div></div></div><pre class="programlisting">156--Using the types directly:157SELECT isbn('978-0-393-04002-9');158SELECT isbn13('0901690546');159SELECT issn('1436-4522');160 161--Casting types:162-- note that you can only cast from ean13 to another type when the163-- number would be valid in the realm of the target type;164-- thus, the following will NOT work: select isbn(ean13('0220356483481'));165-- but these will:166SELECT upc(ean13('0220356483481'));167SELECT ean13(upc('220356483481'));168 169--Create a table with a single column to hold ISBN numbers:170CREATE TABLE test (id isbn);171INSERT INTO test VALUES('9780393040029');172 173--Automatically calculate check digits (observe the '?'):174INSERT INTO test VALUES('220500896?');175INSERT INTO test VALUES('978055215372?');176 177SELECT issn('3251231?');178SELECT ismn('979047213542?');179 180--Using the weak mode:181SELECT isn_weak(true);182INSERT INTO test VALUES('978-0-11-000533-4');183INSERT INTO test VALUES('9780141219307');184INSERT INTO test VALUES('2-205-00876-X');185SELECT isn_weak(false);186 187SELECT id FROM test WHERE NOT is_valid(id);188UPDATE test SET id = make_valid(id) WHERE id = '2-205-00876-X!';189 190SELECT * FROM test;191 192SELECT isbn13(id) FROM test;193</pre></div><div class="sect2" id="ISN-BIBLIOGRAPHY"><div class="titlepage"><div><div><h3 class="title">F.21.5. Bibliography <a href="#ISN-BIBLIOGRAPHY" class="id_link">#</a></h3></div></div></div><p>194 The information to implement this module was collected from195 several sites, including:196 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><a class="ulink" href="https://www.isbn-international.org/" target="_top">https://www.isbn-international.org/</a></p></li><li class="listitem"><p><a class="ulink" href="https://www.issn.org/" target="_top">https://www.issn.org/</a></p></li><li class="listitem"><p><a class="ulink" href="https://www.ismn-international.org/" target="_top">https://www.ismn-international.org/</a></p></li><li class="listitem"><p><a class="ulink" href="https://www.wikipedia.org/" target="_top">https://www.wikipedia.org/</a></p></li></ul></div><p>197 198 The prefixes used for hyphenation were also compiled from:199 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><a class="ulink" href="https://www.gs1.org/standards/id-keys" target="_top">https://www.gs1.org/standards/id-keys</a></p></li><li class="listitem"><p><a class="ulink" href="https://en.wikipedia.org/wiki/List_of_ISBN_identifier_groups" target="_top">https://en.wikipedia.org/wiki/List_of_ISBN_identifier_groups</a></p></li><li class="listitem"><p><a class="ulink" href="https://www.isbn-international.org/content/isbn-users-manual" target="_top">https://www.isbn-international.org/content/isbn-users-manual</a></p></li><li class="listitem"><p><a class="ulink" href="https://en.wikipedia.org/wiki/International_Standard_Music_Number" target="_top">https://en.wikipedia.org/wiki/International_Standard_Music_Number</a></p></li><li class="listitem"><p><a class="ulink" href="https://www.ismn-international.org/ranges.html" target="_top">https://www.ismn-international.org/ranges.html</a></p></li></ul></div><p>200 201 Care was taken during the creation of the algorithms and they202 were meticulously verified against the suggested algorithms203 in the official ISBN, ISMN, ISSN User Manuals.204 </p></div><div class="sect2" id="ISN-AUTHOR"><div class="titlepage"><div><div><h3 class="title">F.21.6. Author <a href="#ISN-AUTHOR" class="id_link">#</a></h3></div></div></div><p>205 Germán Méndez Bravo (Kronuz), 2004–2006206 </p><p>207 This module was inspired by Garrett A. Wollman's208 <code class="filename">isbn_issn</code> code.209 </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="intarray.html" title="F.20. intarray — manipulate arrays of integers">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="lo.html" title="F.22. lo — manage large objects">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.20. intarray — manipulate arrays of integers </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> F.22. lo — manage large objects</td></tr></table></div></body></html>