学习笔记之PostgreSQL / pgAdmin / Psycopg / PostGIS

PostgreSQL: The world's most advanced open source database

• https://www.postgresql.org/
• POSTGRESQL: THE WORLD'S MOST ADVANCED OPEN SOURCE RELATIONAL DATABASE
• PostgreSQL: Documentation: 10: PostgreSQL 10.4 Documentation
  • https://www.postgresql.org/docs/10/static/index.html

PostgreSQL Tutorial - Learn PostgreSQL from Scratch

• http://www.postgresqltutorial.com/

PostgreSQL - 维基百科，自由的百科全书

• https://zh.wikipedia.org/wiki/PostgreSQL
• PostgreSQL是自由的对象-关系型数据库服务器（数据库管理系统），在灵活的BSD风格许可证下发行。它在其他开放源代码数据库系统（比如MySQL和Firebird），和专有系统（比如Oracle、Sybase、IBM的DB2和Microsoft SQL Server）之外，为用户又提供了一种选择。
• PostgreSQL不寻常的名字导致一些读者停下来尝试拼读它，特别是那些把SQL拼读为"sequel"的人。PostgreSQL开发者把它拼读为"post-gress-Q-L"。（Audio sample，5.6k MP3）。它也经常被简略念为"postgres"。

pgAdmin - PostgreSQL Tools

• https://www.pgadmin.org/
• PostgreSQL Tools
• pgAdmin is the most popular and feature rich Open Source administration and development platform for PostgreSQL, the most advanced Open Source database in the world.

PostgreSQL + Python | Psycopg

• http://initd.org/psycopg/
• Psycopg is the most popular PostgreSQL adapter for the Python programming language. At its core it fully implements the Python DB API 2.0 specifications. Several extensions allow access to many of the features offered by PostgreSQL.
• Psycopg – PostgreSQL database adapter for Python — Psycopg 2.7.5.dev0 documentation
  • http://initd.org/psycopg/docs/

GitHub - psycopg/psycopg2: PostgreSQL database adapter for the Python programming language

• https://github.com/psycopg/psycopg2
• PostgreSQL database adapter for the Python programming language http://initd.org/psycopg/

Using psycopg2 with PostgreSQL - PostgreSQL wiki

• https://wiki.postgresql.org/wiki/Using_psycopg2_with_PostgreSQL
• Psycopg2 is a fairly mature driver for interacting with PostgreSQL from the Python scripting language. It is written in C and provides to efficiently perform the full range of SQL operations against Postgres databases. This page deals with the 2nd version of the driver, not much older psycopg driver.
• Psycopg2 Tutorial - PostgreSQL wiki
  • https://wiki.postgresql.org/wiki/Psycopg2_Tutorial

psycopg2_百度百科

• https://baike.baidu.com/item/psycopg2/4439545
• psycopg2，是Python语言的PostgreSQL数据库接口，是对Psycopg 1.1.x版本进行的几乎完全的改写。

PostGIS — Spatial and Geographic Objects for PostgreSQL

• http://postgis.net/
• PostGIS is a spatial database extender for PostgreSQL object-relational database. It adds support for geographic objects allowing location queries to be run in SQL.
• Boundless : Introduction to PostGIS
  • http://workshops.boundlessgeo.com/postgis-intro/
  • PostGIS extends PostgreSQL with robust spatial database management capabilities. More info
• PostGIS 2.4.5dev Manual
  • http://postgis.net/docs/manual-2.4/
  • PostGIS is an extension to the PostgreSQL object-relational database system which allows GIS (Geographic Information Systems) objects to be stored in the database. PostGIS includes support for GiST-based R-Tree spatial indexes, and functions for analysis and processing of GIS objects.
  • Chapter 8. PostGIS Reference
    • https://postgis.net/docs/reference.html#Geometry_Accessors
    • ST_AsGeoJSON — Return the geometry as a GeoJSON element.
      • https://postgis.net/docs/ST_AsGeoJSON.html
    • ST_X — Return the X coordinate of the point, or NULL if not available. Input must be a point.
      • https://postgis.net/docs/ST_X.html
    • ST_Y — Return the Y coordinate of the point, or NULL if not available. Input must be a point.
      • https://postgis.net/docs/ST_Y.html
    • ST_Z — Return the Z coordinate of the point, or NULL if not available. Input must be a point.
      • https://postgis.net/docs/ST_Z.html
    • ST_Distance — For geometry type Returns the 2D Cartesian distance between two geometries in projected units (based on spatial ref). For geography type defaults to return minimum geodesic distance between two geographies in meters.
      • https://postgis.net/docs/ST_Distance.html
  • Chapter 14. PostGIS Special Functions Index
    • https://postgis.net/docs/PostGIS_Special_Functions_Index.html

