{"id":11672,"date":"2021-09-29T18:11:30","date_gmt":"2021-09-29T09:11:30","guid":{"rendered":"http:\/\/www.gisdeveloper.co.kr\/?p=11672"},"modified":"2021-09-29T18:13:08","modified_gmt":"2021-09-29T09:13:08","slug":"postgresql%ec%9d%98-array-%ed%83%80%ec%9e%85","status":"publish","type":"post","link":"http:\/\/www.gisdeveloper.co.kr\/?p=11672","title":{"rendered":"PostgreSQL\uc758 Array \ud0c0\uc785"},"content":{"rendered":"

\ud558\ub098\uc758 \ud544\ub4dc\uc5d0 \uc5ec\ub7ec \uac1c\uc758 \uac12\uc744 \uc800\uc7a5\ud574\uc57c \ud560 \ub54c Array \ud0c0\uc785\uc744 \uc0ac\uc6a9\ud558\uba74 \ub9e4\uc6b0 \uc720\uc6a9\ud569\ub2c8\ub2e4. \uc774 \uae00\uc740 PostgreSQL\uc5d0\uc11c Array \ud0c0\uc785\uc5d0 \ub300\ud55c \ub0b4\uc6a9\uc744 \uc815\ub9ac\ud569\ub2c8\ub2e4.<\/p>\n

\uba3c\uc800 Array \ud0c0\uc785\uc744 \uac00\uc9c0\ub294 \ud14c\uc774\ube14\uc744 \uc0dd\uc131\ud569\ub2c8\ub2e4.<\/p>\n

\r\nCREATE TABLE contacts (\r\n\tid serial PRIMARY KEY,\r\n\tname VARCHAR (100),\r\n\tphones TEXT[]\r\n);\r\n<\/pre>\n
\uc774 \ud14c\uc774\ube14\uc5d0 \ud55c\uac1c\uc758 Row\ub97c \ucd94\uac00\ud558\ub294 SQL\ubb38\uc758 \uc608\ub294 \ub2e4\uc74c\uacfc \uac19\uc2b5\ub2c8\ub2e4.<\/p>\n
\r\nINSERT INTO contacts (name, phones)\r\nVALUES('\ud64d\uae38\ub3d9', ARRAY [ '031-132-7890','02-678-9876' ]);\r\n<\/pre>\n
\ud55c\ubc88\uc5d0 \uc5ec\ub7ec \uac1c\uc758 Row\ub97c \ucd94\uac00\ud558\ub294 SQL\ubb38\uc740 \ub2e4\uc74c \uc608\uc640 \uac19\uc2b5\ub2c8\ub2e4.<\/p>\n
\r\nINSERT INTO contacts (name, phones)\r\nVALUES\r\n    ('\uc77c\uc9c0\ub9e4', ARRAY [ '02-789-5432' ]),\r\n    ('\uae40\uac11\ud658', '{\"010-4567-8765\", \"063-123-1234\"}'),\r\n    ('\uae40\ud615\uc900', '{\"063-432-8765\", \"010-4567-8765\"}');\r\n<\/pre>\n
\ud14c\uc774\ube14\uc758 \ub370\uc774\ud130\ub97c \ud655\uc778\ud574 \ubcf4\uae30 \uc704\ud574 \ub2e4\uc74c SQL\ub97c \uc2e4\ud589\ud574 \ubd05\ub2c8\ub2e4.<\/p>\n
\r\nSELECT name, phones FROM contacts;\r\n<\/pre>\n
\"\"<\/p>\n
\ubc30\uc5f4\uc758 \ud2b9\uc815 \uc778\ub371\uc2a4\ub97c \uc9c0\uc815\ud574\uc11c \uc870\ud68c\ub97c \ud560 \uc218\ub3c4 \uc788\uc2b5\ub2c8\ub2e4. \ub2e4\uc74c\ucc98\ub7fc \ubc30\uc5f4\uc758 2\ubc88\uc9f8 \uac12\ub9cc\uc744 \uc870\ud68c\ud569\ub2c8\ub2e4.<\/p>\n
\r\nSELECT name, phones[2] FROM contacts;\r\n<\/pre>\n
\uacb0\uacfc\ub294 \ub2e4\uc74c\uacfc \uac19\uc2b5\ub2c8\ub2e4.<\/p>\n
\"\"<\/p>\n
\ub2e4\uc74c\uc740 \ubc30\uc5f4\uc758 \uac12 \uc911 2\ubc88\uc9f8\uc5d0 \ub300\ud574 \ud2b9\uc815\uac12\uacfc \uc77c\uce58\ud558\ub294 \ub0b4\uc6a9\uc744 \uc870\ud68c\ud558\ub294 SQL \ubb38\uc785\ub2c8\ub2e4.<\/p>\n
\r\nSELECT name, phones FROM contacts WHERE phones[2] = '010-4567-8765';\r\n<\/pre>\n
\uacb0\uacfc\ub294 \ub2e4\uc74c\uacfc \uac19\uc2b5\ub2c8\ub2e4.<\/p>\n
\"\"<\/p>\n
\ub2e4\uc74c\uc740 \ubc30\uc5f4\uc744 \uad6c\uc131\ud558\ub294 \ubaa8\ub4e0 \uac12 \uc911 \ud2b9\uc815\uac12\uacfc \uc77c\uce58\ud558\ub294 \ub0b4\uc6a9\uc744 \uc870\ud68c\ud558\ub294 SQL \ubb38\uc785\ub2c8\ub2e4.<\/p>\n
\r\nSELECT name, phones FROM contacts WHERE '010-4567-8765' = ANY(phones);\r\n<\/pre>\n
\uacb0\uacfc\ub294 \ub2e4\uc74c\uacfc \uac19\uc2b5\ub2c8\ub2e4.<\/p>\n
\"\"<\/p>\n
\uadf8\ub807\ub2e4\uba74 \ubc30\uc5f4\uc744 \uad6c\uc131\ud558\ub294 \ubaa8\ub4e0 \uac12 \uc911 \ubd80\ubd84\uc801\uc73c\ub85c \uc77c\uce58\ud558\ub294 LIKE \uad6c\ubb38\uc744 \uc0ac\uc6a9\ud574 \ubcf8\ub2e4\uba74 \ub2e4\uc74c SQL\ucc98\ub7fc \uad6c\uc131\ub420 \uac83\uc785\ub2c8\ub2e4. (\uc791\ub3d9\ud558\uc9c0 \uc54a\uc74c)<\/p>\n
\r\nSELECT name, phones FROM contacts\r\nWHERE ANY(phones) LIKE '%32%';\r\n<\/pre>\n
\uc791\ub3d9\ud558\uc9c0 \uc54a\ub294\ub370\uc694. \uc774\ub294 \ub2e4\uc74c\uc5d0 \uc124\uba85\ud560 UNNEST \uba85\ub839\uc744 \uc0ac\uc6a9\ud574\uc57c \ud569\ub2c8\ub2e4. UNNEST\ub294 \ubc30\uc5f4\uc744 \uad6c\uc131\ud558\ub294 \uac12\ub4e4\uc744 \ub3c5\ub9bd\uc2dc\ucf1c \ud558\ub098\uc758 Row\ub85c \uad6c\uc131\ud574 \uc90d\ub2c8\ub2e4. \ub2e4\uc74c SQL \ubb38\uc744 \ud1b5\ud574 \uacb0\uacfc\ub97c \ubcf4\uc2dc\uba74 \uc774\ud574\uac00 \uc26c\uc6b8\uac83\uc785\ub2c8\ub2e4.<\/p>\n
\r\nSELECT name, UNNEST(phones) FROM contacts;\r\n<\/pre>\n
\uacb0\uacfc\ub294 \ub2e4\uc74c\uacfc \uac19\uc2b5\ub2c8\ub2e4.<\/p>\n
\"\"<\/p>\n
\uc774\uc81c \uc55e\uc11c \ubb38\uc81c\uac00 \ub41c LIKE \uad6c\ubb38\uc744 UNNEST\ub97c \uc0ac\uc6a9\ud558\uba74 \ub2e4\uc74c\uacfc \uac19\uc2b5\ub2c8\ub2e4.<\/p>\n
\r\nSELECT name, phones\r\nFROM (\r\n    SELECT name, UNNEST(phones) phone, phones\r\n    FROM contacts) x\r\nWHERE phone LIKE '%32%';\r\n<\/pre>\n
\uacb0\uacfc\ub294 \ub2e4\uc74c\uacfc \uac19\uc2b5\ub2c8\ub2e4.<\/p>\n
\"\"<\/p>\n
\ubc30\uc5f4 \uac12 \uc911 \ud2b9\uc815 \uc21c\uc11c\uc758 \uac12 \ub9cc\uc744 \ubcc0\uacbd\ud560 \uc218\ub3c4 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n
\r\nUPDATE contacts\r\nSET phones[4] = '010-9999-9999'\r\nWHERE name = '\uae40\uac11\ud658';\r\n<\/pre>\n
SQL\ubb38\uc73c\ub85c name\uc774 ‘\uae40\uac11\ud658’\uc778 \uac83\ub9cc \uc870\ud68c\ud574 \ubcf4\uba74 \ub2e4\uc74c\uacfc \uac19\uc2b5\ub2c8\ub2e4.<\/p>\n
\"\"<\/p>\n
\ubcf4\uc2dc\uba74 \uc6d0\ub798\uc758 \ubc30\uc5f4\uc758 \uad6c\uc131 \uac12\uc758 \uac1c\uc218\uac00 2\uac1c\uc600\ub294\ub370 4\ubc88\uc9f8\uc5d0 \uac12\uc744 \ucd94\uac00\ud558\uc5ec 3\ubc88\uc9f8\ub294 NULL\ub85c \uc9c0\uc815\ub41c \uac83\uc744 \ud655\uc778\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n
\ub9c8\uc9c0\ub9c9\uc73c\ub85c Array \ud0c0\uc785\uc758 \ud544\ub4dc\uc5d0 \ub300\ud55c Index\ub97c \uac70\ub294 \uad6c\ubb38\uc740 \ub2e4\uc74c\uacfc \uac19\uc2b5\ub2c8\ub2e4.<\/p>\n
\r\nCREATE INDEX idx_phones on contacts USING GIN (phones);\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
\ud558\ub098\uc758 \ud544\ub4dc\uc5d0 \uc5ec\ub7ec \uac1c\uc758 \uac12\uc744 \uc800\uc7a5\ud574\uc57c \ud560 \ub54c Array \ud0c0\uc785\uc744 \uc0ac\uc6a9\ud558\uba74 \ub9e4\uc6b0 \uc720\uc6a9\ud569\ub2c8\ub2e4. \uc774 \uae00\uc740 PostgreSQL\uc5d0\uc11c Array \ud0c0\uc785\uc5d0 \ub300\ud55c \ub0b4\uc6a9\uc744 \uc815\ub9ac\ud569\ub2c8\ub2e4. \uba3c\uc800 Array \ud0c0\uc785\uc744 \uac00\uc9c0\ub294 \ud14c\uc774\ube14\uc744 \uc0dd\uc131\ud569\ub2c8\ub2e4. CREATE TABLE contacts ( id serial PRIMARY KEY, name VARCHAR (100), phones TEXT[] ); \uc774 \ud14c\uc774\ube14\uc5d0 \ud55c\uac1c\uc758 Row\ub97c \ucd94\uac00\ud558\ub294 SQL\ubb38\uc758 \uc608\ub294 \ub2e4\uc74c\uacfc \uac19\uc2b5\ub2c8\ub2e4. INSERT INTO contacts (name, phones) … <\/p>\n
\ub354 \ubcf4\uae30 “PostgreSQL\uc758 Array \ud0c0\uc785”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-11672","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/posts\/11672","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=11672"}],"version-history":[{"count":13,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/posts\/11672\/revisions"}],"predecessor-version":[{"id":11692,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/posts\/11672\/revisions\/11692"}],"wp:attachment":[{"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=11672"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=11672"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=11672"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}