PostgreSQL-概要

PostgreSQLは、強力なオープンソースのオブジェクトリレーショナルデータベースシステムです。 15年以上の活発な開発フェーズと、信頼性、データの整合性、正確性で高い評価を得ている実績のあるアーキテクチャを備えています。

このチュートリアルでは、PostgreSQLのクイックスタートを提供し、PostgreSQLプログラミングに慣れるようにします。

PostgreSQLとは何ですか？

PostgreSQL（ post-gress-Q-L と発音）は、世界中のボランティアチームによって開発されたオープンソースのリレーショナルデータベース管理システム（DBMS）です。 PostgreSQLは企業やその他の民間企業によって管理されておらず、ソースコードは無料で入手できます。

PostgreSQLの簡単な歴史

PostgreSQLは、もともとPostgresと呼ばれ、マイケルストーンブレイカーというコンピューターサイエンスの教授によってUCBで作成されました。 Stonebrakerは、1986年にComputer Associatesが所有する前身のIngresのフォローアッププロジェクトとしてPostgresを開始しました。

1977-1985 -INGRESと呼ばれるプロジェクトが開発されました。
リレーショナルデータベースの概念実証
1980年にIngres社を設立
1994年にComputer Associatesが購入
1986-1994 -ポストグレス
オブジェクト指向とクエリ言語に焦点を当てたINGRESの概念の開発-Quel
INGRESのコードベースはPOSTGRESのベースとして使用されませんでした
Illustraとして商品化（Informixが購入、IBMが購入）
1994-1995 -Postgres95
SQLのサポートは1994年に追加されました
1995年にPostgres95としてリリース
1996年にPostgreSQL 6.0として再リリース
PostgreSQLグローバル開発チームの設立

PostgreSQLの主な機能

PostgreSQLは、Linux、UNIX（AIX、BSD、HP-UX、SGI IRIX、Mac OS X、Solaris、Tru64）、およびWindowsを含むすべての主要なオペレーティングシステムで実行されます。テキスト、画像、音声、およびビデオをサポートし、C/C ++、Java、Perl、Python、Ruby、Tcl、およびOpen Database Connectivity（ODBC）のプログラミングインターフェイスが含まれています。

PostgreSQLはSQL標準の大部分をサポートし、以下を含む多くの最新の機能を提供します-

複雑なSQLクエリ
SQLサブセレクト
外部キー
引き金
ビュー
トランザクション
マルチバージョン同時実行制御（MVCC）
ストリーミングレプリケーション（9.0以降）
ホットスタンバイ（9.0以降）

PostgreSQLの公式ドキュメントをチェックして、上記の機能を理解できます。 PostgreSQLは、ユーザーがさまざまな方法で拡張できます。たとえば、新しいを追加することにより-

データ型
関数
オペレータ
集計関数
インデックスメソッド

手続き言語のサポート

PostgreSQLは4つの標準手続き言語をサポートしています。これにより、ユーザーは任意の言語で独自のコードを記述でき、PostgreSQLデータベースサーバーで実行できます。これらの手続き言語は、PL/pgSQL、PL/Tcl、PL/Perl、PL/Pythonです。また、PL/PHP、PL/V8、PL/Ruby、PL/Javaなど、他の非標準手続き言語もサポートされています。

PostgreSQL-環境設定

PostgreSQLの基本を理解するには、まずPostgreSQLをインストールしましょう。この章では、Linux、Windows、およびMac OSプラットフォームへのPostgreSQLのインストールについて説明します。

Linux/UnixにPostgreSQLをインストールする

所定の手順に従って、LinuxマシンにPostgreSQLをインストールします。インストールに進む前に、 root としてログインしていることを確認してください。

EnterpriseDBから、必要なPostgreSQLのバージョン番号と、可能な限り正確にプラットフォームを選択してください。
64ビットCentOS-6マシン用に postgresql-9.2.4-1-linux-x64.run をダウンロードしました。さて、次のように実行してみましょう-

[root@host]# chmod +x postgresql-9.2.4-1-linux-x64.run
[root@host]# ./postgresql-9.2.4-1-linux-x64.run
------------------------------------------------------------------------
Welcome to the PostgreSQL Setup Wizard.

------------------------------------------------------------------------
Please specify the directory where PostgreSQL will be installed.

Installation Directory [/opt/PostgreSQL/9.2]:

インストーラーを起動すると、インストールの場所、データベースを使用するユーザーのパスワード、ポート番号などの基本的な質問がいくつか表示されます。したがって、パスワードを除くすべてのデフォルト値を保持します。パスワードは、選択によりパスワードを指定できます。 LinuxマシンにPostgreSQLをインストールし、次のメッセージを表示します-

Please wait while Setup installs PostgreSQL on your computer.

 Installing
 0% ______________ 50% ______________ 100%
 #########################################

-----------------------------------------------------------------------
Setup has finished installing PostgreSQL on your computer.

次のインストール後の手順に従って、データベースを作成します-

[root@host]# su - postgres
Password:
bash-4.1$ createdb testdb
bash-4.1$ psql testdb
psql (8.4.13, server 9.2.4)

test=#

次のコマンドを使用して、実行されていない場合にpostgresサーバーを起動/再起動できます-

[root@host]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

インストールが正しければ、上記のようにPotsgreSQLプロンプト* test =＃*が表示されます。

WindowsへのPostgreSQLのインストール

指定された手順に従って、WindowsマシンにPostgreSQLをインストールします。インストール中にサードパーティのアンチウイルスをオフにしてください。

EnterpriseDBから、必要なPostgreSQLのバージョン番号と、可能な限り正確にプラットフォームを選択してください。
32ビットモードで実行されているWindows PC用にpostgresql-9.2.4-1-windows.exeをダウンロードしたので、PostgreSQLをインストールする管理者として postgresql-9.2.4-1-windows.exe を実行します。インストールする場所を選択します。デフォルトでは、Program Filesフォルダー内にインストールされます。

postgresql install dir

インストールプロセスの次のステップは、データを保存するディレクトリを選択することです。デフォルトでは、「data」ディレクトリの下に保存されます。

postgresql install data dir

次に、セットアップはパスワードを要求するため、お気に入りのパスワードを使用できます。

postgresqlインストールパスワード

次のステップ;ポートをデフォルトのままにします。

postgresqlインストールポート

次のステップで、「ロケール」を尋ねられたときに、「英語、米国」を選択しました。
PostgreSQLをシステムにインストールするのに時間がかかります。インストールプロセスが完了すると、次の画面が表示されます。チェックボックスをオフにして、[完了]ボタンをクリックします。

postgresqlインストール完了

インストールプロセスが完了すると、PostgreSQL 9.2のプログラムメニューからpgAdmin III、StackBuilder、PostgreSQLシェルにアクセスできます。

MacにPostgreSQLをインストールする

所定の手順に従って、PostgreSQLをMacマシンにインストールします。インストールに進む前に、 administrator としてログインしていることを確認してください。

EnterpriseDBで入手可能なMac OS用のPostgreSQLの最新バージョン番号を選択してください
OS Xバージョン10.8.3で動作しているMac OS用に postgresql-9.2.4-1-osx.dmg をダウンロードしました。ここで、ファインダーでdmgイメージを開き、ダブルクリックするだけで、次のウィンドウでPostgreSQLインストーラーが表示されます-

Macへのpostgresqlのインストール、width = 500

次に、 postgres-9.2.4-1-osx アイコンをクリックして、警告メッセージを表示します。警告を受け入れて、インストールを続行します。次のウィンドウに見られるように、管理者パスワードを要求します-

Macのpostgresqlパスワード、width = 500

パスワードを入力し、インストールを続行し、この手順の後、Macマシンを再起動します。次のウィンドウが表示されない場合は、インストールをもう一度開始してください。

Postgresql Macでインストールを開始、width = 500

インストーラーを起動すると、インストールの場所、データベースを使用するユーザーのパスワード、ポート番号などの基本的な質問がいくつか表示されます。したがって、パスワードを除き、すべてをデフォルト値のままにしておきます。パスワードは選択により指定できます。それはあなたがチェックできるアプリケーションフォルダにあなたのMacマシンにPostgreSQLをインストールします-

Mac上のPostgresqlアプリケーション、width = 500

これで、開始するプログラムを起動できます。 SQLシェルから始めましょう。 SQLシェルを起動するとき、表示されるすべてのデフォルト値を使用するだけで、インストール時に選択したパスワードを入力します。すべてがうまくいけば、あなたはpostgresデータベースの中にいて、以下に示すように* postgress＃*プロンプトが表示されます-

Mac上のPostgresql SQLシェル、幅= 500

おめでとうございます!!! これで、PostgreSQLデータベースプログラミングを開始する準備が整いました。

PostgreSQL-構文

この章では、PostgreSQL SQLコマンドのリストと、これらの各コマンドの正確な構文規則を示します。このコマンドセットは、psqlコマンドラインツールから取得されます。 Postgresがインストールされたので、psqlを次のように開きます-

プログラムファイル→PostgreSQL 9.2→SQL Shell（psql）。 *

psqlを使用すると、\ helpコマンドを使用してコマンドの完全なリストを生成できます。特定のコマンドの構文については、次のコマンドを使用します-

 postgres-# \help <command_name>

SQLステートメント

SQLステートメントはトークンで構成され、各トークンはキーワード、識別子、引用符付き識別子、定数、または特殊文字記号のいずれかを表すことができます。以下の表では、単純なSELECTステートメントを使用して、基本的な、しかし完全なSQLステートメントとそのコンポーネントを示しています。

	SELECT	id, name	FROM	states
Token Type	Keyword	Identifiers	Keyword	Identifier
Description	Command	Id and name columns	Clause	Table name

PostgreSQL SQLコマンド

アボート

現在のトランザクションを中止します。

ABORT [ WORK | TRANSACTION ]

ALTER AGGREGATE

集約関数の定義を変更します。

ALTER AGGREGATE name ( type ) RENAME TO new_name
ALTER AGGREGATE name ( type ) OWNER TO new_owner

ALTER CONVERSION

変換の定義を変更します。

ALTER CONVERSION name RENAME TO new_name
ALTER CONVERSION name OWNER TO new_owner

ALTER DATABASE

データベース固有のパラメーターを変更します。

ALTER DATABASE name SET parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name RESET parameter
ALTER DATABASE name RENAME TO new_name
ALTER DATABASE name OWNER TO new_owner

ALTER DOMAIN

ドメイン固有のパラメーターの定義を変更します。

ALTER DOMAIN name { SET DEFAULT expression | DROP DEFAULT }
ALTER DOMAIN name { SET | DROP } NOT NULL
ALTER DOMAIN name ADD domain_constraint
ALTER DOMAIN name DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
ALTER DOMAIN name OWNER TO new_owner

機能を変更する

関数の定義を変更します。

ALTER FUNCTION name ( [ type [, ...] ] ) RENAME TO new_name
ALTER FUNCTION name ( [ type [, ...] ] ) OWNER TO new_owner

ALTER GROUP

ユーザーグループを変更します。

ALTER GROUP groupname ADD USER username [, ... ]
ALTER GROUP groupname DROP USER username [, ... ]
ALTER GROUP groupname RENAME TO new_name

ALTER INDEX

インデックスの定義を変更します。

ALTER INDEX name OWNER TO new_owner
ALTER INDEX name SET TABLESPACE indexspace_name
ALTER INDEX name RENAME TO new_name

ALTER LANGUAGE

手続き言語の定義を変更します。

ALTER LANGUAGE name RENAME TO new_name

ALTER OPERATOR

演算子の定義を変更します。

ALTER OPERATOR name ( { lefttype | NONE }, { righttype | NONE } )
OWNER TO new_owner

ALTER演算子クラス

演算子クラスの定義を変更します。

ALTER OPERATOR CLASS name USING index_method RENAME TO new_name
ALTER OPERATOR CLASS name USING index_method OWNER TO new_owner

ALTER SCHEMA

スキーマの定義を変更します。

ALTER SCHEMA name RENAME TO new_name
ALTER SCHEMA name OWNER TO new_owner

シーケンスの変更

シーケンスジェネレーターの定義を変更します。

ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ]
[ MAXVALUE maxvalue | NO MAXVALUE ]
[ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]

他の机

テーブルの定義を変更します。

ALTER TABLE [ ONLY ] name [* ]
action [, ... ]
ALTER TABLE [ ONLY ] name [ * ]
RENAME [ COLUMN ] column TO new_column
ALTER TABLE name
RENAME TO new_name

_action_は次の行のいずれかです-

ADD [ COLUMN ] column_type [ column_constraint [ ... ] ]
DROP [ COLUMN ] column [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] column TYPE type [ USING expression ]
ALTER [ COLUMN ] column SET DEFAULT expression
ALTER [ COLUMN ] column DROP DEFAULT
ALTER [ COLUMN ] column { SET | DROP } NOT NULL
ALTER [ COLUMN ] column SET STATISTICS integer
ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
ADD table_constraint
DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
CLUSTER ON index_name
SET WITHOUT CLUSTER
SET WITHOUT OIDS
OWNER TO new_owner
SET TABLESPACE tablespace_name

ALTER TABLESPACE

表領域の定義を変更します。

ALTER TABLESPACE name RENAME TO new_name
ALTER TABLESPACE name OWNER TO new_owner

ALTER TRIGGER

トリガーの定義を変更します。

ALTER TRIGGER name ON table RENAME TO new_name

代替タイプ

型の定義を変更します。

ALTER TYPE name OWNER TO new_owner

ALTER USER

データベースユーザーアカウントを変更します。

ALTER USER name [ [ WITH ] option [ ... ] ]
ALTER USER name RENAME TO new_name
ALTER USER name SET parameter { TO | = } { value | DEFAULT }
ALTER USER name RESET parameter

_option_は次のとおりです-

[ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| CREATEDB | NOCREATEDB
| CREATEUSER | NOCREATEUSER
| VALID UNTIL 'abstime'

分析する

データベースに関する統計を収集します。

ANALYZE [ VERBOSE ] [ table [ (column [, ...] ) ] ]

ベギン

トランザクションブロックを開始します。

BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]

_transaction_mode_は次のいずれかです-

ISOLATION LEVEL {
   SERIALIZABLE | REPEATABLE READ | READ COMMITTED
   | READ UNCOMMITTED
}
READ WRITE | READ ONLY

チェックポイント

トランザクションログチェックポイントを強制します。

CHECKPOINT

閉じる

カーソルを閉じます。

CLOSE name

クラスタ

インデックスに従ってテーブルをクラスター化します。

CLUSTER index_name ON table_name
CLUSTER table_name
CLUSTER

COMMENT ON {
   TABLE object_name |
   COLUMN table_name.column_name |
   AGGREGATE agg_name (agg_type) |
   CAST (source_type AS target_type) |
   CONSTRAINT constraint_name ON table_name |
   CONVERSION object_name |
   DATABASE object_name |
   DOMAIN object_name |
   FUNCTION func_name (arg1_type, arg2_type, ...) |
   INDEX object_name |
   LARGE OBJECT large_object_oid |
   OPERATOR op (left_operand_type, right_operand_type) |
   OPERATOR CLASS object_name USING index_method |
   [ PROCEDURAL ] LANGUAGE object_name |
   RULE rule_name ON table_name |
   SCHEMA object_name |
   SEQUENCE object_name |
   TRIGGER trigger_name ON table_name |
   TYPE object_name |
   VIEW object_name
}
IS 'text'

コミット

現在のトランザクションをコミットします。

COMMIT [ WORK | TRANSACTION ]

COPY

ファイルとテーブルの間でデータをコピーします。

COPY table_name [ ( column [, ...] ) ]
FROM { 'filename' | STDIN }
[ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE NOT NULL column [, ...] ]
COPY table_name [ ( column [, ...] ) ]
TO { 'filename' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE QUOTE column [, ...] ]

集約を作成

新しい集約関数を定義します。

CREATE AGGREGATE name (
   BASETYPE = input_data_type,
   SFUNC = sfunc,
   STYPE = state_data_type
   [, FINALFUNC = ffunc ]
   [, INITCOND = initial_condition ]
)

キャストの作成

新しいキャストを定義します。

CREATE CAST (source_type AS target_type)
WITH FUNCTION func_name (arg_types)
[ AS ASSIGNMENT | AS IMPLICIT ]
CREATE CAST (source_type AS target_type)
WITHOUT FUNCTION
[ AS ASSIGNMENT | AS IMPLICIT ]

制約トリガーの作成

新しい制約トリガーを定義します。

CREATE CONSTRAINT TRIGGER name
AFTER events ON
table_name constraint attributes
FOR EACH ROW EXECUTE PROCEDURE func_name ( args )

変換を作成

新しい変換を定義します。

CREATE [DEFAULT] CONVERSION name
FOR source_encoding TO dest_encoding FROM func_name

データベース作成

新しいデータベースを作成します。

CREATE DATABASE name
[ [ WITH ] [ OWNER [=] db_owner ]
   [ TEMPLATE [=] template ]
   [ ENCODING [=] encoding ]
   [ TABLESPACE [=] tablespace ]
]

ドメインの作成

新しいドメインを定義します。

CREATE DOMAIN name [AS] data_type
[ DEFAULT expression ]
[ constraint [ ... ] ]

_constraint_は-

[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | CHECK (expression) }

関数を作成する

新しい関数を定義します。

CREATE [ OR REPLACE ] FUNCTION name ( [ [ arg_name ] arg_type [, ...] ] )
RETURNS ret_type
{ LANGUAGE lang_name
   | IMMUTABLE | STABLE | VOLATILE
   | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
   | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
   | AS 'definition'
   | AS 'obj_file', 'link_symbol'
} ...
[ WITH ( attribute [, ...] ) ]

グループを作る

新しいユーザーグループを定義します。

CREATE GROUP name [ [ WITH ] option [ ... ] ]
Where option can be:
SYSID gid
| USER username [, ...]

インデックスの作成

新しいインデックスを定義します。

CREATE [ UNIQUE ] INDEX name ON table [ USING method ]
( { column | ( expression ) } [ opclass ] [, ...] )
[ TABLESPACE tablespace ]
[ WHERE predicate ]

言語を作成する

新しい手続き言語を定義します。

CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name
HANDLER call_handler [ VALIDATOR val_function ]

オペレーターの作成

新しい演算子を定義します。

CREATE OPERATOR name (
   PROCEDURE = func_name
   [, LEFTARG = left_type ] [, RIGHTARG = right_type ]
   [, COMMUTATOR = com_op ] [, NEGATOR = neg_op ]
   [, RESTRICT = res_proc ] [, JOIN = join_proc ]
   [, HASHES ] [, MERGES ]
   [, SORT1 = left_sort_op ] [, SORT2 = right_sort_op ]
   [, LTCMP = less_than_op ] [, GTCMP = greater_than_op ]
)

演算子クラスの作成

新しい演算子クラスを定義します。

CREATE OPERATOR CLASS name [ DEFAULT ] FOR TYPE data_type
USING index_method AS
{ OPERATOR strategy_number operator_name [ ( op_type, op_type ) ] [ RECHECK ]
   | FUNCTION support_number func_name ( argument_type [, ...] )
   | STORAGE storage_type
} [, ... ]

ルールを作成

新しい書き換えルールを定義します。

CREATE [ OR REPLACE ] RULE name AS ON event
TO table [ WHERE condition ]
DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }

スキーマの作成

新しいスキーマを定義します。

CREATE SCHEMA schema_name
[ AUTHORIZATION username ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION username
[ schema_element [ ... ] ]

シーケンスの作成

新しいシーケンスジェネレーターを定義します。

CREATE [ TEMPORARY | TEMP ] SEQUENCE name
[ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ]
[ MAXVALUE maxvalue | NO MAXVALUE ]
[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]

CREATE TABLE

新しいテーブルを定義します。

CREATE [ [ GLOBAL | LOCAL ] {
   TEMPORARY | TEMP } ] TABLE table_name ( {
      column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ]
      | table_constraint
      | LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ]
   } [, ... ]
)
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace ]

_column_constraint_は-