PostGIS - Wikipedia

• https://en.wikipedia.org/wiki/PostGIS
• PostGIS (/ˈpoʊstdʒɪs/ POST-jis) is an open source software program that adds support for geographic objects to the PostgreSQL object-relational database. PostGIS follows the Simple Features for SQL specification from the Open Geospatial Consortium (OGC).

PostGIS - 维基百科，自由的百科全书

• https://zh.wikipedia.org/wiki/PostGIS
• PostGIS 是一个开源程序，它为对象－关系型数据库PostgreSQL提供了存储空间地理数据的支持，使PostgreSQL成为了一个空间数据库，能够进行空间数据管理、数量测量与几何拓扑分析。PostGIS 实现了Open Geospatial Consortium所提出的基本要素类（点、线、面、多点、多线、多面等）的SQL实现参考。

postgis_百度百科

• https://baike.baidu.com/item/postgis/6091849
• PostgreSQL 是一种对象-关系型数据库管理系统（ORDBMS），也是目前功能最强大、特性最丰富和最复杂的自由软件数据库系统。它起源于伯克利（BSD）的数据库目前功能最强大、特性最丰富和最复杂的研究计划，目前是最重要的开源数据库产品开发项目之一， 有着非常广泛的用户。

---

PostgreSQL DBA常用SQL查询语句

• https://mp.weixin.qq.com/s/zsGTrlBZzjvKexwAYsE5yQ
• http://blog.itpub.net/30126024/viewspace-2655205/

PostgreSQL的几种分布式架构对比

• https://mp.weixin.qq.com/s/nmL73j7hMdKHeSRYXdbdxA

---

PostgreSQL: Documentation: 10: 9.9. Date/Time Functions and Operators

• https://www.postgresql.org/docs/10/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT
• PostgreSQL provides a number of functions that return values related to the current date and time. These SQL-standard functions all return values based on the start time of the current transaction
• PostgreSQL also provides functions that return the start time of the current statement, as well as the actual current time at the instant the function is called.
• All the date/time data types also accept the special literal value now to specify the current date and time (again, interpreted as the transaction start time).
• timestamp '2001-09-28 23:00' - interval '23 hours'

How to compare timestamp with tsrange ?

• PostgreSQL: Documentation: 9.3: Range Functions and Operators
• https://www.postgresql.org/docs/9.3/functions-range.html
• '[2011-01-01,2011-03-01)'::tsrange @> '2011-01-10'::timestamp

How to replace null with string ?

• PostgreSQL: Documentation: 10: 9.17. Conditional Expressions
• https://www.postgresql.org/docs/10/static/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL
• 9.17.2. COALESCE
  • The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. It is often used to substitute a default value for null values when data is retrieved for display

How to use if-else statement ?

• sql - IF-THEN-ELSE statements in postgresql - Stack Overflow
  • https://stackoverflow.com/questions/19029842/if-then-else-statements-in-postgresql
• PostgreSQL: Documentation: 10: 9.17. Conditional Expressions
  • https://www.postgresql.org/docs/10/static/functions-conditional.html#FUNCTIONS-CASE
  • The SQLCASE expression is a generic conditional expression, similar to if/else statements in other programming languages

How to pass parameters to SQL queries ?

• Basic module usage — Psycopg 2.8.4.dev0 documentation
  • http://initd.org/psycopg/docs/usage.html
• The cursor class — Psycopg 2.8.4.dev0 documentation
  • http://initd.org/psycopg/docs/cursor.html?highlight=mogrify#cursor.mogrify
  • mogrify(operation[, parameters])
  • Return a query string after arguments binding. The string returned is exactly the one that would be sent to the database running the execute() method or similar.
  • The returned string is always a bytes string.

How to use psql — PostgreSQL interactive terminal ?

• PostgreSQL: Documentation: 13: psql
• psql -h hostname -d dbname -U usrname -W

---

psycopg2.OperationalError: FATAL:  remaining connection slots are reserved for non-replication superuser connections ?

• postgresql - Heroku "psql: FATAL: remaining connection slots are reserved for non-replication superuser connections" - Stack Overflow
  • https://stackoverflow.com/questions/11847144/heroku-psql-fatal-remaining-connection-slots-are-reserved-for-non-replication
  • https://stackoverflow.com/questions/10419665/how-does-pgbouncer-help-to-speed-up-django/10420469#10420469
• postgresql - How to increase the max connections in postgres? - Stack Overflow
  • https://stackoverflow.com/questions/30778015/how-to-increase-the-max-connections-in-postgres
  • https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
  • https://wiki.postgresql.org/wiki/Number_Of_Database_Connections