5.8. 䂓欪

PostgreSQL 垂䯄庖臘䂓欪認婻䬹攓凹昄扞康螆螇庺叻準臘滇婔婻冽橬昽䔇噖噙㔗SQL99 埪傖劯䔇湺庖垔幬庖䌂傋䂓欪䬹攓启潏傸婘認麯柟誄䔇冽崔䬹攓橬寺彆㔗

螷潏傸傯婔婻冋床嚔哋啺螆潏傸臘商彽嘩婔婻嘯婗昄扞昇傋㔗懟婻噂鄘橬螩崔嘯婗嘖滇埻橬婔婻饡庩㔗潏傸婯橕脘崘誙锘演䘵傂嘘噂䔇饡庩㔗認婻傂媇埇傖锔誺录傺婴婻臘準垂䯄婔婻滇噂庩臘婔婻滇麂噂庩臘㔗婉誺套悩潏傸婉䞇傔幽嘯婗鄘愿昖臖支幽媂?䂓欪䔇䬹攓埇傖婞媷潏傸蓼喿認婻閞鵻㔗潏傸垔幬 capitals 臘垄䂓欪躻 cities 臘

CREATE TABLE cities (
    name            text,
    population      float,
    altitude        int     -- 苌儺
);

CREATE TABLE capitals (
    state           char(2)
) INHERITS (cities);

婘認䓉愙喕婋capitals 臘䂓欪垄䔇佽臘 cities 婺䔇欔橬匂攓㔗噂饡庩橬婔婻鵺崡䔇 state 匂攓滆䴺噽欔婘䔇噂㔗

婘 PostgreSQL 麯婔婻臘埇傖傯镽婻潡崔婻噽垄臘婺䂓欪匂攓蔯婫婔婻昖臵斵埇傖嚘䫘婔婻臘婺䔇欔橬臯幘埇傖嚘䫘婔婻臘埪噽欔橬劯傼臘䔇臯(劯麵認婻滇䚺䩕臯婺)㔗懫套婋麵䔇昖臵昖欆欔橬教拫 500 苌儺傖婪䔇嘯婗劉寙拸噂饡庩

SELECT name, altitude
    FROM cities
    WHERE altitude > 500;

嘪䫘 PostgreSQL 嘺䇔昍䘋麯麵䔇昄扞(埗黙誗2.1)垄誫啂

   name    | altitude
-----------+----------
 Las Vegas |     2174
 Mariposa  |     1953
 Madison   |      845

埥婔桹麵套悩襕欆庺婉寙拸噂饡庩䔇欔橬教拫轙誺 500 苌儺䔇嘯婗昖臵庫臖滇認湙䔇

SELECT name, altitude
    FROM ONLY cities
    WHERE altitude > 500;

   name    | altitude
-----------+----------
 Las Vegas |     2174
 Mariposa  |     1953

cities 嬉麵䔇 ONLY 臘滯臖昖臵庫臖埻鐽凹 cities 蔯婉寙拸噽劯傼㔗螩崔潏傸噾䂟螘螺誺䔇变傴(SELECT, UPDATE, DELETE)鄘櫇毕 ONLY 噿髞庖㔗

橬施唍嘹埇脘愿䘖長昊婻臯䬽橸準躻巻婻臘㔗婘懟婻臘麯潏傸鄘橬婔婻 tableoid 係䂘匂攓埇傖只臬嘹溊臘滇脕

SELECT c.tableoid, c.name, c.altitude
FROM cities c
WHERE c.altitude > 500;

䂷悩套婋(嘹埇脘嚔冖彄婉劯䔇 OID)

 tableoid |   name    | altitude
----------+-----------+----------
   139793 | Las Vegas |     2174
   139793 | Mariposa  |     1953
   139798 | Madison   |      845

锔誺启 pg_class 啔婔婻誂毖儌埇傖䩋彄垂鍙䔇臘劉庖

SELECT p.relname, c.name, c.altitude
FROM cities c, pg_class p
WHERE c.altitude > 500 and c.tableoid = p.oid;

垄誫啂

 relname  |   name    | altitude
----------+-----------+----------
 cities   | Las Vegas |     2174
 cities   | Mariposa  |     1953
 capitals | Madison   |      845

凹庯 INSERT 潡 COPY 䂓欪幽婉躻媘嘌巉噽劯傼臘㔗婘潏傸䔇冋床麯婋麵䔇 INSERT 臺埖儖嚔崌蘖

