\set ECHO none
psql:ssn.sql:6: NOTICE:  return type ssn is only a shell
psql:ssn.sql:8: NOTICE:  argument type ssn is only a shell
psql:ssn.sql:10: NOTICE:  argument type ssn is only a shell
psql:ssn.sql:12: NOTICE:  return type ssn is only a shell
CREATE TABLE ssns(id serial primary key, ssn ssn unique);
NOTICE:  CREATE TABLE will create implicit sequence "ssns_id_seq" for serial column "ssns.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "ssns_pkey" for table "ssns"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "ssns_ssn_key" for table "ssns"
INSERT INTO ssns(ssn) VALUES('123-56-7890');
INSERT INTO ssns(ssn) VALUES('543221342');
-- check for alternative input format
INSERT INTO ssns(ssn) VALUES('543-22-1342');
ERROR:  duplicate key value violates unique constraint "ssns_ssn_key"
-- check for invalid SSNs
INSERT INTO ssns(ssn) VALUES('000-00-0000');
ERROR:  SSN number 000-00-0000 is invalid
INSERT INTO ssns(ssn) VALUES('123-00-5678');
ERROR:  SSN number 123-00-5678 is invalid
INSERT INTO ssns(ssn) VALUES('000-12-3456');
ERROR:  SSN number 000-12-3456 is invalid
INSERT INTO ssns(ssn) VALUES('123-45-0000');
ERROR:  SSN number 123-45-0000 is invalid
INSERT INTO ssns(ssn) VALUES('666-12-4567');
ERROR:  SSN number 666-12-4567 is invalid
INSERT INTO ssns(ssn) VALUES('900-12-4567');
ERROR:  SSN number 900-12-4567 is invalid
INSERT INTO ssns(ssn) VALUES('990-12-4567');
ERROR:  SSN number 990-12-4567 is invalid
INSERT INTO ssns(ssn) VALUES('1234-56-7890');
ERROR:  invalid format of input data 1234-56-7890
HINT:  Valid formats are: AAA-BB-CCCC or AAABBCCCC
INSERT INTO ssns(ssn) VALUES('12356-7890');
ERROR:  invalid format of input data 12356-7890
HINT:  Valid formats are: AAA-BB-CCCC or AAABBCCCC
INSERT INTO ssns(ssn) VALUES('123-567-890');
ERROR:  invalid format of input data 123-567-890
HINT:  Valid formats are: AAA-BB-CCCC or AAABBCCCC
INSERT INTO ssns(ssn) VALUES('12-356-7890');
ERROR:  invalid format of input data 12-356-7890
HINT:  Valid formats are: AAA-BB-CCCC or AAABBCCCC
INSERT INTO ssns(ssn) VALUES('123-56-7890 ');
ERROR:  unexpected character ' ' in input data 123-56-7890 
HINT:  Valid SSN consists of digits and optional dashes
INSERT INTO ssns(ssn) VALUES('0123-056-07890 ');
ERROR:  invalid format of input data 0123-056-07890 
HINT:  Valid formats are: AAA-BB-CCCC or AAABBCCCC
SELECT * FROM ssns;
 id |     ssn     
----+-------------
  1 | 123-56-7890
  2 | 543-22-1342
(2 rows)

-- index scan
TRUNCATE ssns;
INSERT INTO ssns(ssn) SELECT '123-45-'||id FROM generate_series(1000, 1999) id;
SET enable_seqscan = false;
SELECT id,ssn::text FROM ssns WHERE ssn = '123-45-1555';
 id  |     ssn     
-----+-------------
 559 | 123-45-1555
(1 row)

SELECT id,ssn FROM ssns WHERE ssn >= '123-45-1556' LIMIT 5;
 id  |     ssn     
-----+-------------
 560 | 123-45-1556
 561 | 123-45-1557
 562 | 123-45-1558
 563 | 123-45-1559
 564 | 123-45-1560
(5 rows)

SELECT count(id) FROM ssns WHERE ssn <> ('123-45-1666'::text)::ssn;
 count 
-------
   999
(1 row)

RESET enable_seqscan;
-- operators and conversions
SELECT '123-45-7890'::ssn < '122-45-7890'::ssn;
 ?column? 
----------
 f
(1 row)

SELECT '123-45-7890'::ssn > '123-45-7889'::ssn;
 ?column? 
----------
 t
(1 row)

SELECT '123-12-4320'::ssn <> '123-12-4321'::ssn;
 ?column? 
----------
 t
(1 row)

-- COPY FROM/TO
TRUNCATE ssns;
COPY ssns(ssn) FROM STDIN;
COPY ssns TO STDOUT;
1004	123-45-6789
1005	012-04-0078
-- clean up --
DROP TABLE ssns;