Bill & Ted's Excellent Adventure, Part 6

Porting to Postgres will be no big deal. Well, not so far. Here are the changes needed just to create the tables:

CREATE TABLE personnel
(emp INTEGER NOT NULL
,socsec CHAR(11) NOT NULL
,job_ftn CHAR(4) NOT NULL
,dept SMALLINT NOT NULL
,salary DECIMAL(7,2) NOT NULL
,date_bn DATE NOT NULL --WITH DEFAULT
,fst_name VARCHAR(20)
,lst_name VARCHAR(20)
,CONSTRAINT pex1 PRIMARY KEY (emp)
,CONSTRAINT pe01 CHECK (emp > 0)
--,CONSTRAINT pe02 CHECK (LOCATE(' ',socsec) = 0)
--,CONSTRAINT pe03 CHECK (LOCATE('-',socsec,1) = 4)
--,CONSTRAINT pe04 CHECK (LOCATE('-',socsec,5) = 7)
,CONSTRAINT pe05 CHECK (job_ftn <> '')
,CONSTRAINT pe06 CHECK (dept BETWEEN 1 AND 99)
,CONSTRAINT pe07 CHECK (salary BETWEEN 0 AND 99999)
,CONSTRAINT pe08 CHECK (fst_name <> '')
,CONSTRAINT pe09 CHECK (lst_name <> '')
,CONSTRAINT pe10 CHECK (date_bn >= '1900-01-01' ));


CREATE UNIQUE INDEX PEX3 ON PERSONNEL (DEPT, EMP);


Beyond the commented out stuff, Postgres won't accept # in names, so those appended had to go. Had to do the same for Dependents and PersonnelFlat. The Insert routine needed a bunch of changes and ends up not looking at all like the DB2 data, but is populated, which is all I care about at this point (OK, this is a big deal; I wish I'd have sprung for SwisSQL then, and it turns out, there is a review ).


Now for the results:

select * from personnel where 1090000 < emp and emp < 1100000 -- for the join-ed table on SSD
28ms

SELECT p.*
,d.dependent
,d.dep_fst_name
,d.dep_lst_name
FROM personnel AS p
JOIN dependents AS d ON d.emp = p.emp
WHERE 1090000 < p.emp# AND p.emp# < 1100000
60ms

Not quite up to DB2, but not tuned, either. Still rather faster than HDD. Now, where's that Yellow Brick Road?