{"id":2399,"date":"2013-04-17T18:43:54","date_gmt":"2013-04-17T09:43:54","guid":{"rendered":"http:\/\/www.techscore.com\/blog\/?p=2399"},"modified":"2018-11-14T16:33:55","modified_gmt":"2018-11-14T07:33:55","slug":"postgresql9-2-%e7%af%84%e5%9b%b2%e5%9e%8b","status":"publish","type":"post","link":"https:\/\/www.techscore.com\/blog\/2013\/04\/17\/postgresql9-2-%e7%af%84%e5%9b%b2%e5%9e%8b\/","title":{"rendered":"PostgreSQL9.2: \u7bc4\u56f2\u578b"},"content":{"rendered":"<p>\u3053\u3093\u306b\u3061\u308f \u5317\u5ddd\u3067\u3059\u3002<\/p>\n<p>JSON\u578b\u306b\u5f15\u304d\u7d9a\u304d\u3001PostgreSQL9.2\u3067\u8ffd\u52a0\u3055\u308c\u305f\u7bc4\u56f2\u578b\u3092\u89e6\u3063\u3066\u307f\u305f\u3044\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\n<p><a href=\"http:\/\/www.postgresql.org\/docs\/9.2\/static\/rangetypes.html\" title=\"http:\/\/www.postgresql.org\/docs\/9.2\/static\/rangetypes.html\" target=\"_blank\">http:\/\/www.postgresql.org\/docs\/9.2\/static\/rangetypes.html<\/a><br \/>\n<a href=\"http:\/\/www.postgresql.jp\/document\/9.2\/html\/rangetypes.html\" title=\"http:\/\/www.postgresql.jp\/document\/9.2\/html\/rangetypes.html\" target=\"_blank\">http:\/\/www.postgresql.jp\/document\/9.2\/html\/rangetypes.html<\/a><\/p>\n<h2>\u7bc4\u56f2\u578b<\/h2>\n<p>\u7bc4\u56f2\u578b\u306f\u30012\u3064\u306e\u5024\u3067\u7bc4\u56f2\u3092\u8868\u308f\u3059\u30c7\u30fc\u30bf\u578b\u3067\u3042\u308a\u3001<br \/>\n\u4e0b\u8a18\u306e\u3088\u3046\u306b\uff11\u3064\u306e\u30ab\u30e9\u30e0\u3067\u3001\u7bc4\u56f2\u3092\u8868\u3059\u3053\u3068\u304c\u3067\u304d\u307e\u3059\u3002<\/p>\n<pre class=\"lang:pgsql decode:true\">SELECT times from weather_range ;\r\n                     times                     \r\n-----------------------------------------------\r\n [\"2013-04-01 16:00:00\",\"2013-04-01 17:00:00\")<\/pre>\n<p>\u7bc4\u56f2\u578b\u306b\u306f\u3001\u7d44\u307f\u8fbc\u307f\u306e\u7bc4\u56f2\u578b\u304c6\u3064\u7528\u610f\u3055\u308c\u3066\u304a\u308a\u3001\u307b\u3068\u3093\u3069\u306e\u5834\u5408\u306f<br \/>\n\u3053\u306e\u7d44\u307f\u8fbc\u307f\u306e\u7bc4\u56f2\u578b\u3067\u306a\u3093\u3068\u304b\u306a\u308a\u305d\u3046\u3067\u3059\u3002<\/p>\n<p>CREATE TYPE \u3067\u72ec\u81ea\u306e\u7bc4\u56f2\u578b\u3092\u4f5c\u6210\u3059\u308b\u3053\u3068\u3082\u53ef\u80fd\u3067\u3059\u3002<br \/>\n<a href=\"http:\/\/www.postgresql.jp\/document\/9.2\/html\/sql-createtype.html\" title=\"http:\/\/www.postgresql.jp\/document\/9.2\/html\/sql-createtype.html\" target=\"_blank\">http:\/\/www.postgresql.jp\/document\/9.2\/html\/sql-createtype.html<\/a><\/p>\n<ul>\n<li><span style=\"font-size: 13px;line-height: 19px\">int4range \u0014 integer\u306e\u7bc4\u56f2<\/span><\/li>\n<li><span style=\"font-size: 13px;line-height: 19px\">int8range \u0014 bigint\u306e\u7bc4\u56f2<\/span><\/li>\n<li><span style=\"font-size: 13px;line-height: 19px\">numrange \u0014 numeric\u306e\u7bc4\u56f2<\/span><\/li>\n<li><span style=\"font-size: 13px;line-height: 19px\">tsrange \u0014 timestamp without time zone\u306e\u7bc4\u56f2<\/span><\/li>\n<li><span style=\"font-size: 13px;line-height: 19px\">tstzrange \u0014 timestamp with time zone\u306e\u7bc4\u56f2<\/span><\/li>\n<li><span style=\"font-size: 13px;line-height: 19px\">daterange \u0014 date\u306e\u7bc4\u56f2<\/span><\/li>\n<\/ul>\n<p>\u53d6\u308a\u6562\u3048\u305a\u3001\u30c7\u30fc\u30bf\u306e\u767b\u9332\u3001\u691c\u7d22\u3092\u884c\u3044\u305f\u3044\u306e\u3067\u3001\u7bc4\u56f2\u578b\u3092\u30ab\u30e9\u30e0\u306b\u6301\u3064\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3057\u307e\u3059\u3002<br \/>\n\u4eca\u56de\u306f\u3001tsrange \u3092\u5229\u7528\u3057\u307e\u3059\u3002<\/p>\n<pre class=\"lang:pgsql decode:true\">demo=# CREATE TABLE weather_range (weather TEXT , times tsrange);\r\nCREATE TABLE\r\ndemo=# \\d weather_range\r\n Table \"public.weather_range\"\r\n Column  |  Type   | Modifiers \r\n---------+---------+-----------\r\n weather | text    | \r\n times   | tsrange |<\/pre>\n<p>\u4f5c\u6210\u3067\u304d\u307e\u3057\u305f\u3002\u5f15\u304d\u7d9a\u304d\u30c7\u30fc\u30bf\u306e\u767b\u9332\u3092\u884c\u3044\u305f\u3044\u3068\u601d\u3044\u307e\u3059\u304c\u3001<br \/>\ninsert\u6587\u3092\u4f5c\u6210\u3059\u308b\u306b\u3042\u305f\u308a\u5883\u754c\u306e\u8868\u73fe\u65b9\u6cd5\u3092\u77e5\u308b\u5fc5\u8981\u304c\u3042\u308a\u307e\u3059\u3002<\/p>\n<p>\u5883\u754c\u306e\u8868\u73fe\u3068\u306f\u3001\u4e0b\u9650\/\u4e0a\u9650\u5024\u3092 \u542b\u3081\u308b\/\u542b\u3081\u306a\u3044 \u306e\u8868\u73fe\u65b9\u6cd5\u3068\u306a\u308a\u3001<br \/>\n\u5024\u3092\u542b\u3081\u308b\u5834\u5408\u306f\u3001[ or ] \u3067\u8868\u73fe\u3057\u3001\u5024\u3092\u542b\u3081\u306a\u5834\u5408\u306f\u3001( or ) \u8868\u73fe\u3057\u307e\u3059\u3002<\/p>\n<p>\u6587\u7ae0\u3067\u3042\u307e\u308a\u3046\u307e\u304f\u8868\u73fe\u3067\u304d\u306a\u3044\u306e\u3067\u53d6\u308a\u6562\u3048\u305a\u4f7f\u3063\u3066\u307f\u307e\u3059\u3002<br \/>\n\u4e0b\u8a18\u306eSQL\u306e\u5834\u5408\u306f,\u30002013-04-01 15:00 \u4ee5\u4e0a 2013-04-01 16:00 \u672a\u6e80 \u3092\u8868\u3057\u307e\u3059\u3002<\/p>\n<pre class=\"lang:pgsql decode:true\">INSERT INTO weather_range VALUES ('\u6674\u308c', '[2013-04-01 15:00, 2013-04-01 16:00)');<\/pre>\n<p>\u3044\u304f\u3064\u304b\u30c6\u30b9\u30c8\u30c7\u30fc\u30bf\u3092\u767b\u9332\u3057\u307e\u3059\u3002<\/p>\n<pre class=\"lang:pgsql decode:true\">INSERT INTO weather_range VALUES ('\u66c7\u308a', '[2013-04-01 16:00, 2013-04-01 17:00)');\r\nINSERT INTO weather_range VALUES ('\u66c7\u308a', '[2013-04-02 15:00, 2013-04-02 16:00)');\r\nINSERT INTO weather_range VALUES ('\u96e8', '[2013-04-02 16:00, 2013-04-02 17:00)');<\/pre>\n<p>\u7bc4\u56f2\u578b\u306b\u5bfe\u3057\u3066\u5229\u7528\u53ef\u80fd\u306a\u6f14\u7b97\u5b50\u304c\u6e96\u5099\u3055\u308c\u3066\u3044\u307e\u3059\u306e\u3067\u3001\u305d\u308c\u3089\u3092\u4f7f\u3063\u3066\u30c7\u30fc\u30bf\u306e\u691c\u7d22\u3092\u3057\u3066\u307f\u307e\u3059\u3002<\/p>\n<p><a href=\"http:\/\/www.postgresql.jp\/document\/9.2\/html\/functions-range.html#RANGE-OPERATORS-TABLE\" title=\"http:\/\/www.postgresql.jp\/document\/9.2\/html\/functions-range.html#RANGE-OPERATORS-TABLE\" target=\"_blank\">http:\/\/www.postgresql.jp\/document\/9.2\/html\/functions-range.html#RANGE-OPERATORS-TABLE<\/a><\/p>\n<pre class=\"lang:pgsql decode:true\">demo=# SELECT * from weather_range where times @&gt; '2013-04-01 16:00'::timestamp;\r\n weather |                     times                     \r\n---------+-----------------------------------------------\r\n \u66c7\u308a    | [\"2013-04-01 16:00:00\",\"2013-04-01 17:00:00\")\r\n(1 row)\r\n\r\ndemo=# SELECT * from weather_range where times &lt;&lt; tsrange('2013-04-01 20:00:00','2013-04-01 24:00:00');\r\n weather |                     times                     \r\n---------+-----------------------------------------------\r\n \u6674\u308c    | [\"2013-04-01 15:00:00\",\"2013-04-01 16:00:00\")\r\n \u66c7\u308a    | [\"2013-04-01 16:00:00\",\"2013-04-01 17:00:00\")\r\n(2 rows)<\/pre>\n<h2>\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u306e\u5229\u7528<\/h2>\n<p>\u7bc4\u56f2\u578b\u3067\u306f\u3001GiST\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u3092\u5229\u7528\u3057\u307e\u3059\u3002<br \/>\n\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u306b\u3088\u308a\u3001\u7bc4\u56f2\u6f14\u7b97\u5b50\u3092\u5229\u7528\u3057\u305f\u5834\u5408\u306e\u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9\u306e\u5411\u4e0a\u304c\u671f\u5f85\u51fa\u6765\u307e\u3059\u3002<\/p>\n<pre class=\"lang:pgsql decode:true\">demo=# explain SELECT * from weather_range where times &amp;&amp; tsrange('2013-04-01 15:00:00','2013-04-01 16:00:00');\r\n                                       QUERY PLAN                                       \r\n----------------------------------------------------------------------------------------\r\n Index Scan using weather_range_idx on weather_range  (cost=0.00..2.27 rows=1 width=38)\r\n   Index Cond: (times &amp;&amp; '[\"2013-04-01 15:00:00\",\"2013-04-01 16:00:00\")'::tsrange)\r\n(2 rows)\r\n\r\ndemo=# CREATE INDEX weather_range_idx ON weather_range USING gist (times);\r\nCREATE INDEX\r\n\r\ndemo=# explain SELECT * from weather_range where times &amp;&amp; tsrange('2013-04-01 15:00:00','2013-04-01 16:00:00');\r\n                                  QUERY PLAN                                   \r\n-------------------------------------------------------------------------------\r\n Seq Scan on weather_range  (cost=0.00..3.76 rows=1 width=38)\r\n   Filter: (times &amp;&amp; '[\"2013-04-01 15:00:00\",\"2013-04-01 16:00:00\")'::tsrange)\r\n(2 rows)<\/pre>\n<h2>\u7bc4\u56f2\u306e\u5236\u5fa1<\/h2>\n<p>\u7bc4\u56f2\u578b\u306e\u30e1\u30ea\u30c3\u30c8\u3068\u3057\u3066\u3001\u91cd\u306a\u308a\u304c\u7121\u3044\u3053\u3068\u3092DB\u3067\u5236\u5fa1\u3059\u308b\u3053\u3068\u304c\u53ef\u80fd\u3068\u306a\u308a\u307e\u3059\u3002<br \/>\n\u305d\u306e\u3088\u3046\u306a\u5236\u5fa1\u306f\u3001\u6392\u4ed6\u5236\u7d04\u3092\u5229\u7528\u3059\u308b\u3053\u3068\u306b\u3088\u3063\u3066\u53ef\u80fd\u3068\u306a\u308a\u307e\u3059\u3002<\/p>\n<p>weather_range\u30c6\u30fc\u30d6\u30eb\u306b\u6392\u4ed6\u5236\u7d04\u3092\u8ffd\u52a0\u3057\u307e\u3059\u3002\u6392\u4ed6\u5236\u7d04\u306f\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u304c\u3042\u308b\u3053\u3068\u304c\u524d\u63d0\u3067\u3042\u308b\u305f\u3081<br \/>\n\u5408\u308f\u305b\u3066\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u3082\u8ffd\u52a0\u3059\u308b\u5fc5\u8981\u304c\u3042\u308a\u307e\u3059\u3002<\/p>\n<pre class=\"lang:pgsql decode:true\">ALTER TABLE weather_range ADD EXCLUDE USING gist (times WITH &amp;&amp;);\r\n\r\ndemo=# ALTER TABLE weather_range ADD EXCLUDE USING gist (times WITH &amp;&amp;);\r\nNOTICE:  ALTER TABLE \/ ADD EXCLUDE will create implicit index \"weather_range_times_excl\" for table \"weather_range\"\r\nALTER TABLE\r\n\r\ndemo=# \\d weather_range\r\n Table \"public.weather_range\"\r\n Column  |  Type   | Modifiers \r\n---------+---------+-----------\r\n weather | text    | \r\n times   | tsrange | \r\nIndexes:\r\n    \"weather_range_idx\" gist (times)\r\n    \"weather_range_times_excl\" EXCLUDE USING gist (times WITH &amp;&amp;)<\/pre>\n<p>\u6392\u4ed6\u5236\u7d04\u3092\u8ffd\u52a0\u3057\u305f\u30c6\u30fc\u30d6\u30eb\u306b\u91cd\u306a\u308a\u304c\u3042\u308b\u3088\u3046\u306b\u30c7\u30fc\u30bf\u3092\u8ffd\u52a0\u3059\u308b\u3068<br \/>\n\u5236\u7d04\u306b\u3088\u308a\u8ffd\u52a0\u3067\u304d\u306a\u3044\u4e8b\u304c\u78ba\u8a8d\u3067\u304d\u307e\u3059\u3002<\/p>\n<pre class=\"lang:pgsql decode:true\">demo=# INSERT INTO weather_range VALUES ('\u6674\u308c', '[2013-04-01 15:00, 2013-04-01 16:00)');\r\nERROR:  conflicting key value violates exclusion constraint \"weather_range_times_excl\"\r\nDETAIL:  Key (times)=([\"2013-04-01 15:00:00\",\"2013-04-01 16:00:00\")) conflicts with existing key (times)=([\"2013-04-01 15:00:00\",\"2013-04-01 16:00:00\")).<\/pre>\n<h2>\u307e\u3068\u3081<\/h2>\n<p>\u4eca\u307e\u3067\u306f\u3001\uff12\u3064\u306e\u30ab\u30e9\u30e0\u3092\u5229\u7528\u3057\u3066\u7bc4\u56f2\u3092\u8868\u73fe\u3059\u308b\u5fc5\u8981\u304c\u3042\u3063\u305f\u306e\u304c\u3001\u7bc4\u56f2\u578b\u306e\u767b\u5834\u3067\uff11\u3064\u306e\u30ab\u30e9\u30e0\u3067<br \/>\n\u8868\u3059\u4e8b\u304c\u3067\u304d\u307e\u3059\u3002\u307e\u305f\u3001\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u306b\u3088\u308b\u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9\u5411\u4e0a\u3084\u6392\u4ed6\u5236\u7d04\u306b\u3088\u308b\u30c7\u30fc\u30bf\u306e\u6574\u5408\u6027\u306e\u78ba\u4fdd<br \/>\n\u306a\u3069\u306e\u6069\u6075\u3092\u5f97\u3089\u308c\u305d\u3046\u3067\u3059\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u3053\u3093\u306b\u3061\u308f \u5317\u5ddd\u3067\u3059\u3002<\/p>\n<p>JSON\u578b\u306b\u5f15\u304d\u7d9a\u304d\u3001PostgreSQL9.2\u3067\u8ffd\u52a0\u3055\u308c\u305f\u7bc4\u56f2\u578b\u3092\u89e6\u3063\u3066\u307f\u305f\u3044\u3068\u601d\u3044\u307e\u3059\u3002<br \/><a href=\"https:\/\/www.techscore.com\/blog\/2013\/04\/17\/postgresql9-2-%e7%af%84%e5%9b%b2%e5%9e%8b\/\">\u7d9a\u304d\u3092\u8aad\u3080...<\/a><\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[18],"tags":[206,93,92],"_links":{"self":[{"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/posts\/2399"}],"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\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/comments?post=2399"}],"version-history":[{"count":1,"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/posts\/2399\/revisions"}],"predecessor-version":[{"id":17940,"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/posts\/2399\/revisions\/17940"}],"wp:attachment":[{"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/media?parent=2399"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/categories?post=2399"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/tags?post=2399"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}