2024/04/22

PostgreSQL

PostgreSQL是一個開源的物件型關聯式資料庫管理系統，在類似 BSD 授權與 MIT 授權的 PostgreSQL 授權下發行。

Install on openSUSE Tumbleweed

如果不是首次安裝而是升級，需要使用 pg_dump 執行 dump the databases，先對目前的資料進行備份。

首先是安裝（在這裡是 PostgreSQL 16）：

sudo zypper install postgresql16-server postgresql16 postgresql16-devel \
postgresql16-contrib postgresql-devel
如果需要設定 postgres 的密碼， 
sudo passwd postgres
先切換到 root，再切換到 postgres， 
sudo su postgres
然後 init database： 
/usr/bin/initdb /var/lib/pgsql/data
如果要手動開啟服務，使用： 
/usr/bin/pg_ctl -D /var/lib/pgsql/data -l logfile start
關閉： 
/usr/bin/pg_ctl -D /var/lib/pgsql/data -l logfile stop
或者是使用下列的方式開啟服務： 
sudo service postgresql start
關閉服務： 
sudo service postgresql stop

如果要開機的時候就啟動服務，使用： 
sudo chkconfig postgresql on
如果不要，使用： 
sudo chkconfig postgresql off

安裝完成以後，可以使用 psql postgres （使用者登入要使用 postgres）來確定目前的設定檔： 
SHOW config_file;
目前的 server 版本： 
SHOW server_version;
如果要使用 psql 列出目前的 database，使用 \l 命令： 
\l
如果要查詢目前的使用者連線： 
SELECT * FROM pg_stat_activity;
Digging into the currently opened database in psql one can use the \d command. 
\d

Add user to PostgreSQL

如果要使用指令增加使用者和資料庫（需要切換使用者身份到 postgres）： 
createuser danilo

使用 PostgreSQL SQL shell (psql) 增加使用者。

1. Add a user called danilo

Type the following command to create a user called danilo with a password called danilo:
template1=# CREATE USER danilo WITH PASSWORD 'danilo';

2. Add a database called danilo

Type the following command:
template1=# CREATE DATABASE danilo WITH OWNER danilo ENCODING 'UTF8';

3. Now grant all privileges on database

template1=# GRANT ALL PRIVILEGES ON DATABASE danilo to danilo;


如果要改使用者密碼，下面是 user danilo 的範例：
template1=# ALTER USER "danilo" WITH PASSWORD 'danilo';

或者是使用下列的方式設定 postgres：
template1=# \password postgres

要讓密碼生效，要修改 /var/lib/pgsql/data (或者是指定的 PostgreSQL DATADIR) 目錄下的 pg_hba.conf。 預設的認證方式為 trust，這表示不會做任何的密碼檢查，如果要開啟密碼檢查， 要把 trust 改為 scram-sha-256 或者是其它的認證方式（例如 md5 或者是 peer）。

如果設為 peer 就只會讓 local 同樣的 username 連線。
在需要讓其它的本機使用者連線的情況下，可以改為 scram-sha-256，下面是一個例子：

# "local" is for Unix domain socket connections only
local   all             all                                     scram-sha-256
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256

如果想知道目前的 username 與 password，可以使用 psql 查詢 pg_shadow 以後列出來：

psql -Atq -U postgres -d postgres -c "SELECT concat('\"', usename, '\" \"', passwd, '\"') FROM pg_shadow"

如果要查詢目前的使用者列表：

\du

如果要設定某一個使用者為 Superuser：

ALTER USER danilo WITH SUPERUSER;

如果要設定某一個使用者為 No Superuser

ALTER USER danilo WITH NOSUPERUSER;

Configuration

PostgreSQL uses three main configuration files to control overall operations. You can find these files in the initialized data cluster (the folder specified during the initialization process using initdb -d).