[ CONSTRAINT constraint_name ] {
   NOT NULL |
   NULL |
   UNIQUE [ USING INDEX TABLESPACE tablespace ] |
   PRIMARY KEY [ USING INDEX TABLESPACE tablespace ] |
   CHECK (expression) |
   REFERENCES ref_table [ ( ref_column ) ]
   [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
   [ ON DELETE action ] [ ON UPDATE action ]
}
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

そして_table_constraint_は-

[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |
PRIMARY KEY ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |
CHECK ( expression ) |
FOREIGN KEY ( column_name [, ... ] )
REFERENCES ref_table [ ( ref_column [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

テーブルを作成

クエリの結果から新しいテーブルを定義します。

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name
[ (column_name [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ]
AS query

テーブルスペースの作成

新しい表領域を定義します。

CREATE TABLESPACE tablespace_name [ OWNER username ] LOCATION 'directory'

CREATE TRIGGER

新しいトリガーを定義します。

CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE func_name ( arguments )

タイプを作成

新しいデータ型を定義します。

CREATE TYPE name AS
( attribute_name data_type [, ... ] )
CREATE TYPE name (
INPUT = input_function,
OUTPUT = output_function
[, RECEIVE = receive_function ]
[, SEND = send_function ]
[, ANALYZE = analyze_function ]
[, INTERNALLENGTH = { internal_length | VARIABLE } ]
[, PASSEDBYVALUE ]
[, ALIGNMENT = alignment ]
[, STORAGE = storage ]
[, DEFAULT = default ]
[, ELEMENT = element ]
[, DELIMITER = delimiter ]
)

ユーザーを作成

新しいデータベースユーザーアカウントを定義します。

CREATE USER name [ [ WITH ] option [ ... ] ]

_option_は次のとおりです-

SYSID uid
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| CREATEDB | NOCREATEDB
| CREATEUSER | NOCREATEUSER
| IN GROUP group_name [, ...]
| VALID UNTIL 'abs_time'

ビューを作成

新しいビューを定義します。

CREATE [ OR REPLACE ] VIEW name [ ( column_name [, ...] ) ] AS query

割り当て解除

準備済みステートメントの割り当てを解除します。

DEALLOCATE [ PREPARE ] plan_name

DECLARE

カーソルを定義します。

DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
CURSOR [ { WITH | WITHOUT } HOLD ] FOR query
[ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]

DELETE

テーブルの行を削除します。

DELETE FROM [ ONLY ] table [ WHERE condition ]

アグリゲートのドロップ

集約関数を削除します。

DROP AGGREGATE name ( type ) [ CASCADE | RESTRICT ]

ドロップキャスト

キャストを削除します。

DROP CAST (source_type AS target_type) [ CASCADE | RESTRICT ]

ドロップ変換

変換を削除します。

DROP CONVERSION name [ CASCADE | RESTRICT ]

ドロップデータベース

データベースを削除します。

DROP DATABASE name

ドロップドメイン

ドメインを削除します。

DROP DOMAIN name [, ...] [ CASCADE | RESTRICT ]

ドロップ機能

関数を削除します。

DROP FUNCTION name ( [ type [, ...] ] ) [ CASCADE | RESTRICT ]

ドロップグループ

ユーザーグループを削除します。

DROP GROUP name

ドロップインデックス

インデックスを削除します。

DROP INDEX name [, ...] [ CASCADE | RESTRICT ]

ドロップ言語

手続き言語を削除します。

DROP [ PROCEDURAL ] LANGUAGE name [ CASCADE | RESTRICT ]

ドロップ演算子

演算子を削除します。

DROP OPERATOR name ( { left_type | NONE }, { right_type | NONE } )
[ CASCADE | RESTRICT ]

ドロップ演算子クラス

演算子クラスを削除します。

DROP OPERATOR CLASS name USING index_method [ CASCADE | RESTRICT ]

廃棄規則

書き換えルールを削除します。

DROP RULE name ON relation [ CASCADE | RESTRICT ]

ドロップスキーム

スキーマを削除します。

DROP SCHEMA name [, ...] [ CASCADE | RESTRICT ]

ドロップシーケンス

シーケンスを削除します。

DROP SEQUENCE name [, ...] [ CASCADE | RESTRICT ]

ドロップテーブル

テーブルを削除します。

DROP TABLE name [, ...] [ CASCADE | RESTRICT ]

ドロップテーブルスペース

表領域を削除します。

DROP TABLESPACE tablespace_name

ドロップトリガー

トリガーを削除します。

DROP TRIGGER name ON table [ CASCADE | RESTRICT ]

ドロップタイプ

データ型を削除します。

DROP TYPE name [, ...] [ CASCADE | RESTRICT ]

ユーザーを削除

データベースユーザーアカウントを削除します。

DROP USER name

ドロップビュー

ビューを削除します。

DROP VIEW name [, ...] [ CASCADE | RESTRICT ]

END

現在のトランザクションをコミットします。

END [ WORK | TRANSACTION ]

実行

準備されたステートメントを実行します。

EXECUTE plan_name [ (parameter [, ...] ) ]

説明する

文の実行計画を表示します。

EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

フェッチ

カーソルを使用してクエリから行を取得します。

FETCH [ direction { FROM | IN } ] cursor_name

_direction_は空または次のいずれかです。

NEXT
PRIOR
FIRST
LAST
ABSOLUTE count
RELATIVE count
count
ALL
FORWARD
FORWARD count
FORWARD ALL
BACKWARD
BACKWARD count
BACKWARD ALL

付与

アクセス権限を定義します。

GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] table_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE db_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON FUNCTION func_name ([type, ...]) [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

インサート

テーブルに新しい行を作成します。

INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | query }

聴く

通知を聞きます。

LISTEN name

LOAD

共有ライブラリファイルをロードまたはリロードします。

LOAD 'filename'

LOCK

テーブルをロックします。

LOCK [ TABLE ] name [, ...] [ IN lock_mode MODE ] [ NOWAIT ]

_lock_mode_は次のいずれかです-

ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE
| SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE

MOVE

カーソルを置きます。

MOVE [ direction { FROM | IN } ] cursor_name

通知する

通知を生成します。

NOTIFY name

準備する

実行するステートメントを準備します。

PREPARE plan_name [ (data_type [, ...] ) ] AS statement

REINDEX

インデックスを再構築します。

REINDEX { DATABASE | TABLE | INDEX } name [ FORCE ]

リリースセーブポイント

以前に定義したセーブポイントを破棄します。

RELEASE [ SAVEPOINT ] savepoint_name

リセット

ランタイムパラメータの値をデフォルト値に復元します。

RESET name
RESET ALL

取り消す

アクセス権を削除します。

REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] table_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE db_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
ON FUNCTION func_name ([type, ...]) [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

ロールバック

現在のトランザクションを中止します。

ROLLBACK [ WORK | TRANSACTION ]

SAVEPOINTへのロールバック

セーブポイントにロールバックします。

ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name

セーブポイント

現在のトランザクション内で新しいセーブポイントを定義します。

SAVEPOINT savepoint_name

SELECT

テーブルまたはビューから行を取得します。

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
 *| expression [ AS output_name ] [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
[ FOR UPDATE [ OF table_name [, ...] ] ]

from_item

[ ONLY ] table_name [* ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
function_name ( [ argument [, ...] ] )
[ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]
function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
from_item [ NATURAL ] join_type from_item
[ ON join_condition | USING ( join_column [, ...] ) ]

選択する

クエリの結果から新しいテーブルを定義します。

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
 *| expression [ AS output_name ] [, ...]
INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
[ FOR UPDATE [ OF table_name [, ...] ] ]

SET

ランタイムパラメータを変更します。

SET [ SESSION | LOCAL ] name { TO | = } { value | 'value' | DEFAULT }
SET [ SESSION | LOCAL ] TIME ZONE { time_zone | LOCAL | DEFAULT }

制約の設定

現在のトランザクションの制約チェックモードを設定します。

SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }

セッション認証の設定

現在のセッションのセッションユーザー識別子と現在のユーザー識別子を設定します。

SET [ SESSION | LOCAL ] SESSION AUTHORIZATION username
SET [ SESSION | LOCAL ] SESSION AUTHORIZATION DEFAULT
RESET SESSION AUTHORIZATION

トランザクションの設定

現在のトランザクションの特性を設定します。

SET TRANSACTION transaction_mode [, ...]
SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, ...]

_transaction_mode_は次のいずれかです-

ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED
| READ UNCOMMITTED }
READ WRITE | READ ONLY

SHOW

ランタイムパラメータの値を表示します。

SHOW name
SHOW ALL

取引開始

トランザクションブロックを開始します。

START TRANSACTION [ transaction_mode [, ...] ]

_transaction_mode_は次のいずれかです-

ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED
| READ UNCOMMITTED }
READ WRITE | READ ONLY

切り捨て

テーブルを空にします。

TRUNCATE [ TABLE ] name

聞き取れない

通知のリッスンを停止します。

UNLISTEN { name |* }

更新

テーブルの行を更新します。

UPDATE [ ONLY ] table SET column = { expression | DEFAULT } [, ...]
[ FROM from_list ]
[ WHERE condition ]

真空

ガベージコレクションを行い、オプションでデータベースを分析します。

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]

PostgreSQL-データ型

この章では、PostgreSQLで使用されるデータ型について説明します。テーブルの作成中に、各列にデータ型、つまりテーブルフィールドに保存するデータの種類を指定します。

これはいくつかの利点を可能にします-

一貫性-同じデータ型の列に対する操作は一貫した結果をもたらし、通常は最速です。
検証-データ型の適切な使用は、データの形式検証とデータ型の範囲外のデータの拒否を意味します。
コンパクト-列は単一のタイプの値を格納できるため、コンパクトな方法で格納されます。
パフォーマンス-データ型を適切に使用すると、データを最も効率的に保存できます。格納された値は迅速に処理できるため、パフォーマンスが向上します。

PostgreSQLは幅広いデータ型をサポートしています。また、ユーザーは_CREATE TYPE_ SQLコマンドを使用して独自のカスタムデータ型を作成できます。 PostgreSQLには、データ型のさまざまなカテゴリがあります。以下で説明します。

数値型

数値型は、2バイト、4バイト、および8バイトの整数、4バイトと8バイトの浮動小数点数、および選択可能な精度の小数で構成されます。次の表に、使用可能なタイプを示します。

Name	Storage Size	Description	Range
smallint	2 bytes	small-range integer	-32768 to +32767
integer	4 bytes	typical choice for integer	-2147483648 to +2147483647
bigint	8 bytes	large-range integer	-9223372036854775808 to 9223372036854775807
decimal	variable	user-specified precision,exact	up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
numeric	variable	user-specified precision,exact	up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
real	4 bytes	variable-precision,inexact	6 decimal digits precision
double precision	8 bytes	variable-precision,inexact	15 decimal digits precision
smallserial	2 bytes	small autoincrementing integer	1 to 32767
serial	4 bytes	autoincrementing integer	1 to 2147483647
bigserial	8 bytes	large autoincrementing integer	1 to 9223372036854775807

通貨タイプ

_money_タイプは、固定小数精度で通貨額を格納します。 _numeric、int、およびbigint_データ型の値は、_money_にキャストできます。丸めエラーが発生する可能性があるため、お金を扱うために浮動小数点数を使用することはお勧めしません。

Name	Storage Size	Description	Range
money	8 bytes	currency amount	-92233720368547758.08 to +92233720368547758.07

キャラクタータイプ

以下の表は、PostgreSQLで使用可能な汎用文字タイプを示しています。

S. No.

Name & Description

1

character varying(n), varchar(n)

制限付き可変長

2

character(n), char(n)

固定長、空白埋め

3

text

可変長

バイナリデータタイプ

_bytea_データ型を使用すると、以下の表のようにバイナリ文字列を保存できます。

Name	Storage Size	Description
bytea	1 or 4 bytes plus the actual binary string	variable-length binary string

日付/時刻型

PostgreSQLは、以下の表に示すように、SQLの日付と時刻の完全なセットをサポートしています。日付はグレゴリオ暦に従ってカウントされます。ここでは、解像度が day である date タイプを除くすべてのタイプの解像度は* 1マイクロ秒/14桁*です。

Name	Storage Size	Description	Low Value	High Value
timestamp [(p)] [without time zone ]	8 bytes	both date and time (no time zone)	4713 BC	294276 AD
TIMESTAMPTZ	8 bytes	both date and time, with time zone	4713 BC	294276 AD
date	4 bytes	date (no time of day)	4713 BC	5874897 AD
time [ (p)] [ without time zone ]	8 bytes	time of day (no date)	00:00:00	24:00:00
time [ (p)] with time zone	12 bytes	times of day only, with time zone	00:00:00+1459	24:00:00-1459
interval [fields ] [(p) ]	12 bytes	time interval	-178000000 years	178000000 years

ブール型

PostgreSQLは、標準SQL型のブール値を提供します。ブールデータ型には、状態_true 、 false_、およびSQL null値で表される3番目の状態_unknown_を含めることができます。

Name	Storage Size	Description
boolean	1 byte	state of true or false

列挙型

列挙型（列挙型）は、静的な順序付けられた値のセットで構成されるデータ型です。これらは、多くのプログラミング言語でサポートされている列挙型と同等です。

他の型とは異なり、列挙型はCREATE TYPEコマンドを使用して作成する必要があります。このタイプは、静的な順序付けられた値のセットを格納するために使用されます。たとえば、コンパスの方向、すなわち、北、南、東、西、または以下に示す曜日-

CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');

作成された列挙型は、他の型と同様に使用できます。

幾何学的タイプ

幾何データ型は、2次元の空間オブジェクトを表します。最も基本的なタイプであるポイントは、他のすべてのタイプの基礎を形成します。

Name	Storage Size	Representation	Description
point	16 bytes	Point on a plane	(x,y)
line	32 bytes	Infinite line (not fully implemented)	x1,y1),(x2,y2x1,y1),(x2,y2
lseg	32 bytes	Finite line segment	x1,y1),(x2,y2x1,y1),(x2,y2
box	32 bytes	Rectangular box	x1,y1),(x2,y2x1,y1),(x2,y2
path	16+16n bytes	Closed path (similar to polygon)	((x1,y1),…)
path	16+16n bytes	Open path	[(x1,y1),…]
polygon	40+16n	Polygon (similar to closed path)	((x1,y1),…)
circle	24 bytes	Circle	<(x,y),r> (center point and radius)

ネットワークアドレスタイプ

PostgreSQLは、IPv4、IPv6、およびMACアドレスを保存するデータ型を提供します。これらのタイプは入力エラーチェックと特殊な演算子と機能を提供するため、プレーンテキストタイプの代わりにこれらのタイプを使用してネットワークアドレスを保存する方が適切です。

Name	Storage Size	Description
cidr	7 or 19 bytes	IPv4 and IPv6 networks
inet	7 or 19 bytes	IPv4 and IPv6 hosts and networks
macaddr	6 bytes	MAC addresses

ビット列タイプ

ビット文字列型は、ビットマスクを格納するために使用されます。 0または1のいずれかです。 SQLビットタイプには、* bit（n）と bit changing（n）*の2種類があります。nは正の整数です。

テキスト検索タイプ

このタイプは全文検索をサポートします。これは、自然言語ドキュメントのコレクションを検索して、クエリに最も一致するドキュメントを見つけるアクティビティです。これには2つのデータ型があります-

S. No.

Name & Description

1

tsvector

これは、「語彙素」と呼ばれる同じ単語の異なる変形をマージするために正規化された個別の単語のソートされたリストです。

2

tsquery

これは、検索される語彙素を保存し、それらを結合してブール演算子＆（AND）、

UUIDタイプ

UUID（Universally Unique Identifiers）は、ハイフンで区切られたいくつかのグループ、具体的には8桁のグループ、4桁の3つのグループ、12桁のグループが続く、一連の小文字の16進数として書き込まれます128ビットを表す合計32桁。

UUIDの例は-550e8400-e29b-41d4-a716-446655440000です

XMLタイプ

XMLデータ型は、XMLデータを格納するために使用できます。 XMLデータを保存するには、まず次のように関数xmlparseを使用してXML値を作成する必要があります-

XMLPARSE (DOCUMENT '<?xml version="1.0"?>
<tutorial>
<title>PostgreSQL Tutorial </title>
   <topics>...</topics>
</tutorial>')

XMLPARSE (CONTENT 'xyz<foo>bar</foo><bar>foo</bar>')

JSONタイプ

_json_データ型を使用して、JSON（JavaScript Object Notation）データを保存できます。このようなデータは_text_として保存することもできますが、_json_データ型には、保存されている各値が有効なJSON値であることを確認するという利点があります。また、関連するサポート関数も利用できます。これらの関数は、次のようにJSONデータ型を直接処理するために使用できます。

Example	Example Result
array_to_json('\{\{1,5},\{99,100}}'::int[])	[[row_to_json(row(1,'foo'))

配列タイプ

PostgreSQLでは、テーブルの列を可変長の多次元配列として定義できます。組み込みまたはユーザー定義の基本型、列挙型、または複合型の配列を作成できます。

配列の宣言

配列型は次のように宣言できます

CREATE TABLE monthly_savings (
   name text,
   saving_per_quarter integer[],
   scheme text[][]
);

または、キーワード「ARRAY」を使用して

CREATE TABLE monthly_savings (
   name text,
   saving_per_quarter integer ARRAY[4],
   scheme text[][]
);

値を挿入する

配列値はリテラル定数として挿入でき、要素値を中括弧で囲み、コンマで区切ります。以下に例を示します-

INSERT INTO monthly_savings
VALUES (‘Manisha’,
‘{20000, 14600, 23500, 13250}’,
‘{{“FD”, “MF”}, {“FD”, “Property”}}’);

配列へのアクセス

配列にアクセスする例を以下に示します。以下のコマンドは、貯蓄が第4四半期よりも第2四半期に多い人を選択します。

SELECT name FROM monhly_savings WHERE saving_per_quarter[2] > saving_per_quarter[4];

配列の変更

配列を変更する例は次のとおりです。

UPDATE monthly_savings SET saving_per_quarter = '{25000,25000,27000,27000}'
WHERE name = 'Manisha';

またはARRAY式の構文を使用して-

UPDATE monthly_savings SET saving_per_quarter = ARRAY[25000,25000,27000,27000]
WHERE name = 'Manisha';

配列の検索

配列の検索の例を次に示します。

SELECT *FROM monthly_savings WHERE saving_per_quarter[1] = 10000 OR
saving_per_quarter[2] = 10000 OR
saving_per_quarter[3] = 10000 OR
saving_per_quarter[4] = 10000;

配列のサイズがわかっている場合は、上記の検索方法を使用できます。それ以外の場合、次の例は、サイズが不明な場合の検索方法を示しています。

SELECT* FROM monthly_savings WHERE 10000 = ANY (saving_per_quarter);

複合型

この型は、フィールド名とそのデータ型のリスト、つまり、行の構造またはテーブルのレコードを表します。

複合型の宣言

次の例は、複合型を宣言する方法を示しています

CREATE TYPE inventory_item AS (
   name text,
   supplier_id integer,
   price numeric
);

このデータ型は、次のように作成テーブルで使用できます-

CREATE TABLE on_hand (
   item inventory_item,
   count integer
);

複合値入力

複合値をリテラル定数として挿入し、フィールド値を括弧で囲み、それらをコンマで区切ることができます。以下に例を示します-

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

これは、上記で定義した_inventory_item_に有効です。式に複数のフィールドがある限り、ROWキーワードは実際にはオプションです。

複合型へのアクセス

複合列のフィールドにアクセスするには、テーブル名からフィールドを選択するのと同様に、ドットの後にフィールド名を使用します。たとえば、on_handサンプルテーブルからいくつかのサブフィールドを選択するには、クエリは次のようになります-

SELECT (item).name FROM on_hand WHERE (item).price > 9.99;

次のように、テーブル名を使用することもできます（たとえば、マルチテーブルクエリで）。

SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;

範囲タイプ

範囲タイプは、データの範囲を使用するデータタイプを表します。範囲タイプは、離散範囲（例：すべての整数値1から10）または連続範囲（例：午前10:00から午前11:00までの任意の時点）です。

利用可能な組み込みの範囲タイプには、次の範囲が含まれます-

int4range -整数の範囲
int8range -bigintの範囲
numrange -数値の範囲
tsrange -タイムゾーンのないタイムスタンプの範囲
tstzrange -タイムゾーン付きのタイムスタンプの範囲
daterange -日付の範囲

カスタム範囲タイプを作成して、inetタイプをベースとして使用するIPアドレス範囲、floatデータタイプをベースとして使用するfloat範囲など、新しいタイプの範囲を使用可能にすることができます。

範囲タイプは、それぞれ[]および（）文字を使用した包括的および排他的範囲境界をサポートします。たとえば、「[4,9）」は、4から9までのすべての整数を表します。

オブジェクト識別子のタイプ

オブジェクト識別子（OID）は、さまざまなシステムテーブルの主キーとしてPostgreSQLによって内部的に使用されます。 _WITH OIDS_が指定されている場合、または_default_with_oids_構成変数が有効になっている場合のみ、そのような場合にOIDがユーザー作成のテーブルに追加されます。次の表に、いくつかのエイリアスタイプを示します。 OIDエイリアスタイプには、特殊な入力および出力ルーチンを除き、独自の操作はありません。

Name	References	Description	Value Example
oid	any	numeric object identifier	564182
regproc	pg_proc	function name	sum
regprocedure	pg_proc	function with argument types	sum(int4)
regoper	pg_operator	operator name	+
regoperator	pg_operator	operator with argument types	*(integer,integer) or -(NONE,integer)
regclass	pg_class	relation name	pg_type
regtype	pg_type	data type name	integer
regconfig	pg_ts_config	text search configuration	English
regdictionary	pg_ts_dict	text search dictionary	simple

疑似タイプ

PostgreSQLタイプシステムには、集合的に疑似タイプと呼ばれる特別な目的のエントリが多数含まれています。疑似型は列データ型として使用できませんが、関数の引数または結果型を宣言するために使用できます。

以下の表に、既存の疑似タイプをリストします。

S. No.	Name & Description
1	any 関数が入力データ型を受け入れることを示します。
2	anyelement 関数が任意のデータ型を受け入れることを示します。
3	anyarray 関数が配列データ型を受け入れることを示します。
4	anynonarray 関数が配列以外のデータ型を受け入れることを示します。
5	anyenum 関数が列挙データ型を受け入れることを示します。
6	anyrange 関数が範囲データ型を受け入れることを示します。
7	cstring 関数がヌル終了C文字列を受け入れるか返すことを示します。
8	internal 関数がサーバー内部データ型を受け入れるか返すことを示します。
9	language_handler 手続き言語呼び出しハンドラーは、language_handlerを返すように宣言されています。
10	fdw_handler 外部データラッパーハンドラーは、fdw_handlerを返すように宣言されています。
11	record 未指定の行タイプを返す関数を識別します。
12	trigger トリガーを返すようにトリガー関数が宣言されています。
13	void 関数が値を返さないことを示します。

PostgreSQL-データベースの作成

この章では、PostgreSQLで新しいデータベースを作成する方法について説明します。 PostgreSQLは、新しいデータベースを作成する2つの方法を提供します-

SQLコマンドであるCREATE DATABASEを使用します。
_createdb_を使用して、コマンドライン実行可能ファイル。

CREATE DATABASEを使用する

このコマンドは、PostgreSQLシェルプロンプトからデータベースを作成しますが、データベースを作成するには適切な権限が必要です。デフォルトでは、標準システムデータベース_template1_を複製することにより、新しいデータベースが作成されます。

構文

CREATE DATABASEステートメントの基本的な構文は次のとおりです-

CREATE DATABASE dbname;

ここで、_dbname_は作成するデータベースの名前です。

例

以下は、PostgreSQLスキーマに testdb を作成する簡単な例です

postgres=# CREATE DATABASE testdb;
postgres-#

createdbコマンドの使用

PostgreSQLコマンドライン実行可能ファイル_createdb_は、SQLコマンド_CREATE DATABASE_のラッパーです。このコマンドとSQLコマンド_CREATE DATABASE_の唯一の違いは、前者をコマンドラインから直接実行でき、データベースにコメントを1つのコマンドで追加できることです。

構文

_createdb_の構文は次のとおりです-

createdb [option...] [dbname [description]]

パラメーター

以下の表に、パラメーターとその説明を示します。

S. No.

Parameter & Description

1

dbname

作成するデータベースの名前。

2

description

新しく作成されたデータベースに関連付けるコメントを指定します。

3

options

createdbが受け入れるコマンドライン引数。

オプション

次の表に、createdbが受け入れるコマンドライン引数を示します-

S. No.	Option & Description
1	-D tablespace データベースのデフォルトのテーブルスペースを指定します。
2	-e createdbが生成し、サーバーに送信するコマンドをエコーします。
3	-E encoding このデータベースで使用される文字エンコード方式を指定します。
4	-l locale このデータベースで使用されるロケールを指定します。
5	-T template このデータベースの構築元のテンプレートデータベースを指定します。
6	--help createdbコマンドライン引数に関するヘルプを表示して、終了します。
7	-h host サーバーが実行されているマシンのホスト名を指定します。
8	-p port サーバーが接続をリッスンするTCPポートまたはローカルUnixドメインソケットファイル拡張子を指定します。
9	-U username 接続するユーザー名。
10	-w パスワードプロンプトを発行しないでください。
11	-W データベースに接続する前に、createdbにパスワードの入力を要求します。

コマンドプロンプトを開き、PostgreSQLがインストールされているディレクトリに移動します。 binディレクトリに移動し、次のコマンドを実行してデータベースを作成します。

createdb -h localhost -p 5432 -U postgres testdb
password ******

上記のコマンドは、デフォルトでは postgres であるPostgreSQL管理ユーザーのパスワードの入力を求めます。したがって、パスワードを入力し、新しいデータベースの作成に進みます

上記の方法のいずれかを使用してデータベースを作成したら、 \ l 、つまり次のようにバックスラッシュelコマンドを使用してデータベースのリストで確認できます-

postgres-# \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | C       | C     |
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 testdb    | postgres | UTF8     | C       | C     |
(4 rows)

postgres-#

PostgreSQL-SELECTデータベース

この章では、データベースにアクセスするさまざまな方法について説明します。前の章ですでにデータベースを作成していると仮定します。次の方法のいずれかを使用してデータベースを選択できます-

データベースSQLプロンプト
OSコマンドプロンプト

データベースSQLプロンプト

PostgreSQLクライアントをすでに起動しており、次のSQLプロンプトに到達したとします-

postgres=#

次のように、バックスラッシュelコマンドを使用して、 \ l を使用して、使用可能なデータベースリストを確認できます-

postgres-# \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | C       | C     |
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 testdb    | postgres | UTF8     | C       | C     |
(4 rows)

postgres-#

次に、次のコマンドを入力して、目的のデータベースに接続/選択します。ここでは、_testdb_データベースに接続します。

postgres=# \c testdb;
psql (9.2.4)
Type "help" for help.
You are now connected to database "testdb" as user "postgres".
testdb=#

OSコマンドプロンプト

データベースにログインするときに、コマンドプロンプトからデータベースを選択できます。以下は簡単な例です-

psql -h localhost -p 5432 -U postgress testdb
Password for user postgress: ****
psql (9.2.4)
Type "help" for help.
You are now connected to database "testdb" as user "postgres".
testdb=#

これでPostgreSQL testdbにログインし、testdb内でコマンドを実行する準備ができました。データベースを終了するには、コマンド\ qを使用できます。

PostgreSQL-DROPデータベース

この章では、PostgreSQLでデータベースを削除する方法について説明します。データベースを削除するには2つのオプションがあります-

SQLコマンドのDROP DATABASEを使用します。 *_dropdb_を使用して、コマンドライン実行可能ファイル。

'_既存のデータベースを削除すると、データベースに保存されている完全な情報が失われるため、この操作を使用する前に注意してください。_

DROP DATABASEの使用

このコマンドはデータベースを削除します。データベースのカタログエントリを削除し、データを含むディレクトリを削除します。データベース所有者のみが実行できます。このコマンドは、ユーザーまたは他のユーザーがターゲットデータベースに接続している間は実行できません（このコマンドを発行するには、postgresまたは他のデータベースに接続します）。

構文

DROP DATABASEの構文は次のとおりです-

DROP DATABASE [ IF EXISTS ] name

パラメーター

表には、パラメーターとその説明がリストされています。

S. No.

Parameter & Description

1

IF EXISTS*

データベースが存在しない場合でもエラーをスローしないでください。この場合、通知が発行されます。

2

name

削除するデータベースの名前。

'__psql_または_pgAdmin III_からの独自の接続を含む、開いている接続を持つデータベースを削除することはできません。現在接続しているデータベースを削除する場合は、別のデータベースまたは_template1_に切り替える必要があります。したがって、代わりにこのコマンドのラッパーであるプログラム_dropdb_を使用する方が便利な場合があります。_

例

以下は、PostgreSQLスキーマから testdb を削除する簡単な例です-

postgres=# DROP DATABASE testdb;
postgres-#

dropdbコマンドの使用

PostgresSQLコマンドライン実行可能ファイル dropdb は、SQLコマンド_DROP DATABASE_のコマンドラインラッパーです。このユーティリティを使用してデータベースを削除しても、サーバーにアクセスする他の方法を使用しても、データベースを削除しても実質的な違いはありません。 dropdbは既存のPostgreSQLデータベースを破壊します。このコマンドを実行するユーザーは、データベースのスーパーユーザーまたはデータベースの所有者でなければなりません。

構文

_dropdb_の構文は次のとおりです-

dropdb  [option...] dbname

パラメーター

次の表に、パラメーターとその説明を示します

S. No.

Parameter & Description

1

dbname

削除するデータベースの名前。

2

option

dropdbが受け入れるコマンドライン引数。

オプション

次の表は、dropdbが受け入れるコマンドライン引数を示しています-

S. No.	Option & Description
1	-e サーバーに送信されているコマンドを表示します。
2	-i 破壊的なことを行う前に確認プロンプトを発行します。
3	-V dropdbのバージョンを出力して終了します。
4	--if-exists データベースが存在しない場合でもエラーをスローしないでください。この場合、通知が発行されます。
5	--help dropdbコマンドライン引数に関するヘルプを表示し、終了します。
6	-h host サーバーが実行されているマシンのホスト名を指定します。
7	-p port サーバーが接続をリッスンするTCPポートまたはローカルUNIXドメインソケットファイル拡張子を指定します。
8	-U username 接続するユーザー名。
9	-w パスワードプロンプトを発行しないでください。
10	-W データベースに接続する前に、dropdbにパスワードの入力を要求します。
11	--maintenance-db=dbname ターゲットデータベースを削除するために接続するデータベースの名前を指定します。

例

次の例は、OSコマンドプロンプトからデータベースを削除する方法を示しています-

dropdb -h localhost -p 5432 -U postgress testdb
Password for user postgress: ****

上記のコマンドは、データベース testdb を削除します。ここでは、 postgres （template1のpg_rolesの下にある）ユーザー名を使用してデータベースを削除しました。

PostgreSQL-テーブルの作成

PostgreSQLのCREATE TABLEステートメントは、指定されたデータベースのいずれかに新しいテーブルを作成するために使用されます。

構文

CREATE TABLEステートメントの基本的な構文は次のとおりです-

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

CREATE TABLEはキーワードであり、データベースシステムに新しいテーブルを作成するよう指示します。テーブルの一意の名前または識別子は、CREATE TABLEステートメントの後に続きます。最初に、現在のデータベースの空のテーブルは、コマンドを発行したユーザーが所有します。

次に、カッコ内にリストがあり、テーブルの各列とデータ型の種類を定義します。構文は、次の例で明らかになります。

例

以下は、主キーとしてIDを使用してCOMPANYテーブルを作成する例です。NOTNULLは、このテーブルにレコードを作成する際にこれらのフィールドをNULLにできないことを示す制約です-

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

次の章の演習で使用するテーブルをもう1つ作成します。

CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

*\ d* コマンドを使用して、テーブルが正常に作成されたかどうかを確認できます。このコマンドは、接続されたデータベース内のすべてのテーブルをリストダウンするために使用されます。

testdb-# \d

上記のPostgreSQLステートメントは、次の結果を生成します-

           List of relations
 Schema |    Name    | Type  |  Owner
--------+------------+-------+----------
 public | company    | table | postgres
 public | department | table | postgres
(2 rows)

*\ d _tablename_* を使用して、以下に示すように各テーブルを記述します-

testdb-# \d company

上記のPostgreSQLステートメントは、次の結果を生成します-

        Table "public.company"
  Column   |     Type      | Modifiers
-----------+---------------+-----------
 id        | integer       | not null
 name      | text          | not null
 age       | integer       | not null
 address   | character(50) |
 salary    | real          |
 join_date | date          |
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)

PostgreSQL-DROPテーブル

PostgreSQL DROP TABLEステートメントは、テーブル定義と、そのテーブルに関連するすべてのデータ、インデックス、ルール、トリガー、制約を削除するために使用されます。

'_テーブルを削除すると、テーブルで使用可能なすべての情報も永久に失われるため、このコマンドの使用には注意が必要です。_

構文

DROP TABLEステートメントの基本的な構文は次のとおりです-

DROP TABLE table_name;

例

前の章で表DEPARTMENTおよびCOMPANYを作成しました。まず、これらのテーブルを確認します（テーブルをリストするには \ d を使用します）-

testdb-# \d

これは、次の結果を生成します-

           List of relations
 Schema |    Name    | Type  |  Owner
--------+------------+-------+----------
 public | company    | table | postgres
 public | department | table | postgres
(2 rows)

これは、DEPARTMENTおよびCOMPANYテーブルが存在することを意味します。だから私たちは次のようにそれらをドロップしましょう-

testdb=# drop table department, company;

これは、次の結果を生成します-

DROP TABLE
testdb=# \d
relations found.
testdb=#

DROP TABLEが返されたメッセージは、dropコマンドが正常に実行されたことを示します。

PostgreSQL-スキーマ

スキーマ*は、テーブルの名前付きコレクションです。スキーマには、ビュー、インデックス、シーケンス、データ型、演算子、および関数を含めることもできます。スキーマは、オペレーティングシステムレベルのディレクトリに似ていますが、スキーマをネストすることはできません。 PostgreSQLステートメントCREATE SCHEMAはスキーマを作成します。

構文

CREATE SCHEMAの基本的な構文は次のとおりです-

CREATE SCHEMA name;

ここで、_name_はスキーマの名前です。

スキーマにテーブルを作成する構文

スキーマでテーブルを作成する基本的な構文は次のとおりです-

CREATE TABLE myschema.mytable (
...
);

例

スキーマを作成する例を見てみましょう。データベース_testdb_に接続し、次のようにスキーマ_myschema_を作成します-

testdb=# create schema myschema;
CREATE SCHEMA

メッセージ「CREATE SCHEMA」は、スキーマが正常に作成されたことを示します。

さて、次のように上記のスキーマでテーブルを作成しましょう-

testdb=# create table myschema.company(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25),
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

これにより、空のテーブルが作成されます。次のコマンドで作成されたテーブルを確認できます-

testdb=# select * from myschema.company;

これは、次の結果を生成します-

 id | name | age | address | salary
----+------+-----+---------+--------
(0 rows)

スキーマを削除する構文

空のスキーマを削除するには（スキーマ内のすべてのオブジェクトが削除されている）、コマンドを使用します-

DROP SCHEMA myschema;

含まれているすべてのオブジェクトを含むスキーマを削除するには、コマンドを使用します-

DROP SCHEMA myschema CASCADE;

スキーマを使用する利点

これにより、多くのユーザーが互いに干渉することなく1つのデータベースを使用できます。
データベースオブジェクトを論理グループに整理して、管理しやすくします。
サードパーティのアプリケーションを別のスキーマに配置して、他のオブジェクトの名前と衝突しないようにすることができます。

PostgreSQL-クエリの挿入

PostgreSQLの INSERT INTO ステートメントを使用すると、テーブルに新しい行を挿入できます。一度に1つの行を挿入することも、クエリの結果として複数の行を挿入することもできます。

構文

INSERT INTOステートメントの基本的な構文は次のとおりです-

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);

ここで、column1、column2、… columnNは、データを挿入するテーブル内の列の名前です。 *ターゲット列名は任意の順序でリストできます。 VALUES句またはクエリによって提供される値は、左から右への明示的または暗黙的な列リストに関連付けられます。

テーブルのすべての列に値を追加する場合、SQLクエリで列名を指定する必要はありません。ただし、値の順序が表の列と同じ順序であることを確認してください。 SQLのINSERT INTO構文は次のようになります-

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

出力

次の表は、出力メッセージとその意味をまとめたものです-

S. No.

Output Message & Description

1

INSERT oid 1*

1行のみが挿入された場合に返されるメッセージ。 oidは、挿入された行の数値OIDです。

2

INSERT 0 #

複数の行が挿入された場合に返されるメッセージ。＃は挿入された行の数です。

例

次のように testdb でCOMPANYテーブルを作成しましょう-

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL,
   JOIN_DATE      DATE
);

次の例では、COMPANYテーブルに行を挿入します-

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (1, 'Paul', 32, 'California', 20000.00,'2001-07-13');

次の例では、行を挿入しています。ここで_salary_列は省略されているため、デフォルト値があります-

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,JOIN_DATE) VALUES (2, 'Allen', 25, 'Texas', '2007-12-13');

次の例では、値を指定するのではなく、JOIN_DATE列にDEFAULT句を使用します-

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (3, 'Teddy', 23, 'Norway', 20000.00, DEFAULT );

次の例では、複数行のVALUES構文を使用して複数の行を挿入します-

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00, '2007-12-13' ), (5, 'David', 27, 'Texas', 85000.00, '2007-12-13');

上記のすべてのステートメントは、COMPANYテーブルに次のレコードを作成します。次の章では、これらのすべてのレコードをテーブルから表示する方法を説明します。

ID        NAME        AGE        ADDRESS     SALARY   JOIN_DATE
----      ----------  -----      ----------  -------      --------
1         Paul        32         California  20000.0      2001-07-13
2         Allen       25         Texas                    2007-12-13
3         Teddy       23         Norway      20000.0
4         Mark        25         Rich-Mond   65000.0      2007-12-13
5         David       27         Texas       85000.0      2007-12-13

PostgreSQL-SELECTクエリ

PostgreSQLの SELECT ステートメントは、データベーステーブルからデータをフェッチするために使用され、結果テーブルの形式でデータを返します。これらの結果テーブルは、結果セットと呼ばれます。

構文

SELECTステートメントの基本的な構文は次のとおりです-

SELECT column1, column2, columnN FROM table_name;

ここで、column1、column2 …は、値を取得するテーブルのフィールドです。あなたがフィールドで利用可能なすべてのフィールドを取得したい場合は、次の構文を使用することができます-

SELECT *FROM table_name;

例

次のようなレコードを持つテーブルlink：/postgresql/company.sql [COMPANY]を考慮してください-

 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

以下は、CUSTOMERSテーブルで利用可能な顧客のID、名前、および給与フィールドを取得する例です-

testdb=# SELECT ID, NAME, SALARY FROM COMPANY ;

これは、次の結果を生成します-

  id | name  | salary
 ----+-------+--------
   1 | Paul  |  20000
   2 | Allen |  15000
   3 | Teddy |  20000
   4 | Mark  |  65000
   5 | David |  85000
   6 | Kim   |  45000
   7 | James |  10000
(7 rows)

CUSTOMERSテーブルのすべてのフィールドを取得する場合は、次のクエリを使用します-

testdb=# SELECT* FROM COMPANY;

これは、次の結果を生成します-

 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

PostgreSQL-演算子

PostgreSQLの演算子とは何ですか？

演算子は、比較や算術演算などの操作を実行するために主にPostgreSQLステートメントのWHERE句で使用される予約語または文字です。

演算子は、PostgreSQLステートメントで条件を指定し、ステートメントの複数の条件の接続詞として機能するために使用されます。

算術演算子
比較演算子
論理演算子
ビットごとの演算子

PostgreSQLの算術演算子

変数 a が2を保持し、変数 b が3を保持すると仮定します-

リンク：/postgresql/postgresql_arithmetic-operators [例]

Operator	Description	Example
+	Addition - Adds values on either side of the operator	a + b will give 5
-	Subtraction - Subtracts right hand operand from left hand operand	a - b will give -1
*	Multiplication - Multiplies values on either side of the operator	a* b will give 6
/	Division - Divides left hand operand by right hand operand	b/a will give 1
%	Modulus - Divides left hand operand by right hand operand and returns remainder	b % a will give 1
^	Exponentiation - This gives the exponent value of the right hand operand	a ^ b will give 8
	/	square root
	/25.0 will give 5
	/	Cube root
		/27.0 will give 3
!	factorial	5 ! will give 120
!!	factorial (prefix operator)	!! 5 will give 120

PostgreSQL比較演算子

変数aが10を保持し、変数bが20を保持すると仮定します-

リンク：/postgresql/postgresql_comparison-operators [例を表示]

Operator	Description	Example
=	Checks if the values of two operands are equal or not, if yes then condition becomes true.	(a = b) is not true.
!=	Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.	(a != b) is true.
<>	Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.	(a <> b) is true.
>	Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true.	(a > b) is not true.
<	Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true.	(a < b) is true.
>=	Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true.	(a >= b) is not true.
⇐	Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true.	(a ⇐ b) is true.

PostgreSQLの論理演算子

以下は、PostgresSQLで使用可能なすべての論理演算子のリストです。

link：/postgresql/postgresql_logical-operators [例を表示]

No.

演算子と説明

1

そして

AND演算子を使用すると、PostgresSQLステートメントのWHERE句に複数の条件を含めることができます。

2

*NOT*

NOT演算子は、使用される論理演算子の意味を逆にします。 Eg. 存在しない、間にない、中にないなど これは否定演算子です。

3

OR

OR演算子は、PostgresSQLステートメントのWHERE句で複数の条件を組み合わせるために使用されます。

PostgreSQLビット文字列演算子

ビット演算子はビットに対して機能し、ビットごとの操作を実行します。＆および|の真理値表次のとおりです-

p	q	p & q	p
q	0	0	0
0	0	1	0
1	1	1	1
1	1	0	0

A = 60であると仮定します。およびB = 13;今バイナリ形式では、次のようになります-

A = 0011 1100

B = 0000 1101

A＆B = 0000 1100

A | B = 0011 1101

〜A = 1100 0011

リンク：/postgresql/postgresql_bitwise-operators [例を表示]

PostgreSQLでサポートされているビットごとの演算子は、次の表に記載されています-

[cols="^,,^",options="header",]
|===
|Operator |Description |Example |& |Binary AND Operator copies a bit to the result if it exists in both operands. |(A & B) will give 12 which is 0000 1100 || |Binary OR Operator copies a bit if it exists in either operand. |(A | B) will give 61 which is 0011 1101 |~ |Binary Ones Complement Operator is unary and has the effect of 'flipping' bits. |(~A ) will give -61 which is 1100 0011 in 2's complement form due to a signed binary number. |<< |Binary Left Shift Operator. The left operands value is moved left by the number of bits specified by the right operand. |A << 2 will give 240 which is 1111 0000 |>> |Binary Right Shift Operator. The left operands value is moved right by the number of bits specified by the right operand. |A >> 2 will give 15 which is 0000 1111 |# |bitwise XOR. |A # B will give 49 which is 0100 1001
|===

PostgreSQL-式

式は、1つ以上の値、演算子、および値に評価されるPostgresSQL関数の組み合わせです。

PostgreSQLの式は式に似ており、クエリ言語で記述されています。特定のデータセットについてデータベースを照会するために使用することもできます。

構文

次のようにSELECTステートメントの基本的な構文を考慮してください-

SELECT column1, column2, columnN
FROM table_name
WHERE [CONDITION | EXPRESSION];

以下に記載されているPostgreSQLの式にはさまざまな種類があります-

PostgreSQL-ブール式

PostgreSQLのブール式は、一致する単一の値に基づいてデータをフェッチします。以下は構文です-

SELECT column1, column2, columnN
FROM table_name
WHERE SINGLE VALUE MATCHTING EXPRESSION;

次のようなレコードを持つテーブルlink：/postgresql/company.sql [COMPANY]を考慮してください-

testdb# select *from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

これは、PostgreSQLブール式の使用法を示す簡単な例です-

testdb=# SELECT* FROM COMPANY WHERE SALARY = 10000;

上記のPostgreSQLステートメントは、次の結果を生成します-

 id | name  | age | address  | salary
----+-------+-----+----------+--------
  7 | James |  24 | Houston  |  10000
(1 row)

PostgreSQL-数値式

これらの式は、クエリで数学演算を実行するために使用されます。以下は構文です-

SELECT numerical_expression as  OPERATION_NAME
[FROM table_name WHERE CONDITION] ;

ここで、numeric_expressionは、数式または数式に使用されます。以下は、SQL数値式の使用法を示す簡単な例です-

testdb=# SELECT (15 + 6) AS ADDITION ;

上記のPostgreSQLステートメントは、次の結果を生成します-

 addition
----------
       21
(1 row)

avg（）、sum（）、count（）などのいくつかの組み込み関数があり、テーブルまたは特定のテーブル列に対して集計データ計算として知られているものを実行します。

testdb=# SELECT COUNT(*) AS "RECORDS" FROM COMPANY;

上記のPostgreSQLステートメントは、次の結果を生成します-

 RECORDS
---------
       7
(1 row)

PostgreSQL-日付式

日付式は現在のシステムの日付と時刻の値を返し、これらの式はさまざまなデータ操作で使用されます。

testdb=#  SELECT CURRENT_TIMESTAMP;

上記のPostgreSQLステートメントは、次の結果を生成します-

              now
-------------------------------
 2013-05-06 14:38:28.078+05:30
(1 row)

PostgreSQL-WHERE句

PostgreSQLのWHERE句は、単一のテーブルからデータをフェッチするとき、または複数のテーブルと結合するときに条件を指定するために使用されます。

指定された条件が満たされた場合にのみ、テーブルから特定の値を返します。 WHERE句を使用して、結果セットに含めたくない行を除外できます。

WHERE句は、SELECTステートメントで使用されるだけでなく、UPDATE、DELETEステートメントなどでも使用されます。これについては、以降の章で検討します。

構文

WHERE句を持つSELECTステートメントの基本的な構文は次のとおりです-

SELECT column1, column2, columnN
FROM table_name
WHERE [search_condition]

link：/postgresql/postgresql_operators [比較演算子または論理演算子]を使用して、_search_condition_を指定できます。>、<、=、LIKE、NOTなどのように。次の例は、この概念を明確にします。

例

次のようなレコードを持つテーブルlink：/postgresql/company.sql [COMPANY]を考慮してください-

testdb# select *from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

以下に、PostgreSQL論理演算子の使用法を示す簡単な例を示します。次のSELECTステートメントは、AGEが25以上で、かつ給与が65000.00以上であるすべてのレコードをリストダウンします-

testdb=# SELECT* FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;

上記のPostgreSQLステートメントは、次の結果を生成します-

 id | name  | age |  address   | salary
----+-------+-----+------------+--------
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(2 rows)

次のSELECTステートメントは、AGEが25以上、または給与が65000.00以上のすべてのレコードを一覧表示します-

testdb=# SELECT *FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;

上記のPostgreSQLステートメントは、次の結果を生成します-

 id | name  | age | address     | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  2 | Allen |  25 | Texas       |  15000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
(4 rows)

次のSELECTステートメントは、AGEがNULLではないすべてのレコードをリストします。これは、すべてのレコードを意味します。どのレコードもAGEがNULLに等しいためです

testdb=#  SELECT* FROM COMPANY WHERE AGE IS NOT NULL;

上記のPostgreSQLステートメントは、次の結果を生成します-

  id | name  | age | address    | salary
 ----+-------+-----+------------+--------
   1 | Paul  |  32 | California |  20000
   2 | Allen |  25 | Texas      |  15000
   3 | Teddy |  23 | Norway     |  20000
   4 | Mark  |  25 | Rich-Mond  |  65000
   5 | David |  27 | Texas      |  85000
   6 | Kim   |  22 | South-Hall |  45000
   7 | James |  24 | Houston    |  10000
(7 rows)

次のSELECTステートメントは、NAMEが「Pa」で始まるすべてのレコードをリストしますが、「Pa」の後に何があっても問題ありません。

testdb=# SELECT *FROM COMPANY WHERE NAME LIKE 'Pa%';

上記のPostgreSQLステートメントは、次の結果を生成します-

 id | name | age |address    | salary
----+------+-----+-----------+--------
  1 | Paul |  32 | California|  20000

次のSELECTステートメントは、AGE値が25または27のすべてのレコードを一覧表示します-

testdb=# SELECT* FROM COMPANY WHERE AGE IN ( 25, 27 );

上記のPostgreSQLステートメントは、次の結果を生成します-

 id | name  | age | address    | salary
----+-------+-----+------------+--------
  2 | Allen |  25 | Texas      |  15000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(3 rows)

次のSELECTステートメントは、AGE値が25でも27でもないすべてのレコードをリストダウンします-

testdb=# SELECT *FROM COMPANY WHERE AGE NOT IN ( 25, 27 );

上記のPostgreSQLステートメントは、次の結果を生成します-

 id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  3 | Teddy |  23 | Norway     |  20000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
(4 rows)

次のSELECTステートメントは、AGE値がBETWEEN 25 AND 27にあるすべてのレコードをリストダウンします-

testdb=# SELECT* FROM COMPANY WHERE AGE BETWEEN 25 AND 27;

上記のPostgreSQLステートメントは、次の結果を生成します-

 id | name  | age | address    | salary
----+-------+-----+------------+--------
  2 | Allen |  25 | Texas      |  15000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(3 rows)

次のSELECTステートメントは、SQLサブクエリを使用します。サブクエリは、SALARY> 65000以降のWHERE句を持つすべてのレコードを見つけ、EXISTS演算子とともに使用して、返される結果に外部クエリからのAGEが存在するすべてのレコードをリストダウンしますサブクエリによる-

testdb=# SELECT AGE FROM COMPANY
        WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

上記のPostgreSQLステートメントは、次の結果を生成します-

 age
-----
  32
  25
  23
  25
  27
  22
  24
(7 rows)

次のSELECTステートメントはSQLサブクエリを使用します。サブクエリは、SALARY> 65000以降のWHERE句を持つすべてのレコードを検索し、>演算子とともに>演算子を使用して、外部クエリからのAGEが年齢よりも大きいすべてのレコードを一覧表示しますサブクエリによって返された結果-

testdb=# SELECT * FROM COMPANY
        WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

上記のPostgreSQLステートメントは、次の結果を生成します-

 id | name | age | address    | salary
----+------+-----+------------+--------
  1 | Paul |  32 | California |  20000

ANDおよびOR接続演算子

PostgreSQLの AND および OR 演算子は、複数の条件を組み合わせて、PostgreSQLステートメントで選択したデータを絞り込むために使用されます。これらの2つの演算子は、結合演算子と呼ばれます。

これらの演算子は、同じPostgreSQLステートメントで異なる演算子と複数の比較を行う手段を提供します。

AND演算子

*AND* 演算子を使用すると、PostgreSQLステートメントのWHERE句に複数の条件を含めることができます。 AND演算子を使用している間、すべての条件が真の場合、完全な条件が真であると想定されます。 たとえば、[condition1] AND [condition2]は、condition1とcondition2の両方がtrueの場合にのみtrueになります。

構文

WHERE句を使用したAND演算子の基本的な構文は次のとおりです-

SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];

AND演算子を使用して、N個の条件を組み合わせることができます。 PostgreSQLステートメントがアクションを実行するには、トランザクションまたはクエリのいずれであっても、ANDで区切られたすべての条件がTRUEである必要があります。

例

次のようなレコードを持つテーブルlink：/postgresql/company.sql [COMPANY]を考慮してください-

testdb# select *from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

次のSELECTステートメントは、AGEが25以上であり、給与が65000.00以上であるすべてのレコードを一覧表示します-

testdb=# SELECT* FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;

上記のPostgreSQLステートメントは、次の結果を生成します-

 id | name  | age | address    | salary
----+-------+-----+------------+--------
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(2 rows)

OR演算子

OR演算子は、PostgreSQLステートメントのWHERE句で複数の条件を結合するためにも使用されます。 OR演算子を使用している場合、少なくともいずれかの条件が真である場合、完全な条件が真であると見なされます。たとえば、condition1またはcondition2のいずれかが真の場合、[condition1] OR [condition2]は真になります。

構文

WHERE句を持つOR演算子の基本的な構文は次のとおりです-

SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]

