Иерархические и рекурсивные запросы в SQL
- 1 year ago
- 0
- 0
PostgreSQL (???????????? «????-????-???-??» ) — ????????? ????????-??????????? ??????? ?????????? ?????? ?????? (????).
?????????? ? ??????????? ??? ????????? UNIX-???????? ????????, ??????? AIX , ????????? BSD-??????? , HP-UX , IRIX , Linux , macOS , Solaris / OpenSolaris , Tru64 , QNX , ? ????? ??? Microsoft Windows .
PostgreSQL ?????????? ?? ????? SQL ? ???????????? ?????? ?? ???????????? ????????? SQL:2011 ? ??? ???????????? SQL:2016 ? ????? ?????? ? ??????? ? ??????? JSON .
? PostgreSQL ?????? 12 ???? ????????? ??????????? :
???????????? ?????? ???? ?????? | ??? ??????????? |
???????????? ?????? ??????? | 32 ????? |
???????????? ?????? ???? | 1 ????? |
???????? ??????? ? ??????? | ?????????? ????????? ??????? |
???????? ????? ? ?????? | 250—1600, ? ??????????? ?? ????? ????? |
???????? ???????? ? ??????? | ??? ??????????? |
???????? ????????? PostgreSQL ?????????:
PostgreSQL ??????? ?? ?????? ?????????????? ???? Postgres, ????????????? ??? open-source ?????? ? ?????????????? ???????????? ? ?????? . ? ?????????? Postgres, ?????????? ? 1986 ????, ???? ???????????????? ????????? ????? ???????????? , ???????????? ????? ??????? ??????? Ingres , ?? ??? ?????? ??? ?????????????? ????????? Computer Associates . ???????? ???????????????? ??? « Post In gres », ? ??? ???????? Postgres ???? ????????? ?????? ?????? ?????????.
???????????? ? ??? ???????? ????????????? ????? ???? ? ??????? ?????? ??? ? 1986 ?? 1994 ????. ?? ???? ?????? ? ????????? ???? ??????? ?????????, ???????, ???????????????? ???? ? ?????? ??????????. ? 1995 ???? ?????????? ????? ???????????: ???????????? ??????????? ?????????? ???? ? ???????? ???????????? ???? , ???????????? ??? ??????????? ??????????? ????????? (????????????? ???????????? ????????? Informix ), ? ??? ???????? ??????????? ????? ?????? Postgres — Postgres95, ? ??????? ???? ???????? POSTQUEL — ???????? Ingres — ??? ??????? ?? SQL.
?????????? Postgres95 ???? ???????? ?? ??????? ???????????? ? ???????? ??????? ???????????. ????? ???? ???????? ???, ??? ??????? ??? ???????? ? ??????????? ? ??????? ?????? — PostgreSQL.
?????? | ???? ??????? ?????? | ????????? ???????? ?????? | ???? ?????????? ?????? | ????????? ????????? | ????????????? ??????????? |
---|---|---|---|---|---|
6.0 | 1997-01-29 | ?/? | ?/? | ?/? | ????????? ?????? ?????? PostgreSQL. ?????????? ???????, ??????? pg_dumpall, ?????????????? ? ?????????????? ident |
6.1 | 1997-06-08 | ?????? ??????, ?? ??????????????: 6.1.1 | 1997-07-22 | ?/? | ??????????????? ???????, ??????????????????, ??? ?????? money, GEQO (GEnetic Query Optimizer) |
6.2 | 1997-10-02 | ?????? ??????, ?? ??????????????: 6.2.1 | 1997-10-17 | ?/? | ????????? JDBC , ???????? , ????????? ???????????????? ???????, ??????????? |
6.3 | 1998-03-01 | ?????? ??????, ?? ??????????????: 6.3.2 | 1998-04-07 | 2003-03-01 | ????????? subselect ?? SQL-92 , PL/pgTCL |
6.4 | 1998-10-30 | ?????? ??????, ?? ??????????????: 6.4.2 | 1998-12-20 | 2003-10-30 | ????????? VIEW (?????? ? ?????? read-only) ? ??????, PL/pgSQL |
6.5 | 1999-06-09 | ?????? ??????, ?? ??????????????: 6.5.3 | 1999-10-13 | 2004-06-09 | MVCC , ????????? ???????, ????????? ????? ?????????? SQL (CASE, INTERSECT ? EXCEPT) |
7.0 | 2000-05-08 | ?????? ??????, ?? ??????????????: 7.0.3 | 2000-11-11 | 2004-05-08 | ??????? ??????, ????????? SQL-92 ??? ?????????? (JOIN) |
7.1 | 2001-04-13 | ?????? ??????, ?? ??????????????: 7.1.3 | 2001-08-15 | 2006-04-13 | ??????????? ???????????? , OUTER JOIN |
7.2 | 2002-02-04 | ?????? ??????, ?? ??????????????: 7.2.8 | 2005-05-09 | 2007-02-04 | PL/Python, ???? OID ?????????? ?????????????? ? ????????, ??????????????????? ? ??????????? ????????? |
7.3 | 2002-11-27 | ?????? ??????, ?? ??????????????: 7.3.21 | 2008-01-07 | 2007-11-27 | ????? , ????????? ???????, ?????????????? ??????? |
7.4 | 2003-11-17 | ?????? ??????, ?? ??????????????: 7.4.30 | 2010-10-04 | 2010-10-01 | ??????????? ? JOIN?? ? ??????? data warehouse |
8.0 | 2005-01-19 | ?????? ??????, ?? ??????????????: 8.0.26 | 2010-10-04 | 2010-10-01 | ?????? ??? Microsoft Windows , ????? ?????????? ? ??????????? , ????????? ????????????s , point-in-time recovery |
8.1 | 2005-11-08 | ?????? ??????, ?? ??????????????: 8.1.23 | 2010-12-16 | 2010-11-08 | ??????????? ??????????????????, ?????????? commit, ??????????????? ?????? , index bitmap scan ? ???????????? SQL-????????, ??????????? ????? ?????????? ?? ?????? ?????, ???? |
8.2 | 2006-12-05 | ?????? ??????, ?? ??????????????: 8.2.23 | 2011-12-05 | 2011-12-05 | ??????????? ??????????????????, ?????????? ???????? ??? ????????? ??????, ???????????????? ??????????, ????? «??????? ???????» ??? ?????????????? ???? ?? ??????? WAL |
8.3 | 2008-02-04 | ?????? ??????, ?? ??????????????: 8.3.23 | 2013-02-07 | 2013-02-07 | ???????? Heap-only tuples, ?????????????? ????? , SQL/XML , ???? ENUM, ???? UUID |
8.4 | 2009-07-01 | ?????? ??????, ?? ??????????????: 8.4.22 | 2014-07-24 | 2014-07-24 | ??????? ???????, ????? ??????? ?? ?????? ????? (???????) ???????, ???????????? ?????????????? ????, ??????? ?????????? ?? ?????? ????, ????? ????????? ??????? ? ??????????? ??????? |
9.0 | 2010-09-20 | ?????? ??????, ?? ??????????????: 9.0.23 | 2015-10-08 | 2015-10-08 | ?????????? ????????? ???????? ?????????? , ????? ???????? ??????? , ?????????? ??? ????????? ???????, ????????? 64-?????? ?????? Windows |
9.1 | 2011-09-12 | ?????? ??????, ?? ??????????????: 9.1.24 | 2016-10-27 | 2016-10-27 | ?????????? ??????????, ??????? ??????????, ?????????? ??? ????????? ???????, ????????????? ???????, ??????? ???????? ?????????? «????????????? ???????? ???????» , ??????????? ?????? ? ????? ????????? ???????? SQL, ?????????? ? SELinux , ??????????, ??????? ??????? |
9.2 | 2012-09-10 | ?????? ??????, ?? ??????????????: 9.2.24 | 2017-11-09 | 2017-11-09 | ????????? ????????? ??????????, ???????????? ?????? ???????, ?????? ????????? JSON , ?????????? ?????????? ????????????, ??????????? ????, ??????? pg_receivexlog, ??????? space-partitioned GiST |
9.3 | 2013-09-09 | ?????? ??????, ?? ??????????????: 9.3.25 | 2018-11-08 | 2018-11-08 | ????????????? ??????? ??????? ????????, ??????????? ????? ??????? ??? ??????????? ???????????? ??????, ????????? JSON, LATERAL JOIN, ???????? ?????? pg_dump, ????? ??????? pg_isready ??? ??????????? ???????, ???????????? ??????????? ????????? ? ????????????? ???? (view), ???????????? ??????? ???????, ????????????????? ????????????? , ????????? ?????????? |
9.4 | 2014-12-18 | ?????? ??????, ?? ??????????????: 9.4.26 | 2020-02-13 | 2020-02-13 | ??? ?????? JSONB , ???????? ALTER SYSTEM ??? ????????? ???????? ? ???????????? ???????, ??????????? ????????? ????????????????? ????????????? ??? ?????????? ??????, ???????????? ???????????/??????/????????? ??????? ??????? ?????????, API ??????????? ????????????? ??? ??????????? ? ??, ????????? ?????? ???????? GIN , ????????? ??????? ???? huge ??? Linux, ???????????? ???? ???? ?????? ? ?????????????? pg_prewarm, ????? ?????????? ?????? Hstore ??????????????? ??? ???????????????? ?????? ???????? ?????????? ?????? |
9.5 | 2016-01-07 | ?????? ??????, ?? ??????????????: 9.5.25 | 2021-02-11 | 2021-02-11 | UPSERT, ???????? ?????? ?????, TABLESAMPLE, CUBE/ROLLUP, GROUPING SETS, ? ????? ??? ??????? BRIN |
9.6 | 2016-09-29 | ?????? ??????, ?? ??????????????: 9.6.24 | 2021-11-11 | 2021-11-11 | ????????? ???????????? ????????, ?????????????????? ??????????? PostgreSQL ??? ?????? ?? ?????????? ??????? (FDW) - ???????? ???????? pushdown ??? sort/join, ????????????? ?????????? ??????? ???????? ???????, ???????? ???????? vacuum ?? ??????? ???????? |
10 | 2017-10-05 | ?????? ?????????????? ??????: 10.20 | 2022-02-10 | 2022-11-10 | ?????????? ?????????? , ????????????? ??????????????? ??????, ?????????? ???????????? ?????????? ? ???????? |
11 | 2018-10-18 | ?????? ?????????????? ??????: 11.15 | 2022-02-10 | 2023-11-09 | ???????? ???????????? ? ?????????????????? ??? ???????????????, ????????? ?????????? ? ???????? ??????????, ?????????? ??????????? ??? ????????????? ?????????? ? ????????, just-in-time (JIT) ?????????? ????????? |
12 | 2019-10-03 | ?????? ?????????????? ??????: 12.10 | 2022-02-10 | 2024-11-14 | ????????? ???????? ? ????? ????????? ????????????? ????????? ????????????; ????????? ????????? ????? ????? SQL/JSON; ???????????? ???????; ????????? ? ??????????????????? ? ??????????????; ????? ????????? ??? ???????? ???????????? ??????? ?????? ? ????????? |
13 | 2020-09-24 | ?????? ?????????????? ??????: 13.6 | 2022-02-10 | 2025-11-13 | ???????????? ????? ? ???????? B-tree ???????? ? ??????? ?????? ?????, ????????? ?????????????????? ????????, ??????? ?????????? ???????? ??? ???????????????? ???????, ???????? ???????????? ???????? ??? ????????????? ??????????? ??????????, ?????????????? ?????????????? ????????, ??????????????? ?????????? |
14 | 2021-09-30 | ??????? ??????: 14.2 | 2022-02-10 | 2026-11-12 | ????????? ???????????? ?????????? SQL ????? SEARCH ? CYCLE ? ????? ????????? ???????? , ????????? ????????? DISTINCT ? GROUP BY |
??????? ???????? ??????? ????, ???????????? ?? ???????, ? ?? ?? ??????? ??. ???? ??? ????? ???????? ?? ?????? SQL, ?????????? ?????????????? ??????, ????????, ???????? ????????? ? ?????? , ??????? ?? ????? SQL ? ??????? ????????????? ????????? ???????? ??????????. ??????? ????? ???????? ? ?????????????? ?????? ?? ????????? ??????:
PostgreSQL ????????? ????????????? ???????, ???????????? ????? ???????, ??????? ????? ????? ???????????? ??? ??, ??? ? ????????? ?????????? ???????? ???????.
??????? ????? ??????????? ??? ? ??????? ?? ?????????, ??? ? ? ??????? ???????? ????????????.
?????? ??????? ??????????????? ? ????????? ??????????? , ?????? ????? ????? ????????? ???? ????????. ? ??????? ?????? ???????? ????????? ?????????? ??????, ??????? ????????? ????????? ??? ?? ??????????? ?????? ??? ????????? ???????, ??????????????? ? ???????.
???????? ???????????? ??? ???????, ???????????? DML -??????????. ????????, ???????? INSERT ????? ????????? ???????, ??????????? ??????????? ?????? ?? ???????????? ???????????? ????????. ??? ????????? ??????? ??? ????????? ????? ?????????????? ????????? ????? ???????????????? (??. ????).
???????? ????????????? ? ?????????. ????????????? ???????? ??????????? ? ?????????? ???????.
???????? ?????? ( ????. rules) ???????????? ????? ???????? ???????? ???????????????? ???????????? ?? ?????? DML -????????, ?? ? ???????? ???????. ???????? ??????? ?? ????????? ????????? ??????????? ? ???, ??? ??????? ??????????? ?? ????? ??????? ???????, ?? ?????? ???????????? ????? ?????????? ? ?????? ???????? ??????????. ??????? ????????? ?????????????? ????????? ??????? ??? ?????????? SQL-???????? ? ???????. ??????? ???????? ???????? ?????????? ????????? ????????????? ( ????. views): ??? ???????? ????????????? ????????? ???????, ??????? ??????????, ??? ?????? ?????????? ???????? ??????? ? ????????????? ??????? ?????? ????????? ???????? ??????? ? ??????? ???????/???????? ? ?????? ??????? ???????, ??????? ? ?????? ??????????? ?????????????. ??? ???????? ?????????????, ?????????????? ???????? ??????????, ??????? ??? ???????? ???????, ????????? ? ???????? ????? ?????? ???? ?????????? ?????????????.
? PostgreSQL ??????? ????????? ???????? ????????? ?????: B-?????? , ??? , GiST , GIN , BRIN , Bloom . ??? ????????????? ????? ????????? ????? ???? ????????. ??????? ? PostgreSQL ???????? ?????????? ??????????:
ORDER BY ... DESC
?? ?????;
PostgreSQL ???????????? ????????????? ??????????? ?? ??????????? ?????????????? ? ??????? ????????? Multiversion Concurrency Control ( MVCC ). ????????? ????? ??????????? ?????????? ACID ? ??????????? ???????? ????? ? ??????????? ??????.
PostgreSQL ???????????? ??????? ????? ?????????? ????? ??????:
????? ????, ???????????? ????? ?????????????? ????????? ????? ????????? ??? ???? ? ??????????????? ??? ??? ????????? ?????????????? ? ??????? GiST .
PostgreSQL ????? ???? ???????? ????????????? ??? ??????????? ???? ??????????? ? ????? ???????. ???? ??????????? ????????? ???????????:
??????? ????? ??????????? ?????????????? ? ?????? ????? ?? ?????? ?????? (????????????). ??? ???? ??????, ??????????? ? ??????????? ???????, ????????????? ????? ??????????? (???? ??? ?? ??????? ????????) ? ???????? ? ???????????? ???????.
? PostgreSQL 10 ??? ???????? ???????? ??????????????? ??????. ??????????????? ????????????? ??? ?????????? ????? ??????? ?? ?????????, ??? ?????????? ?????? (partitions). ??????????????? ????? ? ?????????????, ?? ????? ????? ??????????? ? ???????????? ????????? ? ????? ??????? ???????????, ??? ????????? ????????? ?????????????? ??????????? ??? ???????????? ????????.
OUTER JOIN
,
UNION
,
UNION ALL
,
EXCEPT
,
INTERSECT
? ???????????
???????? ??????????? ??????????????????? ???????????? ?????????? ?? ?? ??????? ??????, ??????????? ? 2005 ????, ? ???????? ???? PostgreSQL ???? ??????? 20 ?????????? ???? ?? 775 000 ????? ????????? ???? (? ???????, ???? ?????? ?? 39 000 ????? ????) . ??? ?????????: MySQL — 97 ???????, ???? ?????? ?? 8 000 ????? ????; FreeBSD (???????) — 306 ???????, ???? ?????? ?? 2 500 ????? ????; Linux (?????? ????) — 950 ???????, ???? ?????? ?? 800 ????? ????.
???????? PostgreSQL ????????? ?? ??? ?????? ????????? ?????????, ? ??? ????? ????????????, ????? . ?? ???????? ????????? ???????? .
?? ???? PostgreSQL ????????? EnterpriseDB ???? ??????????? ?????? ???????? ???? ????, ?????????? ???????? ??? ????????????? ????????????? — Postgres Plus (??????? ??????? ?????? ?? ????????? ? ????????? ????????? ??????; ????? ????????? ?????? ??? ????????????? ???????????? ???????????? ????????? ????????) ? Postgres Plus Advanced Server (?????????? PostgreSQL ???????????? ????????????? ??? ??????????? ????????????? ? Oracle Database ) . ? ????????? ???????? ?????? ????????? ?????????? ????? ?? ??? ????????????? ? ??????????????? ??? ??????:
?????????? ? ?????? ???????????? ????????, ????????? ?? ???? PostgreSQL ? ??????????? ?? ?????????? ?????????:
Postgres Pro ???????? Postgres Professional — ??????????????? ? ?????? ???????????, ?????????? ??????????????????, ????????????, ??? ???????, ??????????? ? ??????? ????????? ???????. ?????? ?????? PostgreSQL ??????????????? ????? ? ????? ??????? ?????????? ???????????. ? ?????? ??????????? ???????????????? ??????????????? ??? ?????? Oracle .
|deadlink=
(
???????
)