如果查詢 source code，我們發現 PostgreSQL 設定來源有14種，如default、environment variable、configuration file、client等，具體如下： 
const char *constGucSource_Names[] =
{
        /* PGC_S_DEFAULT */ "default",
        /* PGC_S_DYNAMIC_DEFAULT */ "default",
        /* PGC_S_ENV_VAR */ "environment variable",
        /* PGC_S_FILE */ "configuration file",
        /* PGC_S_ARGV */ "command line",
        /* PGC_S_GLOBAL */ "global",
        /* PGC_S_DATABASE */ "database",
        /* PGC_S_USER */ "user",
        /* PGC_S_DATABASE_USER */ "database user",
        /* PGC_S_CLIENT */ "client",
        /* PGC_S_OVERRIDE */ "override",
        /* PGC_S_INTERACTIVE */ "interactive",
        /* PGC_S_TEST */ "test",
        /* PGC_S_SESSION */ "session"
};

如果無法確定設定的來源，可以使用下列的 SQL 述句查詢：

select name, setting, source from pg_settings;

There are several different types of configuration settings, divided up based on the possible inputs they take

  • Boolean: true, false, on, off
  • Integer: Whole numbers (2112)
  • Float: Decimal values (21.12)
  • Memory / Disk: Integers (2112) or "computer units" (512MB, 2112GB). Avoid integers--you need to know the underlying unit to figure out what they mean.
  • Time: "Time units" aka d,m,s (30s). Sometimes the unit is left out; don't do that
  • Strings: Single quoted text ('pg_log')
  • ENUMs: Strings, but from a specific list ('WARNING', 'ERROR')
  • Lists: A comma separated list of strings ('"$user",public,tsearch2)

下面 14 個項定項目是大多數的使用者可能會需要設定的項目：

  1. listen_address
  2. max_connections
  3. shared_buffers
  4. work_mem
  5. maintenance_work_mem
  6. max_fsm_pages
  7. synchronous_commit
  8. checkpoint_segments
  9. wal_buffers
  10. autovacuum
  11. effective_cache_size
  12. default_statistics_target
  13. constraint_exclusion
  14. log_destination & log settings

之所以需要設定一些項目的理由，在於根據自己機器的狀況設定更好的設定值， 可以讓 PostgreSQL 執行的更好。

Each backend process allocates a local memory area for query processing; each area is divided into several sub-areas – whose sizes are either fixed or variable.

  • work_mem: Executor uses this area for sorting tuples by ORDER BY and DISTINCT operations, and for joining tables by merge-join and hash-join operations.
  • maintenance_work_mem: Some kinds of maintenance operations (e.g., VACUUM, REINDEX) use this area.
  • temp_buffers: Executor uses this area for storing temporary tables.

A shared memory area is allocated by a PostgreSQL server when it starts up. This area is also divided into several fix sized sub-areas.

  • shared buffer pool: PostgreSQL loads pages within tables and indexes from a persistent storage to here, and operates them directly.
  • WAL buffer: To ensure that no data has been lost by server failures, PostgreSQL supports the WAL mechanism. WAL data (also referred to as XLOG records) are transaction log in PostgreSQL; and WAL buffer is a buffering area of the WAL data before writing to a persistent storage.
  • commit log: Commit Log(CLOG) keeps the states of all transactions (e.g., in_progress,committed,aborted) for Concurrency Control (CC) mechanism.

如果要查詢目前的設定，可以使用下面的命令：

SHOW ALL;

如果要查詢單項，以 work_mem 來說：

show work_mem;
或者是查詢 pg_settings (a “system view” can be queried)， 
SELECT * FROM pg_settings WHERE name = 'work_mem';

All these can be edited with a text editor.

File Purpose
postgresql.conf Controls the listening port, IP, and default query planner settings, memory settings, path settings, and logging settings. Can be queried via pg_settings database view.
pg_hba.conf Controls the authentication models used by PostgreSQL and can be set per user, per database, per IP range, or a combination of all.
pg_indent.conf Controls mapping of an OS user to a PostgreSQL user.

postgresql.conf

The following settings are all located in the postgresql.conf file. Remember that these are default settings; many of these you can choose to override for each session, for each database, or for each user/role.

Option Description
listen_addresses Use ‘*’ to listen on all IPs of the server, ‘localhost’ to listen on just local, or a comma separated list of IPs to listen on. Requires service restart if changed and can only be set globally.
port Defaults to 5432, but can be changed to allow multiple postgresql daemon clusters/versions to coexist using same IP but different ports.
search_path List of default schemas that don’t need schema qualification. First schema is where non-schema qualified objects are created.
constraint_exclusion Options: on, off, or partial. Partial was introduced in 8.4 and is the new default. Allows planner to skip over tables if constraint ensures query conditions cannot be satisfied by the table. Mostly used for table partitioning via table inheritance.
shared_buffers Controls how much memory is allocated to PostgreSQL and shared across all processes. Requires service restart and can only be set globally.

In PostgreSQL 9.4, a new SQL construction ALTER SYSTEM was introduced that allows you to set these settings at the system level without editing the postgresql.conf. For many, you still need to do a service restart and for others at least a:

SELECT pg_reload_conf();

pg_hba.conf

PostgreSQL supports many authentication schemes to control access to the database. The pg_hba.conf file dictates which schemes are used based on the rules found in this file. You can mix and match various authentication schemes at the same time. The rules are applied sequentially such that the first match fitting a connection is the one that is used. This is important to remember because if you have a more restrictive rule above a less restrictive, then the more restrictive is the one that trumps.

The most commonly used authentication schemes are trust (which allows connections without a password) and md5 (which authenticates with md5 encrypted passwords). Others include: reject, crypt, password (this is plain text), krb5, ident (authenticate simply by identity of user in OS), pam, and ldap.


如果我們需要每個 query 的執行時間，可以在 psq 使用下列的命令開啟設定（預設值有可能是關閉）： 
\timing

Tools

PostgreSQL comes bundled with several tools useful for administration and query writing.

Tool Description
psql Command-line client packaged with PostgreSQL. Good for automating SQL jobs, copying data, outputing simple HTML reports.
createdb, dropdb For creating and dropping a database from the OS shell.
pgAdminIII Popular graphical user interface packaged with PostgreSQL.
pg_restore Command-line tool for restoring compressed or .tar backups.
pg_dump Command-line tool for doing backups. Great for automated backups.
pg_dumpall Command-line tool for dumping all databases into a single backup.
pgAgent A daemon/service that can be downloaded from http://www.pgadmin.org/download/pgagent.php. Used for scheduling SQL jobs and batch shell jobs. Jobs can be added easily and monitored using the PgAdmin III job interface.
pg_basebackup Used for doing filesystem hot backup of db data cluster.
pg_upgrade Used for updating in place from one major version of PostgreSQL to another.

SQL statement

Auto-increment column

PostgreSQL 並不支援標準語法，而是提供一個自己實作的近似語法： 
CREATE TABLE t1 (col1 SERIAL PRIMARY KEY);

UPSERT

在 PostgreSQL 15 之前 PostgreSQL 的 UPSERT 語法並未採用標準的 merge，而是使用自己實作的語法。 不過在 PostgreSQL 15 開始，就已經支援 SQL 的 merge 語法。

舉例來說，我們建立下面一個表格：

create table notes (title varchar(255), body text, modified timestamp, primary key(title));
於是可以使用 ON CONFLICT DO UPDATE SET 方式來決定是要 update 或者是 insert（PS. 舉例是用 GOLANG 的常數宣告）： 
        const insertString = `
              insert into notes (title, body, modified)
              values($1, $2, now()) ON CONFLICT (title)
              DO UPDATE SET (body,modified) = (EXCLUDED.body,now())`

Views

You can create a view over the query, which gives a name to the query that you can refer to like an ordinary table: 
CREATE VIEW myview AS
    SELECT city, temp_lo, temp_hi, prcp, date, location
        FROM weather, cities
        WHERE city = name;

SELECT * FROM myview;

Making liberal use of views is a key aspect of good SQL database design. Views allow you to encapsulate the details of the structure of your tables, which might change as your application evolves, behind consistent interfaces.

Foreign Keys

Foreign Keys 是維護你資料的 referential integrity 的方法，當插人一個新資料的時候，確定參考的資料是存在的。

下面是一個使用的範例： 
CREATE TABLE cities (
        city     varchar(80) primary key,
        location point
);

CREATE TABLE weather (
        city      varchar(80) references cities(city),
        temp_lo   int,
        temp_hi   int,
        prcp      real,
        date      date
);

Unique Constraints

Unique constraints ensure that the data contained in a column, or a group of columns, is unique among all the rows in the table. The syntax is:

CREATE TABLE products (
product_no integer UNIQUE,
name text,
price numeric
);
when written as a column constraint, and: 
CREATE TABLE products (
product_no integer,
name text,
price numeric,
UNIQUE (product_no)
);

Transactions

In PostgreSQL, a transaction is set up by surrounding the SQL commands of the transaction with BEGIN and COMMIT commands.

By default (without BEGIN), PostgreSQL executes transactions in "autocommit" mode, that is, each statement is executed in its own transaction and a commit is implicitly performed at the end of the statement (if execution was successful, otherwise a rollback is done).

Window Functions

A window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. But unlike regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row — the rows retain their separate identities. Behind the scenes, the window function is able to access more than just the current row of the query result.

A window function call always contains an OVER clause directly following the window function's name and argument(s). This is what syntactically distinguishes it from a regular function or aggregate function. The OVER clause determines exactly how the rows of the query are split up for processing by the window function. The PARTITION BY list within OVER specifies dividing the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row.

下面是一個範例： 
SELECT depname, empno, salary,
       rank() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;
結果： 
  depname  | empno | salary | rank
-----------+-------+--------+------
 develop   |     8 |   6000 |    1
 develop   |    10 |   5200 |    2
 develop   |    11 |   5200 |    2
 develop   |     9 |   4500 |    4
 develop   |     7 |   4200 |    5
 personnel |     2 |   3900 |    1
 personnel |     5 |   3500 |    2
 sales     |     1 |   5000 |    1
 sales     |     4 |   4800 |    2
 sales     |     3 |   4800 |    2
(10 rows)

As shown here, the rank function produces a numerical rank within the current row's partition for each distinct ORDER BY value, in the order defined by the ORDER BY clause. rank needs no explicit parameter, because its behavior is entirely determined by the OVER clause.

The rows considered by a window function are those of the "virtual table" produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function. A query can contain multiple window functions that slice up the data in different ways by means of different OVER clauses, but they all act on the same collection of rows defined by this virtual table.

Inheritance

Inheritance is a concept from object-oriented databases. It opens up interesting new possibilities of database design.

下面是一個使用的範例： 
CREATE TABLE cities (
  name       text,
  population real,
  altitude   int     -- (in ft)
);

CREATE TABLE capitals (
  state      char(2)
) INHERITS (cities);
當你查詢的時候，會列出 cities 和 capitals 的結果，如果只要 cities 的部份，下面是一個使用的範例： 
SELECT name, altitude
    FROM ONLY cities
    WHERE altitude > 500;

WITH Queries (Common Table Expressions)

WITH 是用來建立暫存的資料集，稱為通用資料表運算式 CTE (common table expression) 的暫存具名結果集。 通用資料表運算式可以包括指向本身的參考。 這稱為遞迴通用資料表運算式。

下面的資料來自於 PostgreSQL 網站。

In PostgreSQL, the WITH query provides a way to write auxiliary statements for use in a larger query. It helps in breaking down complicated and large queries into simpler forms, which are easily readable.

The WITH query being CTE query, is particularly useful when subquery is executed multiple times. It is equally helpful in place of temporary tables.

The optional RECURSIVE modifier changes WITH from a mere syntactic convenience into a feature that accomplishes things not otherwise possible in standard SQL. Using RECURSIVE, a WITH query can refer to its own output. A very simple example is this query to sum the integers from 1 through 100:

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

The general form of a recursive WITH query is always a non-recursive term, then UNION (or UNION ALL), then a recursive term, where only the recursive term can contain a reference to the query's own output. Such a query is executed as follows:

  1. Evaluate the non-recursive term. For UNION (but not UNION ALL), discard duplicate rows. Include all remainingrows in the result of the recursive query, and also place them in a temporary working table.

  2. So long as the working table is not empty, repeat these steps:

    1. Evaluate the recursive term, substituting the current contents of the working table for the recursive self-reference. For UNION (but not UNION ALL), discard duplicate rows and rows that duplicate any previous result row. Include all remaining rows in the result of the recursive query, and also place them in a temporary intermediate table.

    2. Replace the contents of the working table with the contents of the intermediate table, then empty the intermediate table.

Recursive queries are typically used to deal with hierarchical or tree-structured data. 
WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
    SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
  UNION ALL
    SELECT p.sub_part, p.part, p.quantity
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
  )