OR演算子を使用して、N個の条件を組み合わせることができます。 PostgreSQLステートメントが実行するアクションは、トランザクションまたはクエリのいずれであっても、ORで区切られた条件のいずれか1つのみがTRUEでなければなりません。

例

次のレコードを持つlink：/postgresql/company.sql [COMPANY]テーブルを検討してください-

 # select *from COMPANY;
  id | name  | age | address   | salary
 ----+-------+-----+-----------+--------
   1 | Paul  |  32 | California|  20000
   2 | Allen |  25 | Texas     |  15000
   3 | Teddy |  23 | Norway    |  20000
   4 | Mark  |  25 | Rich-Mond |  65000
   5 | David |  27 | Texas     |  85000
   6 | Kim   |  22 | South-Hall|  45000
   7 | James |  24 | Houston   |  10000
(7 rows)

次のSELECTステートメントは、AGEが25以上、または給与が65000.00以上のすべてのレコードを一覧表示します-

testdb=# SELECT* FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;

上記のPostgreSQLステートメントは、次の結果を生成します-

 id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(4 rows)

PostgreSQL-UPDATEクエリ

PostgreSQL UPDATE クエリは、テーブル内の既存のレコードを変更するために使用されます。 UPDATEクエリでWHERE句を使用して、選択した行を更新できます。そうしないと、すべての行が更新されます。

構文

WHERE句を使用したUPDATEクエリの基本的な構文は次のとおりです-

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

ANDまたはOR演算子を使用して、N個の条件を組み合わせることができます。

例

次のようなレコードを持つテーブルlink：/postgresql/company.sql [COMPANY]を考慮してください-

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

以下は、IDが6である顧客のADDRESSを更新する例です-

testdb=# UPDATE COMPANY SET SALARY = 15000 WHERE ID = 3;

さて、COMPANY表には次のレコードがあります-

 id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
  3 | Teddy |  23 | Norway     |  15000
(7 rows)

COMPANYテーブルのすべてのADDRESSおよびSALARY列の値を変更する場合、WHERE句を使用する必要はなく、UPDATEクエリは次のようになります-

testdb=# UPDATE COMPANY SET ADDRESS = 'Texas', SALARY=20000;

さて、COMPANY表には次のレコードがあります-

 id | name  | age | address | salary
----+-------+-----+---------+--------
  1 | Paul  |  32 | Texas   |  20000
  2 | Allen |  25 | Texas   |  20000
  4 | Mark  |  25 | Texas   |  20000
  5 | David |  27 | Texas   |  20000
  6 | Kim   |  22 | Texas   |  20000
  7 | James |  24 | Texas   |  20000
  3 | Teddy |  23 | Texas   |  20000
(7 rows)

PostgreSQL-DELETEクエリ

PostgreSQL DELETE クエリは、テーブルから既存のレコードを削除するために使用されます。 DELETEクエリでWHERE句を使用して、選択した行を削除できます。そうしないと、すべてのレコードが削除されます。

構文

WHERE句を使用したDELETEクエリの基本的な構文は次のとおりです-

DELETE FROM table_name
WHERE [condition];

ANDまたはOR演算子を使用して、N個の条件を組み合わせることができます。

例

次のようなレコードを持つテーブルlink：/postgresql/company.sql [COMPANY]を考慮してください-

# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

以下は、IDが7である顧客を削除する例です-

testdb=# DELETE FROM COMPANY WHERE ID = 2;

さて、COMPANY表には次のレコードがあります-

 id | name  | age | address     | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
(6 rows)

COMPANYテーブルからすべてのレコードを削除する場合、次のようにDELETEクエリでWHERE句を使用する必要はありません-

testdb=# DELETE FROM COMPANY;

現在、DELETEステートメントによってすべてのレコードが削除されているため、COMPANYテーブルにはレコードがありません。

PostgreSQL-LIKE句

PostgreSQLの LIKE 演算子は、ワイルドカードを使用してパターンに対してテキスト値を照合するために使用されます。検索式がパターン式と一致する場合、LIKE演算子はtrue、つまり 1 を返します。

LIKE演算子と組み合わせて使用される2つのワイルドカードがあります-

パーセント記号（％） *アンダースコア（_）

パーセント記号は、0、1、または複数の数字または文字を表します。下線は、単一の数字または文字を表します。これらのシンボルは組み合わせて使用できます。

これらの2つの記号のいずれかがLIKE節と一緒に使用されない場合、LIKEは等号演算子のように機能します。

構文

％と_の基本的な構文は次のとおりです-

SELECT FROM table_name
WHERE column LIKE 'XXXX%'

or

SELECT FROM table_name
WHERE column LIKE '%XXXX%'

or

SELECT FROM table_name
WHERE column LIKE 'XXXX_'

