Changeset 31
- Timestamp:
- Oct 12, 2006, 9:54:23 PM (15 years ago)
- File:
-
- 1 edited
Legend:
- Unmodified
- Added
- Removed
-
trunk/npemap.org.uk/schema_postgres.sql
r12 r31 1 -- NPE postcode collector schema 1 2 3 4 -- Different Sources of the Postcode 5 CREATE SEQUENCE s_sources; 6 CREATE TABLE sources ( 7 id integer NOT NULL DEFAULT NEXTVAL('s_sources'), 8 name character varying(30) NOT NULL, 9 CONSTRAINT pk_sources PRIMARY KEY (id) 10 ); 11 INSERT INTO sources VALUES (0, 'NPE Postcode Inputter'); 12 INSERT INTO sources VALUES (1, 'FreeThePostcode.org Importer'); 13 SELECT SETVAL('s_sources',1); 14 15 16 -- ID sequence 17 -- so we can have a unique way to reference a postcode submission 18 CREATE SEQUENCE s_postcodes; 19 20 -- Our submitted postcodes 2 21 CREATE TABLE postcodes ( 3 outward character varying(5), 4 inward character varying(5), 5 raw_postcode character varying(30), 6 easting integer, 7 northing integer, 22 id integer NOT NULL DEFAULT NEXTVAL('s_postcodes'), 23 outward character varying(5) NOT NULL, 24 inward character varying(3), 25 raw_postcode character varying(30) NOT NULL, 26 easting integer NOT NULL, 27 northing integer NOT NULL, 8 28 ip inet, 9 created_at timestamp without time zone, 10 source integer 29 created_at timestamp without time zone NOT NULL default NOW(), 30 source integer NOT NULL, 31 32 CONSTRAINT pk_postcode PRIMARY KEY (id), 33 CONSTRAINT fk_postcode_source FOREIGN KEY (source) REFERENCES sources (id) 11 34 ); 12 35 13 36 CREATE INDEX postcode_easting ON postcodes (easting); 37 CREATE INDEX postcode_northing ON postcodes (northing); 38 CREATE INDEX postcode_outward ON postcodes (outward); 39 CREATE INDEX postcode_postcode ON postcodes (outward,inward);
Note: See TracChangeset
for help on using the changeset viewer.