SELECT sub_part, SUM(quantity) as total_quantity
FROM included_parts
GROUP BY sub_part

Index

下面是一個建立 index 的範例： 
CREATE TABLE test1c (
    id integer,
    content varchar COLLATE "x"
);

CREATE INDEX test1c_content_index ON test1c (content);

GIN and GiST Index Types

There are two kinds of indexes that can be used to speed up full text searches. Note that indexes are not mandatory for full text searching, but in cases where a column is searched on a regular basis, an index is usually desirable.

CREATE INDEX name ON table USING GIN (column);
and: 
CREATE INDEX name ON table USING GIST (column);

GIN indexes are the preferred text search index type. As inverted indexes, they contain an index entry for each word (lexeme), with a compressed list of matching locations. Multi-word searches can find the first match, then use the index to remove rows that are lacking additional words. GIN indexes store only the words (lexemes) of tsvector values, and not their weight labels. Thus a table row recheck is needed when using a query that involves weights.

A GiST index is lossy, meaning that the index might produce false matches, and it is necessary to check the actual table row to eliminate such false matches. (PostgreSQL does this automatically when needed.) GiST indexes are lossy because each document is represented in the index by a fixed-length signature. The signature is generated by hashing each word into a single bit in an n-bit string, with all these bits OR-ed together to produce an n-bit document signature. When two words hash to the same bit position there will be a false match. If all words in the query have matches (real or false) then the table row must be retrieved to see if the match is correct.