or

SELECT FROM table_name
WHERE column LIKE '_XXXX'

or

SELECT FROM table_name
WHERE column LIKE '_XXXX_'

ANDまたはOR演算子を使用して、N個の条件を組み合わせることができます。ここで、XXXXは任意の数値または文字列値です。

例

「％」演算子と「_」演算子を使用した異なるLIKE句を持つWHERE部分を示すいくつかの例があります-

S. No.	Statement & Description
1	WHERE SALARY::text LIKE '200%'* 200で始まる値を検索します
2	WHERE SALARY::text LIKE '%200%' 任意の位置に200がある値を検索します
3	WHERE SALARY::text LIKE '_00%' 2番目と3番目の位置に00がある値を検索します
4	WHERE SALARY::text LIKE '2_%_%' 2で始まり、長さが3文字以上の値を検索します
5	WHERE SALARY::text LIKE '%2' 2で終わる値を検索します
6	WHERE SALARY::text LIKE '_2%3' 2番目の位置に2があり、3で終わる値を検索します
7	WHERE SALARY::text LIKE '2___3' 2で始まり3で終わる5桁の数値の値を検索します

'_Postgres LIKEは文字列比較のみです。したがって、上記の例のように整数列を明示的に文字列にキャストする必要があります。_

実際の例を見てみましょう、次のようなレコードを持つテーブルlink：/postgresql/company.sql [COMPANY]を考えてみましょう-

# select *from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

以下は、AGEが2で始まるCOMPANYテーブルのすべてのレコードを表示する例です-

testdb=# SELECT* FROM COMPANY WHERE AGE::text LIKE '2%';

これは、次の結果を生成します-

 id | name  | age | address     | salary
----+-------+-----+-------------+--------
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
  8 | Paul  |  24 | Houston     |  20000
(7 rows)

以下は、ADDRESSにテキスト内にハイフン（-）が含まれるCOMPANYテーブルのすべてのレコードを表示する例です-

testdb=# SELECT * FROM COMPANY WHERE ADDRESS  LIKE '%-%';

これは、次の結果を生成します-

 id | name | age |                      address              | salary
----+------+-----+-------------------------------------------+--------
  4 | Mark |  25 | Rich-Mond                                 |  65000
  6 | Kim  |  22 | South-Hall                                |  45000
(2 rows)

PostgreSQL-LIMIT句

PostgreSQLの LIMIT 句は、SELECTステートメントによって返されるデータ量を制限するために使用されます。

構文

LIMIT句を持つSELECTステートメントの基本的な構文は次のとおりです-

SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]

以下は、OFFSET句と一緒に使用される場合のLIMIT句の構文です-

SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows] OFFSET [row num]

LIMITおよびOFFSETを使用すると、クエリの残りの部分で生成された行の一部のみを取得できます。

例

次のようなレコードを持つテーブルlink：/postgresql/company.sql [COMPANY]を考慮してください-

# select *from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

以下は、テーブルからフェッチする行の数に応じてテーブル内の行を制限する例です-

testdb=# SELECT* FROM COMPANY LIMIT 4;

これは、次の結果を生成します-

 id | name  | age | address     | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
(4 rows)

ただし、特定の状況では、特定のオフセットからレコードのセットを取得する必要がある場合があります。 3番目の位置から始まる3つのレコードをピックアップする例を次に示します-

testdb=# SELECT * FROM COMPANY LIMIT 3 OFFSET 2;

これは、次の結果を生成します-

 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
(3 rows)

PostgreSQL-ORDER BY句

PostgreSQLの ORDER BY 句は、1つ以上の列に基づいてデータを昇順または降順に並べ替えるために使用されます。

構文

ORDER BY句の基本的な構文は次のとおりです-

SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

ORDER BY句では複数の列を使用できます。ソートに使用している列が何であれ、その列がcolumn-listで使用可能であることを確認してください。

例

次のようなレコードを持つテーブルlink：/postgresql/company.sql [COMPANY]を考慮してください-

testdb# select *from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

以下は、SALARYで昇順で結果をソートする例です-

testdb=# SELECT* FROM COMPANY ORDER BY AGE ASC;

これは、次の結果を生成します-

  id | name  | age | address    | salary
 ----+-------+-----+------------+--------
   6 | Kim   |  22 | South-Hall |  45000
   3 | Teddy |  23 | Norway     |  20000
   7 | James |  24 | Houston    |  10000
   8 | Paul  |  24 | Houston    |  20000
   4 | Mark  |  25 | Rich-Mond  |  65000
   2 | Allen |  25 | Texas      |  15000
   5 | David |  27 | Texas      |  85000
   1 | Paul  |  32 | California |  20000
   9 | James |  44 | Norway     |   5000
  10 | James |  45 | Texas      |   5000
(10 rows)

以下は、NAMEとSALARYの昇順で結果をソートする例です-

testdb=# SELECT *FROM COMPANY ORDER BY NAME, SALARY ASC;

これは、次の結果を生成します-

 id | name  | age | address      | salary
----+-------+-----+--------------+--------
  2 | Allen |  25 | Texas        |  15000
  5 | David |  27 | Texas        |  85000
 10 | James |  45 | Texas        |   5000
  9 | James |  44 | Norway       |   5000
  7 | James |  24 | Houston      |  10000
  6 | Kim   |  22 | South-Hall   |  45000
  4 | Mark  |  25 | Rich-Mond    |  65000
  1 | Paul  |  32 | California   |  20000
  8 | Paul  |  24 | Houston      |  20000
  3 | Teddy |  23 | Norway       |  20000
(10 rows)

以下は、NAMEで結果を降順に並べ替える例です-

testdb=# SELECT* FROM COMPANY ORDER BY NAME DESC;

これは、次の結果を生成します-

 id | name  | age | address    | salary
----+-------+-----+------------+--------
  3 | Teddy |  23 | Norway     |  20000
  1 | Paul  |  32 | California |  20000
  8 | Paul  |  24 | Houston    |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
  9 | James |  44 | Norway     |   5000
 10 | James |  45 | Texas      |   5000
  5 | David |  27 | Texas      |  85000
  2 | Allen |  25 | Texas      |  15000
(10 rows)

PostgreSQL-GROUP BY

PostgreSQLの GROUP BY 句は、SELECTステートメントと連携して、同一のデータを持つテーブル内の行をグループ化するために使用されます。これは、出力の冗長性を排除したり、これらのグループに適用される集計を計算するために行われます。

GROUP BY句は、SELECTステートメントのWHERE句に続き、ORDER BY句に先行します。

構文

GROUP BY句の基本的な構文は次のとおりです。 GROUP BY句は、WHERE句の条件に従う必要があり、ORDER BY句を使用する場合はORDER BY句の前になければなりません。

SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN

GROUP BY句では複数の列を使用できます。グループ化に使用している列が何であれ、その列がcolumn-listで使用可能であることを確認してください。

例

次のようなレコードを持つテーブルlink：/postgresql/company.sql [COMPANY]を考慮してください-

# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

あなたが各顧客の給与の合計額を知りたい場合、GROUP BYクエリは次のようになります-

testdb=# SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;

これは、次の結果を生成します-

  name  |  sum
 -------+-------
  Teddy | 20000
  Paul  | 20000
  Mark  | 65000
  David | 85000
  Allen | 15000
  Kim   | 45000
  James | 10000
(7 rows)

ここで、次のINSERTステートメントを使用してCOMPANYテーブルにさらに3つのレコードを作成しましょう-

INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00);
INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00);
INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00);

今、私たちのテーブルには重複した名前を持つ次のレコードがあります-

  id | name  | age | address      | salary
 ----+-------+-----+--------------+--------
   1 | Paul  |  32 | California   |  20000
   2 | Allen |  25 | Texas        |  15000
   3 | Teddy |  23 | Norway       |  20000
   4 | Mark  |  25 | Rich-Mond    |  65000
   5 | David |  27 | Texas        |  85000
   6 | Kim   |  22 | South-Hall   |  45000
   7 | James |  24 | Houston      |  10000
   8 | Paul  |  24 | Houston      |  20000
   9 | James |  44 | Norway       |   5000
  10 | James |  45 | Texas        |   5000
(10 rows)

繰り返しますが、同じステートメントを使用して、NAME列を使用するすべてのレコードを次のようにグループ化します。

testdb=# SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;

これは、次の結果を生成します-

 name  |  sum
-------+-------
 Allen | 15000
 David | 85000
 James | 20000
 Kim   | 45000
 Mark  | 65000
 Paul  | 40000
 Teddy | 20000
(7 rows)

次のようにGROUP BY句とともにORDER BY句を使用してみましょう-

testdb=#  SELECT NAME, SUM(SALARY)
         FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;

これは、次の結果を生成します-

 name  |  sum
-------+-------
 Teddy | 20000
 Paul  | 40000
 Mark  | 65000
 Kim   | 45000
 James | 20000
 David | 85000
 Allen | 15000
(7 rows)

PostgreSQL-WITH句

PostgreSQLでは、WITHクエリは、より大きなクエリで使用する補助ステートメントを記述する方法を提供します。複雑で大規模なクエリを簡単に読みやすい形式に分解するのに役立ちます。これらのステートメントは、よくCommon Table ExpressionsまたはCTEと呼ばれ、1つのクエリに対してのみ存在する一時テーブルを定義すると考えることができます。

WITHクエリはCTEクエリであり、サブクエリが複数回実行される場合に特に役立ちます。一時テーブルの代わりにも同様に役立ちます。集計を一度計算し、クエリでその名前（複数回の場合もある）で参照できるようにします。

WITH句は、クエリで使用する前に定義する必要があります。

構文

WITHクエリの基本的な構文は次のとおりです-

WITH
   name_for_summary_data AS (
      SELECT Statement)
   SELECT columns
   FROM name_for_summary_data
   WHERE conditions <=> (
      SELECT column
      FROM name_for_summary_data)
   [ORDER BY columns]

_name_for_summary_data_は、WITH句に指定された名前です。 name_for_summary_dataは既存のテーブル名と同じにすることができ、優先されます。

WITHでデータ変更ステートメント（INSERT、UPDATE、またはDELETE）を使用できます。これにより、同じクエリで複数の異なる操作を実行できます。

再帰的WITH

例

次のようなレコードを持つテーブルlink：/postgresql/company.sql [COMPANY]を考慮してください-

testdb# select *from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

さて、次のように、上記のテーブルからレコードを選択するWITH句を使用してクエリを記述しましょう-

With CTE AS
(Select
 ID
, NAME
, AGE
, ADDRESS
, SALARY
FROM COMPANY )
Select* From CTE;

上記のPostgreSQLステートメントは、次の結果を生成します-

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

今、私たちは次のように、20000未満の給与の合計を見つけるために、WITH句と一緒にRECURSIVEキーワードを使用してクエリを書いてみましょう-

WITH RECURSIVE t(n) AS (
   VALUES (0)
   UNION ALL
   SELECT SALARY FROM COMPANY WHERE SALARY < 20000
)
SELECT sum(n) FROM t;

上記のPostgreSQLステートメントは、次の結果を生成します-

  sum
-------
 25000
(1 row)

以下に示すように、WITH句とともにデータ変更ステートメントを使用してクエリを記述しましょう。

最初に、表COMPANYと同様の表COMPANY1を作成します。例のクエリは、行をCOMPANYからCOMPANY1に効果的に移動します。 WITHのDELETEは、指定された行をCOMPANYから削除し、RETURNING句によってその内容を返します。そして、プライマリクエリはその出力を読み取り、COMPANY1 TABLEに挿入します-

CREATE TABLE COMPANY1(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

WITH moved_rows AS (
   DELETE FROM COMPANY
   WHERE
      SALARY >= 30000
   RETURNING *
)
INSERT INTO COMPANY1 (SELECT *FROM moved_rows);

上記のPostgreSQLステートメントは、次の結果を生成します-

INSERT 0 3

さて、テーブルCOMPANYとCOMPANY1のレコードは次のとおりです-

testdb=# SELECT* FROM COMPANY;
 id | name  | age |  address   | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  7 | James |  24 | Houston    |  10000
(4 rows)


testdb=# SELECT * FROM COMPANY1;
 id | name  | age | address | salary
----+-------+-----+-------------+--------
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
(3 rows)

PostgreSQL-HAVING句

HAVING句を使用すると、関数の結果が特定の条件を満たす特定の行を選択できます。

WHERE句は選択した列に条件を配置しますが、HAVING句はGROUP BY句によって作成されたグループに条件を配置します。

構文

以下は、SELECTクエリ内のHAVING句の位置です-

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

HAVING句は、クエリ内のGROUP BY句に続き、使用する場合はORDER BY句にも先行する必要があります。以下は、HAVING句を含むSELECTステートメントの構文です-

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

例

次のようなレコードを持つテーブルlink：/postgresql/company.sql [COMPANY]を考慮してください-

# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

以下は、名前カウントが2未満のレコードを表示する例です-

testdb-# SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) < 2;

これは、次の結果を生成します-

  name
 -------
  Teddy
  Paul
  Mark
  David
  Allen
  Kim
  James
(7 rows)

ここで、次のINSERTステートメントを使用してCOMPANYテーブルにさらに3つのレコードを作成しましょう-

INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00);
INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00);
INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00);

今、私たちのテーブルには重複した名前を持つ次のレコードがあります-

  id | name  | age | address      | salary
 ----+-------+-----+--------------+--------
   1 | Paul  |  32 | California   |  20000
   2 | Allen |  25 | Texas        |  15000
   3 | Teddy |  23 | Norway       |  20000
   4 | Mark  |  25 | Rich-Mond    |  65000
   5 | David |  27 | Texas        |  85000
   6 | Kim   |  22 | South-Hall   |  45000
   7 | James |  24 | Houston      |  10000
   8 | Paul  |  24 | Houston      |  20000
   9 | James |  44 | Norway       |   5000
  10 | James |  45 | Texas        |   5000
(10 rows)

以下は、名前のカウントが1より大きいレコードを表示する例です-

testdb-# SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) > 1;

これは、次の結果を生成します-

 name
-------
 Paul
 James
(2 rows)

PostgreSQL-DISTINCTキーワード

PostgreSQLの DISTINCT キーワードをSELECTステートメントと併用して、重複するレコードをすべて削除し、一意のレコードのみを取得します。

テーブルに複数の重複レコードがある場合があります。そのようなレコードをフェッチするとき、重複レコードをフェッチするのではなく、一意のレコードのみをフェッチする方が理にかなっています。

構文

重複レコードを排除するためのDISTINCTキーワードの基本的な構文は次のとおりです-

SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]

例

次のようなレコードを持つテーブルlink：/postgresql/company.sql [COMPANY]を考慮してください-

# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

