4.1.?用法示例

CREATE EXTENSION btree_gist;
  1. 使用btree_gist代替btree的简单示例:

    CREATE TABLE test (a int4);
    -- create index
    CREATE INDEX testidx01 ON test USING GIST (a);
    -- query
    SELECT * FROM test WHERE a < 10;
    -- nearest-neighbor search: find the ten entries closest to "42"
    SELECT *, a <-> 42 AS dist FROM test ORDER BY a <-> 42 LIMIT 10;
  2. 使用一个排他约束来强制规则(一个动物园里的一个笼子只能装一种动物):

    => CREATE TABLE zoo (
      cage   INTEGER,
      animal     TEXT,
      EXCLUDE USING GIST (cage WITH =, animal WITH <>)
    );
    => INSERT INTO zoo VALUES(123, 'zebra');
    INSERT 0 1
    => INSERT INTO zoo VALUES(123, 'zebra');
    INSERT 0 1
    => INSERT INTO zoo VALUES(123, 'lion');
    ERROR:  conflicting key value violates exclusion constraint "zoo_cage_animal_excl"
    DETAIL:  Key (cage, animal)=(123, lion) conflicts with existing key (cage, animal)=(123, zebra).
    => INSERT INTO zoo VALUES(124, 'lion');
    INSERT 0 1
XML 地图 | Sitemap 地图