Lossiness causes performance degradation due to unnecessary fetches of table records that turn out to be false matches. Since random access to table records is slow, this limits the usefulness of GiST indexes. The likelihood of false matches depends on several factors, in particular the number of unique words, so using dictionaries to reduce this number is recommended.

Full Text Search

Full text searching in PostgreSQL is based on the match operator @@, which returns true if a tsvector (document) matches a tsquery (query).

下面是一個使用的例子： 
SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ 'cat & rat'::tsquery;
 ?column?
----------
 t

SELECT 'fat & cow'::tsquery @@ 'a fat cat sat on a mat and ate a fat rat'::tsvector;
 ?column?
----------
 f
另外的例子： 
SELECT to_tsvector('fat cats ate fat rats') @@ to_tsquery('fat & rat');
 ?column?
----------
 t
The @@ operator also supports text input, allowing explicit conversion of a text string to tsvector or tsquery to be skipped in simple cases. The variants available are: 
tsvector @@ tsquery
tsquery  @@ tsvector
text @@ tsquery
text @@ text
It is possible to do a full text search without an index. A simple query to print the title of each row that contains the word friend in its body field is: 
SELECT title
FROM pgweb
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'friend');
PostgreSQL 可以使用 GIN index 來加快 text search 的速度： 
CREATE INDEX pgweb_idx ON pgweb USING GIN (to_tsvector('english', body));

