9 其他特性

test=# CREATE TABLE course_chapters(
  c_no text REFERENCES courses(c_no),
  ch_no text,
  ch_title text,
  txt text,
  CONSTRAINT pkt_ch PRIMARY KEY(ch_no, c_no)
);

test=# INSERT INTO course_chapters(
 c_no, ch_no, ch_title, txt)
VALUES
('CS301', 'I', 'Databases',
 'We start our acquaintance with ' ||
 'the fascinating world of databases'),
('CS301', 'II', 'First Steps',
 'Getting more fascinated with ' ||
 'the world of databases'),
('CS305', 'I', 'Local Networks',
 'Here we start our adventurous journey ' ||
 'through the intriguing world of networks');
INSERT 0 3

test=# SELECT ch_no AS no, ch_title, txt
FROM course_chapters \gx
-[ RECORD 1 ]-----------------------------------------
no       | I
ch_title | Databases
txt      | We start our acquaintance with the
           fascinating world of databases
-[ RECORD 2 ]-----------------------------------------
no       | II
ch_title | First Steps
txt      | Getting more fascinated with the world of
           databases
-[ RECORD 3 ]-----------------------------------------
no       | I
ch_title | Local Networks
txt      | Here we start our adventurous journey
           through the intriguing world of networks

test=# SELECT ch_no AS no, ch_title, txt
FROM course_chapters
WHERE txt LIKE '%fascination%' \gx

test=# SELECT ch_no AS no, ch_title, txt
FROM course_chapters
WHERE txt LIKE '%fascinated%' \gx

test=# ALTER TABLE course_chapters
    ADD txtvector tsvector;
test=# UPDATE course_chapters
    SET txtvector = to_tsvector('english',txt);
test=# SELECT txtvector
FROM course_chapters \gx

test=# UPDATE course_chapters
  SET txtvector =
      setweight(to_tsvector('english',ch_title),'B')
      || ' ' ||
      setweight(to_tsvector('english',txt),'D');
UPDATE 3

test=# SELECT txtvector FROM course_chapters \gx
-[ RECORD 1 ]-----------------------------------------
txtvector | 'acquaint':5 'databas':1B,11 'fascin':8
            'start':3 'world':10
-[ RECORD 2 ]-----------------------------------------
txtvector | 'databas':10 'fascin':5 'first':1B 'get':3
            'step':2B 'world':8

test=# SELECT ch_title
FROM course_chapters
WHERE txtvector @@
      to_tsquery('english','fascination & database');
  ch_title
-------------
 Databases
 First Steps
( rows)

test=# SELECT ch_title,
     ts_rank_cd('{0.1, 0.0, 1.0, 0.0}', txtvector, q)
FROM course_chapters,
     to_tsquery('english','Databases') q
WHERE txtvector @@ q
ORDER BY ts_rank_cd DESC;

test=# SELECT ts_headline(
  'english',
  txt,
  to_tsquery('english', 'world'),
  'StartSel=<b>, StopSel=</b>,
  MaxWords=50, MinWords=5'
)
FROM course_chapters
WHERE to_tsvector('english', txt) @@
      to_tsquery('english', 'world');

test=# CREATE TABLE student_details(
  de_id int,
  s_id int REFERENCES students(s_id),
  details json,
  CONSTRAINT pk_d PRIMARY KEY(s_id, de_id)
);

test=# INSERT INTO student_details
  (de_id, s_id, details)
VALUES
(1, 1451,
'{ "merits": "none",
   "flaws":
   "immoderate ice cream consumption",
   "status" : "expelled"
 }'),
 (2, 1432,
'{ "hobbies":
     { "guitarist":
         { "band": "Postgressors",
           "guitars":["Strat","Telec"]
         }
     }
 }'
),
(3, 1556,
'{ "hobbies": "cosplay",
     "merits":
      { "mother-of-five":
          {  "Basil": "m", "Simon": "m", "Lucie": "f",
             "Mark": "m",  "Alex": "unknown"
          }
      }
 }'
);

test=# SELECT s.name, sd.details
FROM student_details sd, students s
WHERE s.s_id = sd.s_id \gx
-[ RECORD 1 ]--------------------------------------
name    | Anna
details | { "merits": "none",                      +
        |   "flaws":                               +
        |   "immoderate ice cream consumption",    +
        |   "status" : "expelled"                  +
        | }
