test-php/sqlpower.sql

ソース

このソースをダウンロードする。

DROP TABLE personal_n;
CREATE TABLE personal_n (
    code        smallint,
    name        varchar (15),
    age         smallint,
    type        char    (1),
    state       smallint,
    pno         smallint
);

\copy personal_n from 'personal.csv' using delimiters ','

CREATE INDEX personal_n_idx_code ON personal_n (code);
CREATE INDEX personal_n_idx_name ON personal_n (name);
CREATE INDEX personal_n_idx_type ON personal_n (type);
CREATE INDEX personal_n_idx_pno ON personal_n (pno);


DROP TABLE personal_p;
CREATE TABLE personal_p (
    code        smallint     PRIMARY KEY,
    name        varchar (15) NOT NULL,
    age         smallint     NOT NULL,
    type        char    (1)  NOT NULL,
    state       smallint     NOT NULL,
    pno         smallint     NOT NULL
);

\copy personal_p from 'personal.csv' using delimiters ','

CREATE INDEX personal_p_idx_name ON personal_p (name);
CREATE INDEX personal_p_idx_type ON personal_p (type);
CREATE INDEX personal_p_idx_pno ON personal_p (pno);


DROP TABLE personal_na;
CREATE TABLE personal_na (
    code        smallint,
    name        varchar (15),
    age         smallint,
    type        char    (1),
    state       smallint,
    pno         smallint
);

\copy personal_na from 'personal.csv' using delimiters ','

CREATE INDEX personal_na_idx_code ON personal_na (code);
CREATE INDEX personal_na_idx_name ON personal_na (name);
CREATE INDEX personal_na_idx_type ON personal_na (type);
CREATE INDEX personal_na_idx_pno ON personal_na (pno);

vacuum full analyze personal_na;


DROP TABLE personal_pa;
CREATE TABLE personal_pa (
    code        smallint     PRIMARY KEY,
    name        varchar (15) NOT NULL,
    age         smallint     NOT NULL,
    type        char    (1)  NOT NULL,
    state       smallint     NOT NULL,
    pno         smallint     NOT NULL
);

\copy personal_pa from 'personal.csv' using delimiters ','

CREATE INDEX personal_pa_idx_name ON personal_pa (name);
CREATE INDEX personal_pa_idx_type ON personal_pa (type);
CREATE INDEX personal_pa_idx_pno ON personal_pa (pno);

vacuum full analyze personal_pa;


DROP TABLE state_n;
CREATE TABLE state_n (
    statecode   smallint,
    statename   varchar (20)
);

\copy state_n from 'state.csv' using delimiters ','


DROP TABLE state_p;
CREATE TABLE state_p (
    statecode   smallint     PRIMARY KEY,
    statename   varchar (20)
);

\copy state_p from 'state.csv' using delimiters ','


DROP TABLE state_na;
CREATE TABLE state_na (
    statecode   smallint,
    statename   varchar (20)
);

\copy state_na from 'state.csv' using delimiters ','

vacuum full analyze state_na;


DROP TABLE state_pa;
CREATE TABLE state_pa (
    statecode   smallint     PRIMARY KEY,
    statename   varchar (20)
);

\copy state_pa from 'state.csv' using delimiters ','

vacuum full analyze state_pa;