8.10. Arrays

8.10.1. 昄䂇䌂傋䔇弄滯

婺臘滯認底䫘濘潏傸噽录傺婔婻䫌嘺橸䌂傋昄䂇悇潊䔇臘

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

套婪欔䴺婔婻昄䂇䌂傋滇锔誺婘昄䂇噄䘹䌂傋劉劯麵鍇媹桹拸嚓([])準变劉䔇㔗婪麵䔇变傴儖录傺婔婻埆 sal_emp 䔇臘臘䴺镺叻劉庖䔇 name 庖枕滇婔婻 text 䌂傋庖严婾臘䴺镺叻庼庥衻愘䔇 pay_by_quarter 庖枕滇婔婻婔䂘 integer 昄䂇臘䴺镺叻变螇彐䔇 schedule 庖枕滇婔婻婴䂘 text 昄䂇㔗

CREATE TABLE 䔇臺濘噕螩弄滯昄䂇䔇䇞彺崓償懫套

CREATE TABLE tictactoe (
    squares   integer[3][3]
);

婉誺䕞嬉䔇垂䯄幽婉嚺彽昄䂇儺凩鍊彽(京備庯橻弄滯阪庥䔇昄䂇)㔗垂鍙婪䕞嬉䔇垂䯄幘婉嚺彽昄䂇䂘昄㔗䬹垔噄䘹䌂傋䔇昄䂇鄘赆螴婺滇䕩劯䔇䌂傋婉䞇傡傸䔇崓償潡蔙䂘昄㔗啹溴婘 CREATE TABLE 麯垔幬昄庖潡蔙䂘昄鄘婉嘌巉誊臯施䔇臯婺㔗

埥崡誻橬婔䓉臺濘垄镕冻 SQL 湺庖埇傖䫘庯弄滯婔䂘昄䂇㔗pay_by_quarter 埇傖垔幬婺

    pay_by_quarter  integer ARRAY[4],

認婻臺濘襕挗婔婻昘昄婩麟臘䴺昄䂇儺凩㔗婉誺套嬉欔誄PostgreSQL 幽婉嚺彽認婻儺凩鍊彽㔗

8.10.2. 昄䂇唚膷噖

儖昄䂇喍潊桺橸䔇施唍䫘誌拸嚓檪昄䂇噄䘹拸蕙準幽婫䫘锖埙儖垄傸彖嚔(套悩嘹溗 C 闼幽認婯彺哋寡婔婻䂷悇冽償)㔗嘹埇傖婘昄䂇噄䘹唚变啘櫆䘞埯嚘埙嘖套悩認婻唚寙劆锖埙潡蔙誌拸嚓闼幽儌媙釂媹婪埯嚘埙(婋麵橬敘崔䂖誗)㔗啹溴婔婻昄䂇婩麟䔇婩蓕湚嚟套婋

'{ val1 delim val2 delim ... }'

認麯䔇 delim 滇臖䌂傋䔇彖锫严儌滇婘臖䌂傋䔇 pg_type 螄嘘婺毺垔䔇闼婻㔗婘 PostgreSQL 埏婄柊冕䔇湺庖昄扞䌂傋麯鍴庖 box 䌂傋嘪䫘彖埙(;)幋崡噽垄欔橬䌂傋鄘嘪䫘锖埙(,)㔗懟婻 val 襕幽滇婔婻昄䂇噄䘹䌂傋䔇婩麟襕幽滇婔婻床昄䂇㔗婔婻昄䂇婩麟䔇冋床套婋

'{{1,2,3},{4,5,6},{7,8,9}}'

認婻婩麟滇婔婻 3 幻 3 䔇婴䂘昄䂇䫌婬婻昘昄床昄䂇䂇潊㔗

襕儖婔婻昄䂇噄䘹䔇唚螆婺 NULL 䕘毖喍婪 NULL 剿埇(崓償喍方噿)㔗襕儖婔婻昄䂇噄䘹䔇唚螆婺庖严婾"NULL"闼幽嘹媙釂媹婪埯嚘埙㔗