UUID

可以安裝 uuid-ossp 模組來處理 UUID。下面是安裝的方式（需要有 Superuser 的權限）：

CREATE EXTENSION "uuid-ossp";

於是就可以使用下列的方式生成 UUID：

select uuid_generate_v4();

或者也可以使用下列的方式生成 UUID：

select (md5(random()::text || clock_timestamp()::text))::uuid;

NoSQL 與 PostgreSQL

PostgreSQL 提供了可選的 HStore key-value store extension， 並且 HStore extension 具有將 key-value store 轉為 JSON 的能力（hstore_to_json function）。

測試 PostgreSQL 是否有支援 hstore（使用 CREATE EXTENSION 語句）： 
package require tdbc::postgres
tdbc::postgres::connection create db -user postgres -password postgres -port 5432

set statement [db prepare {
     CREATE EXTENSION hstore
}]

$statement foreach row {
 puts $row
}

$statement close
db close
下面是一個使用的範例： 
CREATE TABLE products (
  id serial PRIMARY KEY,
  name varchar,
  attributes hstore
);
From here you can insert whatever you want into the attributes column. And then query based on those various keys or values. 
INSERT INTO products (name, attributes) VALUES (
 'Geek Love: A Novel',
 'author    => "Katherine Dunn",
  pages     => 368,
  category  => fiction'
);

