1-- 2-- Test ISN extension 3-- 4CREATE EXTENSION isn; 5-- 6-- test valid conversions 7-- 8SELECT '9780123456786'::EAN13, -- old book 9 '9790123456785'::EAN13, -- music 10 '9791234567896'::EAN13, -- new book 11 '9771234567898'::EAN13, -- serial 12 '0123456789012'::EAN13, -- upc 13 '1234567890128'::EAN13; 14 ean13 | ean13 | ean13 | ean13 | ean13 | ean13 15-------------------+-------------------+-----------------+-------------------+-----------------+----------------- 16 978-0-12-345678-6 | 979-0-1234-5678-5 | 979-123456789-6 | 977-1234-567-89-8 | 012-345678901-2 | 123-456789012-8 17(1 row) 18 19SELECT '9780123456786'::ISBN, 20 '123456789X'::ISBN, 21 '9780123456786'::ISBN13::ISBN, 22 '9780123456786'::EAN13::ISBN; 23 isbn | isbn | isbn | isbn 24---------------+---------------+---------------+--------------- 25 0-12-345678-9 | 1-234-56789-X | 0-12-345678-9 | 0-12-345678-9 26(1 row) 27 28SELECT -- new books, shown as ISBN13 even for ISBN... 29 '9791234567896'::ISBN, 30 '9791234567896'::ISBN13::ISBN, 31 '9791234567896'::EAN13::ISBN; 32 isbn | isbn | isbn 33-----------------+-----------------+----------------- 34 979-123456789-6 | 979-123456789-6 | 979-123456789-6 35(1 row) 36 37SELECT '9780123456786'::ISBN13, 38 '123456789X'::ISBN13, 39 '9791234567896'::ISBN13, 40 '9791234567896'::EAN13::ISBN13; 41 isbn13 | isbn13 | isbn13 | isbn13 42-------------------+-------------------+-----------------+----------------- 43 978-0-12-345678-6 | 978-1-234-56789-7 | 979-123456789-6 | 979-123456789-6 44(1 row) 45 46SELECT '9790123456785'::ISMN, 47 '9790123456785'::EAN13::ISMN, 48 'M123456785'::ISMN, 49 'M-1234-5678-5'::ISMN; 50 ismn | ismn | ismn | ismn 51---------------+---------------+---------------+--------------- 52 M-1234-5678-5 | M-1234-5678-5 | M-1234-5678-5 | M-1234-5678-5 53(1 row) 54 55SELECT '9790123456785'::ISMN13, 56 'M123456785'::ISMN13, 57 'M-1234-5678-5'::ISMN13; 58 ismn13 | ismn13 | ismn13 59-------------------+-------------------+------------------- 60 979-0-1234-5678-5 | 979-0-1234-5678-5 | 979-0-1234-5678-5 61(1 row) 62 63SELECT '9771234567003'::ISSN, 64 '12345679'::ISSN; 65 issn | issn 66-----------+----------- 67 1234-5679 | 1234-5679 68(1 row) 69 70SELECT '9771234567003'::ISSN13, 71 '12345679'::ISSN13, 72 '9771234567898'::ISSN13, 73 '9771234567898'::EAN13::ISSN13; 74 issn13 | issn13 | issn13 | issn13 75-------------------+-------------------+-------------------+------------------- 76 977-1234-567-00-3 | 977-1234-567-00-3 | 977-1234-567-89-8 | 977-1234-567-89-8 77(1 row) 78 79SELECT '0123456789012'::UPC, 80 '0123456789012'::EAN13::UPC; 81 upc | upc 82--------------+-------------- 83 123456789012 | 123456789012 84(1 row) 85 86-- 87-- test invalid checksums 88-- 89SELECT '1234567890'::ISBN; 90ERROR: invalid check digit for ISBN number: "1234567890", should be X 91LINE 1: SELECT '1234567890'::ISBN; 92 ^ 93SELECT 'M123456780'::ISMN; 94ERROR: invalid check digit for ISMN number: "M123456780", should be 5 95LINE 1: SELECT 'M123456780'::ISMN; 96 ^ 97SELECT '12345670'::ISSN; 98ERROR: invalid check digit for ISSN number: "12345670", should be 9 99LINE 1: SELECT '12345670'::ISSN; 100 ^ 101SELECT '9780123456780'::ISBN; 102ERROR: invalid check digit for ISBN number: "9780123456780", should be 6 103LINE 1: SELECT '9780123456780'::ISBN; 104 ^ 105SELECT '9791234567890'::ISBN13; 106ERROR: invalid check digit for ISBN number: "9791234567890", should be 6 107LINE 1: SELECT '9791234567890'::ISBN13; 108 ^ 109SELECT '0123456789010'::UPC; 110ERROR: invalid check digit for UPC number: "0123456789010", should be 2 111LINE 1: SELECT '0123456789010'::UPC; 112 ^ 113SELECT '1234567890120'::EAN13; 114ERROR: invalid check digit for EAN13 number: "1234567890120", should be 8 115LINE 1: SELECT '1234567890120'::EAN13; 116 ^ 117-- 118-- test invalid conversions 119-- 120SELECT '9790123456785'::ISBN; -- not a book 121ERROR: cannot cast ISMN to ISBN for number: "9790123456785" 122LINE 1: SELECT '9790123456785'::ISBN; 123 ^ 124SELECT '9771234567898'::ISBN; -- not a book 125ERROR: cannot cast ISSN to ISBN for number: "9771234567898" 126LINE 1: SELECT '9771234567898'::ISBN; 127 ^ 128SELECT '0123456789012'::ISBN; -- not a book 129ERROR: cannot cast UPC to ISBN for number: "0123456789012" 130LINE 1: SELECT '0123456789012'::ISBN; 131 ^ 132SELECT '9790123456785'::ISBN13; -- not a book 133ERROR: cannot cast ISMN to ISBN for number: "9790123456785" 134LINE 1: SELECT '9790123456785'::ISBN13; 135 ^ 136SELECT '9771234567898'::ISBN13; -- not a book 137ERROR: cannot cast ISSN to ISBN for number: "9771234567898" 138LINE 1: SELECT '9771234567898'::ISBN13; 139 ^ 140SELECT '0123456789012'::ISBN13; -- not a book 141ERROR: cannot cast UPC to ISBN for number: "0123456789012" 142LINE 1: SELECT '0123456789012'::ISBN13; 143 ^ 144SELECT '9780123456786'::ISMN; -- not music 145ERROR: cannot cast ISBN to ISMN for number: "9780123456786" 146LINE 1: SELECT '9780123456786'::ISMN; 147 ^ 148SELECT '9771234567898'::ISMN; -- not music 149ERROR: cannot cast ISSN to ISMN for number: "9771234567898" 150LINE 1: SELECT '9771234567898'::ISMN; 151 ^ 152SELECT '9791234567896'::ISMN; -- not music 153ERROR: cannot cast ISBN to ISMN for number: "9791234567896" 154LINE 1: SELECT '9791234567896'::ISMN; 155 ^ 156SELECT '0123456789012'::ISMN; -- not music 157ERROR: cannot cast UPC to ISMN for number: "0123456789012" 158LINE 1: SELECT '0123456789012'::ISMN; 159 ^ 160SELECT '9780123456786'::ISSN; -- not serial 161ERROR: cannot cast ISBN to ISSN for number: "9780123456786" 162LINE 1: SELECT '9780123456786'::ISSN; 163 ^ 164SELECT '9790123456785'::ISSN; -- not serial 165ERROR: cannot cast ISMN to ISSN for number: "9790123456785" 166LINE 1: SELECT '9790123456785'::ISSN; 167 ^ 168SELECT '9791234567896'::ISSN; -- not serial 169ERROR: cannot cast ISBN to ISSN for number: "9791234567896" 170LINE 1: SELECT '9791234567896'::ISSN; 171 ^ 172SELECT '0123456789012'::ISSN; -- not serial 173ERROR: cannot cast UPC to ISSN for number: "0123456789012" 174LINE 1: SELECT '0123456789012'::ISSN; 175 ^ 176SELECT '9780123456786'::UPC; -- not a product 177ERROR: cannot cast ISBN to UPC for number: "9780123456786" 178LINE 1: SELECT '9780123456786'::UPC; 179 ^ 180SELECT '9771234567898'::UPC; -- not a product 181ERROR: cannot cast ISSN to UPC for number: "9771234567898" 182LINE 1: SELECT '9771234567898'::UPC; 183 ^ 184SELECT '9790123456785'::UPC; -- not a product 185ERROR: cannot cast ISMN to UPC for number: "9790123456785" 186LINE 1: SELECT '9790123456785'::UPC; 187 ^ 188SELECT '9791234567896'::UPC; -- not a product 189ERROR: cannot cast ISBN to UPC for number: "9791234567896" 190LINE 1: SELECT '9791234567896'::UPC; 191 ^ 192SELECT 'postgresql...'::EAN13; 193ERROR: invalid input syntax for EAN13 number: "postgresql..." 194LINE 1: SELECT 'postgresql...'::EAN13; 195 ^ 196SELECT 'postgresql...'::ISBN; 197ERROR: invalid input syntax for ISBN number: "postgresql..." 198LINE 1: SELECT 'postgresql...'::ISBN; 199 ^ 200SELECT 9780123456786::EAN13; 201ERROR: cannot cast type bigint to ean13 202LINE 1: SELECT 9780123456786::EAN13; 203 ^ 204SELECT 9780123456786::ISBN; 205ERROR: cannot cast type bigint to isbn 206LINE 1: SELECT 9780123456786::ISBN; 207 ^ 208-- 209-- test some comparisons, must yield true 210-- 211SELECT '12345679'::ISSN = '9771234567003'::EAN13 AS "ok", 212 'M-1234-5678-5'::ISMN = '9790123456785'::EAN13 AS "ok", 213 '9791234567896'::EAN13 != '123456789X'::ISBN AS "nope"; 214 ok | ok | nope 215----+----+------ 216 t | t | t 217(1 row) 218 219-- 220-- cleanup 221-- 222DROP EXTENSION isn; 223