認䓉昄䂇婩麟垂鍙婪埻滇潏傸婘誗4.1.2.5麯螘螺誺䔇婔轸䌂傋婩麟䔇婔䓉䬹冋㔗婩麟橔彺滇嘷嘩庖严婾䩋写幽婫嚹锐䂍昄䂇膷噖蘸扵単䔇埇脘驔襕嘪䫘滯䇞䔇䌂傋弄滯㔗

䯄婘潏傸埇傖匘䴺婔底 INSERT 臺埖㔗

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

嬉麵䔇婴婻某噖䔇䂷悩䩋蕙準償認湙

SELECT * FROM sal_emp;
 name  |      pay_by_quarter       |                 schedule
-------+---------------------------+-------------------------------------------
 Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
 Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
(2 rows)

潏傸誻埇傖嘪䫘 ARRAY 悇锹単臺濘

INSERT INTO sal_emp
    VALUES ('Bill',
    ARRAY[10000, 10000, 10000, 10000],
    ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);

INSERT INTO sal_emp
    VALUES ('Carol',
    ARRAY[20000, 25000, 25000, 25000],
    ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);

臙濘懟昄䂇噄䘹滇捞锔䔇 SQL 婩麟潡蔙臘膆嚟懫套庖严婾桺橸滇䫘剘嚘埙寙啘䔇蔯婉滇償昄䂇桺橸闼湙䫘埯嚘埙㔗ARRAY 悇锹単臺濘婘誗4.2.10麯橬敘臥䂖䔇螘螺㔗

崔䂘昄䂇媙釂对陉懟婻䂘䔇噄䘹昄㔗套悩婉对陉儖凚躘髍臇

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"meeting"}}');
ERROR:  multidimensional arrays must have array expressions with matching dimensions

8.10.3. 螪閞昄䂇

䯄婘潏傸埇傖婘認婻臘婪誊臯婔底昖臵㔗饡噽潏傸暫䴺套嘘婔渇螪閞昄䂇䔇婔婻噄䘹㔗認婻昖臵演䘵婘丸庯庼庥衻愘埻寡䔇镺叻劉

SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];

 name
-------
 Carol
(1 row)

昄䂇䔇婋湺昄庖滇喍婘桹拸嚓喙䔇㔗PostgreSQL 䚺䩕嘪䫘傖 1 婺嘺䔇昄䂇幹愇幘儌滇臘婔婻 n 噄䘹䔇昄䂇傯 array[1] 嚔哋彄 array[n] 䂷溘㔗

認婻昖臵演䘵欔橬镺叻丸婬庼庥䔇衻愘

SELECT pay_by_quarter[3] FROM sal_emp;

 pay_by_quarter
----------------
          10000
          25000
(2 rows)

潏傸誻埇傖螪閞婔婻昄䂇䔇傂懟䘷嘵䬺枕潡䓄床昄䂇㔗凹庯婔䂘潡敘崔䂘昄䂇埇傖䫘 婋湺婋䘯:婋湺婪䘯 臘䴺婔婻昄䂇䔇昊婻䬺枕㔗懫套婋麵昖臵演䘵 Bill 臖变崘婴崷䔇丸婔傽螇彐

SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';

        schedule
------------------------
 {{meeting},{training}}
(1 row)

潏傸誻埇傖認湙喍

SELECT schedule[1:2][1] FROM sal_emp WHERE name = 'Bill';

幘埇傖诙埡劯湙䔇䂷悩㔗套悩儖婋湺喍潊 lower:upper 䔇嘵嚟闼幽傂嘘昄䂇婋湺淉嘩攂滇嘷啔婔婻昄䂇䬺枕凹写㔗套悩埻弄滯庖婔婻昄唚闼幽鄘滇啺螆婋䘯婺 1 懫套

SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';

                 schedule
-------------------------------------------
 {{meeting,lunch},{training,presentation}}
(1 row)

傯婔婻昄䂇䔇嘷嬉评啘幋崡檷埡昄扞儖䫘潊婔婻 NULL 蔯婉滇凚躘髍臇㔗懫套套悩 schedule 䕞嬉䔇䂘滇 [1:3][1:2] 䇽劯潏傸檷埡 schedule[3][3] 嚔䫘潊 NULL 㔗䌂嚚䔇誻橬婔婻婋湺髍臇䔇昄䂇嚘䫘幘䫘潊 NULL 蔯婉滇髍臇㔗