次のように、このテーブルにさらに2つのレコードを追加しましょう-

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (8, 'Paul', 32, 'California', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (9, 'Allen', 25, 'Texas', 15000.00 );

さて、COMPANYテーブルのレコードは次のようになります-

 id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
  8 | Paul  |  32 | California |  20000
  9 | Allen |  25 | Texas      |  15000
(9 rows)

まず、次のSELECTクエリが重複する給与レコードを返す方法を見てみましょう-

testdb=# SELECT name FROM COMPANY;

これは、次の結果を生成します-

 name
-------
 Paul
 Allen
 Teddy
 Mark
 David
 Kim
 James
 Paul
 Allen
(9 rows)

ここで、上記のSELECTクエリで DISTINCT キーワードを使用して結果を確認します-

testdb=# SELECT DISTINCT name FROM COMPANY;

これにより、重複エントリがない次の結果が生成されます-

 name
-------
 Teddy
 Paul
 Mark
 David
 Allen
 Kim
 James
(7 rows)

PostgreSQL-制約

制約は、テーブルのデータ列に適用されるルールです。これらは、無効なデータがデータベースに入力されるのを防ぐために使用されます。これにより、データベース内のデータの正確性と信頼性が保証されます。

制約は、列レベルまたは表レベルです。列レベルの制約は1つの列にのみ適用されますが、表レベルの制約は表全体に適用されます。列のデータ型を定義すること自体が制約です。たとえば、DATE型の列は、列を有効な日付に制限します。

以下は、PostgreSQLで一般的に使用される制約です。

* NOT NULL制約*-列にNULL値を設定できないようにします。
UNIQUE Constraint -列のすべての値が異なることを保証します。
* PRIMARYキー*-データベーステーブル内の各行/レコードを一意に識別します。
FOREIGN Key -他のテーブルの列に基づいてデータを制限します。
* CHECK制約*-CHECK制約は、列内のすべての値が特定の条件を満たすことを保証します。
* EXCLUSION制約*-EXCLUDE制約により、指定された列または式で指定された演算子を使用して2行が比較された場合、これらの比較のすべてがTRUEを返すわけではありません。

NOT NULL制約

デフォルトでは、列はNULL値を保持できます。列にNULL値を持たせたくない場合は、この列にNULLが許可されないように指定して、この列にそのような制約を定義する必要があります。 NOT NULL制約は、常に列制約として記述されます。

NULLはデータなしと同じではありません。むしろ、未知のデータを表します。

例

たとえば、次のPostgreSQLステートメントは、COMPANY1という新しいテーブルを作成し、5つの列を追加します。そのうちの3つであるIDとNAMEとAGEは、NULL値を受け入れないように指定します-

CREATE TABLE COMPANY1(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

一意の制約

UNIQUE制約は、2つのレコードが特定の列に同じ値を持つことを防ぎます。たとえば、COMPANYテーブルでは、複数の人が同じ年齢になるのを防ぐことができます。

例

たとえば、次のPostgreSQLステートメントはCOMPANY3という新しいテーブルを作成し、5つの列を追加します。ここでは、AGE列はUNIQUEに設定されているため、同じ年齢の2つのレコードを持つことはできません-

CREATE TABLE COMPANY3(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL UNIQUE,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00
);

主キー制約

PRIMARY KEY制約は、データベーステーブル内の各レコードを一意に識別します。 UNIQUE列はさらに存在できますが、テーブルには主キーが1つしかありません。データベーステーブルを設計する場合、主キーは重要です。主キーは一意のIDです。

これらを使用して、テーブル行を参照します。主キーは、テーブル間のリレーションを作成するときに、他のテーブルの外部キーになります。「長年にわたるコーディングの監視」のため、SQLiteでは主キーがNULLになる可能性があります。これは他のデータベースには当てはまりません

主キーは、データベーステーブル内の各行/レコードを一意に識別するテーブル内のフィールドです。主キーには一意の値が含まれている必要があります。主キー列にNULL値を含めることはできません。

テーブルには、単一または複数のフィールドで構成される主キーを1つだけ含めることができます。複数のフィールドが主キーとして使用される場合、それらは*複合キー*と呼ばれます。

テーブルのいずれかのフィールドで定義された主キーがある場合、そのフィールドの同じ値を持つ2つのレコードを持つことはできません。

例

上記のさまざまな例を見て、IDを主キーとしてCOMAPNY4テーブルを作成しました-

CREATE TABLE COMPANY4(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

外部キー制約

外部キー制約は、列（または列のグループ）の値が別のテーブルのある行に表示される値と一致する必要があることを指定します。これにより、2つの関連するテーブル間の参照整合性が維持されます。これらは外部キーであるため、外部キーと呼ばれます。つまり、テーブルの外です。外部キーは、参照キーと呼ばれることもあります。

例

たとえば、次のPostgreSQLステートメントはCOMPANY5という新しいテーブルを作成し、5つの列を追加します。

CREATE TABLE COMPANY6(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

たとえば、次のPostgreSQLステートメントは、3つの列を追加するDEPARTMENT1という新しいテーブルを作成します。列EMP_IDは外部キーであり、テーブルCOMPANY6のIDフィールドを参照します。

CREATE TABLE DEPARTMENT1(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      references COMPANY6(ID)
);

チェック制約

CHECK制約により、レコードに入力される値を条件でチェックできます。条件が偽と評価された場合、レコードは制約に違反しており、テーブルに入力されません。

例

たとえば、次のPostgreSQLステートメントはCOMPANY5という新しいテーブルを作成し、5つの列を追加します。ここでは、SALARYカラムにCHECKを追加して、SALARYをゼロにすることはできません。

CREATE TABLE COMPANY5(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    CHECK(SALARY > 0)
);

除外制約

除外制約は、指定された演算子を使用して指定された列または式で2行が比較された場合、これらの演算子比較の少なくとも1つがfalseまたはnullを返すようにします。

例

たとえば、次のPostgreSQLステートメントはCOMPANY7という新しいテーブルを作成し、5つの列を追加します。ここでは、EXCLUDE制約を追加します-

CREATE TABLE COMPANY7(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT,
   AGE            INT  ,
   ADDRESS        CHAR(50),
   SALARY         REAL,
   EXCLUDE USING gist
   (NAME WITH =,
   AGE WITH <>)
);

ここで、_USING gist_は、構築のために使用し、施行に使用するインデックスのタイプです。

'_データベースごとに1回、_CREATE EXTENSION btree_gist_コマンドを実行する必要があります。これにより、btree_gist拡張がインストールされ、プレーンスカラーデータ型の除外制約が定義されます。_

私たちは年齢が同じでなければならないことを実施したので、テーブルにレコードを挿入してこれを見てみましょう-

INSERT INTO COMPANY7 VALUES(1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY7 VALUES(2, 'Paul', 32, 'Texas', 20000.00 );
INSERT INTO COMPANY7 VALUES(3, 'Paul', 42, 'California', 20000.00 );

最初の2つのINSERTステートメントの場合、レコードはCOMPANY7テーブルに追加されます。 3番目のINSERT文では、次のエラーが表示されます-

ERROR:  duplicate key value violates unique constraint "company7_pkey"
DETAIL:  Key (id)=(3) already exists.

制約の削除

制約を削除するには、その名前を知る必要があります。名前がわかっている場合は、簡単に削除できます。それ以外の場合は、システム生成の名前を見つける必要があります。ここでは、psqlコマンド\ dテーブル名が役立ちます。一般的な構文は-

ALTER TABLE table_name DROP CONSTRAINT some_name;

PostgreSQL-参加

PostgreSQLの Joins 句は、データベース内の2つ以上のテーブルのレコードを結合するために使用されます。 JOINは、それぞれに共通の値を使用して2つのテーブルのフィールドを結合するための手段です。

PostgreSQLの結合タイプは-

クロスジョイン
インナージョイン
左アウタージョイン
正しいアウタージョイン
フルアウタージョイン

先に進む前に、COMPANYとDEPARTMENTの2つのテーブルについて考えてみましょう。 COMPANYテーブルにデータを挿入するINSERTステートメントを見てきました。 COMPANYテーブルで利用可能なレコードのリストを想定してみましょう-

 id | name  | age | address   | salary | join_date
----+-------+-----+-----------+--------+-----------
  1 | Paul  |  32 | California|  20000 | 2001-07-13
  3 | Teddy |  23 | Norway    |  20000 |
  4 | Mark  |  25 | Rich-Mond |  65000 | 2007-12-13
  5 | David |  27 | Texas     |  85000 | 2007-12-13
  2 | Allen |  25 | Texas     |        | 2007-12-13
  8 | Paul  |  24 | Houston   |  20000 | 2005-07-13
  9 | James |  44 | Norway    |   5000 | 2005-07-13
 10 | James |  45 | Texas     |   5000 | 2005-07-13

別のテーブルはDEPARTMENTであり、次の定義があります-

CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

DEPARTMENTテーブルに入力するINSERTステートメントのリストは次のとおりです-

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (1, 'IT Billing', 1 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (2, 'Engineering', 2 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (3, 'Finance', 7 );

最後に、DEPARTMENTテーブルで利用可能なレコードの次のリストがあります-

 id | dept        | emp_id
----+-------------+--------
  1 | IT Billing  |  1
  2 | Engineering |  2
  3 | Finance     |  7

クロスジョイン

CROSS JOINは、最初のテーブルのすべての行と2番目のテーブルのすべての行を一致させます。入力テーブルにそれぞれx列とy列がある場合、結果のテーブルにはx + y列があります。 CROSS JOINは非常に大きなテーブルを生成する可能性があるため、適切な場合にのみ使用するように注意する必要があります。

以下は、CROSS JOINの構文です-

SELECT ... FROM table1 CROSS JOIN table2 ...

上記の表に基づいて、次のようにCROSS JOINを記述できます-

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;

上記のクエリは、次の結果を生成します-

emp_id| name  |  dept
------|-------|--------------
    1 | Paul  | IT Billing
    1 | Teddy | IT Billing
    1 | Mark  | IT Billing
    1 | David | IT Billing
    1 | Allen | IT Billing
    1 | Paul  | IT Billing
    1 | James | IT Billing
    1 | James | IT Billing
    2 | Paul  | Engineering
    2 | Teddy | Engineering
    2 | Mark  | Engineering
    2 | David | Engineering
    2 | Allen | Engineering
    2 | Paul  | Engineering
    2 | James | Engineering
    2 | James | Engineering
    7 | Paul  | Finance
    7 | Teddy | Finance
    7 | Mark  | Finance
    7 | David | Finance
    7 | Allen | Finance
    7 | Paul  | Finance
    7 | James | Finance
    7 | James | Finance

インナージョイン

INNER JOINは、結合述語に基づいて2つのテーブル（table1とtable2）の列値を組み合わせて、新しい結果テーブルを作成します。クエリは、table1の各行とtable2の各行を比較して、結合述語を満たす行のすべてのペアを見つけます。結合述語が満たされると、table1とtable2の行の一致した各ペアの列値が結果行に結合されます。

INNER JOINは、最も一般的な結合のタイプであり、デフォルトの結合タイプです。オプションでINNERキーワードを使用できます。

以下はINNER JOINの構文です-

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_filed = table2.common_field;

上記の表に基づいて、次のようにINNER JOINを書くことができます-

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID;

上記のクエリは、次の結果を生成します-

 emp_id | name  | dept
--------+-------+------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering

左アウタージョイン

OUTER JOINは、INNER JOINの拡張です。 SQL標準では、LEFT、RIGHT、FULLの3種類のOUTER JOINが定義されており、PostgreSQLはこれらすべてをサポートしています。

LEFT OUTER JOINの場合、最初に内部結合が実行されます。次に、テーブルT2の行との結合条件を満たさないテーブルT1の各行について、T2の列にNULL値を持つ結合行が追加されます。したがって、結合テーブルには、T1の各行に対して常に少なくとも1つの行があります。

以下はLEFT OUTER JOINの構文です-

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

上記の表に基づいて、次のように内部結合を記述できます-

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

上記のクエリは、次の結果を生成します-

 emp_id | name  | dept
--------+-------+------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
        | James |
        | David |
        | Paul  |
        | Mark  |
        | Teddy |
        | James |

正しいアウタージョイン

最初に、内部結合が実行されます。次に、テーブルT1の行との結合条件を満たさないテーブルT2の各行について、T1の列にNULL値を持つ結合された行が追加されます。これは左結合の逆です。結果テーブルには、T2の各行に対して常に行があります。

以下は、右外部結合の構文です-

SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression ...

上記の表に基づいて、次のように内部結合を記述できます-

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY RIGHT OUTER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

上記のクエリは、次の結果を生成します-

 emp_id | name  | dept
--------+-------+--------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 |       | Finance

フルアウタージョイン

最初に、内部結合が実行されます。次に、テーブルT2の行との結合条件を満たさないテーブルT1の各行について、T2の列にNULL値を持つ結合行が追加されます。さらに、T1のどの行とも結合条件を満たさないT2の各行に対して、T1の列にNULL値が含まれる結合行が追加されます。

以下は、FULL OUTER JOINの構文です-

SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ...

上記の表に基づいて、次のように内部結合を記述できます-

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY FULL OUTER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

上記のクエリは、次の結果を生成します-

 emp_id | name  | dept
--------+-------+---------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 |       | Finance
        | James |
        | David |
        | Paul  |
        | Mark  |
        | Teddy |
        | James |

PostgreSQL-UNIONS句

PostgreSQLの UNION 句/演算子は、重複行を返さずに2つ以上のSELECTステートメントの結果を結合するために使用されます。

UNIONを使用するには、各SELECTで選択されている列の数、列式の数、データ型が同じで、順序が同じである必要がありますが、長さが同じである必要はありません。

構文

*UNION* の基本的な構文は次のとおりです-

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

ここで、特定の条件は、要件に基づいて任意の特定の式にすることができます。

例

次の2つのテーブルを検討してください、（a）リンク：/postgresql/company.sql [COMPANY]テーブルは次のとおりです-

testdb=# SELECT *from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

（b）別のテーブルはlink：/postgresql/department.sql [DEPARTMENT]です。

testdb=# SELECT* from DEPARTMENT;
 id | dept        | emp_id
----+-------------+--------
  1 | IT Billing  |      1
  2 | Engineering |      2
  3 | Finance     |      7
  4 | Engineering |      3
  5 | Finance     |      4
  6 | Engineering |      5
  7 | Finance     |      6
(7 rows)

次のように、UNION句とともにSELECTステートメントを使用してこれらの2つのテーブルを結合しましょう-

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID
   UNION
      SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
         ON COMPANY.ID = DEPARTMENT.EMP_ID;

これは、次の結果を生成します-

 emp_id | name  |  dept
--------+-------+--------------
      5 | David | Engineering
      6 | Kim   | Finance
      2 | Allen | Engineering
      3 | Teddy | Engineering
      4 | Mark  | Finance
      1 | Paul  | IT Billing
      7 | James | Finance
(7 rows)

UNION ALL句

UNION ALL演算子は、重複行を含む2つのSELECTステートメントの結果を結合するために使用されます。 UNIONに適用される同じ規則は、UNION ALL演算子にも適用されます。

構文

*UNION ALL* の基本的な構文は次のとおりです-

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION ALL

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

ここで、特定の条件は、要件に基づいて任意の特定の式にすることができます。

例

さて、次のようにSELECTステートメントで上記の2つのテーブルを結合しましょう-

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID
   UNION ALL
      SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
         ON COMPANY.ID = DEPARTMENT.EMP_ID;

これは、次の結果を生成します-

 emp_id | name  | dept
--------+-------+--------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 | James | Finance
      3 | Teddy | Engineering
      4 | Mark  | Finance
      5 | David | Engineering
      6 | Kim   | Finance
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 | James | Finance
      3 | Teddy | Engineering
      4 | Mark  | Finance
      5 | David | Engineering
      6 | Kim   | Finance
(14 rows)

PostgreSQL-NULL値

PostgreSQL NULL は、欠損値を表すために使用される用語です。テーブルのNULL値は、空白のように見えるフィールドの値です。

NULL値を持つフィールドは、値のないフィールドです。 NULL値は、ゼロ値やスペースを含むフィールドとは異なることを理解することが非常に重要です。

構文

テーブルの作成中に NULL を使用する基本的な構文は次のとおりです-

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

ここで、 NOT NULL は、列が指定されたデータ型の明示的な値を常に受け入れることを意味します。 NOT NULLを使用しなかった2つの列があります。したがって、これは、これらの列がNULLになる可能性があることを意味します。

NULL値を持つフィールドは、レコードの作成中に空白のままになっているフィールドです。

例

NULL値は、データを選択するときに問題を引き起こす可能性があります。未知の値を他の値と比較すると、結果は常に未知であり、最終結果に含まれないためです。次の表、link：/postgresql/company.sql [COMPANY]を検討してください。次のレコードがあります-

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

次のように、UPDATEステートメントを使用して、いくつかのNULL値をNULLとして設定します。

testdb=# UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7);

さて、COMPANY表には次のレコードが必要です-

 id | name  | age | address     | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 |             |
  7 | James |  24 |             |
(7 rows)

次に、SALARYがNULLでないすべてのレコードをリストする IS NOT NULL 演算子の使用法を見てみましょう-

testdb=#  SELECT  ID, NAME, AGE, ADDRESS, SALARY
   FROM COMPANY
   WHERE SALARY IS NOT NULL;

上記のPostgreSQLステートメントは、次の結果を生成します-

 id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(5 rows)

以下は、SALARYがNULLであるすべてのレコードをリストダウンする IS NULL 演算子の使用法です-

testdb=#  SELECT  ID, NAME, AGE, ADDRESS, SALARY
        FROM COMPANY
        WHERE SALARY IS NULL;

上記のPostgreSQLステートメントは、次の結果を生成します-

 id | name  | age | address | salary
----+-------+-----+---------+--------
  6 | Kim   |  22 |         |
  7 | James |  24 |         |
(2 rows)

PostgreSQL-ALIAS構文

*ALIAS* と呼ばれる別の名前を付けることで、一時的にテーブルまたは列の名前を変更できます。 テーブルエイリアスの使用とは、特定のPostgreSQLステートメントでテーブルの名前を変更することを意味します。 名前の変更は一時的な変更であり、データベース内の実際のテーブル名は変更されません。

列エイリアスは、特定のPostgreSQLクエリの目的のためにテーブルの列の名前を変更するために使用されます。

構文

テーブル*エイリアスの基本的な構文は次のとおりです-

SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];

列*エイリアスの基本的な構文は次のとおりです-

SELECT column_name AS alias_name
FROM table_name
WHERE [condition];

例

次の2つのテーブルを検討してください、（a）リンク：/postgresql/company.sql [COMPANY]テーブルは次のとおりです-

testdb=# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

（b）別のテーブルはlink：/postgresql/department.sql [DEPARTMENT]です。

 id | dept         | emp_id
----+--------------+--------
  1 | IT Billing   |      1
  2 | Engineering  |      2
  3 | Finance      |      7
  4 | Engineering  |      3
  5 | Finance      |      4
  6 | Engineering  |      5
  7 | Finance      |      6
(7 rows)

さて、次は TABLE ALIAS の使用法です。CおよびDをそれぞれCOMPANYおよびDEPARTMENTテーブルのエイリアスとして使用します-

testdb=# SELECT C.ID, C.NAME, C.AGE, D.DEPT
   FROM COMPANY AS C, DEPARTMENT AS D
   WHERE  C.ID = D.EMP_ID;

上記のPostgreSQLステートメントは、次の結果を生成します-

 id | name  | age |  dept
----+-------+-----+------------
  1 | Paul  |  32 | IT Billing
  2 | Allen |  25 | Engineering
  7 | James |  24 | Finance
  3 | Teddy |  23 | Engineering
  4 | Mark  |  25 | Finance
  5 | David |  27 | Engineering
  6 | Kim   |  22 | Finance
(7 rows)

COMPANY_IDがID列のエイリアスであり、COMPANY_NAMEが名前列のエイリアスである COLUMN ALIAS の使用例を見てみましょう-

testdb=# SELECT C.ID AS COMPANY_ID, C.NAME AS COMPANY_NAME, C.AGE, D.DEPT
   FROM COMPANY AS C, DEPARTMENT AS D
   WHERE  C.ID = D.EMP_ID;

上記のPostgreSQLステートメントは、次の結果を生成します-

 company_id | company_name | age | dept
------------+--------------+-----+------------
      1     | Paul         |  32 | IT Billing
      2     | Allen        |  25 | Engineering
      7     | James        |  24 | Finance
      3     | Teddy        |  23 | Engineering
      4     | Mark         |  25 | Finance
      5     | David        |  27 | Engineering
      6     | Kim          |  22 | Finance
(7 rows)

PostgreSQL-トリガー

PostgreSQL *トリガー*はデータベースコールバック関数で、指定されたデータベースイベントが発生すると自動的に実行/呼び出されます。

以下は、PostgreSQLのトリガーに関する重要なポイントです-

PostgreSQLトリガーを起動するように指定できます
行に対して操作が試行される前（制約がチェックされ、INSERT、UPDATE、またはDELETEが試行される前）
操作が完了した後（制約がチェックされ、INSERT、UPDATE、またはDELETEが完了した後）
操作の代わりに（ビューでの挿入、更新、または削除の場合）
FOR EACH ROWとマークされたトリガーは、操作が変更するすべての行に対して1回呼び出されます。対照的に、FOR EACH STATEMENTとマークされたトリガーは、変更する行数に関係なく、特定の操作に対して1回だけ実行されます。
WHEN句とトリガーアクションの両方は、 NEW.column-name および OLD.column-name という形式の参照を使用して、挿入、削除、または更新される行の要素にアクセスできます。column-nameは名前ですトリガーが関連付けられているテーブルの列の
WHEN句が指定されている場合、指定されたPostgreSQLステートメントは、WHEN句がtrueである行に対してのみ実行されます。 WHEN句が指定されていない場合、PostgreSQLステートメントはすべての行に対して実行されます。
同じイベントに対して同じ種類のトリガーが複数定義されている場合、それらは名前のアルファベット順に起動されます。
BEFORE、AFTER、またはINSTEAD OFキーワードは、関連する行の挿入、変更、または削除に関連してトリガーアクションがいつ実行されるかを決定します。
トリガーは、関連付けられているテーブルが削除されると自動的に削除されます。
変更するテーブルは、トリガーがアタッチされるテーブルまたはビューと同じデータベースに存在する必要があり、 database.tablename ではなく tablename のみを使用する必要があります。
CONSTRAINTオプションを指定すると、_constraint trigger_が作成されます。これは、SET CONSTRAINTSを使用してトリガーを起動するタイミングを調整できることを除いて、通常のトリガーと同じです。制約トリガーは、実装する制約に違反した場合に例外を発生させることが期待されています。

構文

トリガー*を作成する基本的な構文は次のとおりです-

CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
 -- Trigger logic goes here....
];

ここで、 event_name は、前述の表 table_name に対する_INSERT、DELETE、_ UPDATE、、および_TRUNCATE_データベース操作です。オプションで、テーブル名の後にFOR EACH ROWを指定できます。

以下は、次のようにテーブルの1つ以上の指定された列のUPDATE操作でトリガーを作成する構文です-

CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
[
 -- Trigger logic goes here....
];

例

COMPANYテーブルに挿入されているすべてのレコードの監査トライアルを保持したい場合を考えてみましょう。次のように新しく作成します（既にある場合はCOMPANYテーブルを削除します）。

testdb=# CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

監査トライアルを維持するために、新しいレコードのCOMPANYテーブルにエントリがあるたびにログメッセージが挿入されるAUDITという新しいテーブルを作成します-

testdb=# CREATE TABLE AUDIT(
   EMP_ID INT NOT NULL,
   ENTRY_DATE TEXT NOT NULL
);

ここで、IDはAUDITレコードID、EMP_IDはCOMPANYテーブルから取得されるIDです。DATEは、レコードがCOMPANYテーブルに作成されるときにタイムスタンプを保持します。それでは、次のようにCOMPANYテーブルにトリガーを作成しましょう-

testdb=# CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY
FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();

auditlogfunc（）はPostgreSQLの procedure であり、次の定義があります-

CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$
   BEGIN
      INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);
      RETURN NEW;
   END;
$example_table$ LANGUAGE plpgsql;

ここで、実際の作業を開始します。 COMPANYテーブルにレコードを挿入してみましょう。これにより、AUDITテーブルに監査ログレコードが作成されます。それでは、次のようにCOMPANYテーブルに1つのレコードを作成しましょう-

testdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

これにより、次のようなCOMPANYテーブルに1つのレコードが作成されます-

 id | name | age | address      | salary
----+------+-----+--------------+--------
  1 | Paul |  32 | California   |  20000

同時に、AUDITテーブルに1つのレコードが作成されます。このレコードは、COMPANYテーブルのINSERT操作で作成したトリガーの結果です。同様に、要件に基づいてUPDATEおよびDELETE操作でトリガーを作成できます。

 emp_id |          entry_date
--------+-------------------------------
      1 | 2013-05-05 15:49:59.968+05:30
(1 row)

トリガーのリスト

次のように pg_trigger テーブルから現在のデータベースのすべてのトリガーをリストできます-

testdb=# SELECT * FROM pg_trigger;

上記のPostgreSQLステートメントは、すべてのトリガーをリストします。

特定のテーブルのトリガーを一覧表示する場合は、次のようにテーブル名でAND句を使用します-

testdb=# SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='company';

上記のPostgreSQLステートメントは、次のように1つのエントリのみをリストします-

     tgname
-----------------
 example_trigger
(1 row)

トリガーのドロップ

以下は、既存のトリガーを削除するために使用できるDROPコマンドです-

testdb=# DROP TRIGGER trigger_name;

PostgreSQL-インデックス

インデックスは、データ検索を高速化するためにデータベース検索エンジンが使用できる特別なルックアップテーブルです。簡単に言えば、インデックスはテーブル内のデータへのポインタです。データベースのインデックスは、本の裏にあるインデックスに非常によく似ています。

たとえば、特定のトピックについて説明している本のすべてのページを参照する場合は、最初にすべてのトピックをアルファベット順にリストするインデックスを参照してから、1つ以上の特定のページ番号を参照する必要があります。

インデックスは、SELECTクエリとWHERE句を高速化するのに役立ちます。ただし、UPDATEおよびINSERTステートメントを使用すると、データ入力が遅くなります。インデックスは、データに影響を与えずに作成または削除できます。

インデックスの作成には、CREATE INDEXステートメントが含まれます。これにより、インデックスに名前を付け、テーブルとインデックスを作成する列を指定し、インデックスが昇順か降順かを指定できます。

インデックスは、一意の制約と同様に一意にすることもできます。インデックスは、インデックスが存在する列または列の組み合わせの重複エントリを防止します。

CREATE INDEXコマンド

*CREATE INDEX* の基本的な構文は次のとおりです-

CREATE INDEX index_name ON table_name;

インデックスの種類

PostgreSQLには、Bツリー、ハッシュ、GiST、SP-GiST、およびGINといういくつかのインデックスタイプがあります。各インデックスタイプは、さまざまなタイプのクエリに最適なさまざまなアルゴリズムを使用します。デフォルトでは、CREATE INDEXコマンドは、最も一般的な状況に適合するBツリーインデックスを作成します。

単一列インデックス

単一列インデックスは、1つのテーブル列のみに基づいて作成されるインデックスです。基本的な構文は次のとおりです-

CREATE INDEX index_name
ON table_name (column_name);

複数列インデックス

複数列インデックスは、テーブルの複数の列で定義されます。基本的な構文は次のとおりです-

CREATE INDEX index_name
ON table_name (column1_name, column2_name);

単一列インデックスを作成するか複数列インデックスを作成するかは、クエリのWHERE句でフィルター条件として頻繁に使用する可能性のある列を考慮してください。

列を1つだけ使用する場合は、単一列のインデックスを選択する必要があります。 WHERE句でフィルターとして頻繁に使用される2つ以上の列がある場合は、複数列のインデックスが最適です。

一意のインデックス

一意のインデックスは、パフォーマンスだけでなく、データの整合性にも使用されます。一意のインデックスでは、重複する値をテーブルに挿入できません。基本的な構文は次のとおりです-

CREATE UNIQUE INDEX index_name
on table_name (column_name);

部分インデックス

部分インデックスは、テーブルのサブセット上に構築されたインデックスです。サブセットは条件式（部分インデックスの述語と呼ばれる）によって定義されます。インデックスには、述語を満たすテーブル行のエントリのみが含まれます。基本的な構文は次のとおりです-

CREATE INDEX index_name
on table_name (conditional_expression);

暗黙的なインデックス

暗黙的なインデックスは、オブジェクトの作成時にデータベースサーバーによって自動的に作成されるインデックスです。主キー制約と一意制約のインデックスが自動的に作成されます。

例

以下は、リンクにインデックスを作成する例です。給与カラムの/postgresql/company.sql[COMPANY]テーブル-

# CREATE INDEX salary_index ON COMPANY (salary);

次に、 \ d company コマンドを使用して、COMPANYテーブルで使用可能なすべてのインデックスをリストします。

# \d company

これにより、次の結果が生成されます。_company_pkey_は、テーブルの作成時に作成された暗黙的なインデックスです。

       Table "public.company"
 Column  |     Type      | Modifiers
---------+---------------+-----------
 id      | integer       | not null
 name    | text          | not null
 age     | integer       | not null
 address | character(50) |
 salary  | real          |
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)
    "salary_index" btree (salary)

*\ di* コマンドを使用して、インデックスデータベース全体を一覧表示できます-

DROP INDEXコマンド

インデックスは、PostgreSQL DROP コマンドを使用して削除できます。パフォーマンスが低下または改善される可能性があるため、インデックスを削除するときは注意が必要です。

基本的な構文は次のとおりです-

DROP INDEX index_name;

次のステートメントを使用して、以前に作成したインデックスを削除できます-

# DROP INDEX salary_index;

インデックスを回避する必要がある場合

インデックスはデータベースのパフォーマンスを向上させることを目的としていますが、回避する必要がある場合があります。次のガイドラインは、インデックスの使用を再検討する必要がある場合を示しています-

小さいテーブルではインデックスを使用しないでください。
頻繁に大規模なバッチ更新または挿入操作が行われるテーブル。
多数のNULL値を含む列にはインデックスを使用しないでください。
頻繁に操作される列にはインデックスを付けないでください。

PostgreSQL-ALTER TABLEコマンド

PostgreSQLの ALTER TABLE コマンドを使用して、既存のテーブルの列を追加、削除、または変更します。

また、ALTER TABLEコマンドを使用して、既存のテーブルにさまざまな制約を追加および削除します。

構文

既存のテーブルに新しい列を追加する ALTER TABLE の基本的な構文は次のとおりです-

ALTER TABLE table_name ADD column_name datatype;

既存のテーブルの DROP COLUMN へのALTER TABLEの基本的な構文は次のとおりです-

ALTER TABLE table_name DROP COLUMN column_name;

テーブル内の列の DATA TYPE を変更するALTER TABLEの基本的な構文は次のとおりです-

ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;

テーブルの列に NOT NULL 制約を追加するALTER TABLEの基本的な構文は次のとおりです-

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

テーブルに ADD UNIQUE CONSTRAINT を追加するALTER TABLEの基本構文は次のとおりです-

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);

テーブルに ADD CHECK CONSTRAINT を追加するALTER TABLEの基本的な構文は次のとおりです-

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

テーブルへの ADD PRIMARY KEY 制約に対するALTER TABLEの基本的な構文は次のとおりです-

ALTER TABLE table_name
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);

テーブルから DROP CONSTRAINT へのALTER TABLEの基本的な構文は次のとおりです-

ALTER TABLE table_name
DROP CONSTRAINT MyUniqueConstraint;

MySQLを使用している場合、コードは次のとおりです-

ALTER TABLE table_name
DROP INDEX MyUniqueConstraint;

テーブルから DROP PRIMARY KEY 制約へのALTER TABLEの基本的な構文は次のとおりです-

ALTER TABLE table_name
DROP CONSTRAINT MyPrimaryKey;

MySQLを使用している場合、コードは次のとおりです-

ALTER TABLE table_name
DROP PRIMARY KEY;

例

私たちのリンクを検討してください：/postgresql/company.sql [COMPANY]テーブルには次のレコードがあります-

 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000

以下は、既存のテーブルに新しい列を追加する例です-

testdb=# ALTER TABLE COMPANY ADD GENDER char(1);

今、COMPANYテーブルが変更され、次はSELECTステートメントからの出力になります-

 id | name  | age | address     | salary | gender
----+-------+-----+-------------+--------+--------
  1 | Paul  |  32 | California  |  20000 |
  2 | Allen |  25 | Texas       |  15000 |
  3 | Teddy |  23 | Norway      |  20000 |
  4 | Mark  |  25 | Rich-Mond   |  65000 |
  5 | David |  27 | Texas       |  85000 |
  6 | Kim   |  22 | South-Hall  |  45000 |
  7 | James |  24 | Houston     |  10000 |
(7 rows)

以下は、既存のテーブルから性別の列を削除する例です-

testdb=# ALTER TABLE COMPANY DROP GENDER;

今、COMPANYテーブルが変更され、次はSELECTステートメントからの出力になります-

 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000

PostgreSQL-TRUNCATE TABLEコマンド

PostgreSQLの TRUNCATE TABLE コマンドは、既存のテーブルから完全なデータを削除するために使用されます。 DROP TABLEコマンドを使用して完全なテーブルを削除することもできますが、データベースから完全なテーブル構造を削除し、データを保存する場合はこのテーブルをもう一度作成する必要があります。

各テーブルでDELETEと同じ効果がありますが、実際にはテーブルをスキャンしないため、高速です。さらに、後続のVACUUM操作を必要とせずに、ディスク領域をすぐに再利用します。これは、大きなテーブルで最も役立ちます。

構文

*TRUNCATE TABLE* の基本的な構文は次のとおりです-

TRUNCATE TABLE  table_name;

例

COMPANYテーブルには以下のレコードがあると考えてください-

 id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
(7 rows)

以下は切り捨てる例です-

testdb=# TRUNCATE TABLE COMPANY;

今、COMPANY表は切り捨てられ、次はSELECT文の出力になります-

testdb=# SELECT * FROM CUSTOMERS;
 id | name | age | address | salary
----+------+-----+---------+--------
(0 rows)

PostgreSQL-ビュー

ビューは擬似テーブルです。つまり、実際のテーブルではありません。それにもかかわらず、SELECTには通常のテーブルとして表示されます。ビューは、通常のテーブルから特定の列または特定の行を選択して、実際のテーブルのサブセットを表すことができます。ビューは結合されたテーブルを表すこともできます。ビューには個別のアクセス許可が割り当てられているため、それらを使用してテーブルアクセスを制限し、ユーザーがテーブルの特定の行または列のみを表示できるようにすることができます。

ビューには、テーブルのすべての行、または1つ以上のテーブルから選択した行を含めることができます。ビューは、1つまたは複数のテーブルから作成できます。これは、作成されたPostgreSQLクエリに依存してビューを作成します。

仮想テーブルの一種であるビューを使用すると、ユーザーは次のことができます-

ユーザーまたはユーザーのクラスが自然または直感的に見えるようにデータを構造化します。
ユーザーが完全なテーブルではなく、限られたデータしか見ることができないように、データへのアクセスを制限します。
レポートの生成に使用できるさまざまなテーブルのデータを要約します。

ビューは通常のテーブルではないため、ビューでDELETE、INSERT、またはUPDATEステートメントを実行できない場合があります。ただし、ビューでDELETE、INSERT、またはUPDATEを使用するこの問題を修正するルールを作成できます。

ビューを作成する

PostgreSQLビューは、 CREATE VIEW ステートメントを使用して作成されます。 PostgreSQLビューは、単一のテーブル、複数のテーブル、または別のビューから作成できます。

基本的なCREATE VIEW構文は次のとおりです-

CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

通常のPostgreSQL SELECTクエリで使用するのと非常に似た方法で、SELECTステートメントに複数のテーブルを含めることができます。オプションのTEMPまたはTEMPORARYキーワードが存在する場合、ビューは一時スペースに作成されます。一時ビューは、現在のセッションの終了時に自動的に削除されます。

例

考慮してください、link：/postgresql/company.sql [COMPANY]テーブルには以下のレコードがあります-

 id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000

次に、COMPANYテーブルからビューを作成する例を示します。このビューは、COMPANYテーブルの列を数個だけ持つために使用されます-

testdb=# CREATE VIEW COMPANY_VIEW AS
SELECT ID, NAME, AGE
FROM  COMPANY;

これで、実際のテーブルをクエリするのと同様の方法でCOMPANY_VIEWをクエリできます。以下は例です-

testdb=# SELECT * FROM COMPANY_VIEW;

これは、次の結果を生成します-

 id | name  | age