SELECT name, attributes->'author' as author
FROM products
WHERE attributes->'category' = 'fiction'
The obvious benefit here is flexibility, but where it really shines is being able to leverage various index types. In particular, a GIN or GiST index will index every key and value within the hstore. This way when you filter on something it’ll use the index if it makes sense to the planner within Postgres.

PostgreSQL and JSON

在 PostgreSQL 9.2 （及以後）版本中，查詢結果可以轉換為 JSON data type 後返回。 PostgreSQL 也增加了 PLv8 extension，可以使用 JavaScript 來撰寫 PostgreSQL stored procedure。

That has changed with 9.4, with the introduction of the JSONB data type, which stores JSON data in binary form, such that it is both more compact and more efficient than the textual form. Moreover, the same GIN and GIST indexes that now are able to work so well with HStore data also are able to work well, and quickly, with JSONB data. So you can search for and retrieve text from JSONB documents as easily (or more) as would have been the case with a document database, such as MongoDB.

PostgreSQL provides two native operators -> and ->> to help you query JSON data.
  • The operator -> returns JSON object field by key.
  • The operator ->> returns JSON object field by text.
下面是一個使用的例子： 
CREATE TABLE integrations (id UUID, data JSONB);
INSERT INTO integrations VALUES (
  uuid_generate_v4(),
  '{
    "service": "salesforce",
    "id": "AC347D212341XR",
    "email": "craig@citusdata.com",
    "occurred_at": "8/14/16 11:00:00",
    "added": {
      "lead_score": 50
    },
    "updated": {
      "updated_at": "8/14/16 11:00:00"
    }
    }')
下面是一個最簡單的例子： 
CREATE TABLE people(
    id serial primary key,
    data jsonb not null
);

INSERT INTO people(data) VALUES ('{
    "name": "Bob",
    "addresses": [
        {
            "street": "Centre",
            "streetnumber": 24,
            "town": "Thornlie",
            "state": "WesternAustralia",
            "country": "Australia"
        },
        {
            "street": "Example",
            "streetnumber": "4/311-313",
            "town": "Auckland",
            "country": "NewZealand"
        }
    ],
    "phonenumbers": [
        {
            "type": "mobile",
            "number": "12345678"
        }
    ]
}');

INSERT INTO people(data) VALUES ('{
  "name": "Fred",
  "phonenumbers": [
    { "type": "mobile", "number": "12345678" }
  ]
}');

INSERT INTO people(data) VALUES ('{
  "name": "John Doe"
}');
下面則是使用 inner join 查詢 phonenumbers 的做法： 
select
  p1.id AS person1,
  p2.id AS person2,
  p1.data ->> 'name' AS "p1 name",
  p2.data ->> 'name' AS "p2 name",
  pns1 ->> 'type' AS "type",
  pns1 ->> 'number' AS "number"
from people p1
  inner join people p2
    on (p1.id > p2.id)
  cross join lateral jsonb_array_elements(p1.data -> 'phonenumbers') pns1
  inner join lateral jsonb_array_elements(p2.data -> 'phonenumbers') pns2
    on (pns1 -> 'type' = pns2 -> 'type' AND pns1 -> 'number' = pns2 -> 'number');