套悩昄䂇橸躆潡傂嘘婋湺臘膆嚟滇 NULL 闼幽臖昄䂇䔇䬺枕臘膆嚟幘儖䫘潊 NULL 㔗嘖婘噽垄噽垄愙喕婋懫套檷埡婔婻垯噘婘昄䂇䔇嘷嬉评啘幋崡䔇昄䂇䬺桺儖䫘潊婔婻䷺昄䂇(镽䂘)蔯婉滇 NULL 㔗套悩檷埡䔇䬺桺鄘彖襖䕡昄䂇䔇评啘闼幽垄嚔躻媘䚷废婺檷埡襖䕡䔇评啘㔗

傂嘘昄䂇䔇嘷嬉䂘昄鄘埇傖䫘 array_dims 庘昄演䘵

SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';

 array_dims
------------
 [1:2][1:1]
(1 row)

array_dims 䫘潊婔婻 text 䂷悩凹庯庺䌂埇脘懫膄垹滷黙臂嘖滇凹庯䘋废埇脘儌婉闼幽桹冪庖㔗潏傸幘埇傖䫘 array_upper 启 array_lower 庘昄彖彆誫啂昄䂇䬹垔䂘䔇婪䘯启婋䘯㔗

SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';

 array_upper
-------------
           2
(1 row)

8.10.4. 媞櫹昄䂇

婔婻昄䂇唚埇傖垯噘赆傼敪

UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
    WHERE name = 'Carol';

潡蔙嘪䫘 ARRAY 悇锹単臺濘

UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
    WHERE name = 'Carol';

潡蔙埻滇敘桄昊婔婻噄䘹

UPDATE sal_emp SET pay_by_quarter[4] = 15000
    WHERE name = 'Bill';

潡蔙敘桄昊婻䬺桺

UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
    WHERE name = 'Carol';

埇傖锔誺䂍婔婻儔婉庻婘昄䂇噄䘹蕋唚䔇媂濘欷崓昄䂇欔橬嘉庯寘昄䂇橔劯婔婻噄䘹启認婻桄噄䘹幋閘䔇橻蕋唚噄䘹鄘儖螆婺 NULL 㔗冋套套悩 myarray 昄䂇嘷嬉橬 4 婻噄䘹婘凹 myarray[6] 蕋唚幋劯垄儖拖橬 6 婻噄䘹噽婺 myarray[5] 䔇唚儖婺 NULL 㔗䕞嬉埻噕螩凹婔䂘昄䂇嘪䫘認䓉桹濘欷崓(凹崔䂘昄䂇臯婉锔)㔗

婋湺蕋唚噕螩录傺婋湺婉傯 1 嚔哋䔇昄䂇㔗懫套潏傸埇傖䂍 myarray[-2:7] 蕋唚录傺婔婻婋湺唚婘 -2 彄 7 幋閘䔇昄䂇㔗

桄䔇昄䂇唚幘埇傖䫘誂毖淉嘩严 || 悇锹㔗

SELECT ARRAY[1,2] || ARRAY[3,4];
 ?column?
-----------
 {1,2,3,4}
(1 row)

SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];
      ?column?
---------------------
 {{5,6},{1,2},{3,4}}
(1 row)

誂毖淉嘩严噕螩檪婔婻噄䘹寋噖婔䂘昄䂇䔇嚔崘潡蔙䂷儆㔗垄誻毖埖婴婻 N 䂘䔇昄䂇潡蔙婔婻 N 䂘启婔婻 N+1 䂘䔇昄䂇㔗

嘷劏婔䂘昄䂇䔇崘鄘潡儆鄘寋噖剘䋸婔婻噄䘹劯昄䂇䔇婋湺婋䘯媺毕婉埻㔗懫套

SELECT array_dims(1 || '[0:1]={2,3}'::int[]);
 array_dims
------------
 [0:2]
(1 row)

SELECT array_dims(ARRAY[1,2] || 3);
 array_dims
------------
 [1:3]
(1 row)

