PostgreSQL CREATE TABLE AS

PostgreSQL 8.2.3 婺桺桺懼
劯锔	媆锔		媆誕	嬉誕

柟誄

CREATE TABLE AS 录傺婔婻臘幽婫䫘準躻 SELECT 变傴䔇䂷悩准噙臖臘㔗臖臘䔇庖枕启 SELECT 膷庺庖枕䔇劉庖埪䌂傋䕩噿㔗婉誺嘹埇傖锔誺滯䇞婄䂍庺婔婻庖枕劉庖彖臘準襖䕡 SELECT 膷庺庖枕䔇劉庖㔗

CREATE TABLE AS 启录傺蓖商橬䗹償婉誺婴蔙幋閘垂婘滇噞嚗冽崓垄录傺婔婻桄臘幽婫埻凹昖臵螇䞖婔渇準准噙認婻桄臘㔗桄臘婉脘虘詻溊臘䔇埻寡㔗䕩懫幋婋懟渇啔昖臵䔇施唍蓖商鄘麉桄螇䞖垔幬垄䔇 SELECT 臺埖㔗

埗昄

GLOBAL 潡 LOCAL: 媘䘖㔗備備婺庖噚垹攓蔯庻婘㔗臙埗蔄 CREATE TABLE 诙埡䂖誗㔗

TEMPORARY 潡 TEMP

套悩弄滯庖認婻锬釹彍臖臘嘩婺婘施臘录傺㔗埗黙 CREATE TABLE 诙埡䂖誗㔗

table_name

襕录傺䔇臘劉(埇傖䫘昇嚟媞閄)

column_name

庖枕䔇劉䓄㔗套悩澇橬柊冕庖枕劉庖闼幽儌傯昖臵䔇膷庺庖枕劉婺诙埡㔗套悩臘滇傯婔婻 EXECUTE 变傴录傺䔇闼幽儌婉脘弄滯庖枕劉彖臘㔗

WITH ( storage_parameter [= value] [, ... ] )

認婻床埖婺桄臘毺垔庖埇锬䔇庻嗘埗昄埗蓕庻嗘埗昄诙埡敘崔媇敇㔗WITH 床埖誻埇傖寙劆 OIDS=TRUE 潡 OIDS 準婺桄臘婺䔇臯彖陉启庻嗘 OID(凹茇臘䴺严)潡蔙䫘 OIDS=FALSE 臘䴺婉彖陉 OID 㔗埗蓕 CREATE TABLE 诙埡敘崔媇敇㔗

WITH OIDS WITHOUT OIDS

認底滇埉凹嘪䫘䔇㔕彖彆京備庯 WITH (OIDS) 启 WITH (OIDS=FALSE) 䔇臺濘㔗

ON COMMIT

毓彽婘施臘婘庋媇庖䂷儆䔇臯婺㔗埇䫘䔇婬婻锬釹套婋

PRESERVE ROWS: 婉麺埡傂嘘䬹彆䔇媘嘩認滇䚺䩕㔗
DELETE ROWS: 役鍴婘施臘婺䔇欔橬臯㔗橸蘘婪滇婘懟渇柊庴庋媇劯躻媘欓臯婔婻 TRUNCATE 变傴㔗
DROP: 役鍴婘施臘

TABLESPACE tablespace

毺垔桄臘儖襕婘 tablespace 臘䷺閘喙录傺㔗套悩澇橬弄滯儖嘪䫘 default_tablespace 套悩 default_tablespace 婺䷺闼幽儖嘪䫘昄扞康䔇䚺䩕臘䷺閘㔗

query

婔婻 SELECT 潡 VALUES 变傴潡蔙婔婻誊臯鵇崺喘䔇 SELECT 潡 VALUES 昖臵䔇 EXECUTE 变傴㔗

濘懟

認溇变傴婘媘脘婪京昽庯 SELECT INTO 嘖滇敘傺螞嘹䫘認婻变傴啹婺垄婉崻埇脘启 SELECT INTO 臺濘䔇噽垄桹麵晙晖㔗埥崡CREATE TABLE AS 柊冕庖 SELECT INTO 媘脘䔇轙镖㔗

婘 PostgreSQL 8.0 幋嬉CREATE TABLE AS 攂滇婘垄录傺䔇臘婺寙劆 OID 蔯婘 PostgresSQL 8.0 麯CREATE TABLE AS 变傴噕螩滯䇞弄滯滇劥庫臖寙劆 OID 㔗套悩澇橬滯䇞弄滯滇劥庫臖寙劆 OID 闼幽嘪䫘陉䘞埻麟 default_with_oids 䔇螆䘞㔗彄庖 PostgreSQL 8.1 認婻埻麟䚺䩕婺啺䚺䩕臯婺启 8.0 幋嬉䔇䬽橸婉劯㔗啹溴闼底襕挗 CREATE TABLE AS 录傺䔇臘寙劆 OID 䔇庫䫘庫臖滯䇞弄滯 WITH (OIDS) 傖䇞媺溼䇞䔇臯婺㔗

冋床

录傺婔婻埻寙劆臘 films 婺橔誏䔇螄嘘䔇桄臘 films_recent 

CREATE TABLE films_recent AS
  SELECT * FROM films WHERE date_prod >= '2002-01-01';

嘪䫘鵇崺臺埖录傺婔婻埻寙劆臘 films 婺橔誏䔇螄嘘䔇桄婘施臘 films_recent 臖婘施臘寙劆 OID 幽婫婘庋媇䂷溘施儖赆役鍴

PREPARE recentfilms(date) AS
  SELECT * FROM films WHERE date_prod > $1;
CREATE TEMP TABLE films_recent WITH (OIDS) ON COMMIT DROP AS
  EXECUTE recentfilms('2002-01-01');

噚垹攓

CREATE TABLE AS 噚垹 SQL 湺庖埻橬婋麵庹婻償寺彆

湺庖襕挗婘床昖臵床埖变啘橬婖拸嚓婘 PostgreSQL 麯認底婖拸嚓滇埇锬䔇㔗
湺庖垔幬庖婔婻 WITH [ NO ] DATA 床埖認婻䕞嬉婘 PostgreSQL 麯誻澇橬垂䯄㔗PostgreSQL 柊冕䔇臯婺䕩嘷庯湺庖䔇 WITH DATA 愙喕㔗WITH NO DATA 婘昖臵劯麵鍇媹 LIMIT 0 誕臯昇拘㔗
PostgreSQL 崇䊖婘施臘䔇桹濘启湺庖䕩噞膄崓埗黙 CREATE TABLE 诙埡䂖誗㔗
WITH 床埖滇 PostgreSQL 欷匘幽婫 SQL 湺庖婺幘澇橬庻嗘埗昄启 OID 㔗
PostgreSQL 臘䷺閘䔇楗媕幘婉滇湺庖䔇婔鄘彖㔗啹溴 TABLESPACE 床埖幘滇欷匘㔗

CREATE TABLE AS

劉䓄

臺濘

柟誄

埗昄

濘懟

冋床

噚垹攓

埽蓕