The ? operator will tell us if some part of JSON has a top level key: 
SELECT *
FROM companies
WHERE data->'company'->'tags' ? 'B2B'
如果要回來的 JSONB 結果比較漂亮，可以使用： 
SELECT jsonb_pretty(data)
FROM companies;

PostgreSQL and XML

PostgreSQL 提供了 XML data type and support functions 的功能 (SQL/XML)， 並且提供了一定程度的 XPATH 支援（xpath_exists function） 與輸出 XML 文件的能力，因此也可以考慮將 PostgreSQL 作為一個 XML 文件資料庫使用。

下面是一個 PostgreSQL XPath function 處理 XML 字串的例子（要注意例子中在 xpath() 的第三個參數設定了 namespace 的別名 n）： 
WITH x AS ( SELECT
'<promotions xmlns="http://www.demandware.com/xml/impex/promotion/2008-01-31">
    <campaign campaign-id="2013-1st-semester-jet-giveaways">
        <description>2013 1st Semester Jet Giveaways</description>
        <enabled-flag>true</enabled-flag>
        <start-date>2013-01-01T05:00:00.000Z</start-date>
        <end-date>2013-07-01T04:00:00.000Z</end-date>
        <customer-groups>
            <customer-group group-id="Everyone"/>
        </customer-groups>
    </campaign>
 </promotions>'::xml AS t
)
SELECT xpath('/n:promotions/n:campaign/n:description/text()', t
           , '{{n,http://www.demandware.com/xml/impex/promotion/2008-01-31}}')
FROM   x;

List extensions

如果要列出目前系統上的 PostgreSQL extension name（使用 TDBC）： 

package require tdbc::postgres
tdbc::postgres::connection create db -user postgres -password postgres -port 5432

set statement [db prepare {
     select extname from pg_extension
}]

$statement foreach row {
 puts $row
}

$statement close
db close

List tables

使用 psql 列出 schema public 下的所有 table： 
\dt public.*
You can select the tables from information_schema: 
SELECT * FROM information_schema.tables WHERE table_schema = 'public';
或者也可以使用下列的方式： 
select * from pg_tables where schemaname='public';

Client

C API (libpq)

The libpq library is the C interface to PostgreSQL. It is a set of library functions that allow client programs to interact with PostgreSQL.

下面是一個使用的範例，例印 PostgreSQL libpq 的版本： 
#include <libpq-fe.h>

int main() {

    int lib_ver = PQlibVersion();

    printf("Version of libpq: %d\n", lib_ver);

    return 0;
}
使用下列的方式得到 include/library path： 
$ pg_config --includedir
/usr/include/postgresql
$ pg_config --libdir
/usr/lib/postgresql95/lib64
所以可以使用下列的方式編譯： 
gcc lib_version.c -I`pg_config --includedir` -L`pg_config --libdir` -lpq -std=c99 -o test

再來是列印 PostgreSQL server 的版本： 
#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>

void do_exit(PGconn *conn) {

    PQfinish(conn);
    exit(1);
}

int main() {

    PGconn *conn = PQconnectdb("user=postgres password=postgres dbname=postgres");

    if (PQstatus(conn) == CONNECTION_BAD) {

        fprintf(stderr, "Connection to database failed: %s\n",
            PQerrorMessage(conn));
        do_exit(conn);
    }

    int ver = PQserverVersion(conn);

    printf("Server version: %d\n", ver);

    PQfinish(conn);

    return 0;
}

下面是一個簡單的 query 程式： 
#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>

void do_exit(PGconn *conn) {

    PQfinish(conn);
    exit(1);
}

int main() {

    PGconn *conn = PQconnectdb("user=postgres password=postgres dbname=postgres");i

    if (PQstatus(conn) == CONNECTION_BAD) {

        fprintf(stderr, "Connection to database failed: %s\n",
            PQerrorMessage(conn));
        do_exit(conn);
    }

    PGresult *res = PQexec(conn, "SELECT VERSION()");

    if (PQresultStatus(res) != PGRES_TUPLES_OK) {

        printf("No data retrieved\n");
        PQclear(res);
        do_exit(conn);
    }

    printf("%s\n", PQgetvalue(res, 0, 0));

    PQclear(res);
    PQfinish(conn);

    return 0;
}