----+-------+-----
  1 | Paul  |  32
  2 | Allen |  25
  3 | Teddy |  23
  4 | Mark  |  25
  5 | David |  27
  6 | Kim   |  22
  7 | James |  24
(7 rows)

ビューの削除

ビューを削除するには、 view_name を指定してDROP VIEWステートメントを使用します。基本的なDROP VIEW構文は次のとおりです-

testdb=# DROP VIEW view_name;

次のコマンドは、前のセクションで作成したCOMPANY_VIEWビューを削除します-

testdb=# DROP VIEW COMPANY_VIEW;

PostgreSQL-トランザクション

トランザクションは、データベースに対して実行される作業単位です。トランザクションとは、ユーザーが手動で実行する場合でも、何らかのデータベースプログラムで自動的に実行する場合でも、論理的な順序で実行される作業の単位またはシーケンスです。

トランザクションは、データベースに対する1つ以上の変更の伝播です。たとえば、レコードを作成、レコードを更新、またはテーブルからレコードを削除する場合、テーブルでトランザクションを実行します。データの整合性を確保し、データベースエラーを処理するには、トランザクションを制御することが重要です。

実際には、多くのPostgreSQLクエリをグループにまとめ、トランザクションの一部としてそれらすべてをまとめて実行します。

トランザクションのプロパティ

トランザクションには、通常、頭字語ACIDで参照される次の4つの標準プロパティがあります-

Atomicity -ワークユニット内のすべての操作が正常に完了することを保証します。そうでない場合、トランザクションは失敗した時点で中止され、以前の操作は以前の状態にロールバックされます。
一貫性-トランザクションが正常にコミットされると、データベースの状態が適切に変更されるようにします。
分離-トランザクションが相互に独立して透過的に動作できるようにします。
耐久性-システム障害の場合に、コミットされたトランザクションの結果または効果が持続することを保証します。

トランザクション制御

次のコマンドは、トランザクションを制御するために使用されます-

BEGIN TRANSACTION -トランザクションを開始します。
COMMIT -変更を保存するには、代わりに END TRANSACTION コマンドを使用できます。
ROLLBACK -変更をロールバックします。

トランザクション制御コマンドは、DMLコマンドのINSERT、UPDATE、およびDELETEでのみ使用されます。これらの操作はデータベースで自動的にコミットされるため、テーブルの作成中または削除中は使用できません。

BEGIN TRANSACTIONコマンド

トランザクションは、BEGIN TRANSACTIONまたは単にBEGINコマンドを使用して開始できます。このようなトランザクションは通常、次のCOMMITまたはROLLBACKコマンドが検出されるまで持続します。ただし、データベースが閉じている場合、またはエラーが発生した場合、トランザクションはROLLBACKします。

以下は、トランザクションを開始するための簡単な構文です-

BEGIN;

or

BEGIN TRANSACTION;

COMMITコマンド

COMMITコマンドは、トランザクションによって呼び出された変更をデータベースに保存するために使用されるトランザクションコマンドです。

COMMITコマンドは、最後のCOMMITまたはROLLBACKコマンド以降のすべてのトランザクションをデータベースに保存します。

COMMITコマンドの構文は次のとおりです-

COMMIT;

or

END TRANSACTION;

ROLLBACKコマンド

ROLLBACKコマンドは、データベースにまだ保存されていないトランザクションを取り消すために使用されるトランザクションコマンドです。

ROLLBACKコマンドは、最後のCOMMITまたはROLLBACKコマンドが発行されてからトランザクションを取り消すためにのみ使用できます。

ROLLBACKコマンドの構文は次のとおりです-

ROLLBACK;

例

link：/postgresql/company.sql [COMPANY]テーブルが以下のレコードを持っていることを考慮してください-

 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000

次に、トランザクションを開始し、年齢= 25のテーブルからレコードを削除し、最後にROLLBACKコマンドを使用してすべての変更を元に戻します。

testdb=# BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
ROLLBACK;

COMPANYテーブルを確認する場合は、次のレコードがまだ残っています-

 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000

次に、別のトランザクションを開始して、年齢= 25のテーブルからレコードを削除し、最後にCOMMITコマンドを使用してすべての変更をコミットします。

testdb=# BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
COMMIT;

COMPANYテーブルを確認する場合、次のレコードがまだあります-

 id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  3 | Teddy |  23 | Norway     |  20000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
(5 rows)

PostgreSQL-ロック

_Locks_または_Exclusive Locks_または_Write Locks_は、ユーザーが行またはテーブル全体を変更できないようにします。 UPDATEおよびDELETEによって変更された行は、トランザクションの間、自動的に排他的にロックされます。これにより、トランザクションがコミットまたはロールバックされるまで、他のユーザーが行を変更できなくなります。

ユーザーが他のユーザーを待つ必要があるのは、同じ行を変更しようとしているときだけです。異なる行を変更する場合、待つ必要はありません。 SELECTクエリは待つ必要はありません。

データベースは自動的にロックを実行します。ただし、場合によっては、ロックを手動で制御する必要があります。手動ロックは、LOCKコマンドを使用して実行できます。トランザクションのロックタイプとスコープを指定できます。

LOCKコマンドの構文

LOCKコマンドの基本的な構文は次のとおりです-

LOCK [ TABLE ]
name
 IN
lock_mode

name -ロックする既存のテーブルの名前（オプションでスキーマ修飾）。テーブル名の前にONLYを指定すると、そのテーブルのみがロックされます。 ONLYが指定されていない場合、テーブルとそのすべての子テーブル（存在する場合）がロックされます。
lock_mode -ロックモードは、このロックが競合するロックを指定します。ロックモードが指定されていない場合、最も制限的なモードであるACCESS EXCLUSIVEが使用されます。可能な値は次のとおりです。ACCESSSHARE、ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE。

'_取得されると、現在のトランザクションの残りの間ロックが保持されます。 UNLOCK TABLEコマンドはありません。ロックは常にトランザクション終了時に解放されます。_

デッドロック

デッドロックは、2つのトランザクションが互いの操作を完了するのを待っているときに発生する可能性があります。 PostgreSQLはそれらを検出してROLLBACKで終了できますが、デッドロックは依然として不便です。アプリケーションでこの問題が発生しないようにするには、同じ順序でオブジェクトをロックするように設計してください。

アドバイザリーロック

PostgreSQLは、アプリケーション定義の意味を持つロックを作成する手段を提供します。これらは_advisory locks_と呼ばれます。システムはそれらの使用を強制しないので、それらを正しく使用するのはアプリケーション次第です。アドバイザリロックは、MVCCモデルに不適合なロック戦略に役立ちます。

たとえば、アドバイザリロックの一般的な使用法は、いわゆる「フラットファイル」データ管理システムに典型的な悲観的なロック戦略をエミュレートすることです。テーブルに保存されたフラグは同じ目的に使用できますが、アドバイザリロックはより高速で、テーブルの膨張を防ぎ、セッションの終了時にサーバーによって自動的にクリーンアップされます。

例

次のようなレコードを持つテーブルlink：/postgresql/company.sql [COMPANY]を考慮してください-

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

次の例では、ACCESS EXCLUSIVEモードでtestdbデータベース内のCOMPANYテーブルをロックします。 LOCKステートメントは、トランザクションモードでのみ機能します-

testdb=#BEGIN;
LOCK TABLE company1 IN ACCESS EXCLUSIVE MODE;

上記のPostgreSQLステートメントは、次の結果を生成します-

LOCK TABLE

上記のメッセージは、トランザクションが終了するまでテーブルがロックされていることを示しており、トランザクションを終了するには、トランザクションをロールバックまたはコミットする必要があります。

PostgreSQL-サブクエリ

サブクエリまたは内部クエリまたはネストされたクエリは、別のPostgreSQLクエリ内のクエリであり、WHERE句内に埋め込まれます。

サブクエリは、取得するデータをさらに制限する条件としてメインクエリで使用されるデータを返すために使用されます。

サブクエリは、=、<、>、> =、⇐、INなどの演算子とともにSELECT、INSERT、UPDATEおよびDELETEステートメントで使用できます。

サブクエリが従わなければならないいくつかのルールがあります-

サブクエリは括弧で囲む必要があります。
サブクエリは、選択された列を比較するサブクエリのメインクエリに複数の列がある場合を除き、SELECT句に1つの列しか含めることができません。
メインクエリはORDER BYを使用できますが、ORDER BYはサブクエリでは使用できません。 GROUP BYを使用して、サブクエリのORDER BYと同じ機能を実行できます。
複数の行を返すサブクエリは、IN、EXISTS、NOT IN、ANY/SOME、ALL演算子などの複数の値演算子でのみ使用できます。 *BETWEEN演算子は、サブクエリでは使用できません。ただし、BETWEENはサブクエリ内で使用できます。

SELECTステートメントを使用したサブクエリ

サブクエリは、SELECTステートメントで最も頻繁に使用されます。基本的な構文は次のとおりです-

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
      (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE])

例

次のレコードを持つlink：/postgresql/company.sql [COMPANY]テーブルを考慮してください-

 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

さて、SELECTステートメントで次のサブクエリを確認しましょう-

testdb=# SELECT*
   FROM COMPANY
   WHERE ID IN (SELECT ID
      FROM COMPANY
      WHERE SALARY > 45000) ;

これは、次の結果を生成します-

 id | name  | age |  address    | salary
----+-------+-----+-------------+--------
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
(2 rows)

INSERTステートメントを使用したサブクエリ

サブクエリはINSERTステートメントでも使用できます。 INSERTステートメントは、サブクエリから返されたデータを使用して別のテーブルに挿入します。サブクエリで選択したデータは、任意の文字、日付、または数字関数で変更できます。

基本的な構文は次のとおりです-

INSERT INTO table_name [ (column1 [, column2 ]) ]
   SELECT [ *|column1 [, column2 ] ]
   FROM table1 [, table2 ]
   [ WHERE VALUE OPERATOR ]

例

COMPANYテーブルと同様の構造を持つテーブルCOMPANY_BKPを考えてみましょう。COMPANY_BKPをテーブル名として使用して、同じCREATE TABLEを使用して作成できます。さて、COMPANYテーブル全体をCOMPANY_BKPにコピーするには、次の構文を使用します-

testdb=# INSERT INTO COMPANY_BKP
   SELECT *FROM COMPANY
   WHERE ID IN (SELECT ID
      FROM COMPANY) ;

UPDATEステートメントを使用したサブクエリ

サブクエリは、UPDATEステートメントと組み合わせて使用できます。 UPDATEステートメントでサブクエリを使用すると、テーブル内の単一または複数の列を更新できます。

基本的な構文は次のとおりです-

UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

例

COMPANYテーブルのバックアップであるCOMPANY_BKPテーブルがあると仮定します。

次の例では、AGEが27以上のすべての顧客について、COMPANYテーブルのSALARYを0.50回更新します-

testdb=# UPDATE COMPANY
   SET SALARY = SALARY* 0.50
   WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
      WHERE AGE >= 27 );

これは2つの行に影響し、最後にCOMPANYテーブルには次のレコードがあります-

 id | name  | age | address     | salary
----+-------+-----+-------------+--------
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
  1 | Paul  |  32 | California  |  10000
  5 | David |  27 | Texas       |  42500
(7 rows)

DELETEステートメントを使用したサブクエリ

サブクエリは、上記の他のステートメントと同様に、DELETEステートメントと組み合わせて使用できます。

基本的な構文は次のとおりです-

DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

例

COMPANYテーブルのバックアップであるCOMPANY_BKPテーブルがあるとします。

次の例では、AGEが27以上のすべての顧客のCOMPANYテーブルからレコードを削除します-

testdb=# DELETE FROM COMPANY
   WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
      WHERE AGE > 27 );

これは2つの行に影響し、最後にCOMPANYテーブルには次のレコードがあります-

 id | name  | age | address     | salary
----+-------+-----+-------------+--------
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
  5 | David |  27 | Texas       |  42500
(6 rows)

PostgreSQL-自動インクリメント

PostgreSQLには、smallserial、serial、_bigserial_のデータ型があります。これらは真の型ではなく、一意の識別子列を作成するための表記上の利便性にすぎません。これらは、他のデータベースでサポートされているAUTO_INCREMENTプロパティに似ています。

_serial_列に一意の制約を設定するか、主キーにする場合は、他のデータ型と同様に指定する必要があります。

型名_serial_は、_integer_列を作成します。タイプ名_bigserial_は、_bigint_列を作成します。テーブルの有効期間中に2 ^ 31 ^を超える識別子の使用が予想される場合は、_bigserial_を使用する必要があります。タイプ名_smallserial_は、_smallint_列を作成します。

構文

*SERIAL* dataypeの基本的な使用法は次のとおりです-

CREATE TABLE tablename (
   colname SERIAL
);

例

COMPANYテーブルが次のように作成されることを考慮してください-

testdb=# CREATE TABLE COMPANY(
   ID  SERIAL PRIMARY KEY,
   NAME           TEXT      NOT NULL,
   AGE            INT       NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

ここで、次のレコードをテーブルCOMPANYに挿入します-

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Paul', 32, 'California', 20000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Allen', 25, 'Texas', 15000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Teddy', 23, 'Norway', 20000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Mark', 25, 'Rich-Mond ', 65000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'David', 27, 'Texas', 85000.00 );


INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Kim', 22, 'South-Hall', 45000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'James', 24, 'Houston', 10000.00 );

これは7つのタプルをテーブルCOMPANYに挿入し、COMPANYは次のレコードを持ちます-

 id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000

PostgreSQL-特権

データベースでオブジェクトが作成されるたびに、所有者が割り当てられます。所有者は通常、作成ステートメントを実行した人です。ほとんどの種類のオブジェクトの初期状態では、所有者（またはスーパーユーザー）のみがオブジェクトを変更または削除できます。他のロールまたはユーザーがそれを使用できるようにするには、_privileges_またはアクセス許可を付与する必要があります。

PostgreSQLのさまざまな種類の権限は-

SELECT、
インサート、
更新、
削除、
切り捨て、
参考文献、
引き金、
CREATE、
CONNECT、
一時的、
EXECUTE、および
使用法

オブジェクトのタイプ（テーブル、関数など）に応じて、オブジェクトに特権が適用されます。ユーザーに特権を割り当てるには、GRANTコマンドが使用されます。

GRANTの構文

GRANTコマンドの基本的な構文は次のとおりです-

GRANT privilege [, ...]
ON object [, ...]
TO { PUBLIC | GROUP group | username }

特権-値は、SELECT、INSERT、UPDATE、DELETE、RULE、ALLです。
オブジェクト-アクセスを許可するオブジェクトの名前。可能なオブジェクトは次のとおりです。テーブル、ビュー、シーケンス
PUBLIC -すべてのユーザーを表す短い形式。 GROUP group *-特権を付与するグループ。
username -特権を付与するユーザーの名前。 PUBLICは、すべてのユーザーを表す短い形式です。

特権は、REVOKEコマンドを使用して取り消すことができます。

REVOKEの構文

REVOKEコマンドの基本的な構文は次のとおりです-

REVOKE privilege [, ...]
ON object [, ...]
FROM { PUBLIC | GROUP groupname | username }

特権-値は、SELECT、INSERT、UPDATE、DELETE、RULE、ALLです。
オブジェクト-アクセスを許可するオブジェクトの名前。可能なオブジェクトは次のとおりです。テーブル、ビュー、シーケンス
PUBLIC -すべてのユーザーを表す短い形式。 GROUP group *-特権を付与するグループ。
username -特権を付与するユーザーの名前。 PUBLICは、すべてのユーザーを表す短い形式です。

例

特権を理解するには、まず次のようにユーザーを作成しましょう-

testdb=# CREATE USER manisha WITH PASSWORD 'password';
CREATE ROLE

CREATE ROLEというメッセージは、ユーザー「manisha」が作成されたことを示しています。

次のようなレコードを持つテーブルlink：/postgresql/company.sql [COMPANY]を考慮してください-

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

次に、次のようにユーザー「manisha」にテーブルCOMPANYのすべての特権を付与しましょう-

testdb=# GRANT ALL ON COMPANY TO manisha;
GRANT

メッセージGRANTは、すべての特権がユーザーに割り当てられていることを示します。

次に、次のようにユーザー「manisha」から特権を取り消しましょう-

testdb=# REVOKE ALL ON COMPANY FROM manisha;
REVOKE

メッセージREVOKEは、すべての特権がユーザーから取り消されたことを示します。

次のようにユーザーを削除することもできます-

testdb=# DROP USER manisha;
DROP ROLE

メッセージDROP ROLEは、ユーザー「Manisha」がデータベースから削除されたことを示します。

PostgreSQL-日付/時刻関数と演算子

章のリンク：/postgresql/postgresql_data_types [データ型]で、日付/時刻データ型について説明しました。では、日付/時刻演算子と関数を見てみましょう。

次の表は、基本的な算術演算子の動作を示しています-

Operator	Example	Result
+	date '2001-09-28' + integer '7'	date '2001-10-05'
+	date '2001-09-28' + interval '1 hour'	timestamp '2001-09-28 01:00:00'
+	date '2001-09-28' + time '03:00'	timestamp '2001-09-28 03:00:00'
+	interval '1 day' + interval '1 hour'	interval '1 day 01:00:00'
+	timestamp '2001-09-28 01:00' + interval '23 hours'	timestamp '2001-09-29 00:00:00'
+	time '01:00' + interval '3 hours'	time '04:00:00'
-	- interval '23 hours'	interval '-23:00:00'
-	date '2001-10-01' - date '2001-09-28'	integer '3' (days)
-	date '2001-10-01' - integer '7'	date '2001-09-24'
-	date '2001-09-28' - interval '1 hour'	timestamp '2001-09-27 23:00:00'
-	time '05:00' - time '03:00'	interval '02:00:00'
-	time '05:00' - interval '2 hours'	time '03:00:00'
-	timestamp '2001-09-28 23:00' - interval '23 hours'	timestamp '2001-09-28 00:00:00'
-	interval '1 day' - interval '1 hour'	interval '1 day -01:00:00'
-	timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'	interval '1 day 15:00:00'
*	900* interval '1 second'	interval '00:15:00'
*	21* interval '1 day'	interval '21 days'
*	double precision '3.5'* interval '1 hour'	interval '03:30:00'
/	interval '1 hour'/double precision '1.5'	interval '00:40:00'

以下は、利用可能なすべての重要な日付と時刻の機能のリストです。

S. No.	Function & Description
1	AGE() 引数を引く
2	CURRENT DATE/TIME() 現在の日時
3	DATE_PART() サブフィールドの取得（抽出と同等）
4	EXTRACT() サブフィールドを取得
5	ISFINITE() 有限の日付、時間、間隔（+/-無限ではない）をテストする
6	JUSTIFY 間隔を調整する

[function_age]#

AGE（タイムスタンプ、タイムスタンプ）、AGE（タイムスタンプ）

S. No.

Function & Description

1

AGE(timestamp, timestamp)

TIMESTAMP形式の2番目の引数で呼び出された場合、AGE（）は引数を減算し、年と月を使用し、INTERVAL型の「シンボリック」結果を生成します。

2

AGE(timestamp)

TIMESTAMPのみを引数として呼び出した場合、AGE（）はcurrent_dateから（真夜中に）減算します。

関数AGE（timestamp、timestamp）の例は-

testdb=# SELECT AGE(timestamp '2001-04-10', timestamp '1957-06-13');

上記のPostgreSQLステートメントは、次の結果を生成します-

           age
-------------------------
 43 years 9 mons 27 days

関数AGE（timestamp）の例は-

testdb=# select age(timestamp '1957-06-13');

上記のPostgreSQLステートメントは、次の結果を生成します-

           age
--------------------------
 55 years 10 mons 22 days

[function_currentdatetime]#

現在の日付/時刻（）

PostgreSQLは、現在の日付と時刻に関連する値を返す多くの関数を提供します。以下はいくつかの機能です-

S. No.	Function & Description
1	CURRENT_DATE 現在の日付を提供します。
2	CURRENT_TIME タイムゾーンで値を提供します。
3	CURRENT_TIMESTAMP タイムゾーンで値を提供します。
4	CURRENT_TIME(precision) オプションで精度パラメーターを使用します。これにより、結果は秒フィールドのその小数桁に丸められます。
5	CURRENT_TIMESTAMP(precision) オプションで精度パラメーターを使用します。これにより、結果は秒フィールドのその小数桁に丸められます。
6	LOCALTIME タイムゾーンなしで値を提供します。
7	LOCALTIMESTAMP タイムゾーンなしで値を提供します。
8	LOCALTIME(precision) オプションで精度パラメーターを使用します。これにより、結果は秒フィールドのその小数桁に丸められます。
9	LOCALTIMESTAMP(precision) オプションで精度パラメーターを使用します。これにより、結果は秒フィールドのその小数桁に丸められます。

上記の表の関数を使用した例-

testdb=# SELECT CURRENT_TIME;
       timetz
--------------------
 08:01:34.656+05:30
(1 row)


testdb=# SELECT CURRENT_DATE;
    date
------------
 2013-05-05
(1 row)


testdb=# SELECT CURRENT_TIMESTAMP;
              now
-------------------------------
 2013-05-05 08:01:45.375+05:30
(1 row)


testdb=# SELECT CURRENT_TIMESTAMP(2);
         timestamptz
------------------------------
 2013-05-05 08:01:50.89+05:30
(1 row)


testdb=# SELECT LOCALTIMESTAMP;
       timestamp
------------------------
 2013-05-05 08:01:55.75
(1 row)

PostgreSQLはまた、現在のステートメントの開始時刻と、その関数が呼び出された瞬間の実際の現在時刻を返す関数を提供します。これらの機能は-

S. No.	Function & Description
1	transaction_timestamp() CURRENT_TIMESTAMPと同等ですが、返される内容を明確に反映するように名前が付けられています。
2	statement_timestamp() 現在のステートメントの開始時間を返します。
3	clock_timestamp() 実際の現在時刻を返すため、その値は単一のSQLコマンド内でも変化します。
4	timeofday() 実際の現在時刻を返しますが、タイムゾーン値を含むタイムスタンプではなく、フォーマットされたテキスト文字列として返されます。
5	now() これは、transaction_timestamp（）と同等の従来のPostgreSQLです。

[function_datepart]#

DATE_PART（テキスト、タイムスタンプ）、DATE_PART（テキスト、間隔）、DATE_TRUNC（テキスト、タイムスタンプ）

S. No.

Function & Description

1

DATE_PART('field', source)

これらの関数はサブフィールドを取得します。 _field_パラメータは、名前ではなく文字列値である必要があります。

有効なフィールド名は次のとおりです：century、day、decade、dow、doy、epoch、hour、isodow、isoyear、microseconds、millennium、ms、minute、month、quarter、second、timezone、timezone_hour、timezone_minute、week、year.

2

DATE_TRUNC('field', source)

この関数は、数値の_trunc_関数に概念的に似ています。 _source_は、タイプtimestampまたはintervalの値式です。 _field_は、入力値を切り捨てる精度を選択します。戻り値のタイプは_timestamp_または_interval_です。

_field_の有効な値は次のとおりです。_マイクロ秒、ミリ秒、秒、分、時間、日、週、月、四半期、年、10年、世紀、千年

以下は、DATE_PART（_ 'field'_、source）関数の例です-

testdb=# SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
 date_part
-----------
        16
(1 row)


testdb=# SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
 date_part
-----------
         4
(1 row)

以下は、DATE_TRUNC（_ 'field'_、source）関数の例です-

testdb=# SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
     date_trunc
---------------------
 2001-02-16 20:00:00
(1 row)


testdb=# SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
     date_trunc
---------------------
 2001-01-01 00:00:00
(1 row)

[function_extract]#

EXTRACT（タイムスタンプからのフィールド）、EXTRACT（間隔からのフィールド）

EXTRACT（field FROM source）*関数は、日付/時刻値から年や時間などのサブフィールドを取得します。 _source_は、タイプ_timestamp、time、またはinterval_の値式でなければなりません。 _field_は、ソース値から抽出するフィールドを選択する識別子または文字列です。 EXTRACT関数は、_double precision_型の値を返します。

有効なフィールド名は次のとおりです（DATE_PART関数のフィールド名と同様）：世紀、日、10年、ダウ、ドイ、エポック、時間、アイソドウ、アイソイヤー、マイクロ秒、ミレニアム、ミリ秒、分、月、四半期、秒、タイムゾーン、タイムゾーン、時間ゾーン、timezone_minute、week、year。

以下は、EXTRACT（_ 'field'_、source）関数の例です-

testdb=# SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');
 date_part
-----------
        20
(1 row)


testdb=# SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
 date_part
-----------
        16
(1 row)

[function_isfinite]#

ISFINITE（日付）、ISFINITE（タイムスタンプ）、ISFINITE（間隔）

S. No.

Function & Description

1

ISFINITE(date)

有限の日付のテスト。

2

ISFINITE(timestamp)

有限タイムスタンプのテスト。

3

ISFINITE(interval)

有限間隔のテスト。

以下は、ISFINITE（）関数の例です-

testdb=# SELECT isfinite(date '2001-02-16');
 isfinite
----------
 t
(1 row)


testdb=# SELECT isfinite(timestamp '2001-02-16 21:28:30');
 isfinite
----------
 t
(1 row)


testdb=# SELECT isfinite(interval '4 hours');
 isfinite
----------
 t
(1 row)

[function_justify]#

JUSTIFY_DAYS（間隔）、JUSTIFY_HOURS（間隔）、JUSTIFY_INTERVAL（間隔）

S. No.

Function & Description

1

JUSTIFY_DAYS(interval)

30日間の期間が月として表されるように間隔を調整します。 interval タイプを返します

2

JUSTIFY_HOURS(interval)

24時間の期間が日として表されるように間隔を調整します。 interval タイプを返します

3

JUSTIFY_INTERVAL(interval)