套悩儖婴婻䕩劯䂘昄䔇昄䂇誂毖婘婔蕙䂷悩昄䂇儖媺毕噥淉嘩昄䔇崡北䂘昄䔇婋湺婋䘯㔗䂷悩滇認湙婔婻昄䂇寙劆噥淉嘩昄䔇懟婻噄䘹劯麵虘五埿淉嘩昄䔇懟婻噄䘹㔗懫套

SELECT array_dims(ARRAY[1,2] || ARRAY[3,4,5]);
 array_dims
------------
 [1:5]
(1 row)

SELECT array_dims(ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8],[9,0]]);
 array_dims
------------
 [1:5][1:2]
(1 row)

套悩儖婔婻 N 䂘䔇昄䂇寋彄婔婻 N+1 䂘昄䂇䔇嚔崘潡蔙䂷儆䂷悩启婪麵昄䂇噄䘹䔇愙喕䌂嚚㔗懟婻 N 䂘䔇床昄䂇垂鍙婪鄘滇 N+1 䂘昄䂇䔇橔崡北䔇噄䘹㔗懫套

SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]);
 array_dims
------------
 [1:3][1:2]
(1 row)

昄䂇幘埇傖䫘 array_prepend, array_append, array_cat 庘昄悇锹㔗嬉婴婻埻櫇毕婔䂘昄䂇蔯 array_cat 櫇毕崔䂘昄䂇㔗臙濘懟嘪䫘婪麵螘螺䔇誂毖淉嘩严襕懫䕘毖嘪䫘認底庘昄喘㔗垂鍙婪認底庘昄婂襕䫘庯垂䯄誂毖淉嘩严㔗婉誺婘䫘潙垔幬䔇录傺庘昄麯䕘毖嘪䫘傡傸埇脘橬媙襕㔗婔底冋床

SELECT array_prepend(1, ARRAY[2,3]);
 array_prepend
---------------
 {1,2,3}
(1 row)

SELECT array_append(ARRAY[1,2], 3);
 array_append
--------------
 {1,2,3}
(1 row)

SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
 array_cat
-----------
 {1,2,3,4}
(1 row)

SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);
      array_cat
---------------------
 {{1,2},{3,4},{5,6}}
(1 row)

SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);
      array_cat
---------------------
 {{5,6},{1,2},{3,4}}

8.10.5. 婘昄䂇婺演䘵

襕抩䘵婔婻昄䂇婺䔇昄唚嘹媙釂演昖臖昄䂇䔇懟婔婻唚㔗嘹埇傖欋噖崇䊖(套悩嘹䘖長昄䂇儺凩)㔗懫套

SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
                            pay_by_quarter[2] = 10000 OR
                            pay_by_quarter[3] = 10000 OR
                            pay_by_quarter[4] = 10000;

婉誺凹庯崓昄䂇蔯蘔認婻桹濘冽媆儌嚔螷庺蓬冖方蕪幽婫套悩嘹婉䘖長昄䂇儺凩闼儌澇傔幽䫘庖㔗埥崡婔婻桹濘婘誗9.17麯柟誄㔗婪麵䔇昖臵埇傖䫘婋麵䔇傼敪

SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);

埥崡嘹埇傖䫘婋麵䔇臺埖欆庺昄䂇婺欔橬噄䘹唚鄘京庯 10000 䔇臯

SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

8.10.6. 昄䂇膷噖启膷庺臺濘

婔婻昄䂇唚䔇崡鄘臘䯄嘵嚟䫌婔底湹扞臖昄䂇噄䘹䌂傋䔇 I/O 蘸扵蓇彍彖悊䔇釹䂇潊喉媹婪婔底湺滯臖昄䂇䂷悇䔇媞閄㔗認底媞閄䫌啘䂘婘昄䂇唚变啘䔇誌拸嚓({ 启 })媹婪䕩闂釹幋閘䔇彖锫庖严䂇潊㔗彖锫庖严锔婩滇婔婻锖埙(,)嘖幘埇傖滇噽垄䔇婩薪垄䫌臖昄䂇噄䘹䌂傋䔇 typdelim 螆䘞喿垔㔗婘 PostgreSQL 柊冕䔇湺庖昄扞䌂傋麯鍴庖 box 䌂傋嘪䫘彖埙(;)崡欔橬噽垄䌂傋鄘嘪䫘锖埙㔗婘崔䂘昄䂇麯懟婻䂘鄘橬躻噌亓彆䔇誌拸嚓幽婫婘劯亓䕩闂䔇誌拸嚓釹幋閘媙釂喍婪彖锫严㔗