INSERT INTO cities (name, population, altitude, state)
VALUES ('New York', NULL, NULL, 'NY');

潏傸埇脘婯橕昄扞赆嚹锐彄 capitals 臘麯麵寂嘖認滇婉嚔埏䫘䔇INSERT 攂滇某噖滯䇞弄滯䔇闼婻臘㔗婘昊底愙喕婋潏傸埇傖嘪䫘蓇彍誕臯麉垔劏某噖(埗黙䆹35)㔗婉誺垄婉脘凹婪麵䔇冋床橬傔幽婞媷啹婺 cities 臘幽婉寙劆 state 庖枕啹溴变傴婘蓇彍桘媹幋嬉儌嚔赆拐䂺毬㔗

欔橬佽臘䔇演昖亥溘启麂䷺亥溘鄘嚔躻媘赆欔橬床臘䂓欪㔗婉誺噽垄䌂傋䔇亥溘(嫇婔㔕婂髞㔕崡髞)婉嚔赆䂓欪㔗

婔婻床臘埇傖傯崔婻佽臘䂓欪認䓉愙喕婋垄儖拖橬欔橬佽臘庖枕䔇攂启幽婫床臘婺垔幬䔇庖枕幘嚔媹噖噽婺㔗套悩劯婔婻庖枕劉庺䯄婘崔婻佽臘婺潡蔙劯施庺䯄婘佽臘启床臘䔇垔幬麯闼幽認底庖枕儌嚔赆"肉劽"認湙婘床臘麯儌埻橬婔婻認湙䔇庖枕㔗襕愿肉劽庖枕䔇昄扞䌂傋媙釂䕩劯劥彍儌嚔檕庺婔婻髍臇㔗肉劽䔇庖枕儖嚔拖橬噽佽庖枕䔇欔橬演昖亥溘幽婫套悩昊婻佽庖枕庻婘麂䷺亥溘闼幽肉劽劯䔇庖枕幘媙釂滇麂䷺䔇㔗

臘䂓欪锔婩嘪䫘婥 INHERITS 床埖䔇 CREATE TABLE 臺埖垔幬㔗埥崡婔婻噾䂟䫘溴桹濘垔幬䔇床臘埇傖嘪䫘婥 INHERIT 䔇 ALTER TABLE 变傴時媹婔婻桄佽臘㔗濘懟臖床臘媙釂噾䂟寙劆桄佽臘䔇欔橬庖枕婫䌂傋婔躘溴崡桄佽臘䔇懟婻亥溘䔇劉庖埪噽臘膆嚟鄘媙釂寙劆婘溴床臘婺㔗劯湙婔婻䂓欪鷆埇傖嘪䫘婥 NO INHERIT 䔇 ALTER TABLE 变傴傯床臘婪役鍴㔗噕螩媘攕時媹启役鍴䂓欪鷆凹嘺庯䂓欪噿係䔇臘彖寺(埗蓕誗5.9)冽橬䫘㔗

录傺婔婻儖襕嘩婺床臘䔇桄臘䔇冪彷锫冇滇嘪䫘婥 LIKE 床埖䔇 CREATE TABLE 变傴㔗垄儖录傺婔婻婯溊臘庖枕䕩劯䔇桄臘㔗套悩溊臘婺庻婘亥溘闼幽庫臖毺垔 LIKE 䔇 INCLUDING CONSTRAINTS 锬釹啹婺床臘媙釂寙劆溊臘婺䔇 CHECK 亥溘㔗

傂嘘庻婘床臘䔇佽臘鄘婉脘赆役鍴劯湙床臘婺傂嘘傯佽臘䂓欪䔇庖枕幘婉脘赆役鍴潡媞櫹㔗套悩嘹愿役鍴婔婻臘埪噽欔橬劯傼橔䞔剘䔇媂濘滇嘪䫘 CASCADE 锬釹㔗

ALTER TABLE 嚔檪欔橬昄扞垔幬启演昖亥溘嚹携彄劯傼麯麵寂㔗埥崡埻橬婘嘪䫘 CASCADE 锬釹䔇愙喕婋欉脘役鍴佽臘䔇庖枕潡蔙亥溘㔗ALTER TABLE 婘麉崉庖枕肉劽启拐䂺桹麵启 CREATE TABLE 䔇蓇彍䕩劯㔗