PostgreSQL-クイックガイド
PostgreSQLは、強力なオープンソースのオブジェクトリレーショナルデータベースシステムです。15年以上の活発な開発フェーズと、信頼性、データの整合性、および正確性で高い評価を得ている実証済みのアーキテクチャを備えています。
このチュートリアルでは、PostgreSQLのクイックスタートを提供し、PostgreSQLプログラミングに慣れることができます。
PostgreSQLとは何ですか？
PostgreSQL（発音は post-gress-Q-L）は、世界中のボランティアチームによって開発されたオープンソースのリレーショナルデータベース管理システム（DBMS）です。PostgreSQLは企業やその他の民間団体によって管理されておらず、ソースコードは無料で入手できます。
PostgreSQLの簡単な歴史
もともとPostgresと呼ばれていたPostgreSQLは、MichaelStonebrakerという名前のコンピュータサイエンスの教授によってUCBで作成されました。Stonebrakerは、現在ComputerAssociatesが所有している前身のIngresのフォローアッププロジェクトとして1986年にPostgresを開始しました。
1977-1985 −INGRESというプロジェクトが開発されました。
リレーショナルデータベースの概念実証
1980年にイングレス社を設立。
1994年にComputerAssociatesによって購入されました
1986-1994 − POSTGRES
オブジェクト指向とクエリ言語に焦点を当てたINGRESの概念の開発-Quel
INGRESのコードベースはPOSTGRESの基礎として使用されませんでした
Illustraとして商品化（Informixが購入、IBMが購入）
1994-1995 − Postgres95
SQLのサポートは1994年に追加されました
1995年にPostgres95としてリリース
1996年にPostgreSQL6.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サブセレクト
- 外部キー
- Trigger
- Views
- Transactions
- マルチバージョン同時実行制御（MVCC）
- ストリーミングレプリケーション（9.0以降）
- ホットスタンバイ（9.0以降）
上記の機能を理解するには、PostgreSQLの公式ドキュメントを確認してください。PostgreSQLは、ユーザーがさまざまな方法で拡張できます。たとえば、新しい-を追加することによって
- データ型
- Functions
- Operators
- 集計関数
- インデックスメソッド
手続き型言語のサポート
PostgreSQLは、4つの標準的な手続き型言語をサポートしています。これにより、ユーザーは任意の言語で独自のコードを記述でき、PostgreSQLデータベースサーバーで実行できます。これらの手続き型言語は、PL / pgSQL、PL / Tcl、PL / Perl、およびPL / Pythonです。さらに、PL / PHP、PL / V8、PL / Ruby、PL / Javaなどの他の非標準の手続き型言語もサポートされています。
PostgreSQLの基本を理解するために、まずPostgreSQLをインストールしましょう。この章では、Linux、Windows、およびMacOSプラットフォームへのPostgreSQLのインストールについて説明します。
Linux / UnixへのPostgreSQLのインストール
所定の手順に従って、LinuxマシンにPostgreSQLをインストールします。としてログインしていることを確認してくださいroot インストールに進む前に。
必要なPostgreSQLのバージョン番号と、可能な限り正確に、EnterpriseDBから必要なプラットフォームを選択します
私がダウンロードしました postgresql-9.2.4-1-linux-x64.run私の64ビットCentOS-6マシン用。さて、次のように実行しましょう-
[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をインストールします。インストール中は、サードパーティのアンチウイルスがオフになっていることを確認してください。
必要なPostgreSQLのバージョン番号と、可能な限り正確に、EnterpriseDBから必要なプラットフォームを選択します
32ビットモードで実行しているWindowsPC用にpostgresql-9.2.4-1-windows.exeをダウンロードしたので、実行しましょう postgresql-9.2.4-1-windows.exe管理者としてPostgreSQLをインストールします。インストールする場所を選択します。デフォルトでは、ProgramFilesフォルダー内にインストールされます。
インストールプロセスの次のステップは、データが保存されるディレクトリを選択することです。デフォルトでは、「data」ディレクトリの下に保存されます。
次に、セットアップでパスワードの入力を求められるため、お気に入りのパスワードを使用できます。
次のステップ。ポートをデフォルトのままにします。
次のステップで、「ロケール」を求められたときに、「英語、米国」を選択しました。
システムにPostgreSQLをインストールするのに時間がかかります。インストールプロセスが完了すると、次の画面が表示されます。チェックボックスをオフにして、[完了]ボタンをクリックします。
インストールプロセスが完了すると、PostgreSQL9.2のプログラムメニューからpgAdminIII、StackBuilder、およびPostgreSQLシェルにアクセスできます。
MacへのPostgreSQLのインストール
所定の手順に従って、MacマシンにPostgreSQLをインストールします。としてログインしていることを確認してくださいadministrator インストールに進む前に。
EnterpriseDBで入手可能なMacOS用PostgreSQLの最新バージョン番号を選択してください
私がダウンロードしました postgresql-9.2.4-1-osx.dmgOSXバージョン10.8.3で実行されているMacOSの場合。ここで、ファインダーでdmgイメージを開き、ダブルクリックするだけで、次のウィンドウにPostgreSQLインストーラーが表示されます。
次に、をクリックします postgres-9.2.4-1-osxアイコン。警告メッセージが表示されます。警告を受け入れて、さらにインストールを続行します。次のウィンドウに示すように、管理者パスワードを要求されます-
パスワードを入力してインストールを続行し、この手順の後、Macマシンを再起動します。次のウィンドウが表示されない場合は、インストールをもう一度開始してください。
インストーラーを起動すると、インストールの場所、データベースを使用するユーザーのパスワード、ポート番号など、いくつかの基本的な質問が表示されます。したがって、入力できるパスワードを除いて、すべてをデフォルト値のままにしてください。あなたの選択に従って。MacマシンのアプリケーションフォルダにPostgreSQLがインストールされます。これを確認できます-
これで、最初に任意のプログラムを起動できます。SQLシェルから始めましょう。SQL Shellを起動するときは、インストール時に選択したパスワードを入力する以外は、表示されるすべてのデフォルト値を使用してください。すべてがうまくいけば、postgresデータベースとpostgress# 以下のようなプロンプトが表示されます-
おめでとう！！！これで、PostgreSQLデータベースプログラミングを開始する準備が整いました。
この章では、PostgreSQL SQLコマンドのリストと、これらの各コマンドの正確な構文規則について説明します。この一連のコマンドは、psqlコマンドラインツールから取得されます。Postgresがインストールされたので、psqlを-として開きます。
Program Files → PostgreSQL 9.2 → SQL Shell(psql).
psqlを使用すると、\ helpコマンドを使用してコマンドの完全なリストを生成できます。特定のコマンドの構文については、次のコマンドを使用してください-
postgres-# \help <command_name>SQLステートメント
SQLステートメントはトークンで構成され、各トークンはキーワード、識別子、引用符で囲まれた識別子、定数、または特殊文字記号のいずれかを表すことができます。以下の表では、単純なSELECTステートメントを使用して、基本的でありながら完全なSQLステートメントとそのコンポーネントを示しています。
| 選択する | ID、名前 | から | 州 | |
|---|---|---|---|---|
| トークンタイプ | キーワード | 識別子 | キーワード | 識別子 |
| 説明 | コマンド | ID列と名前列 | 句 | テーブル名 |
PostgreSQLSQLコマンド
アボート
現在のトランザクションを中止します。
ABORT [ WORK | TRANSACTION ]ALTER AGGREGATE
集計関数の定義を変更します。
ALTER AGGREGATE name ( type ) RENAME TO new_name
ALTER AGGREGATE name ( type ) OWNER TO new_owner変換の変更
変換の定義を変更します。
ALTER CONVERSION name RENAME TO new_name
ALTER CONVERSION name OWNER TO new_ownerALTER 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_ownerALTERDOMAIN
ドメイン固有のパラメーターの定義を変更します。
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 groupname ADD USER username [, ... ]
ALTER GROUP groupname DROP USER username [, ... ]
ALTER GROUP groupname RENAME TO new_nameALTERINDEX
インデックスの定義を変更します。
ALTER INDEX name OWNER TO new_owner
ALTER INDEX name SET TABLESPACE indexspace_name
ALTER INDEX name RENAME TO new_name言語の変更
手続き型言語の定義を変更します。
ALTER LANGUAGE name RENAME TO new_nameオペレーターの変更
演算子の定義を変更します。
ALTER OPERATOR name ( { lefttype | NONE }, { righttype | NONE } )
OWNER TO new_ownerALTER OPERATOR CLASS
演算子クラスの定義を変更します。
ALTER OPERATOR CLASS name USING index_method RENAME TO new_name
ALTER OPERATOR CLASS name USING index_method OWNER TO new_ownerALTERSCHEMA
スキーマの定義を変更します。
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アクションが次の行のいずれかである場合-
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_nameALTERTABLESPACE
表領域の定義を変更します。
ALTER TABLESPACE name RENAME TO new_name
ALTER TABLESPACE name OWNER TO new_ownerALTER TRIGGER
トリガーの定義を変更します。
ALTER TRIGGER name ON table RENAME TO new_name変更タイプ
タイプの定義を変更します。
ALTER TYPE name OWNER TO new_ownerALTER 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オプションはどこにありますか-
[ 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 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_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 TABLE AS
クエリの結果から新しいテーブルを定義します。
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name
[ (column_name [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ]
AS queryCREATETABLESPACE
新しいテーブルスペースを定義します。
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 [ ... ] ]オプションはどこにありますか-
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 name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
CURSOR [ { WITH | WITHOUT } HOLD ] FOR query
[ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]削除
テーブルの行を削除します。
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データベース
データベースを削除します。
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 ]DROPTABLESPACE
表領域を削除します。
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 [ WORK | TRANSACTION ]実行する
プリペアドステートメントを実行します。
EXECUTE plan_name [ (parameter [, ...] ) ]説明
ステートメントの実行プランを表示します。
EXPLAIN [ ANALYZE ] [ VERBOSE ] statementフェッチ
カーソルを使用してクエリから行を取得します。
FETCH [ direction { FROM | IN } ] cursor_name方向が空または-のいずれかである可能性がある場合
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 'filename'ロック
テーブルをロックします。
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 [ direction { FROM | IN } ] cursor_name通知
通知を生成します。
NOTIFY name準備する
実行するステートメントを準備します。
PREPARE plan_name [ (data_type [, ...] ) ] AS statementREINDEX
インデックスを再構築します。
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 ]セーブポイントへのロールバック
セーブポイントにロールバックします。
ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_nameセーブポイント
現在のトランザクション内で新しいセーブポイントを定義します。
SAVEPOINT savepoint_name選択する
テーブルまたはビューから行を取得します。
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 [, ...] ] ][ 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 INTO
クエリの結果から新しいテーブルを定義します。
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 [ 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 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で使用されるデータ型について説明します。テーブルの作成時に、列ごとにデータ型、つまりテーブルフィールドに保存するデータの種類を指定します。
これにより、いくつかの利点が得られます-
Consistency −同じデータ型の列に対する操作は一貫した結果をもたらし、通常は最速です。
Validation −データ型の適切な使用は、データのフォーマット検証とデータ型の範囲外のデータの拒否を意味します。
Compactness −列は単一のタイプの値を格納できるため、コンパクトな方法で格納されます。
Performance−データ型を適切に使用すると、データを最も効率的に保存できます。保存された値は迅速に処理できるため、パフォーマンスが向上します。
PostgreSQLは幅広いデータ型をサポートしています。さらに、ユーザーはCREATE TYPESQLコマンドを使用して独自のカスタムデータ型を作成できます。PostgreSQLにはさまざまなデータ型のカテゴリがあります。それらについては、以下で説明します。
数値タイプ
数値タイプは、2バイト、4バイト、および8バイトの整数、4バイトおよび8バイトの浮動小数点数、および選択可能な精度の小数で構成されます。次の表に、使用可能なタイプを示します。
| 名前 | ストレージサイズ | 説明 | 範囲 |
|---|---|---|---|
| smallint | 2バイト | 小範囲整数 | -32768〜 + 32767 |
| 整数 | 4バイト | 整数の典型的な選択 | -2147483648から+2147483647 |
| bigint | 8バイト | 広い範囲の整数 | -9223372036854775808から9223372036854775807 |
| 10進数 | 変数 | ユーザー指定の精度、正確 | 小数点の前に最大131072桁。小数点以下16383桁まで |
| 数値 | 変数 | ユーザー指定の精度、正確 | 小数点の前に最大131072桁。小数点以下16383桁まで |
| リアル | 4バイト | 可変精度、不正確 | 10進数の6桁の精度 |
| 倍精度 | 8バイト | 可変精度、不正確 | 10進数の15桁の精度 |
| smallserial | 2バイト | 小さな自動インクリメント整数 | 1〜32767 |
| シリアル | 4バイト | 自動インクリメント整数 | 1から2147483647 |
| bigserial | 8バイト | 大きな自動インクリメント整数 | 1から9223372036854775807 |
通貨の種類
お金の種類は固定小数精度で通貨額を格納します。数値、整数、およびbigintデータ型の値は、moneyにキャストできます。丸め誤差が発生する可能性があるため、お金を処理するために浮動小数点数を使用することはお勧めしません。
| 名前 | ストレージサイズ | 説明 | 範囲 |
|---|---|---|---|
| お金 | 8バイト | 通貨額 | -92233720368547758.08〜 + 92233720368547758.07 |
文字の種類
以下の表に、PostgreSQLで使用可能な汎用文字タイプを示します。
| S.いいえ。 | 名前と説明 |
|---|---|
| 1 | character varying(n), varchar(n) 制限付きの可変長 |
| 2 | character(n), char(n) 固定長、ブランクパッド |
| 3 | text 可変長無制限 |
バイナリデータ型
bytea型のデータ・タイプは、下記の表のようにバイナリ文字列の保存を可能にします。
| 名前 | ストレージサイズ | 説明 |
|---|---|---|
| ビテア | 1バイトまたは4バイトと実際のバイナリ文字列 | 可変長バイナリ文字列 |
日付/時刻タイプ
PostgreSQLは、次の表に示すように、SQLの日付と時刻のタイプのフルセットをサポートしています。日付はグレゴリオ暦に従ってカウントされます。ここでは、すべてのタイプの解像度が1 microsecond / 14 digits を除いて date タイプ、その解像度は day。
| 名前 | ストレージサイズ | 説明 | 低い価値 | 高い価値 |
|---|---|---|---|---|
| タイムスタンプ[（p）] [タイムゾーンなし] | 8バイト | 日付と時刻の両方（タイムゾーンなし） | 紀元前4713年 | 294276 AD |
| タイムスタンプツ | 8バイト | 日付と時刻の両方、タイムゾーン付き | 紀元前4713年 | 294276 AD |
| 日付 | 4バイト | 日付（時刻なし） | 紀元前4713年 | 5874897 AD |
| 時間[（p）] [タイムゾーンなし] | 8バイト | 時刻（日付なし） | 00:00:00 | 24:00:00 |
| 時間帯のある時間[（p）] | 12バイト | 時間帯のみの時間帯 | 00：00：00 + 1459 | 24：00：00-1459 |
| 間隔[フィールド] [（p）] | 12バイト | 時間間隔 | -17800 0000年 | 17800 0000年 |
ブール型
PostgreSQLは、標準のSQL型ブール値を提供します。ブールデータ型は、状態true、false、および3番目の状態unknownを持つことができ、SQLnull値で表されます。
| 名前 | ストレージサイズ | 説明 |
|---|---|---|
| ブール値 | 1バイト | 真または偽の状態 |
列挙型
列挙（列挙）型は、静的で順序付けられた値のセットを構成するデータ型です。これらは、多くのプログラミング言語でサポートされている列挙型と同等です。
他の型とは異なり、列挙型はCREATETYPEコマンドを使用して作成する必要があります。このタイプは、静的で順序付けられた値のセットを格納するために使用されます。たとえば、コンパスの方向、つまり、NORTH、SOUTH、EAST、WEST、または以下に示す曜日。
CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');列挙されたものは、作成されると、他のタイプと同じように使用できます。
幾何学的タイプ
幾何学的データ型は、2次元の空間オブジェクトを表します。最も基本的なタイプであるポイントは、他のすべてのタイプの基礎を形成します。
| 名前 | ストレージサイズ | 表現 | 説明 |
|---|---|---|---|
| ポイント | 16バイト | 平面上のポイント | （x、y） |
| ライン | 32バイト | 無限線（完全には実装されていません） | （（x1、y1）、（x2、y2）） |
| lseg | 32バイト | 有限線分 | （（x1、y1）、（x2、y2）） |
| ボックス | 32バイト | 長方形の箱 | （（x1、y1）、（x2、y2）） |
| 道 | 16 + 16nバイト | 閉じたパス（ポリゴンと同様） | （（x1、y1）、...） |
| 道 | 16 + 16nバイト | オープンパス | [（x1、y1）、...] |
| ポリゴン | 40 + 16n | ポリゴン（閉じたパスに似ています） | （（x1、y1）、...） |
| サークル | 24バイト | サークル | <（x、y）、r>（中心点と半径） |
ネットワークアドレスタイプ
PostgreSQLは、IPv4、IPv6、およびMACアドレスを格納するためのデータ型を提供します。これらのタイプは入力エラーチェックと特殊な演算子および関数を提供するため、プレーンテキストタイプの代わりにこれらのタイプを使用してネットワークアドレスを格納することをお勧めします。
| 名前 | ストレージサイズ | 説明 |
|---|---|---|
| cidr | 7または19バイト | IPv4およびIPv6ネットワーク |
| inet | 7または19バイト | IPv4およびIPv6ホストとネットワーク |
| macaddr | 6バイト | MACアドレス |
ビット文字列タイプ
ビット文字列タイプは、ビットマスクを格納するために使用されます。それらは0または1のいずれかです。2つのSQLビットタイプがあります。bit(n) そして bit varying(n)、ここで、nは正の整数です。
テキスト検索タイプ
このタイプは、全文検索をサポートします。これは、自然言語のドキュメントのコレクションを検索して、クエリに最適なドキュメントを見つけるアクティビティです。これには2つのデータ型があります-
| S.いいえ。 | 名前と説明 |
|---|---|
| 1 | tsvector これは、「語彙素」と呼ばれる、同じ単語のさまざまなバリアントをマージするために正規化された個別の単語のソートされたリストです。 |
| 2 | tsquery これは、検索される語彙素を格納し、ブール演算子＆（AND）、|を尊重してそれらを結合します。（または ！（NOT）。括弧を使用して、演算子のグループ化を強制できます。 |
UUIDタイプ
UUID（Universally Unique Identifiers）は、小文字の16進数のシーケンスとして、ハイフンで区切られたいくつかのグループ、具体的には8桁のグループ、4桁の3つのグループ、12桁のグループで記述されます。 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オブジェクト表記）データを格納するために使用することができます。このようなデータはテキストとして保存することもできますが、jsonデータ型には、保存されている各値が有効なJSON値であることを確認できるという利点があります。次のように、JSONデータ型を直接処理するために使用できる関連するサポート関数も利用できます。
| 例 | 結果の例 |
|---|---|
| array_to_json（ '{{1,5}、{99,100}}' :: int []） | [[1,5]、[99,100]] |
| row_to_json（row（1、 'foo'）） | {"f1"：1、 "f2"： "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時から午前11時までの任意の時点）にすることができます。
利用可能な組み込みの範囲タイプには、次の範囲が含まれます-
int4range −整数の範囲
int8range −bigintの範囲
numrange −数値の範囲
tsrange −タイムゾーンのないタイムスタンプの範囲
tstzrange −タイムゾーン付きのタイムスタンプの範囲
daterange −日付の範囲
カスタム範囲タイプを作成して、inetタイプをベースとして使用するIPアドレス範囲や、floatデータタイプをベースとして使用するfloat範囲など、新しいタイプの範囲を使用可能にすることができます。
範囲タイプは、それぞれ[]文字と（）文字を使用した包括的および排他的範囲境界をサポートします。たとえば、「[4,9）」は、4から始まり、9を含まないすべての整数を表します。
オブジェクト識別子の種類
オブジェクト識別子（OID）は、さまざまなシステムテーブルの主キーとしてPostgreSQLによって内部的に使用されます。場合WITH OIDSが指定されているか、default_with_oidsの構成変数が有効になっている、だけにして、このような場合にOIDは、ユーザ作成のテーブルに追加されます。次の表に、いくつかのエイリアスタイプを示します。OIDエイリアスタイプには、特殊な入力および出力ルーチンを除いて、独自の操作はありません。
| 名前 | 参考文献 | 説明 | 値の例 |
|---|---|---|---|
| oid | どれか | 数値オブジェクト識別子 | 564182 |
| regproc | pg_proc | 関数名 | 和 |
| regprocedure | pg_proc | 引数型の関数 | sum（int4） |
| regoper | pg_operator | オペレーター名 | + |
| regoperator | pg_operator | 引数型の演算子 | *（integer、integer）または-（NONE、integer） |
| regclass | pg_class | 関係名 | pg_type |
| regtype | pg_type | データ型名 | 整数 |
| regconfig | pg_ts_config | テキスト検索構成 | 英語 |
| regdictionary | pg_ts_dict | テキスト検索辞書 | シンプル |
疑似タイプ
PostgreSQL型システムには、まとめて疑似型と呼ばれるいくつかの特別な目的のエントリが含まれています。疑似型は列データ型として使用できませんが、関数の引数または結果型を宣言するために使用できます。
以下の表に、既存の疑似タイプを示します。
| S.いいえ。 | 名前と説明 |
|---|---|
| 1 | any 関数が任意の入力データ型を受け入れることを示します。 |
| 2 | anyelement 関数が任意のデータ型を受け入れることを示します。 |
| 3 | anyarray 関数が任意の配列データ型を受け入れることを示します。 |
| 4 | anynonarray 関数が配列以外のデータ型を受け入れることを示します。 |
| 5 | anyenum 関数が任意の列挙型データ型を受け入れることを示します。 |
| 6 | anyrange 関数が任意の範囲データ型を受け入れることを示します。 |
| 7 | cstring 関数がnullで終了するC文字列を受け入れるか返すことを示します。 |
| 8 | internal 関数がサーバー内部のデータ型を受け入れるか返すことを示します。 |
| 9 | language_handler 手続き型言語の呼び出しハンドラーは、language_handlerを返すように宣言されています。 |
| 10 | fdw_handler 外部データラッパーハンドラーは、fdw_handlerを返すように宣言されています。 |
| 11 | record 不特定の行タイプを返す関数を識別します。 |
| 12 | trigger トリガー関数は、トリガーを返すように宣言されています。 |
| 13 | void 関数が値を返さないことを示します。 |
この章では、PostgreSQLで新しいデータベースを作成する方法について説明します。PostgreSQLは、新しいデータベースを作成する2つの方法を提供します-
- SQLコマンドであるCREATEDATABASEを使用します。
- createdbを使用してコマンドライン実行可能ファイル。
CREATEDATABASEの使用
このコマンドはPostgreSQLシェルプロンプトからデータベースを作成しますが、データベースを作成するには適切な権限が必要です。デフォルトでは、新しいデータベースは、標準のシステムデータベーステンプレート1のクローンを作成することによって作成されます。
構文
CREATEDATABASEステートメントの基本的な構文は次のとおりです。
CREATE DATABASE dbname;ここで、dbnameは、作成するデータベースの名前です。
例
以下は簡単な例です。 testdb PostgreSQLスキーマで
postgres=# CREATE DATABASE testdb;
postgres-#createdbコマンドの使用
PostgreSQLのコマンドライン実行可能CREATEDBは、 SQLコマンドのラッパですCREATE DATABASEを。このコマンドとSQLコマンドCREATEDATABASEの唯一の違いは、前者はコマンドラインから直接実行でき、コメントをデータベースにすべて1つのコマンドで追加できることです。
構文
createdbの構文は次のとおりです-
createdb [option...] [dbname [description]]パラメーター
以下の表に、パラメーターとその説明を示します。
| S.いいえ。 | パラメータと説明 |
|---|---|
| 1 | dbname 作成するデータベースの名前。 |
| 2 | description 新しく作成されたデータベースに関連付けるコメントを指定します。 |
| 3 | options createdbが受け入れるコマンドライン引数。 |
オプション
次の表に、createdbが受け入れるコマンドライン引数を示します。
| S.いいえ。 | オプションと説明 |
|---|---|
| 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 ******上記のコマンドは、PostgreSQL管理者ユーザーのパスワードの入力を求めます。 postgres、デフォルトで。したがって、パスワードを入力して、新しいデータベースの作成に進みます
上記のいずれかの方法でデータベースを作成したら、を使用してデータベースのリストでデータベースを確認できます。 \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-#この章では、データベースにアクセスするさまざまな方法について説明します。前の章でデータベースをすでに作成していると仮定します。次のいずれかの方法でデータベースを選択できます-
- データベースSQLプロンプト
- OSコマンドプロンプト
データベースSQLプロンプト
PostgreSQLクライアントをすでに起動していて、次のSQLプロンプトが表示されたとします。
postgres=#利用可能なデータベースリストは、 \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-#次に、次のコマンドを入力して、目的のデータベースに接続/選択します。ここでは、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=#これでPostgreSQLtestdbにログインし、testdb内でコマンドを実行する準備が整いました。データベースを終了するには、コマンド\ qを使用できます。
この章では、PostgreSQLでデータベースを削除する方法について説明します。データベースを削除するには2つのオプションがあります-
- SQLコマンドであるDROPDATABASEを使用します。
- dropdbを使用してコマンドライン実行可能ファイル。
既存のデータベースを削除すると、データベースに保存されている完全な情報が失われるため、この操作を使用する前に注意してください。
DROPDATABASEの使用
このコマンドはデータベースを削除します。データベースのカタログエントリを削除し、データを含むディレクトリを削除します。データベースの所有者のみが実行できます。このコマンドは、あなたまたは他の誰かがターゲットデータベースに接続している間は実行できません（このコマンドを発行するには、postgresまたは他のデータベースに接続してください）。
構文
DROPDATABASEの構文を以下に示します-
DROP DATABASE [ IF EXISTS ] nameパラメーター
この表は、パラメーターとその説明を示しています。
| S.いいえ。 | パラメータと説明 |
|---|---|
| 1 | IF EXISTS データベースが存在しない場合でもエラーをスローしないでください。この場合、通知が発行されます。 |
| 2 | name 削除するデータベースの名前。 |
psqlまたは pgAdminIII からの独自の接続を含め、接続が開いているデータベースを削除することはできません 。現在接続しているデータベースを削除する場合は、別のデータベースまたはtemplate1に切り替える必要があります 。したがって、代わりに、このコマンドのラッパーであるプログラムdropdbを使用する方が便利な場合 があります。
例
以下は、削除する簡単な例です testdb PostgreSQLスキーマから-
postgres=# DROP DATABASE testdb;
postgres-#dropdbコマンドの使用
PostgresSQLコマンドライン実行可能ファイル dropdbSQLコマンドDROPDATABASEのコマンドラインラッパーです。このユーティリティを介したデータベースの削除と、サーバーにアクセスするための他の方法を介したデータベースの削除との間に効果的な違いはありません。dropdbは、既存のPostgreSQLデータベースを破棄します。このコマンドを実行するユーザーは、データベースのスーパーユーザーまたはデータベースの所有者である必要があります。
構文
dropdbの構文は次のとおりです-
dropdb [option...] dbnameパラメーター
次の表に、パラメーターとその説明を示します。
| S.いいえ。 | パラメータと説明 |
|---|---|
| 1 | dbname 削除するデータベースの名前。 |
| 2 | option dropdbが受け入れるコマンドライン引数。 |
オプション
次の表に、dropdbが受け入れるコマンドライン引数を示します。
| S.いいえ。 | オプションと説明 |
|---|---|
| 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 CREATE TABLEステートメントは、指定されたデータベースのいずれかに新しいテーブルを作成するために使用されます。
構文
CREATETABLEステートメントの基本的な構文は次のとおりです-
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);CREATE TABLEはキーワードであり、データベースシステムに新しいテーブルを作成するように指示します。テーブルの一意の名前または識別子は、CREATETABLEステートメントの後に続きます。最初は、現在のデータベースの空のテーブルは、コマンドを発行したユーザーが所有しています。
次に、括弧内にリストがあり、テーブルの各列とそれがどのような種類のデータ型であるかを定義します。構文は、以下の例で明らかになります。
例
以下は、主キーとしてIDを持つCOMPANYテーブルを作成する例であり、NOT NULLは、このテーブルにレコードを作成するときにこれらのフィールドを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 TABLEステートメントは、テーブル定義と、そのテーブルに関連するすべてのデータ、インデックス、ルール、トリガー、および制約を削除するために使用されます。
このコマンドを使用するときは注意が必要です。テーブルが削除されると、テーブルで使用可能なすべての情報も永久に失われるためです。
構文
DROPTABLEステートメントの基本的な構文は次のとおりです。
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コマンドが正常に実行されたことを示しています。
A schemaテーブルの名前付きコレクションです。スキーマには、ビュー、インデックス、シーケンス、データ型、演算子、および関数を含めることもできます。スキーマは、スキーマをネストできないことを除いて、オペレーティングシステムレベルのディレクトリに類似しています。PostgreSQLステートメントCREATESCHEMAは、スキーマを作成します。
構文
CREATESCHEMAの基本的な構文は次のとおりです-
CREATE SCHEMA name;どこの名前は、スキーマの名前です。
スキーマにテーブルを作成するための構文
スキーマでテーブルを作成するための基本的な構文は次のとおりです。
CREATE TABLE myschema.mytable (
...
);例
スキーマを作成する例を見てみましょう。データベースtestdbに接続し、次のようにスキーマmyschemaを作成します-
testdb=# create schema myschema;
CREATE SCHEMA「CREATESCHEMA」というメッセージは、スキーマが正常に作成されたことを示します。
ここで、上記のスキーマに次のようにテーブルを作成しましょう。
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 INSERT INTOステートメントを使用すると、新しい行をテーブルに挿入できます。クエリの結果として、一度に1行または複数行を挿入できます。
構文
INSERTINTOステートメントの基本的な構文は次のとおりです。
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);ここで、column1、column2、... columnNは、データを挿入するテーブル内の列の名前です。
ターゲット列名は、任意の順序でリストできます。VALUES句またはクエリによって提供される値は、明示的または暗黙的な列リストに左から右に関連付けられます。
テーブルのすべての列に値を追加する場合は、SQLクエリで列名を指定する必要がない場合があります。ただし、値の順序がテーブルの列と同じ順序であることを確認してください。SQL INSERTINTO構文は次のようになります-
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);出力
次の表は、出力メッセージとその意味をまとめたものです。
| S.いいえ。 | 出力メッセージと説明 |
|---|---|
| 1 | INSERT oid 1 行が1つだけ挿入された場合に返されるメッセージ。oidは、挿入された行の数値OIDです。 |
| 2 | INSERT 0 # 複数の行が挿入された場合に返されるメッセージ。＃は挿入された行の数です。 |
例
でCOMPANYテーブルを作成しましょう testdb 次のように-
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');次の例は、行を挿入することです。ここでは、給与列が省略されているため、デフォルト値-になります。
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-13PostgreSQL SELECTステートメントは、データベーステーブルからデータをフェッチするために使用されます。データベーステーブルは、結果テーブルの形式でデータを返します。これらの結果テーブルは、結果セットと呼ばれます。
構文
SELECTステートメントの基本的な構文は次のとおりです。
SELECT column1, column2, columnN FROM table_name;ここで、column1、column2 ...は、値をフェッチするテーブルのフィールドです。フィールドで使用可能なすべてのフィールドをフェッチする場合は、次の構文を使用できます-
SELECT * FROM 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)以下は、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ステートメントのWHERE句で使用される予約語または文字です。
演算子は、PostgreSQLステートメントで条件を指定し、ステートメントで複数の条件の接続詞として機能するために使用されます。
- 算術演算子
- 比較演算子
- 論理演算子
- ビット演算子
PostgreSQL算術演算子
変数を想定 a 2と変数を保持します b 3を保持し、次に−
例
| オペレーター | 説明 | 例 |
|---|---|---|
| + | 加算-演算子のいずれかの側に値を加算します | a + bは5を与えます |
| - | 減算-左側のオペランドから右側のオペランドを減算します | a-bは-1を与えます |
| * | 乗算-演算子のいずれかの側で値を乗算します | a * bは6を与えます |
| / | 除算-左側のオペランドを右側のオペランドで除算します | b / aは1を与えます |
| ％ | モジュラス-左側のオペランドを右側のオペランドで除算し、余りを返します | b％aは1を与えます |
| ^ | べき乗-これは、右側のオペランドの指数値を示します | a ^ bは8を与えます |
| | / | 平方根 | | /25.0は5を与えます |
| || / | 立方根 | || /27.0は3を与えます |
| ！ | 階乗 | 5！120を与える |
| !! | 階乗（プレフィックス演算子） | !! 5は120を与えます |
PostgreSQL比較演算子
変数aが10を保持し、変数bが20を保持すると仮定すると、−
例を表示
| オペレーター | 説明 | 例 |
|---|---|---|
| = | 2つのオペランドの値が等しいかどうかをチェックし、等しい場合は条件が真になります。 | （a = b）は真ではありません。 |
| ！= | 2つのオペランドの値が等しいかどうかをチェックし、値が等しくない場合は条件が真になります。 | （a！= b）は真です。 |
| <> | 2つのオペランドの値が等しいかどうかをチェックし、値が等しくない場合は条件が真になります。 | （a <> b）は真です。 |
| >> | 左のオペランドの値が右のオペランドの値より大きいかどうかを確認します。大きい場合は、条件が真になります。 | （a> b）は正しくありません。 |
| < | 左のオペランドの値が右のオペランドの値よりも小さいかどうかを確認します。小さい場合は、条件が真になります。 | （a <b）は真です。 |
| > = | 左のオペランドの値が右のオペランドの値以上であるかどうかをチェックします。はいの場合、条件は真になります。 | （a> = b）は正しくありません。 |
| <= | 左のオペランドの値が右のオペランドの値以下であるかどうかをチェックします。はいの場合、条件は真になります。 | （a <= b）は真です。 |
PostgreSQL論理演算子
これは、PostgresSQLで使用可能なすべての論理演算子のリストです。
例を表示
| S.いいえ。 | オペレーターと説明 |
|---|---|
| 1 | AND AND演算子を使用すると、PostgresSQLステートメントのWHERE句に複数の条件を含めることができます。 |
| 2 | NOT NOT演算子は、使用される論理演算子の意味を逆にします。例えば。存在しない、間、ないなど。This is negate operator。 |
| 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 | 1 |
A = 60の場合を想定します。およびB = 13; 現在、バイナリ形式では次のようになります-
A = 0011 1100
B = 0000 1101
-----------------
A＆B = 0000 1100
A | B = 0011 1101
〜A = 1100 0011
例を表示
PostgreSQLでサポートされているビット演算子を次の表に示します-
| オペレーター | 説明 | 例 |
|---|---|---|
| ＆ | バイナリAND演算子は、両方のオペランドに存在する場合、結果にビットをコピーします。 | （A＆B）は00001100である12を与えます |
| | | バイナリOR演算子は、いずれかのオペランドに存在する場合、ビットをコピーします。 | （A | B）は611 1101である61を与えます |
| 〜 | Binary Ones Complement Operatorは単項であり、ビットを「反転」させる効果があります。 | （〜A）は、符号付き2進数のため、2の補数形式で11000011である-61を返します。 |
| << | バイナリ左シフト演算子。左のオペランドの値は、右のオペランドで指定されたビット数だけ左に移動します。 | << 2は、11110000である240を与えます |
| >> | バイナリ右シフト演算子。左のオペランドの値は、右のオペランドで指定されたビット数だけ右に移動します。 | >> 2は、00001111である15を与えます |
| ＃ | ビットごとのXOR。 | A＃Bは01001001である49を与えます |
式は、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;次のようなレコードを持つテーブル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句は、単一のテーブルからデータをフェッチするとき、または複数のテーブルと結合するときに条件を指定するために使用されます。
指定された条件が満たされた場合にのみ、テーブルから特定の値を返します。WHERE句を使用して、結果セットに含めたくない行を除外できます。
WHERE句は、SELECTステートメントで使用されるだけでなく、UPDATE、DELETEステートメントなどでも使用されます。これらについては、以降の章で説明します。
構文
WHERE句を含むSELECTステートメントの基本的な構文は次のとおりです。
SELECT column1, column2, columnN
FROM table_name
WHERE [search_condition]比較演算子または論理演算子を使用してsearch_conditionを指定できます。>、<、=、LIKE、NOTなどのように。次の例は、この概念を明確にします。
例
次のようなレコードを持つテーブル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以上であるすべてのレコードをリストします。AND 給与が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以上であるすべてのレコードをリストします。 OR 給与が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でないすべてのレコードをリストします。これは、すべてのレコードを意味します。これは、どのレコードにもNULLに等しいAGEがないためです。
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値が25と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以降のAGEフィールドを持つすべてのレコードを検索します。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以降のAGEフィールドを持つすべてのレコードを検索します。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 | 20000PostgreSQL AND そして OR演算子は、複数の条件を組み合わせて、PostgreSQLステートメントで選択したデータを絞り込むために使用されます。これらの2つの演算子は、接続演算子と呼ばれます。
これらの演算子は、同じPostgreSQLステートメント内の異なる演算子と複数の比較を行う手段を提供します。
AND演算子
ザ・ AND演算子を使用すると、PostgreSQLステートメントのWHERE句に複数の条件を含めることができます。AND演算子を使用している間、すべての条件が真の場合、完全な条件は真と見なされます。たとえば、[condition1] AND [condition2]は、condition1とcondition2の両方が真の場合にのみ真になります。
構文
WHERE句を使用したAND演算子の基本的な構文は次のとおりです。
SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];AND演算子を使用して、N個の条件を組み合わせることができます。PostgreSQLステートメントによって実行されるアクションは、トランザクションであろうとクエリであろうと、ANDで区切られたすべての条件がTRUEである必要があります。
例
次のようなレコードを持つテーブル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以上であるすべてのレコードをリストします。 AND 給与が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またはcondition2のいずれかが真の場合に真になります。
構文
WHERE句を使用したOR演算子の基本的な構文は次のとおりです。
SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]OR演算子を使用して、N個の条件を組み合わせることができます。PostgreSQLステートメントによって実行されるアクションは、トランザクションであろうとクエリであろうと、ORで区切られた条件のいずれか1つだけがTRUEである必要があります。
例
次のレコードを持つ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以上であるすべてのレコードをリストします。 OR 給与が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クエリは、テーブル内の既存のレコードを変更するために使用されます。WHERE句をUPDATEクエリとともに使用して、選択した行を更新できます。そうしないと、すべての行が更新されます。
構文
WHERE句を使用したUPDATEクエリの基本的な構文は次のとおりです。
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];ANDまたはOR演算子を使用して、N個の条件を組み合わせることができます。
例
次のようなレコードを持つテーブル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クエリは、テーブルから既存のレコードを削除するために使用されます。WHERE句をDELETEクエリとともに使用して、選択した行を削除できます。そうしないと、すべてのレコードが削除されます。
構文
WHERE句を使用したDELETEクエリの基本的な構文は次のとおりです。
DELETE FROM table_name
WHERE [condition];ANDまたはOR演算子を使用して、N個の条件を組み合わせることができます。
例
次のようなレコードを持つテーブル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演算子は、ワイルドカードを使用してテキスト値をパターンと照合するために使用されます。検索式をパターン式と一致させることができる場合、LIKE演算子はtrueを返します。1。
LIKE演算子と組み合わせて使用される2つのワイルドカードがあります-
- パーセント記号（％）
- アンダースコア（_）
パーセント記号は、0、1、または複数の数字または文字を表します。アンダースコアは、単一の数字または文字を表します。これらの記号は組み合わせて使用できます。
これらの2つの符号のいずれかがLIKE句と組み合わせて使用されていない場合、LIKEはequals演算子のように機能します。
構文
％と_の基本的な構文は次のとおりです-
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.いいえ。 | ステートメントと説明 |
|---|---|
| 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桁の数字の値を検索します |
PostgresLIKEは文字列比較のみです。したがって、上記の例のように、整数列を文字列に明示的にキャストする必要があります。
実際の例を見てみましょう。次のようなレコードを持つテーブル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 句は、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を使用すると、残りのクエリによって生成された行の一部のみを取得できます。
例
次のようなレコードを持つテーブル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 句は、1つ以上の列に基づいて、データを昇順または降順で並べ替えるために使用されます。
構文
ORDERBY句の基本的な構文は次のとおりです。
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];ORDERBY句では複数の列を使用できます。並べ替えに使用している列が何であれ、その列がcolumn-listで使用可能であることを確認してください。
例
次のようなレコードを持つテーブル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句は、SELECTステートメントと連携して使用され、同一のデータを持つテーブル内の行をグループ化します。これは、これらのグループに適用される出力および/または計算集計の冗長性を排除するために行われます。
GROUP BY句は、SELECTステートメントのWHERE句の後に続き、ORDERBY句の前にあります。
構文
GROUPBY句の基本的な構文を以下に示します。GROUP BY句は、WHERE句の条件に従う必要があり、ORDER BY句を使用する場合は、その前に配置する必要があります。
SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnNGROUPBY句では複数の列を使用できます。グループ化に使用している列が何であれ、その列がcolumn-listで使用可能であることを確認してください。
例
次のようなレコードを持つテーブル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)各顧客の給与の合計額を知りたい場合、GROUPBYクエリは次のようになります-
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)次のように、ORDERBY句をGROUPBY句と一緒に使用しましょう-
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クエリは、より大きなクエリで使用するための補助ステートメントを作成する方法を提供します。複雑で大きなクエリを、読みやすい単純な形式に分解するのに役立ちます。これらのステートメントは、共通テーブル式またはCTEと呼ばれることが多く、1つのクエリに対してのみ存在する一時テーブルを定義するものと考えることができます。
CTEクエリであるWITHクエリは、サブクエリが複数回実行される場合に特に役立ちます。一時テーブルの代わりにも同様に役立ちます。集計を1回計算し、クエリでその名前（複数回の場合もあります）で参照できるようにします。
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
例
次のようなレコードを持つテーブル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)ここで、次のように、RECURSIVEキーワードとWITH句を使用してクエリを記述し、20000未満の給与の合計を求めます。
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)HAVING句を使用すると、関数の結果が特定の条件を満たす特定の行を選択できます。
WHERE句は、選択した列に条件を設定しますが、HAVING句は、GROUPBY句によって作成されたグループに条件を設定します。
構文
以下は、SELECTクエリでのHAVING句の位置です。
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BYHAVING句は、クエリでGROUP BY句の後に続く必要があり、使用する場合はORDERBY句の前にもある必要があります。以下は、HAVING句を含むSELECTステートメントの構文です。
SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2例
次のようなレコードを持つテーブル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 キーワードをSELECTステートメントと組み合わせて使用して、重複するすべてのレコードを削除し、一意のレコードのみをフェッチします。
テーブルに複数の重複レコードがある場合があります。このようなレコードをフェッチするときは、重複するレコードをフェッチするのではなく、一意のレコードのみをフェッチする方が理にかなっています。
構文
重複レコードを排除するためのDISTINCTキーワードの基本構文は次のとおりです。
SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]例
次のようなレコードを持つテーブル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)さあ、使ってみましょう DISTINCT 上記のSELECTクエリでキーワードを入力して結果を確認します-
testdb=# SELECT DISTINCT name FROM COMPANY;これにより、重複するエントリがない場合に次の結果が生成されます-
name
-------
Teddy
Paul
Mark
David
Allen
Kim
James
(7 rows)制約は、テーブルのデータ列に適用されるルールです。これらは、無効なデータがデータベースに入力されるのを防ぐために使用されます。これにより、データベース内のデータの正確性と信頼性が保証されます。
制約は、列レベルまたはテーブルレベルにすることができます。列レベルの制約は1つの列にのみ適用されますが、テーブルレベルの制約はテーブル全体に適用されます。列のデータ型を定義すること自体が制約です。たとえば、タイプDATEの列は、列を有効な日付に制約します。
以下は、PostgreSQLで利用可能な一般的に使用される制約です。
NOT NULL Constraint −列にNULL値を含めることができないようにします。
UNIQUE Constraint −列のすべての値が異なることを確認します。
PRIMARY Key −データベーステーブルの各行/レコードを一意に識別します。
FOREIGN Key −他のテーブルの列に基づいてデータを制約します。
CHECK Constraint − CHECK制約は、列のすべての値が特定の条件を満たすことを保証します。
EXCLUSION Constraint − EXCLUDE制約は、指定された演算子を使用して指定された列または式で2つの行が比較された場合、これらの比較のすべてがTRUEを返すわけではないことを保証します。
NOTNULL制約
デフォルトでは、列は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テーブルで、2人以上が同じ年齢にならないようにすることができます。
例
たとえば、次の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つだけ含めることができます。複数のフィールドが主キーとして使用される場合、それらはcomposite key。
テーブルのいずれかのフィールドに主キーが定義されている場合、そのフィールドの値が同じである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制約
CHECK制約を使用すると、条件はレコードに入力されている値をチェックできます。条件がfalseと評価された場合、レコードは制約に違反し、テーブルに入力されません。
例
たとえば、次の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の要点は、構築して施行に使用するインデックスのタイプです。
データベースごとに1回 、コマンドCREATE EXTENSIONbtree_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: conflicting key value violates exclusion constraint "company7_name_age_excl"
DETAIL: Key (name, age)=(Paul, 42) conflicts with existing key (name, age)=(Paul, 32).制約の削除
制約を削除するには、その名前を知っている必要があります。名前がわかっていれば、簡単に落とすことができます。それ以外の場合は、システムで生成された名前を見つける必要があります。ここでは、psqlコマンドの\ dテーブル名が役立ちます。一般的な構文は次のとおりです。
ALTER TABLE table_name DROP CONSTRAINT some_name;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は非常に大きなテーブルを生成する可能性があるため、適切な場合にのみ使用するように注意する必要があります。
以下は、CROSSJOINの構文です。
SELECT ... FROM table1 CROSS JOIN table2 ...上記の表に基づいて、次のようにCROSSJOINを記述できます。
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キーワードを使用できます。
以下は、INNERJOINの構文です。
SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_filed = table2.common_field;上記の表に基づいて、次のようにINNERJOINを記述できます。
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は、INNERJOINの拡張です。SQL標準では、LEFT、RIGHT、およびFULLの3種類の外部結合が定義されており、PostgreSQLはこれらすべてをサポートしています。
LEFT OUTER JOINの場合、最初に内部結合が実行されます。次に、テーブルT2のどの行とも結合条件を満たさないテーブルT1の各行について、結合された行がT2の列にNULL値で追加されます。したがって、結合されたテーブルには、T1の各行に対して常に少なくとも1つの行があります。
以下は、LEFT OUTERJOINの構文です。
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の各行に常に1つの行があります。
以下は、RIGHT OUTERJOINの構文です。
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 OUTERJOINの構文です。
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 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）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）別のテーブルは次のように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)次に、SELECTステートメントとUNION句を使用して、これら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)UNIONALL条項
UNION ALL演算子は、重複行を含む2つのSELECTステートメントの結果を組み合わせるために使用されます。UNIONに適用されるのと同じルールが、UNIONALL演算子にも適用されます。
構文
の基本構文 UNION ALL 次のとおりです-
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION ALL
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]ここで、指定された条件は、要件に基づいた任意の式にすることができます。
例
ここで、上記の2つのテーブルをSELECTステートメントで次のように結合します。
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欠測値を表すために使用される用語です。テーブルの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列は常に指定されたデータ型の明示的な値を受け入れる必要があることを意味します。NOTNULLを使用しなかった列が2つあります。したがって、これはこれらの列がNULLになる可能性があることを意味します。
NULL値を持つフィールドは、レコードの作成中に空白のままにされたフィールドです。
例
不明な値を他の値と比較する場合、結果は常に不明であり、最終結果に含まれないため、NULL値はデータの選択時に問題を引き起こす可能性があります。次の表を考えてみましょう。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)次に、の使用法を見てみましょう IS NOT NULL SALARYが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)以下はの使用法です IS NULL SALARYが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)別の名前を付けることで、テーブルまたは列の名前を一時的に変更できます。 ALIAS。テーブルエイリアスの使用は、特定のPostgreSQLステートメントでテーブルの名前を変更することを意味します。名前の変更は一時的な変更であり、実際のテーブル名はデータベース内で変更されません。
列エイリアスは、特定のPostgreSQLクエリの目的でテーブルの列の名前を変更するために使用されます。
構文
の基本構文 table エイリアスは次のとおりです-
SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];の基本構文 column エイリアスは次のとおりです-
SELECT column_name AS alias_name
FROM table_name
WHERE [condition];例
次の2つのテーブルについて考えてみます。（a）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）別のテーブルは次のように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)の使用例を見てみましょう COLUMN ALIAS ここで、COMPANY_IDはID列のエイリアスであり、COMPANY_NAMEは名前列のエイリアスです-
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 Triggers データベースコールバック関数であり、指定されたデータベースイベントが発生すると自動的に実行/呼び出されます。
以下は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キーワードは、関連する行の挿入、変更、または削除に関連して、トリガーアクションがいつ実行されるかを決定します。
トリガーは、関連付けられているテーブルが削除されると自動的に削除されます。
変更するテーブルは、トリガーがアタッチされているテーブルまたはビューと同じデータベースに存在する必要があり、 tablenameではなく database.tablename。
CONSTRAINTオプションを指定すると、制約トリガーが作成されます。これは通常のトリガーと同じですが、トリガーの発火のタイミングがSETCONSTRAINTSを使用して調整できる点が異なります。制約トリガーは、それらが実装する制約に違反した場合に例外を発生させることが期待されます。
構文
作成の基本的な構文 trigger 次のとおりです-
CREATE TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
-- Trigger logic goes here....
];ここに、 event_name上記のテーブルでのINSERT、DELETE、 UPDATE、およびTRUNCATEデータベース操作である可能性がありますtable_name。オプションで、テーブル名の後にFOR EACHROWを指定できます。
以下は、テーブルの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
);監査トライアルを継続するために、AUDITという新しいテーブルを作成します。このテーブルには、COMPANYテーブルに新しいレコードのエントリがあるたびにログメッセージが挿入されます。
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;インデックスは、データベース検索エンジンがデータ取得を高速化するために使用できる特別なルックアップテーブルです。簡単に言えば、インデックスはテーブル内のデータへのポインタです。データベースの索引は、本の裏にある索引と非常によく似ています。
たとえば、特定のトピックについて説明している本のすべてのページを参照する場合は、最初にすべてのトピックをアルファベット順にリストするインデックスを参照してから、1つ以上の特定のページ番号を参照する必要があります。
インデックスは、SELECTクエリとWHERE句を高速化するのに役立ちます。ただし、UPDATEステートメントとINSERTステートメントを使用すると、データ入力が遅くなります。インデックスは、データに影響を与えることなく作成または削除できます。
インデックスの作成には、CREATE INDEXステートメントが含まれます。これにより、インデックスに名前を付けたり、テーブルとインデックスを作成する列を指定したり、インデックスが昇順か降順かを指定したりできます。
インデックスは、UNIQUE制約と同様に一意にすることもできます。この点で、インデックスは、インデックスが存在する列または列の組み合わせでのエントリの重複を防ぎます。
CREATEINDEXコマンド
の基本構文 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);暗黙のインデックス
暗黙インデックスは、オブジェクトの作成時にデータベースサーバーによって自動的に作成されるインデックスです。主キー制約と一意の制約のインデックスが自動的に作成されます。
例
以下は、給与列のCOMPANYテーブルにインデックスを作成する例です。
# CREATE INDEX salary_index ON COMPANY (salary);それでは、COMPANYテーブルで利用可能なすべてのインデックスを使用してリストアップしましょう。 \d 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 コマンド-
DROPINDEXコマンド
PostgreSQLを使用してインデックスを削除できます DROPコマンド。パフォーマンスが低下または改善される可能性があるため、インデックスを削除するときは注意が必要です。
基本的な構文は次のとおりです-
DROP INDEX index_name;次のステートメントを使用して、以前に作成したインデックスを削除できます-
# DROP INDEX salary_index;インデックスはいつ避けるべきですか？
インデックスはデータベースのパフォーマンスを向上させることを目的としていますが、回避する必要がある場合があります。次のガイドラインは、インデックスの使用をいつ再検討する必要があるかを示しています。
小さなテーブルではインデックスを使用しないでください。
頻繁に大規模なバッチ更新または挿入操作が行われるテーブル。
多数のNULL値を含む列にインデックスを使用しないでください。
頻繁に操作される列にはインデックスを付けないでください。
PostgreSQL ALTER TABLE コマンドは、既存のテーブルの列を追加、削除、または変更するために使用されます。
また、ALTER TABLEコマンドを使用して、既存のテーブルにさまざまな制約を追加および削除します。
構文
の基本構文 ALTER TABLE 既存のテーブルに新しい列を追加するには、次のようにします。
ALTER TABLE table_name ADD column_name datatype;ALTERTABLEの基本構文から DROP COLUMN 既存のテーブルでは次のとおりです-
ALTER TABLE table_name DROP COLUMN column_name;を変更するためのALTERTABLEの基本構文 DATA TYPE テーブルの列の例は次のとおりです-
ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;追加するALTERTABLEの基本構文 NOT NULL テーブルの列に対する制約は次のとおりです-
ALTER TABLE table_name MODIFY column_name datatype NOT NULL;ALTERTABLEの基本構文から ADD UNIQUE CONSTRAINT テーブルへの変換は次のとおりです-
ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);ALTERTABLEの基本構文から ADD CHECK CONSTRAINT テーブルへの変換は次のとおりです-
ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);ALTERTABLEの基本構文から ADD PRIMARY KEY テーブルへの制約は次のとおりです-
ALTER TABLE table_name
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);ALTERTABLEの基本構文から DROP CONSTRAINT 表からは次のとおりです-
ALTER TABLE table_name
DROP CONSTRAINT MyUniqueConstraint;MySQLを使用している場合、コードは次のとおりです。
ALTER TABLE table_name
DROP INDEX MyUniqueConstraint;ALTERTABLEの基本構文から DROP PRIMARY KEY テーブルからの制約は次のとおりです-
ALTER TABLE table_name
DROP CONSTRAINT MyPrimaryKey;MySQLを使用している場合、コードは次のとおりです。
ALTER TABLE table_name
DROP PRIMARY KEY;例
私たちの考えてみましょう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 | 10000PostgreSQL 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)ビューは疑似テーブルです。つまり、それらは実際のテーブルではありません。それにもかかわらず、SELECTには通常のテーブルとして表示されます。ビューは、通常のテーブルから特定の列または特定の行を選択して、実際のテーブルのサブセットを表すことができます。ビューは、結合されたテーブルを表すこともできます。ビューには個別のアクセス許可が割り当てられているため、ビューを使用してテーブルアクセスを制限し、ユーザーがテーブルの特定の行または列のみを表示できるようにすることができます。
ビューには、テーブルのすべての行、または1つ以上のテーブルから選択した行を含めることができます。ビューは、1つまたは複数のテーブルから作成できます。これは、ビューを作成するために記述されたPostgreSQLクエリに依存します。
仮想テーブルの一種であるビューを使用すると、ユーザーは次のことができます。
ユーザーまたはユーザーのクラスが自然または直感的に感じる方法でデータを構造化します。
データへのアクセスを制限して、ユーザーが完全なテーブルではなく限られたデータのみを表示できるようにします。
レポートの生成に使用できるさまざまなテーブルのデータを要約します。
ビューは通常のテーブルではないため、ビューに対してDELETE、INSERT、またはUPDATEステートメントを実行できない場合があります。ただし、ルールを作成して、ビューでDELETE、INSERT、またはUPDATEを使用する場合のこの問題を修正できます。
ビューの作成
PostgreSQLビューは、 CREATE VIEWステートメント。PostgreSQLビューは、単一のテーブル、複数のテーブル、または別のビューから作成できます。
基本的なCREATEVIEW構文は次のとおりです-
CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];通常のPostgreSQLSELECTクエリで使用するのと非常によく似た方法で、SELECTステートメントに複数のテーブルを含めることができます。オプションのTEMPまたはTEMPORARYキーワードが存在する場合、ビューは一時スペースに作成されます。一時ビューは、現在のセッションの終了時に自動的に削除されます。
例
考えてみましょう、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)ビューの削除
ビューを削除するには、DROPVIEWステートメントを view_name。基本的なDROPVIEW構文は次のとおりです-
testdb=# DROP VIEW view_name;次のコマンドは、前のセクションで作成したCOMPANY_VIEWビューを削除します-
testdb=# DROP VIEW COMPANY_VIEW;トランザクションは、データベースに対して実行される作業単位です。トランザクションは、ユーザーが手動で行う場合でも、ある種のデータベースプログラムが自動的に行う場合でも、論理的な順序で実行される作業の単位またはシーケンスです。
トランザクションとは、データベースへの1つ以上の変更の伝播です。たとえば、レコードを作成する場合、レコードを更新する場合、またはテーブルからレコードを削除する場合は、テーブルでトランザクションを実行しています。データの整合性を確保し、データベースエラーを処理するには、トランザクションを制御することが重要です。
実際には、多くのPostgreSQLクエリをグループにまとめ、トランザクションの一部としてそれらすべてを一緒に実行します。
トランザクションのプロパティ
トランザクションには次の4つの標準プロパティがあり、通常は頭字語ACID-と呼ばれます。
Atomicity−ワークユニット内のすべての操作が正常に完了していることを確認します。それ以外の場合、トランザクションは失敗した時点で中止され、以前の操作は以前の状態にロールバックされます。
Consistency −トランザクションが正常にコミットされると、データベースの状態が適切に変更されるようにします。
Isolation −トランザクションが互いに独立して透過的に動作できるようにします。
Durability −システム障害が発生した場合でも、コミットされたトランザクションの結果または効果が持続することを保証します。
トランザクション制御
次のコマンドは、トランザクションを制御するために使用されます-
BEGIN TRANSACTION −トランザクションを開始します。
COMMIT −変更を保存するには、代わりに次を使用できます END TRANSACTION コマンド。
ROLLBACK −変更をロールバックします。
トランザクション制御コマンドは、DMLコマンドのINSERT、UPDATE、およびDELETEでのみ使用されます。これらの操作はデータベースで自動的にコミットされるため、テーブルの作成中または削除中には使用できません。
BEGINTRANSACTIONコマンド
トランザクションは、BEGINTRANSACTIONまたは単にBEGINコマンドを使用して開始できます。このようなトランザクションは通常、次のCOMMITまたはROLLBACKコマンドが検出されるまで持続します。ただし、データベースが閉じられた場合、またはエラーが発生した場合、トランザクションもロールバックします。
以下は、トランザクションを開始するための簡単な構文です。
BEGIN;
or
BEGIN TRANSACTION;COMMITコマンド
COMMITコマンドは、トランザクションによって呼び出された変更をデータベースに保存するために使用されるトランザクションコマンドです。
COMMITコマンドは、最後のCOMMITまたはROLLBACKコマンド以降のすべてのトランザクションをデータベースに保存します。
COMMITコマンドの構文は次のとおりです-
COMMIT;
or
END TRANSACTION;ROLLBACKコマンド
ROLLBACKコマンドは、データベースにまだ保存されていないトランザクションを元に戻すために使用されるトランザクションコマンドです。
ROLLBACKコマンドは、最後のCOMMITまたはROLLBACKコマンドが発行されてからのトランザクションを元に戻すためにのみ使用できます。
ROLLBACKコマンドの構文は次のとおりです-
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ここで、トランザクションを開始し、age = 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ここで、別のトランザクションを開始し、age = 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)ロックまたは排他ロックまたは書き込みロックは、ユーザーが行またはテーブル全体を変更できないようにします。UPDATEおよびDELETEによって変更された行は、トランザクションの期間中、自動的に排他的にロックされます。これにより、トランザクションがコミットまたはロールバックされるまで、他のユーザーが行を変更できなくなります。
ユーザーが他のユーザーを待たなければならないのは、同じ行を変更しようとしているときだけです。異なる行を変更する場合、待機する必要はありません。SELECTクエリは待つ必要はありません。
データベースは自動的にロックを実行します。ただし、場合によっては、ロックを手動で制御する必要があります。手動ロックは、LOCKコマンドを使用して実行できます。これにより、トランザクションのロックタイプとスコープを指定できます。
LOCKコマンドの構文
LOCKコマンドの基本的な構文は次のとおりです。
LOCK [ TABLE ]
name
IN
lock_modename−ロックする既存のテーブルの名前（オプションでスキーマ修飾）。テーブル名の前にONLYが指定されている場合、そのテーブルのみがロックされます。ONLYが指定されていない場合、テーブルとそのすべての子孫テーブル（存在する場合）がロックされます。
lock_mode−ロックモードは、このロックが競合するロックを指定します。ロックモードが指定されていない場合は、最も制限の厳しいモードであるACCESSEXCLUSIVEが使用されます。可能な値は、ACCESS SHARE、ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESSEXCLUSIVEです。
取得されると、ロックは現在のトランザクションの残りの間保持されます。UNLOCKTABLEコマンドはありません。ロックは常にトランザクションの終了時に解放されます。
DeadLocks
デッドロックは、2つのトランザクションが相互に操作を終了するのを待っているときに発生する可能性があります。PostgreSQLはそれらを検出し、ROLLBACKで終了できますが、デッドロックは依然として不便な場合があります。アプリケーションでこの問題が発生しないようにするには、オブジェクトを同じ順序でロックするようにアプリケーションを設計してください。
アドバイザリーロック
PostgreSQLは、アプリケーション定義の意味を持つロックを作成するための手段を提供します。これらはアドバイザリロックと呼ばれます。システムはそれらの使用を強制しないので、それらを正しく使用するのはアプリケーション次第です。アドバイザリロックは、MVCCモデルに不自然に適合するロック戦略に役立ちます。
たとえば、アドバイザリロックの一般的な使用法は、いわゆる「フラットファイル」データ管理システムに典型的な悲観的なロック戦略をエミュレートすることです。テーブルに格納されているフラグは同じ目的で使用できますが、アドバイザリロックはより高速で、テーブルの肥大化を回避し、セッションの終了時にサーバーによって自動的にクリーンアップされます。
例
次のようなレコードを持つテーブル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)次の例では、testdbデータベース内のCOMPANYテーブルをACCESSEXCLUSIVEモードでロックします。LOCKステートメントはトランザクションモードでのみ機能します-
testdb=#BEGIN;
LOCK TABLE company1 IN ACCESS EXCLUSIVE MODE;上記のPostgreSQLステートメントは次の結果を生成します-
LOCK TABLE上記のメッセージは、トランザクションが終了するまでテーブルがロックされていることを示しています。トランザクションを終了するには、トランザクションをロールバックまたはコミットする必要があります。
サブクエリまたは内部クエリまたはネストされたクエリは、別のPostgreSQLクエリ内のクエリであり、WHERE句内に埋め込まれています。
サブクエリは、取得するデータをさらに制限するための条件としてメインクエリで使用されるデータを返すために使用されます。
サブクエリは、SELECT、INSERT、UPDATE、およびDELETEステートメントで、=、<、>、> =、<=、INなどの演算子とともに使用できます。
サブクエリが従わなければならないいくつかのルールがあります-
サブクエリは括弧で囲む必要があります。
サブクエリのメインクエリに複数の列があり、選択した列を比較しない限り、サブクエリはSELECT句に1つの列しか含めることができません。
メインクエリではORDERBYを使用できますが、ORDERBYをサブクエリで使用することはできません。GROUP BYを使用して、サブクエリでORDERBYと同じ機能を実行できます。
複数の行を返すサブクエリは、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])例
次のレコードを持つ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を使用して同じCREATETABLEを使用して作成できます。ここで、完全な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_BKPテーブルが利用可能であると仮定します。これはCOMPANYテーブルのバックアップです。
次の例では、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のデータ型はsmallserial、serial、bigserialです。これらは真の型ではなく、一意の識別子列を作成するための表記上の便宜にすぎません。これらは、他のいくつかのデータベースでサポートされているAUTO_INCREMENTプロパティに似ています。
ご希望の場合は、シリアルユニーク制約を持っているか、主キーにする列を、それは今だけで他のデータ型と同様に、指定しなければなりません。
タイプ名serialは整数列を作成します。タイプ名bigserialはbigint列を作成します。BIGSERIALは、あなたが2つの以上の使用が予想される場合に使用されなければならない31台の寿命にわたって識別子を。タイプ名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のさまざまな種類の特権は次のとおりです。
- SELECT,
- INSERT,
- UPDATE,
- DELETE,
- TRUNCATE,
- REFERENCES,
- TRIGGER,
- CREATE,
- CONNECT,
- TEMPORARY,
- 実行、および
- USAGE
オブジェクトのタイプ（テーブル、関数など）に応じて、特権がオブジェクトに適用されます。ユーザーに特権を割り当てるには、GRANTコマンドを使用します。
GRANTの構文
GRANTコマンドの基本構文は次のとおりです。
GRANT privilege [, ...]
ON object [, ...]
TO { PUBLIC | GROUP group | username }privilege −値は次のようになります：SELECT、INSERT、UPDATE、DELETE、RULE、ALL。
object−アクセスを許可するオブジェクトの名前。可能なオブジェクトは次のとおりです：テーブル、ビュー、シーケンス
PUBLIC −すべてのユーザーを表す短い形式。
グループ group −特権を付与するグループ。
username−特権を付与するユーザーの名前。PUBLICは、すべてのユーザーを表す短い形式です。
特権は、REVOKEコマンドを使用して取り消すことができます。
REVOKEの構文
REVOKEコマンドの基本的な構文は次のとおりです。
REVOKE privilege [, ...]
ON object [, ...]
FROM { PUBLIC | GROUP groupname | username }privilege −値は次のようになります：SELECT、INSERT、UPDATE、DELETE、RULE、ALL。
object−アクセスを許可するオブジェクトの名前。可能なオブジェクトは次のとおりです：テーブル、ビュー、シーケンス
PUBLIC −すべてのユーザーを表す短い形式。
グループ group −特権を付与するグループ。
username−特権を付与するユーザーの名前。PUBLICは、すべてのユーザーを表す短い形式です。
例
特権を理解するために、最初に次のようにUSERを作成しましょう-
testdb=# CREATE USER manisha WITH PASSWORD 'password';
CREATE ROLEメッセージCREATEROLEは、USER "manisha"が作成されたことを示します。
次のようなレコードを持つテーブル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)次に、次のように、テーブルCOMPANYのすべての権限をユーザー「manisha」に付与します。
testdb=# GRANT ALL ON COMPANY TO manisha;
GRANTメッセージGRANTは、すべての特権がUSERに割り当てられていることを示します。
次に、次のようにユーザー「manisha」から特権を取り消しましょう-
testdb=# REVOKE ALL ON COMPANY FROM manisha;
REVOKEメッセージREVOKEは、すべての特権がUSERから取り消されたことを示します。
次のようにユーザーを削除することもできます-
testdb=# DROP USER manisha;
DROP ROLEメッセージDROPROLEは、USER'Manisha 'がデータベースから削除されたことを示します。
私たちは、章の日付/時刻データ型について議論していたデータ型。それでは、日付/時刻の演算子と関数を見てみましょう。
次の表に、基本的な算術演算子の動作を示します。
| オペレーター | 例 | 結果 |
|---|---|---|
| + | 日付「2001-09-28」+整数「7」 | 日付「2001-10-05」 |
| + | 日付「2001-09-28」+間隔「1時間」 | タイムスタンプ '2001-09-28 01:00:00' |
| + | 日付「2001-09-28」+時間「03:00」 | タイムスタンプ '2001-09-28 03:00:00' |
| + | 間隔「1日」+間隔「1時間」 | 間隔「1日01:00:00」 |
| + | タイムスタンプ '2001-09-28 01：00' +間隔'23時間 ' | タイムスタンプ '2001-09-29 00:00:00' |
| + | 時間「01:00」+間隔「3時間」 | 時間'04：00：00 ' |
| - | -間隔「23時間」 | 間隔 '-23：00：00' |
| - | 日付「2001-10-01」-日付「2001-09-28」 | 整数「3」（日） |
| - | 日付「2001-10-01」-整数「7」 | 日付「2001-09-24」 |
| - | 日付「2001-09-28」-間隔「1時間」 | タイムスタンプ '2001-09-27 23:00:00' |
| - | 時間「05:00」-時間「03:00」 | 間隔「02:00:00」 |
| - | 時間「05:00」-間隔「2時間」 | 時間'03：00：00 ' |
| - | タイムスタンプ '2001-09-2823：00'-間隔'23時間' | タイムスタンプ '2001-09-28 00:00:00' |
| - | 間隔「1日」-間隔「1時間」 | 間隔「1日-01：00：00」 |
| - | タイムスタンプ '2001-09-2903：00'-タイムスタンプ' 2001-09-27 12:00 ' | 間隔「1日15:00:00」 |
| * | 900 *間隔「1秒」 | 間隔'00：15：00 ' |
| * | 21 *間隔「1日」 | 間隔「21日」 |
| * | 倍精度「3.5」*間隔「1時間」 | 間隔「03:30:00」 |
| / | 間隔「1時間」/倍精度「1.5」 | 間隔「00:40:00」 |
以下は、利用可能なすべての重要な日付と時刻に関連する機能のリストです。
| S.いいえ。 | 機能と説明 |
|---|---|
| 1 | 年齢（） 引数を引く |
| 2 | 現在の日付/時刻（） 現在の日時 |
| 3 | DATE_PART（） サブフィールドを取得する（抽出に相当） |
| 4 | エキス（） サブフィールドを取得 |
| 5 | ISFINITE（） 有限の日付、時刻、間隔をテストします（+/-無限大ではありません） |
| 6 | 正当化する 間隔を調整する |
AGE（タイムスタンプ、タイムスタンプ）、AGE（タイムスタンプ）
| S.いいえ。 | 機能と説明 |
|---|---|
| 1 | AGE(timestamp, timestamp) 2番目の引数のTIMESTAMP形式で呼び出されると、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現在の日付/時刻（）
PostgreSQLは、現在の日付と時刻に関連する値を返す多くの関数を提供します。以下はいくつかの機能です-
| S.いいえ。 | 機能と説明 |
|---|---|
| 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.いいえ。 | 機能と説明 |
|---|---|
| 1 | transaction_timestamp() これはCURRENT_TIMESTAMPと同等ですが、返される内容を明確に反映するように名前が付けられています。 |
| 2 | statement_timestamp() 現在のステートメントの開始時刻を返します。 |
| 3 | clock_timestamp() 実際の現在時刻を返すため、1つのSQLコマンド内でも値が変化します。 |
| 4 | timeofday() 実際の現在の時刻を返しますが、タイムゾーン値のタイムスタンプではなく、フォーマットされたテキスト文字列として返します。 |
| 5 | now() これは、transaction_timestamp（）と同等の従来のPostgreSQLです。 |
DATE_PART（テキスト、タイムスタンプ）、DATE_PART（テキスト、間隔）、DATE_TRUNC（テキスト、タイムスタンプ）
| S.いいえ。 | 機能と説明 |
|---|---|
| 1 | DATE_PART('field', source) これらの関数はサブフィールドを取得します。フィールドのパラメータは文字列値ではなく名前である必要があります。 有効なフィールド名は、century、day、decade、dow、doy、epoch、hour、isodow、isoyear、microseconds、millennium、milses、minute、month、quarter、second、timezone、timezone_hour、timezone_minute、week、yearです。 |
| 2 | DATE_TRUNC('field', source) この関数は、概念的には数値の切り捨て関数に似ています。sourceは、timestamp型またはinterval型の値式です。フィールドは、入力値を切り捨てる精度を選択します。戻り値のタイプはtimestampまたはintervalです。 有効な値のフィールドは以下のとおりです。マイクロ秒、ミリ秒、秒、分、時間、日、週、月、四半期、年、十年、世紀、千年紀 |
以下は、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)EXTRACT（タイムスタンプからのフィールド）、EXTRACT（間隔からのフィールド）
ザ・ EXTRACT(field FROM source)関数は、日付/時刻の値から年や時間などのサブフィールドを取得します。ソースは、型の値式でなければならないタイムスタンプ、時刻、または間隔。フィールドが選択どのフィールドが元の値から抽出することが識別子または文字列です。EXTRACT関数は、倍精度型の値を返します。
有効なフィールド名は次のとおりです（DATE_PART関数のフィールド名と同様）：century、day、decade、dow、doy、epoch、hour、isodow、isoyear、microseconds、millennium、mismilles、minute、month、quarter、second、timezone、timezone_hour 、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)ISFINITE（日付）、ISFINITE（タイムスタンプ）、ISFINITE（間隔）
| S.いいえ。 | 機能と説明 |
|---|---|
| 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)JUSTIFY_DAYS（interval）、JUSTIFY_HOURS（interval）、JUSTIFY_INTERVAL（interval）
| S.いいえ。 | 機能と説明 |
|---|---|
| 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 functionsは、ストアドプロシージャとも呼ばれ、通常はデータベース内の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テーブルのレコードの総数を返します。次のレコードを持つ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組み込み関数のリストです-
PostgreSQLCOUNT関数-PostgreSQLCOUNT集計関数は、データベーステーブルの行数をカウントするために使用されます。
PostgreSQLMAX関数-PostgreSQLMAX集計関数を使用すると、特定の列の最大（最大）値を選択できます。
PostgreSQLMIN関数-PostgreSQLMIN集計関数を使用すると、特定の列の最小（最小）値を選択できます。
PostgreSQLAVG関数-PostgreSQLAVG集計関数は、特定のテーブル列の平均値を選択します。
PostgreSQLSUM関数-PostgreSQLSUM集計関数を使用すると、数値列の合計を選択できます。
PostgreSQLARRAY関数-PostgreSQLARRAY集計関数は、nullを含む入力値を連結して配列に入れます。
PostgreSQL数値関数-SQLで数値を操作するために必要なPostgreSQL関数の完全なリスト。
PostgreSQL文字列関数-PostgreSQLで文字列を操作するために必要なPostgreSQL関数の完全なリスト。
このチュートリアルでは、 libpqxxライブラリ。PostgreSQLの公式C ++クライアントAPIです。libpqxxのソースコードはBSDライセンスの下で入手できるので、自由にダウンロードして他の人に渡し、変更し、販売し、自分のコードに含め、選択した人と変更を共有することができます。
インストール
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 installC / C ++ PostgreSQLインターフェースの使用を開始する前に、 pg_hba.conf PostgreSQLインストールディレクトリにファイルを追加し、次の行を追加します-
# 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.いいえ。 | APIと説明 |
|---|---|
| 1 | pqxx::connection C( const std::string & dbstring ) これは、データベースへの接続に使用されるtypedefです。ここで、dbstringは、たとえば、データベースに接続するために必要なパラメーターを提供します。dbname = testdb user = postgres password=pass123 hostaddr=127.0.0.1 port=5432。 接続が正常にセットアップされると、さまざまな便利な関数publicfunctionを提供する接続オブジェクトを使用してCが作成されます。 |
| 2 | C.is_open() メソッドis_open（）は、接続オブジェクトのパブリックメソッドであり、ブール値を返します。接続がアクティブな場合、このメソッドはtrueを返し、そうでない場合はfalseを返します。 |
| 3 | C.disconnect() このメソッドは、開いているデータベース接続を切断するために使用されます。 |
| 4 | pqxx::work W( C ) これは、接続Cを使用してトランザクションオブジェクトを作成するために使用されるtypedefであり、最終的にはトランザクションモードで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 successfullyINSERT操作
次の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 successfullySELECT操作
次の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 successfullyUPDATE操作
次の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 successfullyDELETE操作
次の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インストール
JavaプログラムでPostgreSQLの使用を開始する前に、マシンにPostgreSQLJDBCとJavaがセットアップされていることを確認する必要があります。マシンへのJavaのインストールについては、Javaチュートリアルを確認できます。それでは、PostgreSQLJDBCドライバーのセットアップ方法を確認しましょう。
postgresql-jdbcリポジトリから最新バージョンのpostgresql-（VERSION）.jdbc.jarをダウンロードします。
ダウンロードしたjarファイルpostgresql-（VERSION）.jdbc.jarをクラスパスに追加するか、以下の例で説明するように、-classpathオプションと一緒に使用できます。
次のセクションでは、JavaJDBCの概念についてほとんど知識がないことを前提としています。持っていない場合は、以下で説明する概念に慣れるために、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");
}
}上記のプログラムをコンパイルして実行する前に、 pg_hba.conf PostgreSQLインストールディレクトリにファイルを追加し、次の行を追加します-
# 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に接続しましょう。ここでは、postgres ユーザーIDとして 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");
}
}プログラムをコンパイルして実行すると、COMPANYテーブルが作成されます。 testdb データベースと次の2行を表示します-
Opened database successfully
Table created successfullyINSERT操作
次の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 successfullySELECT操作
次の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 successfullyUPDATE操作
次の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 successfullyDELETE操作
次の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インストール
PHP 5.3.xの最新リリースでは、PostgreSQL拡張機能がデフォルトで有効になっています。を使用して無効にすることができます--without-pgsqlコンパイル時。それでも、yumコマンドを使用してPHP-PostgreSQLインターフェース-をインストールできます。
yum install php-pgsqlPHP PostgreSQLインターフェースの使用を開始する前に、 pg_hba.conf PostgreSQLインストールディレクトリにファイルを追加し、次の行を追加します-
# IPv4 local connections:
host all all 127.0.0.1/32 md5postgresサーバーが実行されていない場合は、次のコマンドを使用して起動/再起動できます。
[root@host]# service postgresql restart
Stopping postgresql service: [ OK ]
Starting postgresql service: [ OK ]Windowsユーザーは、この拡張機能を使用するためにphp_pgsql.dllを有効にする必要があります。このDLLは、PHP5.3.xの最新リリースのWindowsディストリビューションに含まれています。
詳細なインストール手順については、PHPチュートリアルとその公式Webサイトを確認してください。
PHPインターフェースAPI
以下は重要なPHPルーチンであり、PHPプログラムからPostgreSQLデータベースを操作するための要件を満たすことができます。より洗練されたアプリケーションを探している場合は、PHPの公式ドキュメントを調べることができます。
| S.いいえ。 | APIと説明 |
|---|---|
| 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 ) このルーチンは、PostgreSQL結果リソースの行数（たとえば、SELECTステートメントによって返される行数）を返します。 |
| 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);
?>上記のプログラムを実行すると、COMPANYテーブルが作成されます。 testdb 次のメッセージが表示されます-
Opened database successfully
Table created successfullyINSERT操作
次の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 successfullySELECT操作
次の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 successfullyUPDATE操作
次の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 successfullyDELETE操作
次の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プログラミング言語のデータベースアクセスモジュールであるPerlDBIモジュールを使用して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 installDBI用の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 installPerl PostgreSQLインターフェースの使用を開始する前に、 pg_hba.conf PostgreSQLインストールディレクトリにファイルを追加し、次の行を追加します-
# IPv4 local connections:
host all all 127.0.0.1/32 md5postgresサーバーが実行されていない場合は、次のコマンドを使用して起動/再起動できます。
[root@host]# service postgresql restart
Stopping postgresql service: [ OK ]
Starting postgresql service: [ OK ]DBIインターフェースAPI
以下は重要なDBIルーチンであり、PerlプログラムからSQLiteデータベースを操作するための要件を満たすことができます。より洗練されたアプリケーションを探している場合は、PerlDBIの公式ドキュメントを調べることができます。
| S.いいえ。 | APIと説明 |
|---|---|
| 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 is a statement handle returned by $dbh→prepare（$ sql）呼び出し。 |
| 5 | $sth→fetchrow_array() このルーチンは、データの次の行をフェッチし、フィールド値を含むリストとして返します。ヌルフィールドは、リスト内のundef値として返されます。 |
| 6 | $DBI::err これは$ h→errと同等です。ここで $h is any of the handle types like $dbh、 $sth, or $drh。これにより、最後に呼び出されたドライバメソッドからネイティブデータベースエンジンのエラーコードが返されます。 |
| 7 | $DBI::errstr これは$ h→errstrと同等です。ここで $h is any of the handle types like $dbh、 $sth, or $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();上記のプログラムを実行すると、COMPANYテーブルが作成されます。 testdb 次のメッセージが表示されます-
Opened database successfully
Table created successfullyINSERT操作
次の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 successfullySELECT操作
次の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 successfullyUPDATE操作
次の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 successfullyDELETE操作
次の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は、psycopg2モジュールを使用してPythonと統合できます。sycopg2は、Pythonプログラミング言語用のPostgreSQLデータベースアダプタです。psycopg2は、非常に小さくて速く、岩のように安定することを目的として作成されました。このモジュールは、デフォルトでPythonバージョン2.5.x以降と一緒に出荷されるため、個別にインストールする必要はありません。
マシンにインストールされていない場合は、yumコマンドを使用して次のようにインストールできます-
$yum install python-psycopg2psycopg2モジュールを使用するには、最初にデータベースを表すConnectionオブジェクトを作成する必要があります。次に、オプションで、すべてのSQLステートメントの実行に役立つカーソルオブジェクトを作成できます。
Pythonpsycopg2モジュールAPI
以下は重要なpsycopg2モジュールルーチンです。これは、PythonプログラムからPostgreSQLデータベースを操作するための要件で十分です。より洗練されたアプリケーションをお探しの場合は、Pythonpsycopg2モジュールの公式ドキュメントをご覧ください。
| S.いいえ。 | APIと説明 |
|---|---|
| 1 | psycopg2.connect(database="testdb", user="postgres", password="cohondob", host="127.0.0.1", port="5432") このAPIは、PostgreSQLデータベースへの接続を開きます。データベースが正常に開かれると、接続オブジェクトが返されます。 |
| 2 | connection.cursor() このルーチンは、 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 この読み取り専用属性は、最後の最後の実行*（）によって変更、挿入、または削除されたデータベース行の総数を返します。 |
| 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()上記のプログラムを実行すると、COMPANYテーブルが作成されます。 test.db 次のメッセージが表示されます-
Opened database successfully
Table created successfullyINSERT操作
次の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 successfullySELECT操作
次の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 successfullyUPDATE操作
次の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 successfullyDELETE操作
次の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