JUSTIFY_DAYSおよびJUSTIFY_HOURSを使用して間隔を調整し、さらに符号を調整します。 interval タイプを返します

以下は、ISFINITE（）関数の例です-

testdb=# SELECT justify_days(interval '35 days');
 justify_days
--------------
 1 mon 5 days
(1 row)


testdb=# SELECT justify_hours(interval '27 hours');
 justify_hours
----------------
 1 day 03:00:00
(1 row)


testdb=# SELECT justify_interval(interval '1 mon -1 hour');
 justify_interval
------------------
 29 days 23:00:00
(1 row)

PostgreSQL-関数

PostgreSQL 関数（ストアドプロシージャとも呼ばれる）を使用すると、通常はデータベース内の1つの関数で複数のクエリとラウンドトリップを実行する操作を実行できます。他のアプリケーションは中間層や複製コードの代わりにストアドプロシージャと直接やり取りできるため、関数を使用するとデータベースを再利用できます。

関数は、SQL、PL/pgSQL、C、Pythonなどの選択した言語で作成できます。

構文

関数を作成するための基本的な構文は次のとおりです-

CREATE [OR REPLACE] FUNCTION function_name (arguments)
RETURNS return_datatype AS $variable_name$
   DECLARE
      declaration;
      [...]
   BEGIN
      < function_body >
      [...]
      RETURN { variable_name | value }
   END; LANGUAGE plpgsql;

どこで、

function-name は、関数の名前を指定します。
[OR REPLACE]オプションを使用すると、既存の関数を変更できます。
関数には return ステートメントが含まれている必要があります。
RETURN 句は、関数から返すデータ型を指定します。 return_datatype は、ベース、複合、またはドメインタイプにすることも、テーブル列のタイプを参照することもできます。
function-body には実行可能部分が含まれます。
ASキーワードは、スタンドアロン関数を作成するために使用されます。
plpgsql は、関数が実装されている言語の名前です。ここでは、PostgreSQLに対してこのオプションを使用します。SQL、C、内部、またはユーザー定義の手続き言語の名前を指定できます。後方互換性のために、名前を単一引用符で囲むことができます。

例

次の例は、スタンドアロン関数の作成と呼び出しを示しています。この関数は、COMPANYテーブル内のレコードの総数を返します。私たちはlink：/postgresql/company.sql [COMPANY]テーブルを使用します。このテーブルには次のレコードがあります-

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

関数totalRecords（）は次のとおりです-

CREATE OR REPLACE FUNCTION totalRecords ()
RETURNS integer AS $total$
declare
    total integer;
BEGIN
   SELECT count(*) into total FROM COMPANY;
   RETURN total;
END;
$total$ LANGUAGE plpgsql;

上記のクエリが実行されると、結果は次のようになります-

testdb# CREATE FUNCTION

ここで、この関数の呼び出しを実行して、COMPANYテーブルのレコードを確認しましょう

testdb=# select totalRecords();

上記のクエリが実行されると、結果は次のようになります-

 totalrecords
--------------
      7
(1 row)

PostgreSQL-便利な関数

PostgreSQL組み込み関数は、集計関数とも呼ばれ、文字列または数値データの処理を実行するために使用されます。

以下は、すべての汎用PostgreSQLの組み込み関数のリストです-

link：/postgresql/postgresql_count_function [PostgreSQL COUNT関数]-PostgreSQL COUNT集約関数を使用して、データベーステーブルの行数をカウントします。
link：/postgresql/postgresql_max_function [PostgreSQL MAX関数]-PostgreSQL MAX集約関数を使用すると、特定の列の最大（最大）値を選択できます。
link：/postgresql/postgresql_min_function [PostgreSQL MIN関数]-PostgreSQL MIN集約関数を使用すると、特定の列の最小（最小）値を選択できます。
link：/postgresql/postgresql_avg_function [PostgreSQL AVG関数]-PostgreSQL AVG集約関数は、特定のテーブル列の平均値を選択します。
link：/postgresql/postgresql_sum_function [PostgreSQL SUM関数]-PostgreSQL SUM集約関数では、数値列の合計を選択できます。
link：/postgresql/postgresql_array_functions [PostgreSQL ARRAY関数]-PostgreSQL ARRAY集約関数は、nullを含む入力値を配列に連結します。
link：/postgresql/postgresql_numeric_functions [PostgreSQL数値関数]-SQLで数値を操作するために必要なPostgreSQL関数の完全なリスト。
link：/postgresql/postgresql_string_functions [PostgreSQL文字列関数]-PostgreSQLで文字列を操作するために必要なPostgreSQL関数の完全なリスト。

PostgreSQL-C/C ++インターフェイス

このチュートリアルでは、PostgreSQL用の公式C ++クライアントAPIである libpqxx ライブラリを使用します。 libpqxxのソースコードはBSDライセンスの下で利用できます。したがって、自由にダウンロードして他の人に渡したり、変更したり、販売したり、自分のコードに含めたり、選択した人と変更を共有したりできます。

インストール

libpqxxの最新バージョンは、リンクhttp://pqxx.org/download/software/libpqxx/[Libpqxxのダウンロード]からダウンロードできます。だから、最新バージョンをダウンロードして、次の手順に従ってください-

wget http://pqxx.org/download/software/libpqxx/libpqxx-4.0.tar.gz
tar xvfz libpqxx-4.0.tar.gz
cd libpqxx-4.0
./configure
make
make install

C/C ++ PostgreSQLインターフェイスの使用を開始する前に、PostgreSQLインストールディレクトリで pg_hba.conf ファイルを見つけ、次の行を追加します-

# IPv4 local connections:
host    all         all         127.0.0.1/32          md5

次のコマンドを使用して、実行されていない場合にpostgresサーバーを起動/再起動できます-

[root@host]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

C/C ++インターフェイスAPI

以下は、C/C ++プログラムからPostgreSQLデータベースを操作するための要件を満たすことができる重要なインターフェイスルーチンです。より高度なアプリケーションを探している場合は、libpqxxの公式ドキュメントを調べるか、市販のAPIを使用できます。

S. No.	API & Description
1	pqxx::connection C( const std::string & dbstring ) これは、データベースへの接続に使用されるtypedefです。ここで、dbstringはdatbaseに接続するために必要なパラメーターを提供します（例： dbname = testdb user = postgres password = pass123 hostaddr = 127.0.0.1 port = 5432 ）。接続が正常にセットアップされると、さまざまな便利な関数public functionを提供する接続オブジェクトを使用してCが作成されます。
2	C.is_open() is_open（）メソッドは接続オブジェクトのパブリックメソッドであり、ブール値を返します。接続がアクティブな場合、このメソッドはtrueを返し、そうでない場合はfalseを返します。
3	C.disconnect() このメソッドは、開いているデータベース接続を切断するために使用されます。
4	pqxx::work W( C ) これはtypedefであり、接続Cを使用してトランザクションオブジェクトを作成するために使用され、最終的にはトランザクションモードでSQLステートメントを実行するために使用されます。トランザクションオブジェクトが正常に作成されると、トランザクションオブジェクトに関連するパブリックメソッドへのアクセスに使用される変数Wに割り当てられます。
5	W.exec(const std::string & sql) トランザクションオブジェクトのこのパブリックメソッドは、SQLステートメントの実行に使用されます。
6	W.commit() トランザクションオブジェクトのこのパブリックメソッドは、トランザクションをコミットするために使用されます。
7	W.abort() トランザクションオブジェクトのこのパブリックメソッドは、トランザクションをロールバックするために使用されます。
8	pqxx::nontransaction N( C ) これは、接続Cを使用して非トランザクションオブジェクトを作成するために使用されるtypedefであり、最終的には非トランザクションモードでSQLステートメントを実行するために使用されます。トランザクションオブジェクトが正常に作成されると、変数Nに割り当てられ、非トランザクションオブジェクトに関連するパブリックメソッドへのアクセスに使用されます。
9	N.exec(const std::string & sql) 非トランザクションオブジェクトからのこのパブリックメソッドは、SQLステートメントを実行するために使用され、返されたすべてのレコードを保持するインタレーターである結果オブジェクトを返します。

データベースへの接続

次のCコードセグメントは、ポート5432でローカルマシンで実行されている既存のデータベースに接続する方法を示しています。ここでは、行の継続にバックスラッシュ\を使用しました。

#include <iostream>
#include <pqxx/pqxx>

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   try {
      connection C("dbname = testdb user = postgres password = cohondob \
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can't open database" << endl;
         return 1;
      }
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }
}

ここで、上記のプログラムをコンパイルして実行し、データベース testdb に接続します。データベースは既にスキーマで利用可能であり、ユーザー_postgres_とパスワード_pass123_を使用してアクセスできます。

データベース設定に基づいて、ユーザーIDとパスワードを使用できます。 -lpqxxと-lpqを指定された順序で保持することを忘れないでください！そうしないと、リンカは、「PQ」で始まる名前の欠落している関数について苦情を訴えます。

$g++ test.cpp -lpqxx -lpq
$./a.out
Opened database successfully: testdb

テーブルを作成する

次のCコードセグメントは、以前に作成されたデータベースにテーブルを作成するために使用されます-

#include <iostream>
#include <pqxx/pqxx>

using namespace std;
using namespace pqxx;

int main(int argc, char *argv[]) {
   char* sql;

   try {
      connection C("dbname = testdb user = postgres password = cohondob \
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can't open database" << endl;
         return 1;
      }

     /*Create SQL statement*/
      sql = "CREATE TABLE COMPANY("  \
      "ID INT PRIMARY KEY     NOT NULL," \
      "NAME           TEXT    NOT NULL," \
      "AGE            INT     NOT NULL," \
      "ADDRESS        CHAR(50)," \
      "SALARY         REAL );";

     /*Create a transactional object.*/
      work W(C);

     /*Execute SQL query*/
      W.exec( sql );
      W.commit();
      cout << "Table created successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

上記のプログラムをコンパイルして実行すると、testdbデータベースにCOMPANYテーブルが作成され、次のステートメントが表示されます-

Opened database successfully: testdb
Table created successfully

INSERT操作

次のCコードセグメントは、上記の例で作成したCOMPANYテーブルにレコードを作成する方法を示しています-

#include <iostream>
#include <pqxx/pqxx>

using namespace std;
using namespace pqxx;

int main(int argc, char *argv[]) {
   char* sql;

   try {
      connection C("dbname = testdb user = postgres password = cohondob \
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can't open database" << endl;
         return 1;
      }

     /*Create SQL statement*/
      sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
         "VALUES (1, 'Paul', 32, 'California', 20000.00 ); " \
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
         "VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); "     \
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
         "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );" \
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
         "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";

     /*Create a transactional object.*/
      work W(C);

     /*Execute SQL query*/
      W.exec( sql );
      W.commit();
      cout << "Records created successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

上記のプログラムがコンパイルおよび実行されると、COMPANYテーブルに特定のレコードが作成され、次の2行が表示されます-

Opened database successfully: testdb
Records created successfully

SELECT操作

次のCコードセグメントは、上記の例で作成したCOMPANYテーブルからレコードを取得して表示する方法を示しています-

#include <iostream>
#include <pqxx/pqxx>

using namespace std;
using namespace pqxx;

int main(int argc, char *argv[]) {
   char* sql;

   try {
      connection C("dbname = testdb user = postgres password = cohondob \
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can't open database" << endl;
         return 1;
      }

     /*Create SQL statement*/
      sql = "SELECT * from COMPANY";

     /*Create a non-transactional object.*/
      nontransaction N(C);

     /*Execute SQL query*/
      result R( N.exec( sql ));

     /*List down all the records*/
      for (result::const_iterator c = R.begin(); c != R.end(); ++c) {
         cout << "ID = " << c[0].as<int>() << endl;
         cout << "Name = " << c[1].as<string>() << endl;
         cout << "Age = " << c[2].as<int>() << endl;
         cout << "Address = " << c[3].as<string>() << endl;
         cout << "Salary = " << c[4].as<float>() << endl;
      }
      cout << "Operation done successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

上記のプログラムがコンパイルされて実行されると、次の結果が生成されます-

Opened database successfully: testdb
ID = 1
Name = Paul
Age = 32
Address = California
Salary = 20000
ID = 2
Name = Allen
Age = 25
Address = Texas
Salary = 15000
ID = 3
Name = Teddy
Age = 23
Address = Norway
Salary = 20000
ID = 4
Name = Mark
Age = 25
Address = Rich-Mond
Salary = 65000
Operation done successfully

更新操作

次のCコードセグメントは、UPDATEステートメントを使用してレコードを更新し、更新されたレコードをCOMPANYテーブルから取得して表示する方法を示しています-

#include <iostream>
#include <pqxx/pqxx>

using namespace std;
using namespace pqxx;

int main(int argc, char *argv[]) {
   char* sql;

   try {
      connection C("dbname = testdb user = postgres password = cohondob \
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can't open database" << endl;
         return 1;
      }

     /*Create a transactional object.*/
      work W(C);
     /*Create  SQL UPDATE statement*/
      sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1";
     /*Execute SQL query*/
      W.exec( sql );
      W.commit();
      cout << "Records updated successfully" << endl;

     /*Create SQL SELECT statement*/
      sql = "SELECT * from COMPANY";

     /*Create a non-transactional object.*/
      nontransaction N(C);

     /*Execute SQL query*/
      result R( N.exec( sql ));

     /*List down all the records*/
      for (result::const_iterator c = R.begin(); c != R.end(); ++c) {
         cout << "ID = " << c[0].as<int>() << endl;
         cout << "Name = " << c[1].as<string>() << endl;
         cout << "Age = " << c[2].as<int>() << endl;
         cout << "Address = " << c[3].as<string>() << endl;
         cout << "Salary = " << c[4].as<float>() << endl;
      }
      cout << "Operation done successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

上記のプログラムがコンパイルされて実行されると、次の結果が生成されます-

Opened database successfully: testdb
Records updated successfully
ID = 2
Name = Allen
Age = 25
Address = Texas
Salary = 15000
ID = 3
Name = Teddy
Age = 23
Address = Norway
Salary = 20000
ID = 4
Name = Mark
Age = 25
Address = Rich-Mond
Salary = 65000
ID = 1
Name = Paul
Age = 32
Address = California
Salary = 25000
Operation done successfully

削除操作

次のCコードセグメントは、DELETEステートメントを使用してレコードを削除し、COMPANYテーブルから残りのレコードを取得して表示する方法を示しています-

#include <iostream>
#include <pqxx/pqxx>

using namespace std;
using namespace pqxx;

int main(int argc, char *argv[]) {
   char* sql;

   try {
      connection C("dbname = testdb user = postgres password = cohondob \
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can't open database" << endl;
         return 1;
      }

     /*Create a transactional object.*/
      work W(C);
     /*Create  SQL DELETE statement*/
      sql = "DELETE from COMPANY where ID = 2";
     /*Execute SQL query*/
      W.exec( sql );
      W.commit();
      cout << "Records deleted successfully" << endl;

     /*Create SQL SELECT statement*/
      sql = "SELECT * from COMPANY";

     /*Create a non-transactional object.*/
      nontransaction N(C);

     /*Execute SQL query*/
      result R( N.exec( sql ));

     /*List down all the records*/
      for (result::const_iterator c = R.begin(); c != R.end(); ++c) {
         cout << "ID = " << c[0].as<int>() << endl;
         cout << "Name = " << c[1].as<string>() << endl;
         cout << "Age = " << c[2].as<int>() << endl;
         cout << "Address = " << c[3].as<string>() << endl;
         cout << "Salary = " << c[4].as<float>() << endl;
      }
      cout << "Operation done successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

上記のプログラムがコンパイルされて実行されると、次の結果が生成されます-

Opened database successfully: testdb
Records deleted successfully
ID = 3
Name = Teddy
Age = 23
Address = Norway
Salary = 20000
ID = 4
Name = Mark
Age = 25
Address = Rich-Mond
Salary = 65000
ID = 1
Name = Paul
Age = 32
Address = California
Salary = 25000
Operation done successfully

PostgreSQL-JAVAインターフェイス

インストール

JavaプログラムでPostgreSQLを使用する前に、PostgreSQL JDBCとJavaがマシンにセットアップされていることを確認する必要があります。マシンにJavaをインストールするためのJavaチュートリアルを確認できます。 PostgreSQL JDBCドライバーのセットアップ方法を確認しましょう。

postgresql-jdbcリポジトリから_postgresql-（VERSION）.jdbc.jar_の最新バージョンをダウンロードします。
ダウンロードしたjarファイル_postgresql-（VERSION）.jdbc.jar_をクラスパスに追加するか、以下の例で説明するように-classpathオプションと一緒に使用できます。

次のセクションでは、Java JDBCの概念に関する知識がほとんどないことを前提としています。お持ちでない場合は、リンク：/jdbc/jdbc-create-database [JDBCチュートリアル]を30分ほどかけて、以下で説明する概念に慣れることをお勧めします。

データベースへの接続

次のJavaコードは、既存のデータベースに接続する方法を示しています。データベースが存在しない場合は、データベースが作成され、最終的にデータベースオブジェクトが返されます。

import java.sql.Connection;
import java.sql.DriverManager;

public class PostgreSQLJDBC {
   public static void main(String args[]) {
      Connection c = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "postgres", "123");
      } catch (Exception e) {
         e.printStackTrace();
         System.err.println(e.getClass().getName()+": "+e.getMessage());
         System.exit(0);
      }
      System.out.println("Opened database successfully");
   }
}

上記のプログラムをコンパイルして実行する前に、PostgreSQLインストールディレクトリで pg_hba.conf ファイルを見つけ、次の行を追加します-

# IPv4 local connections:
host    all         all         127.0.0.1/32          md5

次のコマンドを使用して、実行されていない場合にpostgresサーバーを起動/再起動できます-

[root@host]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

次に、上記のプログラムをコンパイルして実行し、testdbに接続します。ここでは、データベースにアクセスするために、ユーザーIDとして postgres を、パスワードとして 123 を使用しています。これは、データベースの構成とセットアップに従って変更できます。また、現在のバージョンのJDBCドライバー postgresql-9.2-1002.jdbc3.jar が現在のパスで使用可能であると想定しています。

C:\JavaPostgresIntegration>javac PostgreSQLJDBC.java
C:\JavaPostgresIntegration>java -cp c:\tools\postgresql-9.2-1002.jdbc3.jar;C:\JavaPostgresIntegration PostgreSQLJDBC
Open database successfully

テーブルを作成する

次のJavaプログラムを使用して、以前に開いたデータベースにテーブルを作成します。このテーブルがターゲットデータベースにまだないことを確認してください。

import java.sql.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;


public class PostgreSQLJDBC {
   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "manisha", "123");
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "CREATE TABLE COMPANY " +
            "(ID INT PRIMARY KEY     NOT NULL," +
            " NAME           TEXT    NOT NULL, " +
            " AGE            INT     NOT NULL, " +
            " ADDRESS        CHAR(50), " +
            " SALARY         REAL)";
         stmt.executeUpdate(sql);
         stmt.close();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Table created successfully");
   }
}

プログラムをコンパイルして実行すると、 testdb データベースにCOMPANYテーブルが作成され、次の2行が表示されます-

Opened database successfully
Table created successfully

INSERT操作

次のJavaプログラムは、上記の例で作成したCOMPANYテーブルにレコードを作成する方法を示しています-

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class PostgreSQLJDBC {
   public static void main(String args[]) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "manisha", "123");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
            + "VALUES (1, 'Paul', 32, 'California', 20000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
            + "VALUES (2, 'Allen', 25, 'Texas', 15000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
            + "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
            + "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
         stmt.executeUpdate(sql);

         stmt.close();
         c.commit();
         c.close();
      } catch (Exception e) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Records created successfully");
   }
}

上記のプログラムがコンパイルおよび実行されると、COMPANYテーブルに特定のレコードが作成され、次の2行が表示されます-

Opened database successfully
Records created successfully

SELECT操作

次のJavaプログラムは、上記の例で作成したCOMPANYテーブルからレコードを取得して表示する方法を示しています-

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class PostgreSQLJDBC {
   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "manisha", "123");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         ResultSet rs = stmt.executeQuery( "SELECT *FROM COMPANY;" );
         while ( rs.next() ) {
            int id = rs.getInt("id");
            String  name = rs.getString("name");
            int age  = rs.getInt("age");
            String  address = rs.getString("address");
            float salary = rs.getFloat("salary");
            System.out.println( "ID = " + id );
            System.out.println( "NAME = " + name );
            System.out.println( "AGE = " + age );
            System.out.println( "ADDRESS = " + address );
            System.out.println( "SALARY = " + salary );
            System.out.println();
         }
         rs.close();
         stmt.close();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Operation done successfully");
   }
}

プログラムがコンパイルおよび実行されると、次の結果が生成されます-

Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 20000.0

ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0

ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

更新操作

次のJavaコードは、UPDATEステートメントを使用してレコードを更新し、COMPANYテーブルから更新されたレコードをフェッチして表示する方法を示しています-

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class PostgreSQLJDBC {
   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "manisha", "123");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;";
         stmt.executeUpdate(sql);
         c.commit();

         ResultSet rs = stmt.executeQuery( "SELECT* FROM COMPANY;" );
         while ( rs.next() ) {
            int id = rs.getInt("id");
            String  name = rs.getString("name");
            int age  = rs.getInt("age");
            String  address = rs.getString("address");
            float salary = rs.getFloat("salary");
            System.out.println( "ID = " + id );
            System.out.println( "NAME = " + name );
            System.out.println( "AGE = " + age );
            System.out.println( "ADDRESS = " + address );
            System.out.println( "SALARY = " + salary );
            System.out.println();
         }
         rs.close();
         stmt.close();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Operation done successfully");
   }
}

プログラムがコンパイルおよび実行されると、次の結果が生成されます-

Opened database successfully
ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0

ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 25000.0

Operation done successfully

削除操作

次のJavaコードは、DELETEステートメントを使用してレコードを削除し、COMPANYテーブルから残りのレコードを取得して表示する方法を示しています-

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class PostgreSQLJDBC6 {
   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "manisha", "123");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "DELETE from COMPANY where ID = 2;";
         stmt.executeUpdate(sql);
         c.commit();

         ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
         while ( rs.next() ) {
            int id = rs.getInt("id");
            String  name = rs.getString("name");
            int age  = rs.getInt("age");
            String  address = rs.getString("address");
            float salary = rs.getFloat("salary");
            System.out.println( "ID = " + id );
            System.out.println( "NAME = " + name );
            System.out.println( "AGE = " + age );
            System.out.println( "ADDRESS = " + address );
            System.out.println( "SALARY = " + salary );
            System.out.println();
         }
         rs.close();
         stmt.close();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Operation done successfully");
   }
}

プログラムがコンパイルおよび実行されると、次の結果が生成されます-

Opened database successfully
ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 25000.0
Operation done successfully

PostgreSQL-PHPインターフェイス

インストール

PostgreSQL拡張は、PHP 5.3.xの最新リリースではデフォルトで有効になっています。コンパイル時に*-without-pgsql *を使用して無効にすることができます。それでも、yumコマンドを使用してPHP -PostgreSQLインターフェースをインストールできます-

yum install php-pgsql

PHP PostgreSQLインターフェースの使用を開始する前に、PostgreSQLインストールディレクトリで pg_hba.conf ファイルを見つけ、次の行を追加します-

# IPv4 local connections:
host    all         all         127.0.0.1/32          md5

次のコマンドを使用して、実行されていない場合に、postgresサーバーを起動/再起動できます-

[root@host]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

Windowsユーザーは、この拡張機能を使用するためにphp_pgsql.dllを有効にする必要があります。このDLLは、PHP 5.3.xの最新リリースのWindowsディストリビューションに含まれています

インストール手順の詳細については、PHPチュートリアルとその公式Webサイトをご覧ください。

PHPインターフェイスAPI

以下は重要なPHPルーチンであり、PHPプログラムからPostgreSQLデータベースを操作するための要件を満たすことができます。より洗練されたアプリケーションを探しているなら、PHP公式ドキュメントを調べることができます。

S. No.	API & Description
1	resource pg_connect ( string $connection_string [, int $connect_type ] ) これにより、connection_stringで指定されたPostgreSQLデータベースへの接続が開きます。 PGSQL_CONNECT_FORCE_NEWがconnect_typeとして渡される場合、connection_stringが既存の接続と同一であっても、pg_connect（）の2回目の呼び出しの場合に新しい接続が作成されます。
2	bool pg_connection_reset ( resource $connection ) このルーチンは接続をリセットします。エラー回復に役立ちます。成功した場合にTRUE、失敗した場合にFALSEを返します。
3	int pg_connection_status ( resource $connection ) このルーチンは、指定された接続のステータスを返します。 PGSQL_CONNECTION_OKまたはPGSQL_CONNECTION_BADを返します。
4	string pg_dbname ([ resource $connection ] ) このルーチンは、指定されたPostgreSQL接続リソースのデータベースの名前を返します。
5	resource pg_prepare ([ resource $connection ], string $stmtname, string $query ) これは、指定されたパラメーターで準備されたステートメントを作成する要求を送信し、完了を待ちます。
6	resource pg_execute ([ resource $connection ], string $stmtname, array $params ) このルーチンは、指定されたパラメーターを使用して準備済みステートメントを実行する要求を送信し、結果を待ちます。
7	resource pg_query ([ resource $connection ], string $query ) このルーチンは、指定されたデータベース接続でクエリを実行します。
8	array pg_fetch_row ( resource $result [, int $row ] ) このルーチンは、指定された結果リソースに関連付けられた結果から1行のデータをフェッチします。
9	array pg_fetch_all ( resource $result ) このルーチンは、結果リソースのすべての行（レコード）を含む配列を返します。
10	int pg_affected_rows ( resource $result ) このルーチンは、INSERT、UPDATE、およびDELETEクエリの影響を受ける行の数を返します。
11	int pg_num_rows ( resource $result ) このルーチンは、SELECTステートメントによって返される行数など、PostgreSQL結果リソースの行数を返します。
12	bool pg_close ([ resource $connection ] ) このルーチンは、指定された接続リソースに関連付けられたPostgreSQLデータベースへの非永続的な接続を閉じます。
13	string pg_last_error ([ resource $connection ] ) このルーチンは、指定された接続の最後のエラーメッセージを返します。
14	string pg_escape_literal ([ resource $connection ], string $data ) このルーチンは、テキストフィールドに挿入するためにリテラルをエスケープします。
15	string pg_escape_string ([ resource $connection ], string $data ) このルーチンは、データベースを照会するための文字列をエスケープします。