ODBC (Linux/openSUSE)

下載 PostgreSQL ODBC Driver 編譯並且安裝以後，

unixODBC 需要設定二個檔案：/etc/unixODBC/odbc.ini 與 /etc/unixODBC/odbcinst.ini。 下面則是我目前 PostgreSQL 的設定。

odbcinst.ini：

[PSQL]
Description=PostgreSQL
Driver64=/usr/lib64/psqlodbcw.so
UsageCount=1

odbc.ini：

[PostgreSQL]
Description=PostgreSQL Data Source
Driver=PSQL
Database=postgres
UserName=postgres
Password=postgres
Host=localhost
Port=5432
ReadOnly=No
ShowSystemTables=No

下面就是測試的 Tcl script：

package require tdbc::odbc

set connStr "DSN=PostgreSQL; UID=postgres; PWD=postgres;"
tdbc::odbc::connection create db $connStr

set statement [db prepare {
    SELECT VERSION()
}]

$statement foreach row {
    puts [dict get $row version]
}

$statement close
db close

JDBC

PostgreSQL 提供了 JDBC driver 可以使用。

下面是使用 TDBCJDBC 的範例： 
package require tdbc::jdbc

set className    {org.postgresql.Driver}
set url          jdbc:postgresql://localhost:5432/danilo
set username     danilo
set password     danilo

tdbc::jdbc::connection create db $className $url $username $password -readonly 0

set statement [db prepare {select extname, extversion from pg_extension}]
puts "List extension name and version:"
$statement foreach row {
    puts "[dict get $row extname] - [dict get $row extversion]"
}

$statement close

db close

Node.js

我們使用 node-postgres 來連線。

首先建立一個目錄 pg-test，並且在目錄下執行

npm init -y

這會建立一個 package.json 檔案，我們可以對需要修改的部份再進行修改。修改 package.json，加入下列的設定：

  "type": "module",

再來使用 NPM 安裝 node-postgres：

npm install pg --save

建立 index.js，內容如下：

import pg from 'pg';

const config = {
    host: 'localhost',
    user: 'danilo',
    password: 'danilo',
    database: 'danilo',
    port: 5432,
    ssl: false
};

const client = new pg.Client(config);

client.connect(err => {
    if (err) {
        console.log("Connection failed.");
        throw err;
    }
});

const query = 'SELECT VERSION()';

client.query(query)
.then(res => {
   const rows = res.rows;
   console.log(`PostgreSQL version: ${rows[0].version}`);
   client.end();
   process.exit();
}).catch(err => {
   console.log(err);
});

執行的時候使用 node index.js 來執行。

Tcl client side interface

Pgtcl is a Tcl package for client programs to interface with PostgreSQL servers. 建立在 libpq 的基礎之上。

下面是一個最簡單的例子：

package require Pgtcl

set db [pg_connect -conninfo [list host = localhost user = postgres password = postgres dbname = postgres]]

pg_select $db "select version() as versoin" version {
    parray version
}

pg_disconnect $db

和 JDBC 單純的使用 ? 來代表參數不同，Pgtcl/libpq 使用 $1, $2 等來代表要傳遞的參數。


如果要使用共同的資料庫存取介面，建議使用 Tcl 8.6 內建的 TDBC 所提供的 PostgreSQL driver 來與 PostgreSQL server 進行連線。 Pgtcl 只有在單純使用 PostgreSQL 時才需要使用。

要注意的是，TDBC-Postgres 使用 PQprepare 來送出 SQL statement，同時並不指定 type，而是讓 PostgreSQL 來推斷 type， 再使用 PQdescribePrepared 來取得相關的 param 資料。但是 Pgtcl 不支援 PQprepare（只送出 SQL statement 到 server 分析）， 所以需要使用 PREPARE statement 才行。

on
Labels:

沒有留言:

張貼留言

注意：只有此網誌的成員可以留言。

訂閱： 張貼留言 (Atom)