{"id":4841,"date":"2014-12-09T09:00:09","date_gmt":"2014-12-09T00:00:09","guid":{"rendered":"http:\/\/www.techscore.com\/blog\/?p=4841"},"modified":"2018-11-14T16:33:50","modified_gmt":"2018-11-14T07:33:50","slug":"postgresql%e3%83%91%e3%83%bc%e3%83%86%e3%82%a3%e3%82%b7%e3%83%a7%e3%83%8b%e3%83%b3%e3%82%b0%e3%81%ae%e3%82%b9%e3%82%b9%e3%83%a1","status":"publish","type":"post","link":"https:\/\/www.techscore.com\/blog\/2014\/12\/09\/postgresql%e3%83%91%e3%83%bc%e3%83%86%e3%82%a3%e3%82%b7%e3%83%a7%e3%83%8b%e3%83%b3%e3%82%b0%e3%81%ae%e3%82%b9%e3%82%b9%e3%83%a1\/","title":{"rendered":"PostgreSQL\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30cb\u30f3\u30b0\u306e\u30b9\u30b9\u30e1"},"content":{"rendered":"<p>\u3053\u3093\u306b\u3061\u306f \u6751\u4e0a\u3067\u3059\u3002<\/p>\n<p>\u3053\u306e\u8a18\u4e8b\u306f<a title=\"TECHSCORE Advent Calendar 2014\" href=\"http:\/\/www.techscore.com\/blog\/2014\/11\/27\/techscore-advent-calendar-2014\/\">TECHSCORE Advent Calendar 2014<\/a>\u30019\u65e5\u76ee\u306e\u8a18\u4e8b\u3067\u3059\u3002<\/p>\n<p>\u672c\u65e5\u306f\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30cb\u30f3\u30b0\u306b\u3064\u3044\u3066\u66f8\u304d\u305f\u3044\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\n<h2>\u30c7\u30fc\u30bf\u306e\u5206\u5272<\/h2>\n<p>\u30b7\u30b9\u30c6\u30e0\u958b\u767a\u3092\u3059\u308b\u4e0a\u3067\u30c7\u30fc\u30bf\u3092\u5206\u5272\u3057\u305f\u3044\u6642\u304c\u3088\u304f\u3042\u308b\u3068\u601d\u3044\u307e\u3059\u3002<br \/>\n\u4f8b\u3048\u3070<\/p>\n<ol>\n<li>\u30de\u30eb\u30c1\u30c6\u30ca\u30f3\u30c8\u306a\u6642<\/li>\n<li>\u30b9\u30ad\u30fc\u30de\u3001\u30c6\u30fc\u30d6\u30eb\u30b9\u30da\u30fc\u30b9\u3067\u30c7\u30fc\u30bf\u3092\u5206\u3051\u305f\u3044<\/li>\n<li>\u30c7\u30fc\u30bf\u91cf\u5897\u52a0\u306b\u3088\u308a\u3001\u4f55\u304b\u306e\u30ad\u30fc\u3067\u30c6\u30fc\u30d6\u30eb\u3092\u5206\u3051\u305f\u3044<\/li>\n<\/ol>\n<p>\u306a\u3069\u3067\u3059\u3002<\/p>\n<p>\u4eca\u56de\u306f\u4e0a\u8a18\u306e3\u3064\u3081\u306b\u3064\u3044\u3066\u66f8\u304d\u307e\u3059\u3002<\/p>\n<h2>\u30c6\u30fc\u30d6\u30eb\u306e\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30cb\u30f3\u30b0<\/h2>\n<p>\u30c7\u30fc\u30bf\u3092\u5206\u5272(\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30cb\u30f3\u30b0)\u3057\u305f\u3044\u3082\u306e\u306b\u306f\u3069\u3093\u306a\u3082\u306e\u304c\u3042\u308b\u304b\uff1f<\/p>\n<p>\u4f8b\u3048\u3070\u3001\u5c65\u6b74\u30c7\u30fc\u30bf<br \/>\n\u30e6\u30fc\u30b6\u30fc\u306e\u5c65\u6b74\u30c7\u30fc\u30bf\u3092\u683c\u7d0d\u3059\u308b\u30c7\u30fc\u30bf\u306f\u65e5\u3092\u8ffd\u3046\u3054\u3068\u306b\u5897\u3048\u3066\u3044\u304d\u307e\u3059\u3002<\/p>\n<p>\u4ed6\u306b\u306f\u3001\u304a\u6c17\u306b\u5165\u308a\u5546\u54c1<br \/>\n\u3053\u306e\u5834\u5408\u3001\u6700\u5927\u3067\u300c\u30e6\u30fc\u30b6\u30fc\u6570 \u2715 \u5546\u54c1\u6570\u300d\u306e\u30c7\u30fc\u30bf\u304c\u683c\u7d0d\u3055\u308c\u307e\u3059\u3002<\/p>\n<p>\u3053\u3046\u3044\u3063\u305f\u30c6\u30fc\u30d6\u30eb\u306b\u5bfe\u3057\u3066\u306f\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30cb\u30f3\u30b0\u3092\u3059\u308b\u5fc5\u8981\u304c\u3067\u3066\u304d\u307e\u3059\u3002<\/p>\n<p>\u30c7\u30fc\u30bf\u3092\u5206\u5272\u3059\u308b\u5834\u5408\u306f\u3001\u5358\u7d14\u306b\u30c6\u30fc\u30d6\u30eb\u3092\u5206\u3051\u308c\u3070\u3044\u3044\u306e\u3067\u3059\u304c\u3001\u30a2\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u304b\u3089\u306f\u3081\u3093\u3069\u304f\u3055\u3044\u5b9f\u88c5\u304c\u5fc5\u8981\u306b\u306a\u308a\u307e\u3059\u3002<\/p>\n<p>\u305f\u3068\u3048\u3070\u3001\u5e74\u6708\u3067\u5224\u65ad\u3057\u3066\u691c\u7d22\u3059\u308b\u30c6\u30fc\u30d6\u30eb\u3092\u5224\u65ad\u3057\u305f\u308a\u3001\u5e74\u6708\u3092\u307e\u305f\u3050\u5834\u5408\u306f\u30c7\u30fc\u30bf\u3092\u7d71\u5408\u3057\u305f\u308a\u3002\u3002\u3002<\/p>\n<p>\u305d\u306e\u305f\u3081\u3001\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30cb\u30f3\u30b0\u306fDB\u30ec\u30a4\u30e4\u30fc\u3067\u884c\u3046\u306e\u304c\u30d9\u30b9\u30c8\u3067\u3059\u3002<\/p>\n<p>\u5546\u7528\u306eDB\u3067\u306f\u3053\u306e\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30cb\u30f3\u30b0\u304c\u7c21\u5358\u306b\u3067\u304d\u308b\u3082\u306e\u3082\u3042\u308a\u307e\u3059\u304c\u3001PostgreSQL\u306e\u5834\u5408\u306f\u5358\u7d14\u306b\u306f\u3044\u304d\u307e\u305b\u3093\u3002<\/p>\n<h2>PostgreSQL\u306b\u3088\u308b\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30cb\u30f3\u30b0<\/h2>\n<p>PostgreSQL\u306e\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30cb\u30f3\u30b0\u306f\u4e0b\u8a18\u306e\u8981\u9818\u3067\u884c\u3044\u307e\u3059\u3002<\/p>\n<ol>\n<li>\u5206\u5272\u5143\u3068\u306a\u308b\u30c6\u30fc\u30d6\u30eb\u306e\u4f5c\u6210<\/li>\n<li>\u5206\u5272\u3057\u305f\u30c7\u30fc\u30bf\u304c\u5165\u308b\u30c6\u30fc\u30d6\u30eb\u306e\u4f5c\u6210<\/li>\n<li>\u30c7\u30fc\u30bf\u3092\u5206\u5272\u3059\u308b\u95a2\u6570\u306e\u4f5c\u6210<\/li>\n<li>\u30c7\u30fc\u30bf\u3092\u5206\u5272\u3059\u308b\u30c8\u30ea\u30ac\u3092\u4f5c\u6210<\/li>\n<\/ol>\n<p>\u3068\u3044\u3063\u305f\u611f\u3058\u306b\u306a\u308a\u307e\u3059\u3002<\/p>\n<p>\u8a66\u3057\u306b\u5546\u54c1\u30c6\u30fc\u30d6\u30eb\u3092\u300ctype\u300d\u306b\u3088\u3063\u3066\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30cb\u30f3\u30b0\u3057\u3066\u307f\u307e\u3057\u3087\u3046\u3002<\/p>\n<p>\u30c6\u30fc\u30d6\u30eb\u30ec\u30a4\u30a2\u30a6\u30c8\u306f\u3053\u3093\u306a\u611f\u3058\u3067\u3059\u3002<\/p>\n<pre>techscore=# \\d items1\r\n                              \u30c6\u30fc\u30d6\u30eb \"public.items1\"\r\n  \u5217  |           \u578b           |                       \u4fee\u98fe\u8a9e\r\n------+------------------------+-----------------------------------------------------\r\n id   | integer                | not null default nextval('items1_id_seq'::regclass)\r\n code | character varying(255) | not null\r\n name | character varying(255) | not null\r\n type | integer                | not null\r\n\u30a4\u30f3\u30c7\u30c3\u30af\u30b9:\r\n    \"items1_pkey\" PRIMARY KEY, btree (id)\r\n    \"items1_type_idx\" btree (type)\r\n<\/pre>\n<p>\u6b21\u306b\u4e0b\u8a18\u306eSQL\u3092\u5b9f\u884c\u3057\u3066\u3001\u5206\u5272\u3057\u305f\u30c7\u30fc\u30bf\u304c\u5165\u308b\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u308a\u307e\u3059\u3002<\/p>\n<pre class=\"lang:sql decode:true \">-- \u5206\u5272\r\nCREATE TABLE items1_type1( LIKE items1 INCLUDING DEFAULTS INCLUDING INDEXES, CHECK (type = 1)) INHERITS (items1);\r\nCREATE TABLE items1_type2( LIKE items1 INCLUDING DEFAULTS INCLUDING INDEXES, CHECK (type = 2)) INHERITS (items1);\r\nCREATE TABLE items1_type3( LIKE items1 INCLUDING DEFAULTS INCLUDING INDEXES, CHECK (type = 3)) INHERITS (items1);\r\nCREATE TABLE items1_type4( LIKE items1 INCLUDING DEFAULTS INCLUDING INDEXES, CHECK (type = 4)) INHERITS (items1);\r\n<\/pre>\n<p>items1\u3092\u7d99\u627f\u3057\u305f\u30c6\u30fc\u30d6\u30eb\u3067\u3059\u3002<br \/>\n\u3053\u308c\u3089\u306e\u30c6\u30fc\u30d6\u30eb\u306b\u306fCHECK\u5236\u7d04\u3067\u3069\u306e\u30bf\u30a4\u30d7\u304c\u683c\u7d0d\u3055\u308c\u308b\u304b\u3092\u5b9a\u7fa9\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n<p>\u6b21\u306b\u95a2\u6570\u3092\u4f5c\u6210\u3057\u307e\u3059\u3002<\/p>\n<pre class=\"lang:sql decode:true \">CREATE OR REPLACE FUNCTION items1_insert_trigger_func()\r\n  RETURNS trigger AS\r\n$BODY$\r\n  DECLARE\r\n    child text;\r\n  BEGIN\r\n    child := 'items1_type' || new.type;\r\n    EXECUTE 'INSERT INTO ' || child || ' VALUES(($1).*)' USING new;\r\n    RETURN NULL;\r\n  END;\r\n$BODY$\r\n  LANGUAGE plpgsql VOLATILE\r\n  COST 100;\r\n<\/pre>\n<p>\u30dd\u30a4\u30f3\u30c8\u306f7-8\u884c\u76ee\u3067\u3059\u3002<br \/>\n\u300citems1_type\u300d\u3068type\u306e\u9805\u76ee\u3092\u6587\u5b57\u5217\u7d50\u5408\u3057\u3066\u3001\u633f\u5165\u5148\u3092type\u306b\u3088\u3063\u3066\u5206\u3051\u3066\u3044\u307e\u3059\u3002<br \/>\n\u3053\u308c\u3067\u3001\u633f\u5165\u6642\u306b\u30c7\u30fc\u30bf\u304c\u5206\u5272\u3055\u308c\u3066\u633f\u5165\u3055\u308c\u3066\u3044\u304d\u307e\u3059\u3002<\/p>\n<p>\u95a2\u6570\u304c\u4f5c\u6210\u3067\u304d\u305f\u3089\u3053\u306e\u95a2\u6570\u3092\u5b9f\u884c\u3059\u308b\u30c8\u30ea\u30ac\u3092\u4f5c\u6210\u3057\u307e\u3059\u3002<\/p>\n<pre class=\"lang:sql decode:true \">CREATE TRIGGER items1_insert_trigger\r\n  BEFORE INSERT\r\n  ON items1\r\n  FOR EACH ROW\r\n  EXECUTE PROCEDURE items1_insert_trigger_func();\r\n<\/pre>\n<p>items\u306e\u884c\u3054\u3068\u306e\u633f\u5165\u524d\u306b\u30c6\u30fc\u30d6\u30eb\u5206\u5272\u7528\u306e\u95a2\u6570\u3092\u5b9f\u884c\u3059\u308b\u3088\u3046\u306b\u8a2d\u5b9a\u3057\u307e\u3059\u3002<\/p>\n<p>\u3053\u308c\u3067\u6e96\u5099\u304c\u6574\u3044\u307e\u3057\u305f\u3002<br \/>\n\u30c7\u30fc\u30bf\u3092\u633f\u5165\u3057\u3066\u307f\u307e\u3057\u3087\u3046\u3002<br \/>\n\u633f\u5165\u5148\u306f\u30c7\u30fc\u30bf\u306e\u5206\u5272\u5143\u3068\u306a\u308b\u30c6\u30fc\u30d6\u30eb\u306b\u5bfe\u3057\u3066\u884c\u3044\u307e\u3059\u3002<\/p>\n<pre class=\"lang:sql decode:true \">insert into items1(code, name, type) values ('item_11', '\u5546\u54c111', 1);\r\ninsert into items1(code, name, type) values ('item_12', '\u5546\u54c112', 1);\r\ninsert into items1(code, name, type) values ('item_13', '\u5546\u54c113', 1);\r\ninsert into items1(code, name, type) values ('item_14', '\u5546\u54c114', 1);\r\ninsert into items1(code, name, type) values ('item_15', '\u5546\u54c115', 1);\r\n\r\ninsert into items1(code, name, type) values ('item_21', '\u5546\u54c121', 2);\r\ninsert into items1(code, name, type) values ('item_22', '\u5546\u54c122', 2);\r\ninsert into items1(code, name, type) values ('item_23', '\u5546\u54c123', 2);\r\ninsert into items1(code, name, type) values ('item_24', '\u5546\u54c124', 2);\r\ninsert into items1(code, name, type) values ('item_25', '\u5546\u54c125', 2);\r\n\r\ninsert into items1(code, name, type) values ('item_31', '\u5546\u54c131', 3);\r\ninsert into items1(code, name, type) values ('item_32', '\u5546\u54c132', 3);\r\ninsert into items1(code, name, type) values ('item_33', '\u5546\u54c133', 3);\r\ninsert into items1(code, name, type) values ('item_34', '\u5546\u54c134', 3);\r\ninsert into items1(code, name, type) values ('item_35', '\u5546\u54c135', 3);\r\n\r\ninsert into items1(code, name, type) values ('item_41', '\u5546\u54c141', 4);\r\ninsert into items1(code, name, type) values ('item_42', '\u5546\u54c142', 4);\r\ninsert into items1(code, name, type) values ('item_43', '\u5546\u54c143', 4);\r\ninsert into items1(code, name, type) values ('item_44', '\u5546\u54c144', 4);\r\ninsert into items1(code, name, type) values ('item_45', '\u5546\u54c145', 4);\r\n<\/pre>\n<p>\u3067\u306f\u5206\u5272\u3067\u304d\u3066\u3044\u308b\u304b\u3069\u3046\u304b\u78ba\u8a8d\u3057\u3066\u307f\u307e\u3057\u3087\u3046\u3002<\/p>\n<pre class=\"lang:sql decode:true \">techscore=# select * from items1_type3;\r\n id |  code   |  name  | type\r\n----+---------+--------+------\r\n 11 | item_31 | \u5546\u54c131 |    3\r\n 12 | item_32 | \u5546\u54c132 |    3\r\n 13 | item_33 | \u5546\u54c133 |    3\r\n 14 | item_34 | \u5546\u54c134 |    3\r\n 15 | item_35 | \u5546\u54c135 |    3\r\n(5 \u884c)\r\n\r\ntechscore=# select * from items1 where type=3;\r\n id |  code   |  name  | type\r\n----+---------+--------+------\r\n 15 | item_35 | \u5546\u54c135 |    3\r\n 14 | item_34 | \u5546\u54c134 |    3\r\n 13 | item_33 | \u5546\u54c133 |    3\r\n 12 | item_32 | \u5546\u54c132 |    3\r\n 11 | item_31 | \u5546\u54c131 |    3\r\n(5 \u884c)\r\n<\/pre>\n<p>\u3068\u308a\u3042\u3048\u305a\u3001\u30c7\u30fc\u30bf\u306f\u633f\u5165\u3055\u308c\u3066\u5206\u5272\u3082\u3067\u304d\u3066\u3044\u307e\u3059\u3002<br \/>\n\u5b9f\u884c\u8a08\u753b\u3082\u898b\u3066\u307f\u307e\u3057\u3087\u3046\u3002<\/p>\n<pre class=\"lang:sql decode:true \">techscore=# explain  select * from items1 where type=3;\r\n                                            QUERY PLAN\r\n---------------------------------------------------------------------------------------------------\r\n Append  (cost=0.00..8.16 rows=2 width=1040)\r\n   -&gt;  Seq Scan on items1  (cost=0.00..0.00 rows=1 width=1040)\r\n         Filter: (type = 3)\r\n   -&gt;  Index Scan using items1_type3_type_idx on items1_type3  (cost=0.14..8.16 rows=1 width=1040)\r\n         Index Cond: (type = 3)\r\n(5 \u884c)\r\n\r\ntechscore=# explain  select * from items1 where type=3 or type=4;\r\n                              QUERY PLAN\r\n----------------------------------------------------------------------\r\n Append  (cost=0.00..22.10 rows=5 width=1040)\r\n   -&gt;  Seq Scan on items1  (cost=0.00..0.00 rows=1 width=1040)\r\n         Filter: ((type = 3) OR (type = 4))\r\n   -&gt;  Seq Scan on items1_type3  (cost=0.00..11.05 rows=2 width=1040)\r\n         Filter: ((type = 3) OR (type = 4))\r\n   -&gt;  Seq Scan on items1_type4  (cost=0.00..11.05 rows=2 width=1040)\r\n         Filter: ((type = 3) OR (type = 4))\r\n(7 \u884c)\r\n<\/pre>\n<p>\u5b9f\u884c\u8a08\u753b\u3092\u898b\u308b\u3068CHECK\u5236\u7d04\u304c\u52b9\u3044\u3066\u304a\u308a\u4f59\u8a08\u306a\u30c6\u30fc\u30d6\u30eb\u30b9\u30ad\u30e3\u30f3\u304c\u884c\u308f\u308c\u3066\u3044\u306a\u3044\u3053\u3068\u3082\u78ba\u8a8d\u3067\u304d\u307e\u3059\u3002<\/p>\n<p>\u3067\u306f\u30c7\u30fc\u30bf\u306e\u524a\u9664\u3092\u3057\u3066\u307f\u307e\u3057\u3087\u3046\u3002<\/p>\n<pre class=\"lang:sql decode:true \">techscore=# explain delete from items1 where type=3;\r\n                                           QUERY PLAN\r\n------------------------------------------------------------------------------------------------\r\n Delete on items1  (cost=0.00..8.16 rows=2 width=6)\r\n   -&gt;  Seq Scan on items1  (cost=0.00..0.00 rows=1 width=6)\r\n         Filter: (type = 3)\r\n   -&gt;  Index Scan using items1_type3_type_idx on items1_type3  (cost=0.14..8.16 rows=1 width=6)\r\n         Index Cond: (type = 3)\r\n(5 \u884c)\r\n\r\ntechscore=# delete from items1 where type=3;\r\nDELETE 5\r\n\r\ntechscore=# explain delete from items1;\r\n                             QUERY PLAN\r\n--------------------------------------------------------------------\r\n Delete on items1  (cost=0.00..42.80 rows=281 width=6)\r\n   -&gt;  Seq Scan on items1  (cost=0.00..0.00 rows=1 width=6)\r\n   -&gt;  Seq Scan on items1_type1  (cost=0.00..10.70 rows=70 width=6)\r\n   -&gt;  Seq Scan on items1_type2  (cost=0.00..10.70 rows=70 width=6)\r\n   -&gt;  Seq Scan on items1_type3  (cost=0.00..10.70 rows=70 width=6)\r\n   -&gt;  Seq Scan on items1_type4  (cost=0.00..10.70 rows=70 width=6)\r\n(6 \u884c)\r\n\r\ntechscore=# delete from items1;\r\nDELETE 15\r\n\r\ntechscore=# select * from items1;\r\n id | code | name | type\r\n----+------+------+------\r\n(0 \u884c)\r\n\r\n<\/pre>\n<p>\u30c7\u30fc\u30bf\u3082\u7dba\u9e97\u306b\u6d88\u3048\u3066\u304f\u308c\u307e\u3059\u306d\u3002<\/p>\n<h2>\u305d\u306e\u4ed6\u306e\u30b3\u30de\u30f3\u30c9<\/h2>\n<p>SQL\u3067\u306f\u30c6\u30fc\u30d6\u30eb\u5206\u5272\u304c\u3046\u307e\u304f\u3044\u304f\u3053\u3068\u304c\u308f\u304b\u308a\u307e\u3057\u305f\u3002<br \/>\n\u3067\u306fCOPY\u3084truncate\u3068\u3044\u3063\u305f\u30b3\u30de\u30f3\u30c9\u3067\u306f\u3069\u3046\u3067\u3057\u3087\u3046\u304b\uff1f<\/p>\n<h3>COPY IMPORT<\/h3>\n<pre class=\"lang:sql decode:true \">techscore=# COPY items1 (code, name, type) FROM '\/tmp\/techscore_items.csv' WITH CSV;\r\nCOPY 0\r\n\r\ntechscore=# select count(*) from items1;\r\n count\r\n-------\r\n    20\r\n(1 \u884c)\r\n<\/pre>\n<p>COPY\u30b3\u30de\u30f3\u30c9\u3067\u8fd4\u3055\u308c\u308b\u4ef6\u6570\u304c0\u4ef6\u306b\u306a\u3063\u3066\u3057\u307e\u3044\u307e\u3057\u305f\u3002<br \/>\n\u3067\u3059\u304c\u3001\u53d6\u8fbc\u306f\u3067\u304d\u3066\u3044\u308b\u3088\u3046\u3067\u3059\u3002<\/p>\n<h3>COPY EXPORT<\/h3>\n<pre class=\"lang:sql decode:true \">techscore=# COPY items1 TO '\/tmp\/techscore_items_export';\r\nCOPY 0\r\n<\/pre>\n<p>EXPORT\u30820\u4ef6\u3067\u8fd4\u3055\u308c\u307e\u3059\u3002<br \/>\n\u3057\u304b\u3082\uff01\uff01<\/p>\n<pre>wc -l \/tmp\/techscore_items_export\r\n0 \/tmp\/techscore_items_export\r\n<\/pre>\n<p>\u30bc\u30ed\u4ef6\u3002\u3002\u3002<\/p>\n<p>EXPORT\u306f\u5bfe\u5fdc\u3067\u304d\u306a\u3044\u307f\u305f\u3044\u3067\u3059\u3002\u3002\u3002<br \/>\n\u3067\u3059\u304c\u3001SQL\u3067\u306fEXPORT\u3067\u304d\u308b\u3088\u3046\u3067\u3059\u3002<\/p>\n<pre class=\"lang:sql decode:true \">techscore=# COPY (select * from items1) TO '\/tmp\/techscore_items_export';\r\nCOPY 20\r\n<\/pre>\n<pre>wc -l \/tmp\/techscore_items_export\r\n20 \/tmp\/techscore_items_export\r\n<\/pre>\n<h3>TRUNCATE<\/h3>\n<pre class=\"lang:sql decode:true \">techscore=# select count(*) from items1;\r\n count\r\n-------\r\n    20\r\n(1 \u884c)\r\n\r\ntechscore=# truncate items1;\r\nTRUNCATE TABLE\r\ntechscore=# select count(*) from items1;\r\n count\r\n-------\r\n     0\r\n(1 \u884c)\r\n\r\ntechscore=#\r\n<\/pre>\n<p>truncate\u306f\u3067\u304d\u3066\u305d\u3046\u3067\u3059\u306d\u3002<\/p>\n<h2>\u904b\u7528\u3067\u306f<\/h2>\n<p>PostgreSQL\u306e\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30cb\u30f3\u30b0\u3067\u3059\u304c\u3001\u5b9f\u969b\u306e\u904b\u7528\u3067\u3082\u4f7f\u7528\u3057\u3066\u3044\u307e\u3059\u3002<br \/>\n1000\u4e07\u4ef6\u4ee5\u4e0a\u306e\u30c6\u30fc\u30d6\u30eb(\u4e2d\u306b\u306f\u5104)\u306b\u5bfe\u3057\u3066\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30cb\u30f3\u30b0\u3092\u5b9f\u65bd\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n<p>\u305d\u306e\u4e2d\u3067\u5f97\u305f\u30ce\u30a6\u30cf\u30a6\u3082\u7d39\u4ecb\u3057\u307e\u3059\u3002<\/p>\n<ul>\n<ul>\n<li>CHECK\u5236\u7d04\u304c\u8907\u96d1\u306a\u3082\u306e\u306f\u52b9\u304b\u306a\u3044<\/li>\n<\/ul>\n<\/ul>\n<p>\u30c6\u30fc\u30d6\u30eb\u306e\u9805\u76ee\u306e\u5024\u306e\u307f\u3067CHECK\u5236\u7d04\u3092\u8a2d\u3051\u308b\u5834\u5408\u306f\u554f\u984c\u3042\u308a\u307e\u305b\u3093\u304c\u3001\u5c11\u3057\u8907\u96d1\u306b\u306a\u3063\u305f\u5834\u5408\u306f\u6a5f\u80fd\u3057\u307e\u305b\u3093\u3002<br \/>\n\u4f8b\u3048\u3070\u3001\u300c\u304a\u6c17\u306b\u5165\u308a\u5546\u54c1\u300d\u306a\u3069\u3092\u30e6\u30fc\u30b6\u30fc\u3054\u3068\u306b\u7ba1\u7406\u3057\u305f\u3044\u5834\u5408\u3067\u306f\u3001\u30e6\u30fc\u30b6\u30fcID\u3092100\u3067\u5272\u3063\u305f\u3042\u307e\u308a\u3092suffix\u3068\u3057\u3066\u30c6\u30fc\u30d6\u30eb\u3092\u5206\u5272\u3059\u308b\u5834\u5408\u306f\u3001\u691c\u7d22\u6642\u306bCHECK\u5236\u7d04\u304c\u3046\u307e\u304f\u52d5\u4f5c\u305b\u305a\u3001\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30cb\u30f3\u30b0\u3055\u308c\u305f\u30c6\u30fc\u30d6\u30eb\u3092\u5168\u4ef6SCAN\u3057\u3066\u3057\u307e\u3044\u307e\u3059\u3002\u3053\u3046\u3044\u3063\u305f\u5834\u5408\u306fCHECK\u5236\u7d04\u7528\u306e\u9805\u76ee\u3092\u8a2d\u3051\u3066\u691c\u7d22\u306e\u6761\u4ef6\u306b\u542b\u3081\u308b\u3088\u3046\u306b\u3057\u305f\u65b9\u304c\u826f\u3044\u3067\u3059\u3002<\/p>\n<ul>\n<ul>\n<li>\u5206\u5272\u6570<\/li>\n<\/ul>\n<\/ul>\n<p>PostgreSQL\u306e\u30c9\u30ad\u30e5\u30e1\u30f3\u30c8\u3067\u306f\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30cb\u30f3\u30b0\u306f100\u500b\u307e\u3067\u3092\u63a8\u5968\u3057\u3066\u304a\u308a\u3001\u4f55\u5343\u3082\u306e\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30f3\u3092\u4f7f\u7528\u3059\u308b\u3053\u3068\u306f\u907f\u3051\u308b\u3088\u3046\u306b\u66f8\u304b\u308c\u3066\u3044\u307e\u3059\u3002<br \/>\n<a href=\"https:\/\/www.postgresql.jp\/document\/9.3\/html\/ddl-partitioning.html\">PostgreSQL\u30c9\u30ad\u30e5\u30e1\u30f3\u30c8<\/a><br \/>\n100\u500b\u4ee5\u4e0a\u306e\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30cb\u30f3\u30b0\u306f\u904b\u7528\u3067\u3082\u3084\u3063\u3066\u304a\u308a\u307e\u305b\u3093\u3002<\/p>\n<ul>\n<ul>\n<li>\u30ec\u30b9\u30dd\u30f3\u30b9<\/li>\n<\/ul>\n<\/ul>\n<p>\u30ec\u30b9\u30dd\u30f3\u30b9\u304c\u6c17\u306b\u306a\u3063\u305f\u3053\u3068\u306f\u3042\u308a\u307e\u305b\u3093\u3002(CHECK\u5236\u7d04\u304c\u3046\u307e\u304f\u52d5\u4f5c\u3057\u3066\u3044\u308b\u6642\uff01\uff01)<br \/>\n\u3042\u3068\u3001COPY\u30b3\u30de\u30f3\u30c9\u306b\u3088\u308b\u5927\u91cf\u306e\u30c7\u30fc\u30bfIMPORT(1000\u4e07\u4ef6\u307b\u3069)\u306f\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30cb\u30f3\u30b0\u304c\u306a\u3051\u308c\u3070\u30011\u5206\u534a\u307b\u3069\u3067\u3059\u307f\u307e\u3059\u304c\u3001\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30cb\u30f3\u30b0\u3057\u3066\u3044\u308b\u30688\u5206\u3050\u3089\u3044\u304b\u304b\u308a\u307e\u3059\u3002<br \/>\n\u5927\u91cf\u306e\u30c7\u30fc\u30bf\u6295\u5165\u306e\u969b\u306f\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30cb\u30f3\u30b0\u5148\u306e\u30c6\u30fc\u30d6\u30eb\u306b\u5bfe\u3057\u3066COPY\u30b3\u30de\u30f3\u30c9\u3092\u5b9f\u884c\u3059\u308b\u65b9\u304c\u901f\u3044\u3067\u3059\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u3053\u3093\u306b\u3061\u306f \u6751\u4e0a\u3067\u3059\u3002<\/p>\n<p>\u3053\u306e\u8a18\u4e8b\u306fTECHSCORE Advent Calendar 2014\u30019\u65e5\u76ee\u306e\u8a18\u4e8b\u3067\u3059\u3002<\/p>\n<p>\u672c\u65e5\u306f\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30cb\u30f3\u30b0\u306b\u3064\u3044\u3066\u66f8\u304d\u305f\u3044\u3068\u601d\u3044\u307e\u3059\u3002<br \/><a href=\"https:\/\/www.techscore.com\/blog\/2014\/12\/09\/postgresql%e3%83%91%e3%83%bc%e3%83%86%e3%82%a3%e3%82%b7%e3%83%a7%e3%83%8b%e3%83%b3%e3%82%b0%e3%81%ae%e3%82%b9%e3%82%b9%e3%83%a1\/\">\u7d9a\u304d\u3092\u8aad\u3080...<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[18],"tags":[141,206],"_links":{"self":[{"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/posts\/4841"}],"collection":[{"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/comments?post=4841"}],"version-history":[{"count":32,"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/posts\/4841\/revisions"}],"predecessor-version":[{"id":5180,"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/posts\/4841\/revisions\/5180"}],"wp:attachment":[{"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/media?parent=4841"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/categories?post=4841"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/tags?post=4841"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}