-[ RECORD 2 ]--------------------------------------
name    | Victor
details | { "hobbies":                             +
        |      { "guitarist":                      +
        |          { "band": "Postgressors",       +
        |            "guitars":["Strat","Telec"]   +
        |          }                               +
        |      }                                   +
        | }
-[ RECORD 3 ]--------------------------------------
name    | Nina
details | { "hobbies": "cosplay",                  +
        |   "merits":                              +
        |      { "mother-of-five":                 +
        |          { "Basil": "m",                 +
        |            "Simon": "m",                 +
        |            "Lucie": "f",                 +
        |            "Mark": "m",                  +
        |            "Alex": "unknown"             +
        |          }                               +
        |      }                                   +
        | }

test=# SELECT s.name, sd.details
FROM  student_details sd, students s
WHERE s.s_id = sd.s_id
AND   sd.details ->> 'merits' IS NOT NULL \gx

test=# SELECT s.name, sd.details
FROM  student_details sd, students s
WHERE s.s_id = sd.s_id
AND   sd.details ->> 'merits' IS NOT NULL
AND   sd.details ->> 'merits' != 'none';

test=# SELECT sd.de_id, s.name, sd.details
FROM  student_details sd, students s
WHERE s.s_id = sd.s_id
AND   sd.details ->> 'guitars' IS NOT NULL \gx

test=# SELECT sd.de_id, s.name,
      sd.details #> '{hobbies,guitarist,guitars}'
FROM  student_details sd, students s
WHERE s.s_id = sd.s_id
AND   sd.details #> '{hobbies,guitarist,guitars}'
      IS NOT NULL;

test=# ALTER TABLE student_details
ADD details_b jsonb;

test=# UPDATE student_details
SET details_b = to_jsonb(details);
test=# SELECT de_id, details_b
FROM student_details \gx
-[ RECORD 1 ]--------------------------------------
de_id     | 1
details_b | {"flaws": "immoderate ice cream
            consumption", "merits": "none",
            "status": "expelled"}
-[ RECORD 2 ]--------------------------------------
de_id     | 2
details_b | {"hobbies": {"guitarist": {"guitars":
            ["Strat", "Telec"], "band":
            "Postgressors"}}}
-[ RECORD 3 ]--------------------------------------
de_id     | 3
details_b | {"hobbies": "cosplay", "merits":
            {"mother-of-five": {"Basil": "m",
            "Lucie": "f", "Alex": "unknown",
            "Mark": "m", "Simon": "m"}}}

test=# SELECT s.name,
      jsonb_pretty(sd.details_b) json
FROM  student_details sd, students s
WHERE s.s_id = sd.s_id
AND   sd.details_b @>
      '{"merits":{"mother-of-five":{}}}' \gx

test=# SELECT s.name,
      jsonb_each(sd.details_b)
FROM  student_details sd, students s
WHERE s.s_id = sd.s_id
AND   sd.details_b @>
      '{"merits":{"mother-of-five":{}}}' \gx

test=# SELECT s_id, jsonb_path_query(
  details::jsonb,
  '$.hobbies ? (@ == "cosplay")'
)
FROM student_details;

test=# SELECT s_id, jsonb_path_query(
  details::jsonb,
  '$.hobbies.guitarist.band?(@=="Postgressors")'
)
FROM student_details;

test=# SELECT s_id, jsonb_path_query(
  details::jsonb,
  '$.hobbies.guitarist?(@.band=="Postgressors").band'
)
FROM student_details;

test=# SELECT s_id, jsonb_path_exists(
  details::jsonb, '$.** ? (@ == "Strat")'
)
FROM student_details;

test=# CREATE TABLE tv_series(
  se_id int,

test=# INSERT INTO tv_series (se_id, s_id, se_details)
VALUES
(1, 1451,
'{"title": ["Game of Thrones",
            "Peaky Blinders"]}'
),

text=# SELECT jsonb_array_elements(
  se_details->'title'
)
FROM tv_series;

text=# SELECT se_id, se_details
FROM tv_series
WHERE se_details->'title' IS NOT JSON ARRAY \gx

text=# UPDATE tv_series
SET se_details = jsonb_set(
  se_details,
  '{title}',
  '["Babylon 5"]'
)
WHERE se_id = 2;

