\set ECHO none
\i ssn.sql
\set ECHO all

CREATE TABLE ssns(id serial primary key, ssn ssn unique);
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');
-- check for invalid SSNs
INSERT INTO ssns(ssn) VALUES('000-00-0000');
INSERT INTO ssns(ssn) VALUES('123-00-5678');
INSERT INTO ssns(ssn) VALUES('000-12-3456');
INSERT INTO ssns(ssn) VALUES('123-45-0000');
INSERT INTO ssns(ssn) VALUES('666-12-4567');
INSERT INTO ssns(ssn) VALUES('900-12-4567');
INSERT INTO ssns(ssn) VALUES('990-12-4567');
INSERT INTO ssns(ssn) VALUES('1234-56-7890');
INSERT INTO ssns(ssn) VALUES('12356-7890');
INSERT INTO ssns(ssn) VALUES('123-567-890');
INSERT INTO ssns(ssn) VALUES('12-356-7890');
INSERT INTO ssns(ssn) VALUES('123-56-7890 ');
INSERT INTO ssns(ssn) VALUES('0123-056-07890 ');


SELECT * FROM ssns;

-- 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';
SELECT id,ssn FROM ssns WHERE ssn >= '123-45-1556' LIMIT 5;
SELECT count(id) FROM ssns WHERE ssn <> ('123-45-1666'::text)::ssn;
RESET enable_seqscan;

-- operators and conversions
SELECT '123-45-7890'::ssn < '122-45-7890'::ssn;
SELECT '123-45-7890'::ssn > '123-45-7889'::ssn;
SELECT '123-12-4320'::ssn <> '123-12-4321'::ssn;

-- COPY FROM/TO
TRUNCATE ssns;
COPY ssns(ssn) FROM STDIN;
123-45-6789
012040078
\.
COPY ssns TO STDOUT;

-- clean up --
DROP TABLE ssns;