This article is half-done without your Comment! *** Please share your thoughts via Comment ***
PostgreSQL provides a wide variety of data types and I am always sharing information about strange data types.
PostgreSQL has ISN Data types, in which you can store few International Standard Number like: ISBN – International Standard Book Numbers, ISMN – International Standard Music Numbers,
ISSN – International Standard Serial Numbers, UPC – Universal Product Codes, EAN13 – European Article Numbers.
You cannot insert invalid format data in ISN data types.
When you insert data in ISN type column, internally It checks the format of data and force to insert only valid format data.
When we stored data in its specified data types, It improves the overall query performance because It knows data format and accordingly assign storage capacity.
Small Demonstration:
First, Create a ISN Extension / Module:
|
1 |
CREATE EXTENSION ISN; |
Create a sample table with few ISN datatypes:
|
1 2 3 4 5 6 7 |
CREATE TABLE tbl_TestISNDataType ( ID INT ,MyISBN ISBN ,MyISSN ISSN ,MyISMN ISMN ); |
Insert a sample data:
|
1 2 |
INSERT INTO tbl_TestISNDataType VALUES (1,'978-3-16-148410-0','3691-2689','M-47213-542-3'); |
SELECT inserted ISN data with valid ISBN number:
|
1 2 3 4 |
SELECT *FROM tbl_TestISNDataType; id | myisbn | myissn | myismn ----+---------------+-----------+--------------- 1 | 3-16-148410-X | 3691-2689 | M-47213-542-3 |
Few sample functions to get valid ISN value:
|
1 2 3 4 |
SELECT isbn('978-3-16-148410-0'); SELECT isbn13('0901690546'); SELECT issn('3691268?'); SELECT ismn('979047213542?'); |
Leave a Reply