套悩昄䂇噄䘹唚滇䷺庖严婾潡蔙寙劆誌拸嚓㔕彖锫严㔕埯嚘埙㔕埉桩溹㔕䷺䍘潡蔙对陉噿髞庖 NULL 闼幽昄䂇膷庺誺䘋儖婘認底唚变啘寙啘埯嚘埙㔗婘噄䘹唚麯寙劆䔇埯嚘埙启埉桩溹儖赆埉桩溹锄锩㔗凹庯昄唚昄扞䌂傋嘹埇傖垬噘婄啺螆昄唚澇橬埯嚘埙寙啘嘖滇凹庯桺橸䌂傋潏傸儌驔襕庖崺喘麵凹橬埯嚘埙寙啘启澇橬埯嚘埙寙啘婴䓉愙喕庖㔗

䚺䩕施婔婻昄䂇䔇昊䂘䔇婋湺䘵嚘滇螆䘞婺 1 䔇㔗套悩婔婻昄䂇䔇昊䂘䔇婋湺婉京庯 1 闼幽儌嚔婘昄䂇䂷悇媞閄嘘麯麵櫆䘞婔婻垂鍙䔇䂘昄㔗認婻媞閄䫌桹拸嚓([])啘䂘婘懟婻昄䂇䂘䔇婋䘯启婪䘯䘵嚘婺閘橬婔婻喐埙(:)彖锫䔇庖严婾䂇潊㔗昄䂇䂘昄媞閄劯麵虘五婔婻京埙淉嘩严(=)㔗懫套

SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
 FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss;

 e1 | e2
----+----
  1 |  6
(1 row)

備嘷婔婻潡崔婻婋䘯婉京庯 1 施昄䂇膷庺䘋废欉婘䂷悩婺寙劆滯䇞䔇儺凩㔗

套悩婔婻昄䂇噄䘹䔇唚喍潊 NULL(方螺崓償喍套嘘)闼幽臖噄䘹䔇唚儌滇 NULL 㔗蔯嚘埙启埉桩溹埇傖臘䴺膷噖桺橸庖严婾"NULL"唚㔗埥崡婺庖噚垹 8.2 幋嬉䔇䬽橸埇傖儖array_nulls 陉䘞埗昄螆婺 off 傖䥕溵儖 NULL 臖彆婺 NULL 㔗

套嬉欔䴺嘷幥喍婔婻昄䂇唚䔇施唍埇傖婘傂嘘噄䘹唚变啘媹婪埯嚘埙㔗嘷噄䘹唚埇脘螷昄䂇唚蓼悊単库䫘溓幬施嘹媙釂認幽啔㔗冋套噄䘹唚寙劆誌拸埙㔕锖埙(潡蔙滇噽垄彖嬾严)㔕埯嚘埙㔕埉桩溹㔕婘嚔崘/䂷儆崇橬䷺䍘严㔕对陉 NULL 䔇庖严婾㔗襕婘噄䘹唚婺寙劆埯嚘埙潡埉桩溹埇傖媹婔婻嬉凚埉桩溹㔗嘷䇽嘹幘埇傖嘪䫘埉桩溹锄锩準媺檴傂嘘埇脘嚘蕙臺濘晙晖䔇庖严㔗

嘹埇傖婘噥誌拸嚓嬉麵潡蔙埿誌拸嚓劯麵喍䷺䍘㔗嘹誻埇傖婘傂懟䋸䆋䔇釹庖严婾嬉麵潡蔙劯麵喍䷺䍘㔗欔橬認底愙喕婋認底䷺䍘鄘嚔赆媘䘖㔗婉誺婘埯嚘埙寙啘䔇噄䘹麯麵䔇䷺䍘潡蔙滇噄䘹麯赆婴膹麂䷺䍘庖严寙啘䔇䷺䍘鄘婉嚔赆媘䘖㔗