test=# SELECT DISTINCT jsonb_array_elements(
  se_details->'title'
)
FROM tv_series;

test=# SELECT name, default_version
FROM pg_available_extensions
WHERE name = 'oracle_fdw' \gx

test=# CREATE EXTENSION oracle_fdw;

test=# \dew

test=# CREATE SERVER oracle_srv
  FOREIGN DATA WRAPPER oracle_fdw
  OPTIONS (dbserver '//localhost:1521/orcl');

test=# CREATE USER MAPPING FOR postgres
  SERVER oracle_srv
  OPTIONS (user 'scott', password 'tiger');

test=# CREATE SCHEMA oracle_hr;

test=# IMPORT FOREIGN SCHEMA "SCOTT"
  LIMIT TO (dept, emp)
  FROM SERVER oracle_srv
  INTO oracle_hr;

test=# \det oracle_hr.*

test=# SELECT * FROM oracle_hr.emp LIMIT 1 \gx

test=# INSERT INTO oracle_hr.dept(deptno, dname, loc)
  VALUES (50, 'EDUCATION', 'MOSCOW');

test=# SELECT * FROM oracle_hr.dept;

test=# CREATE EXTENSION mysql_fdw;

test=# CREATE SERVER mysql_srv
  FOREIGN DATA WRAPPER mysql_fdw
  OPTIONS (host 'localhost', port '3306');
CREATE SERVER

test=# CREATE USER MAPPING FOR postgres
  SERVER mysql_srv
  OPTIONS (username 'root', password 'p@ssw0rd');
CREATE USER MAPPING

test=# CREATE FOREIGN TABLE employees (
  emp_no      int,
  birth_date  date,
  first_name  varchar(14),
  last_name   varchar(16),
  gender      varchar(1),
  hire_date   date)
SERVER mysql_srv
  OPTIONS (dbname 'employees',
           table_name 'employees');
CREATE FOREIGN TABLE

test=# SELECT * FROM employees LIMIT 1 \gx
-[ RECORD 1 ]----------
emp_no     | 10001
birth_date | 1953-09-02
first_name | Georgi
last_name  | Facello
gender     | M
hire_date  | 1986-06-26

test=# CREATE EXTENSION tds_fdw;
CREATE EXTENSION

test=# CREATE SERVER sqlserver_srv
  FOREIGN DATA WRAPPER tds_fdw
  OPTIONS (servername 'localhost', port '1433',
           database 'AdventureWorks');
CREATE SERVER

test=# CREATE USER MAPPING FOR postgres
  SERVER sqlserver_srv
  OPTIONS (username 'sa', password 'p@ssw0rd');
CREATE USER MAPPING

test=# CREATE SCHEMA sqlserver_hr;
CREATE SCHEMA

test=# IMPORT FOREIGN SCHEMA HumanResources
  FROM SERVER sqlserver_srv
  INTO sqlserver_hr;
IMPORT FOREIGN SCHEMA

test=# SELECT ft.ftrelid::regclass AS "Table"
FROM pg_foreign_table ft;
                 Table
----------------------------------------
sqlserver_hr.Department
sqlserver_hr.Employee
sqlserver_hr.EmployeeDepartmentHistory
sqlserver_hr.EmployeePayHistory
sqlserver_hr.JobCandidate
sqlserver_hr.Shift
(6 rows)

test=# SELECT "DepartmentID", "Name", "GroupName"
FROM sqlserver_hr."Department"
LIMIT 4;
 DepartmentID |    Name     |        GroupName
--------------+-------------+-------------------------
            1 | Engineering | Research and Development
            2 | Tool Design | Research and Development
            3 | Sales       | Sales and Marketing
            4 | Marketing   | Sales and Marketing
(4 rows)

test=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION

test=# CREATE SERVER postgres_srv
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (dbname 'demo');
CREATE SERVER

test=# CREATE USER MAPPING FOR postgres
  SERVER postgres_srv
  OPTIONS (user 'postgres');
CREATE USER MAPPING

test=# IMPORT FOREIGN SCHEMA bookings
  FROM SERVER postgres_srv
  INTO public;
IMPORT FOREIGN SCHEMA

test=# SELECT * FROM bookings LIMIT 3;