データベースへの接続

次のPHPコードは、ローカルマシン上の既存のデータベースに接続する方法を示しており、最終的にデータベース接続オブジェクトが返されます。

<?php
   $host        = "host = 127.0.0.1";
   $port        = "port = 5432";
   $dbname      = "dbname = testdb";
   $credentials = "user = postgres password=pass123";

   $db = pg_connect( "$host $port $dbname $credentials"  );
   if(!$db) {
      echo "Error : Unable to open database\n";
   } else {
      echo "Opened database successfully\n";
   }
?>

さて、上記のプログラムを実行してデータベース testdb を開きます。データベースが正常に開かれた場合、次のメッセージが表示されます-

Opened database successfully

テーブルを作成する

次のPHPプログラムは、以前に作成されたデータベースにテーブルを作成するために使用されます-

<?php
   $host        = "host = 127.0.0.1";
   $port        = "port = 5432";
   $dbname      = "dbname = testdb";
   $credentials = "user = postgres password=pass123";

   $db = pg_connect( "$host $port $dbname $credentials"  );
   if(!$db) {
      echo "Error : Unable to open database\n";
   } else {
      echo "Opened database successfully\n";
   }

   $sql =<<<EOF
      CREATE TABLE COMPANY
      (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL);
EOF;

   $ret = pg_query($db, $sql);
   if(!$ret) {
      echo pg_last_error($db);
   } else {
      echo "Table created successfully\n";
   }
   pg_close($db);
?>

上記のプログラムが実行されると、 testdb にCOMPANYテーブルが作成され、次のメッセージが表示されます-

Opened database successfully
Table created successfully

INSERT操作

次のPHPプログラムは、上記の例で作成したCOMPANYテーブルにレコードを作成する方法を示しています-

<?php
   $host        = "host=127.0.0.1";
   $port        = "port=5432";
   $dbname      = "dbname = testdb";
   $credentials = "user = postgres password=pass123";

   $db = pg_connect( "$host $port $dbname $credentials"  );
   if(!$db) {
      echo "Error : Unable to open database\n";
   } else {
      echo "Opened database successfully\n";
   }

   $sql =<<<EOF
      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (1, 'Paul', 32, 'California', 20000.00 );

      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 );

      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );

      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
EOF;

   $ret = pg_query($db, $sql);
   if(!$ret) {
      echo pg_last_error($db);
   } else {
      echo "Records created successfully\n";
   }
   pg_close($db);
?>

上記の特定のプログラムが実行されると、COMPANYテーブルに特定のレコードが作成され、次の2行が表示されます-

Opened database successfully
Records created successfully

SELECT操作

次のPHPプログラムは、上記の例で作成したCOMPANYテーブルからレコードを取得して表示する方法を示しています-

<?php
   $host        = "host = 127.0.0.1";
   $port        = "port = 5432";
   $dbname      = "dbname = testdb";
   $credentials = "user = postgres password=pass123";

   $db = pg_connect( "$host $port $dbname $credentials"  );
   if(!$db) {
      echo "Error : Unable to open database\n";
   } else {
      echo "Opened database successfully\n";
   }

   $sql =<<<EOF
      SELECT *from COMPANY;
EOF;

   $ret = pg_query($db, $sql);
   if(!$ret) {
      echo pg_last_error($db);
      exit;
   }
   while($row = pg_fetch_row($ret)) {
      echo "ID = ". $row[0] . "\n";
      echo "NAME = ". $row[1] ."\n";
      echo "ADDRESS = ". $row[2] ."\n";
      echo "SALARY =  ".$row[4] ."\n\n";
   }
   echo "Operation done successfully\n";
   pg_close($db);
?>

上記のプログラムが実行されると、次の結果が生成されます。テーブルの作成中に使用された順序でフィールドが返されることに注意してください。

Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  20000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

更新操作

次のPHPコードは、UPDATEステートメントを使用してレコードを更新し、更新されたレコードをCOMPANYテーブルから取得して表示する方法を示しています-

<?php
   $host        = "host=127.0.0.1";
   $port        = "port=5432";
   $dbname      = "dbname = testdb";
   $credentials = "user = postgres password=pass123";

   $db = pg_connect( "$host $port $dbname $credentials"  );
   if(!$db) {
      echo "Error : Unable to open database\n";
   } else {
      echo "Opened database successfully\n";
   }
   $sql =<<<EOF
      UPDATE COMPANY set SALARY = 25000.00 where ID=1;
EOF;
   $ret = pg_query($db, $sql);
   if(!$ret) {
      echo pg_last_error($db);
      exit;
   } else {
      echo "Record updated successfully\n";
   }

   $sql =<<<EOF
      SELECT* from COMPANY;
EOF;

   $ret = pg_query($db, $sql);
   if(!$ret) {
      echo pg_last_error($db);
      exit;
   }
   while($row = pg_fetch_row($ret)) {
      echo "ID = ". $row[0] . "\n";
      echo "NAME = ". $row[1] ."\n";
      echo "ADDRESS = ". $row[2] ."\n";
      echo "SALARY =  ".$row[4] ."\n\n";
   }
   echo "Operation done successfully\n";
   pg_close($db);
?>

上記のプログラムが実行されると、次の結果が生成されます-

Opened database successfully
Record updated successfully
ID = 2
NAME = Allen
ADDRESS = 25
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = 23
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = 25
SALARY =  65000

ID = 1
NAME = Paul
ADDRESS = 32
SALARY =  25000

Operation done successfully

削除操作

次のPHPコードは、DELETEステートメントを使用してレコードを削除し、COMPANYテーブルから残りのレコードを取得して表示する方法を示しています-

<?php
   $host        = "host = 127.0.0.1";
   $port        = "port = 5432";
   $dbname      = "dbname = testdb";
   $credentials = "user = postgres password=pass123";

   $db = pg_connect( "$host $port $dbname $credentials"  );
   if(!$db) {
      echo "Error : Unable to open database\n";
   } else {
      echo "Opened database successfully\n";
   }
   $sql =<<<EOF
      DELETE from COMPANY where ID=2;
EOF;
   $ret = pg_query($db, $sql);
   if(!$ret) {
      echo pg_last_error($db);
      exit;
   } else {
      echo "Record deleted successfully\n";
   }

   $sql =<<<EOF
      SELECT * from COMPANY;
EOF;

   $ret = pg_query($db, $sql);
   if(!$ret) {
      echo pg_last_error($db);
      exit;
   }
   while($row = pg_fetch_row($ret)) {
      echo "ID = ". $row[0] . "\n";
      echo "NAME = ". $row[1] ."\n";
      echo "ADDRESS = ". $row[2] ."\n";
      echo "SALARY =  ".$row[4] ."\n\n";
   }
   echo "Operation done successfully\n";
   pg_close($db);
?>

上記のプログラムが実行されると、次の結果が生成されます-

Opened database successfully
Record deleted successfully
ID = 3
NAME = Teddy
ADDRESS = 23
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = 25
SALARY =  65000

ID = 1
NAME = Paul
ADDRESS = 32
SALARY =  25000

Operation done successfully

PostgreSQL-Perlインターフェイス

インストール

PostgreSQLは、Perlプログラミング言語用のデータベースアクセスモジュールであるPerl DBIモジュールを使用してPerlと統合できます。標準のデータベースインターフェイスを提供する一連のメソッド、変数、および規則を定義します。

以下に、Linux/UnixマシンにDBIモジュールをインストールする簡単な手順を示します-

$ wget http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.625.tar.gz
$ tar xvfz DBI-1.625.tar.gz
$ cd DBI-1.625
$ perl Makefile.PL
$ make
$ make install

あなたがDBI用のSQLiteドライバをインストールする必要がある場合、それは次のようにインストールすることができます-

$ wget http://search.cpan.org/CPAN/authors/id/T/TU/TURNSTEP/DBD-Pg-2.19.3.tar.gz
$ tar xvfz DBD-Pg-2.19.3.tar.gz
$ cd DBD-Pg-2.19.3
$ perl Makefile.PL
$ make
$ make install

Perl PostgreSQLインターフェースの使用を開始する前に、PostgreSQLインストールディレクトリで pg_hba.conf ファイルを見つけ、次の行を追加します-

# IPv4 local connections:
host    all         all         127.0.0.1/32          md5

次のコマンドを使用して、実行されていない場合に、postgresサーバーを起動/再起動できます-

[root@host]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

DBIインターフェイスAPI

以下は重要なDBIルーチンで、PerlプログラムからSQLiteデータベースを操作するための要件を満たすことができます。より洗練されたアプリケーションをお探しの場合は、Perl DBIの公式ドキュメントをご覧ください。

S. No.	API & Description
1	DBI→connect($data_source, "userid", "password", \%attr) 要求された$ data_sourceへのデータベース接続またはセッションを確立します。接続が成功した場合、データベースハンドルオブジェクトを返します。データソースの形式は次のとおりです。 DBI：Pg：dbname = $ database; host = 127.0.0.1; port = 5432 PgはPostgreSQLドライバー名で、testdbはデータベースの名前です。
2	$dbh→do($sql) このルーチンは、単一のSQLステートメントを準備して実行します。影響を受けた行の数またはエラー時にundefを返します。 -1の戻り値は、行数が不明、適用できない、または利用できないことを意味します。ここで、$ dbhはDBI→connect（）呼び出しによって返されるハンドルです。
3	$dbh→prepare($sql) このルーチンは、データベースエンジンによる後の実行のためにステートメントを準備し、ステートメントハンドルオブジェクトへの参照を返します。
4	$sth→execute() このルーチンは、準備されたステートメントの実行に必要な処理を実行します。エラーが発生すると、undefが返されます。実行が成功すると、影響を受ける行の数に関係なく常にtrueが返されます。ここで、$ sthは、$ dbh→prepare（$ sql）呼び出しによって返されるステートメントハンドルです。
5	$sth→fetchrow_array() このルーチンは、データの次の行をフェッチし、フィールド値を含むリストとしてそれを返します。 nullフィールドは、リスト内のundef値として返されます。
6	$DBI::err これは、$ h→errと同等です。$ hは、$ dbh、$ sth、$ drhなどのハンドルタイプのいずれかです。これは、最後に呼び出されたドライバーメソッドからネイティブデータベースエンジンのエラーコードを返します。
7	$DBI::errstr これは、$ h→errstrと同等です。$ hは、$ dbh、$ sth、$ drhなどのハンドルタイプのいずれかです。これは、最後に呼び出されたDBIメソッドからのネイティブデータベースエンジンエラーメッセージを返します。
8	$dbh→disconnect() このルーチンは、DBI→connect（）の呼び出しによって以前に開かれたデータベース接続を閉じます。

データベースへの接続

次のPerlコードは、既存のデータベースに接続する方法を示しています。データベースが存在しない場合は、データベースが作成され、最終的にデータベースオブジェクトが返されます。

#!/usr/bin/perl

use DBI;
use strict;

my $driver  = "Pg";
my $database = "testdb";
my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432";
my $userid = "postgres";
my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
   or die $DBI::errstr;

print "Opened database successfully\n";

次に、上記のプログラムを実行してデータベース testdb を開きます。データベースが正常に開かれている場合は、次のメッセージが表示されます-

Open database successfully

テーブルを作成する

次のPerlプログラムを使用して、以前に作成したデータベースにテーブルを作成します-

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "Pg";
my $database = "testdb";
my $dsn = "DBI:$driver:dbname=$database;host=127.0.0.1;port=5432";
my $userid = "postgres";
my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
   or die $DBI::errstr;
print "Opened database successfully\n";

my $stmt = qq(CREATE TABLE COMPANY
      (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL););
my $rv = $dbh->do($stmt);
if($rv < 0) {
   print $DBI::errstr;
} else {
   print "Table created successfully\n";
}
$dbh->disconnect();

上記のプログラムが実行されると、 testdb にCOMPANYテーブルが作成され、次のメッセージが表示されます-

Opened database successfully
Table created successfully

INSERT操作

次のPerlプログラムは、上記の例で作成したCOMPANYテーブルにレコードを作成する方法を示しています-

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "Pg";
my $database = "testdb";
my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432";
my $userid = "postgres";
my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
   or die $DBI::errstr;
print "Opened database successfully\n";

my $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   VALUES (1, 'Paul', 32, 'California', 20000.00 ));
my $rv = $dbh->do($stmt) or die $DBI::errstr;

$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   VALUES (2, 'Allen', 25, 'Texas', 15000.00 ));
$rv = $dbh->do($stmt) or die $DBI::errstr;

$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   VALUES (3, 'Teddy', 23, 'Norway', 20000.00 ));
$rv = $dbh->do($stmt) or die $DBI::errstr;

$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 ););
$rv = $dbh->do($stmt) or die $DBI::errstr;

print "Records created successfully\n";
$dbh->disconnect();

上記のプログラムが実行されると、COMPANYテーブルに特定のレコードが作成され、次の2行が表示されます-

Opened database successfully
Records created successfully

SELECT操作

次のPerlプログラムは、上記の例で作成したCOMPANYテーブルからレコードを取得して表示する方法を示しています-

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "Pg";
my $database = "testdb";
my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432";
my $userid = "postgres";
my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
   or die $DBI::errstr;
print "Opened database successfully\n";

my $stmt = qq(SELECT id, name, address, salary  from COMPANY;);
my $sth = $dbh->prepare( $stmt );
my $rv = $sth->execute() or die $DBI::errstr;
if($rv < 0) {
   print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) {
      print "ID = ". $row[0] . "\n";
      print "NAME = ". $row[1] ."\n";
      print "ADDRESS = ". $row[2] ."\n";
      print "SALARY =  ". $row[3] ."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();

上記のプログラムが実行されると、次の結果が生成されます-

Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  20000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

更新操作

次のPerlコードは、UPDATEステートメントを使用してレコードを更新し、COMPANYテーブルから更新されたレコードをフェッチして表示する方法を示しています-

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "Pg";
my $database = "testdb";
my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432";
my $userid = "postgres";
my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
   or die $DBI::errstr;
print "Opened database successfully\n";

my $stmt = qq(UPDATE COMPANY set SALARY = 25000.00 where ID=1;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;
if( $rv < 0 ) {
   print $DBI::errstr;
}else{
   print "Total number of rows updated : $rv\n";
}
$stmt = qq(SELECT id, name, address, salary  from COMPANY;);
my $sth = $dbh->prepare( $stmt );
$rv = $sth->execute() or die $DBI::errstr;
if($rv < 0) {
   print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) {
      print "ID = ". $row[0] . "\n";
      print "NAME = ". $row[1] ."\n";
      print "ADDRESS = ". $row[2] ."\n";
      print "SALARY =  ". $row[3] ."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();

上記のプログラムが実行されると、次の結果が生成されます-

Opened database successfully
Total number of rows updated : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  25000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

削除操作

次のPerlコードは、DELETEステートメントを使用してレコードを削除し、COMPANYテーブルから残りのレコードを取得して表示する方法を示しています-

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "Pg";
my $database = "testdb";
my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432";
my $userid = "postgres";
my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
   or die $DBI::errstr;
print "Opened database successfully\n";

my $stmt = qq(DELETE from COMPANY where ID=2;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;
if( $rv < 0 ) {
   print $DBI::errstr;
} else{
   print "Total number of rows deleted : $rv\n";
}
$stmt = qq(SELECT id, name, address, salary  from COMPANY;);
my $sth = $dbh->prepare( $stmt );
$rv = $sth->execute() or die $DBI::errstr;
if($rv < 0) {
   print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) {
      print "ID = ". $row[0] . "\n";
      print "NAME = ". $row[1] ."\n";
      print "ADDRESS = ". $row[2] ."\n";
      print "SALARY =  ". $row[3] ."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();

上記のプログラムが実行されると、次の結果が生成されます-

Opened database successfully
Total number of rows deleted : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  25000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

PostgreSQL-Pythonインターフェイス

インストール

PostgreSQLは、psycopg2モジュールを使用してPythonと統合できます。 sycopg2は、Pythonプログラミング言語用のPostgreSQLデータベースアダプターです。 psycopg2は、非常に小さく高速で、岩のように安定することを目的に書かれています。このモジュールはデフォルトでPythonバージョン2.5.x以降に同梱されているため、このモジュールを個別にインストールする必要はありません。

マシンにインストールしていない場合は、yumコマンドを使用して次のようにインストールできます-

$yum install python-psycopg2

psycopg2モジュールを使用するには、まずデータベースを表すConnectionオブジェクトを作成する必要があります。その後、オプションで、すべてのSQLステートメントの実行に役立つカーソルオブジェクトを作成できます。

Python psycopg2モジュールAPI

以下は重要なpsycopg2モジュールルーチンであり、PythonプログラムからPostgreSQLデータベースを操作するための要件を満たすことができます。より洗練されたアプリケーションを探しているなら、Python psycopg2モジュールの公式ドキュメントを調べることができます。

S. No.	API & Description
1	psycopg2.connect(database="testdb", user="postgres", password="cohondob", host="127.0.0.1", port="5432") このAPIは、PostgreSQLデータベースへの接続を開きます。データベースが正常に開くと、接続オブジェクトが返されます。
2	connection.cursor() このルーチンは、Pythonを使用したデータベースプログラミング全体で使用されるカーソルを作成します。
3	cursor.execute(sql [, optional parameters]) このルーチンは、SQLステートメントを実行します。 SQLステートメントはパラメーター化できます（つまり、SQLリテラルではなくプレースホルダー）。 psycopg2モジュールは、％s記号を使用したプレースホルダーをサポートしますたとえば、cursor.execute（ "insert into people values（％s、％s）"、（who、age））
4	cursor.executemany(sql, seq_of_parameters) このルーチンは、シーケンスsqlで見つかったすべてのパラメーターシーケンスまたはマッピングに対してSQLコマンドを実行します。
5	cursor.callproc(procname[, parameters]) このルーチンは、指定された名前でストアドデータベースプロシージャを実行します。パラメータのシーケンスには、プロシージャが予期する引数ごとに1つのエントリが含まれている必要があります。
6	cursor.rowcount 最後の最後のexecute *（）によって変更、挿入、または削除されたデータベース行の総数を返すこの読み取り専用属性。
7	connection.commit() このメソッドは、現在のトランザクションをコミットします。このメソッドを呼び出さないと、最後のcommit（）の呼び出し以降に行った操作は、他のデータベース接続からは見えません。
8	connection.rollback() このメソッドは、commit（）の最後の呼び出し以降のデータベースへの変更をロールバックします。
9	connection.close() このメソッドは、データベース接続を閉じます。これはcommit（）を自動的に呼び出さないことに注意してください。最初にcommit（）を呼び出さずにデータベース接続を閉じると、変更は失われます！
10	cursor.fetchone() このメソッドは、クエリ結果セットの次の行をフェッチして単一のシーケンスを返します。使用可能なデータがなくなったらNoneを返します。
11	cursor.fetchmany([size=cursor.arraysize]) このルーチンは、クエリ結果の次の行セットをフェッチし、リストを返します。使用可能な行がなくなると、空のリストが返されます。このメソッドは、sizeパラメーターで指定された数の行をフェッチしようとします。
12	cursor.fetchall() このルーチンは、クエリ結果のすべての（残りの）行をフェッチし、リストを返します。使用可能な行がない場合、空のリストが返されます。

データベースへの接続

次のPythonコードは、既存のデータベースに接続する方法を示しています。データベースが存在しない場合は、データベースが作成され、最終的にデータベースオブジェクトが返されます。

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database="testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")

print "Opened database successfully"

ここでは、名前としてデータベース testdb を指定することもできます。データベースが正常に開かれた場合、次のメッセージが表示されます-

Open database successfully

テーブルを作成する

次のPythonプログラムは、以前に作成されたデータベースにテーブルを作成するために使用されます-

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
print "Opened database successfully"

cur = conn.cursor()
cur.execute('''CREATE TABLE COMPANY
      (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL);''')
print "Table created successfully"

conn.commit()
conn.close()

上記のプログラムが実行されると、 test.db にCOMPANYテーブルが作成され、次のメッセージが表示されます-

Opened database successfully
Table created successfully

INSERT操作

次のPythonプログラムは、上記の例で作成したCOMPANYテーブルにレコードを作成する方法を示しています-

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
print "Opened database successfully"

cur = conn.cursor()

cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (1, 'Paul', 32, 'California', 20000.00 )");

cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");

cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");

cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )");

conn.commit()
print "Records created successfully";
conn.close()

上記のプログラムが実行されると、COMPANYテーブルに特定のレコードが作成され、次の2行が表示されます-

Opened database successfully
Records created successfully

SELECT操作

次のPythonプログラムは、上記の例で作成されたCOMPANYテーブルからレコードを取得して表示する方法を示しています-

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
print "Opened database successfully"

cur = conn.cursor()

cur.execute("SELECT id, name, address, salary  from COMPANY")
rows = cur.fetchall()
for row in rows:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "\n"

print "Operation done successfully";
conn.close()

上記のプログラムが実行されると、次の結果が生成されます-

Opened database successfully
ID =  1
NAME =  Paul
ADDRESS =  California
SALARY =  20000.0

ID =  2
NAME =  Allen
ADDRESS =  Texas
SALARY =  15000.0

ID =  3
NAME =  Teddy
ADDRESS =  Norway
SALARY =  20000.0

ID =  4
NAME =  Mark
ADDRESS =  Rich-Mond
SALARY =  65000.0

Operation done successfully

更新操作

次のPythonコードは、UPDATEステートメントを使用してレコードを更新し、COMPANYテーブルから更新されたレコードを取得して表示する方法を示しています-

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
print "Opened database successfully"

cur = conn.cursor()

cur.execute("UPDATE COMPANY set SALARY = 25000.00 where ID = 1")
conn.commit()
print "Total number of rows updated :", cur.rowcount

cur.execute("SELECT id, name, address, salary  from COMPANY")
rows = cur.fetchall()
for row in rows:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "\n"

print "Operation done successfully";
conn.close()

上記のプログラムが実行されると、次の結果が生成されます-

Opened database successfully
Total number of rows updated : 1
ID =  1
NAME =  Paul
ADDRESS =  California
SALARY =  25000.0

ID =  2
NAME =  Allen
ADDRESS =  Texas
SALARY =  15000.0

ID =  3
NAME =  Teddy
ADDRESS =  Norway
SALARY =  20000.0

ID =  4
NAME =  Mark
ADDRESS =  Rich-Mond
SALARY =  65000.0

Operation done successfully

削除操作

次のPythonコードは、DELETEステートメントを使用してレコードを削除し、COMPANYテーブルから残りのレコードを取得して表示する方法を示しています-

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
print "Opened database successfully"

cur = conn.cursor()

cur.execute("DELETE from COMPANY where ID=2;")
conn.commit()
print "Total number of rows deleted :", cur.rowcount

cur.execute("SELECT id, name, address, salary  from COMPANY")
rows = cur.fetchall()
for row in rows:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "\n"

print "Operation done successfully";
conn.close()

上記のプログラムが実行されると、次の結果が生成されます-

Opened database successfully
Total number of rows deleted : 1
ID =  1
NAME =  Paul
ADDRESS =  California
SALARY =  20000.0

ID =  3
NAME =  Teddy
ADDRESS =  Norway
SALARY =  20000.0

ID =  4
NAME =  Mark
ADDRESS =  Rich-Mond
SALARY =  65000.0

Operation done successfully

Postgresql-quick-guide

PostgreSQL-概要

PostgreSQLとは何ですか？

PostgreSQLの簡単な歴史

PostgreSQLの主な機能

手続き言語のサポート

PostgreSQL-環境設定

Linux/UnixにPostgreSQLをインストールする

WindowsへのPostgreSQLのインストール

MacにPostgreSQLをインストールする

PostgreSQL-構文

SQLステートメント

PostgreSQL SQLコマンド

アボート

ALTER AGGREGATE

ALTER CONVERSION

ALTER DATABASE

ALTER DOMAIN

機能を変更する

ALTER GROUP

ALTER INDEX

ALTER LANGUAGE

ALTER OPERATOR

ALTER演算子クラス

ALTER SCHEMA

シーケンスの変更

他の机

ALTER TABLESPACE

ALTER TRIGGER

代替タイプ

ALTER USER

分析する

ベギン

チェックポイント

閉じる

クラスタ

コメント

コミット

COPY

集約を作成

キャストの作成

制約トリガーの作成

変換を作成

データベース作成

ドメインの作成

関数を作成する

グループを作る

インデックスの作成

言語を作成する

オペレーターの作成

演算子クラスの作成

ルールを作成

スキーマの作成

シーケンスの作成

CREATE TABLE

テーブルを作成

テーブルスペースの作成

CREATE TRIGGER

タイプを作成

ユーザーを作成

ビューを作成

割り当て解除

DECLARE

DELETE

アグリゲートのドロップ

ドロップキャスト

ドロップ変換

ドロップデータベース

ドロップドメイン

ドロップ機能

ドロップグループ

ドロップインデックス

ドロップ言語

ドロップ演算子

ドロップ演算子クラス

廃棄規則

ドロップスキーム

ドロップシーケンス

ドロップテーブル

ドロップテーブルスペース