textとvarchar(character varying)の違い

textとvarcharは共に文字型の型ですが、結論から言うと違いはありません

あえて言うならvarcharは文字数を指定できるので、データ量の見積もりが可能という程度。

 

ちなみに最大文字数は、varchar、textともには10,485,760バイト(=1GB)です。

※ SJIS、EUC_JP、UNICODEなら全角・半角関わらず1文字=1バイト

 

また、調べた中には、次のような使い方(考え方)もあるようでした。

『 ある文字数までのものはすべてvarchar(XX)で定義。それを超えるものはすべてtextで定義。』

例えば200文字まではvarcharで定義し、それ以上の文字は(文字数を定義する必要はないとして)

すべてtextとするといった取り決めをする。という方法が紹介されていました。

これは取り決めの場合の例ですが、ご参考まで。

 

 

関連記事(文字列操作)

文字列の結合、nullの結合

改行コードで結合、置換する

文字数のカウント(length)、バイト数を取得する

文字の切り取り(Left、Right、Substring)

検索文字位置の取得(strpos)

全角半角スペースを除去する(trim)

upper(小文字を大文字に変換する)

大文字小文字を変換する、区別せず比較する

文字を置換する、複数置換する(replace、translate)

前0埋め、後ろ0埋め(lpad、rpad)

Nullの時に別の値に置き換える

 

OID(Object Identifier:オブジェクト識別子)とは

いまさら感は否めないですが、OIDについて調べたことのメモです。

OIDとは、オブジェクト識別子といいPostgreSQLの内部で管理する値です。

 

特徴をまとめると次の通り

・ データベース、テーブル、ロール、関数、操作、データ型などに割り振られる

※ テーブル作成時の『WITH OIDS』はPostgreSQL12で廃止

・ 0は使わない(0は無効な値の扱い)。符号なし4バイト(最大値:4,294,967,295)の値をとる

・ OIDは一周して同じ値をとる場合があるため、一意であると仮定してはならない

・ 主キーを持たないテーブル、重複行があるテーブルなどに有効

 

 

OIDを取得するSQL

各OIDを取得するSQLです。

--データベースのoidを取得する
select datid,datname from pg_stat_database;

--テーブルのoidを取得する
select relid,relname from pg_stat_all_tables;

--ユーザー(ロール)のoidを取得する
select oid,* from pg_roles;

テーブルが削除できない時の対処方法

ここでは、テーブルを削除しようとした時、エラーが発生した場合の対処方法を説明します。

 

 

1.外部キー制約がある時

外部キーで他のテーブルから参照されているテーブルを削除すると、

『constraint 外部キー名 on table テーブル名1 depends on table テーブル名2

というエラーメッセージが表示されます。

 

このメッセージは、『他のオブジェクトが依存しているため、テーブル名2を削除できません。』

という意味です。この場合、本来テーブル名2を削除したいのに、

テーブル名1から参照がかかっているため削除できないことを示しています。

 

このエラーの対処方法は次の3つです。

※すべてを行うのではなくどれか一つで大丈夫です。

 

対処方法1

参照しているテーブル(上のメッセージではテーブル名1)を先に消す

--参照元のテーブルを先に消した後、本来削除したいテーブルを消す
DROP TABLE 参照元のテーブル名;     -- 参照元のテーブルの削除
DROP TABLE 本来消したいテーブル名;   -- 削除したいテーブルの削除

 

対処方法2

参照しているテーブル(上のメッセージではテーブル名1)の外部キー制約を消す

--参照元のテーブルから外部キー制約を削除した後、本来消したいテーブルを消す
ALTER TABLE 参照元テーブル名 DROP CONSTRAINT 外部キー名; --外部キー削除
DROP TABLE 本来消したいテーブル名;                      -- 削除したいテーブルの削除

 

対処方法3

DROP ・・・ CASCADE文を使用する

--削除したいテーブル名の後ろに「CASCADE」をつける
DROP TABLE 本来消したいテーブル名 CASCADE;  -- 削除したいテーブルの削除

※ CASCADEをつけた場合、関わる制約・参照を削除します。この場合は外部キーを自動で削除します。

 

 

2.削除するテーブルがVIEWで使われている時

VIEWで使用されているテーブルを削除すると、

『view ビュー名 depends on table テーブル名1

というエラーメッセージが表示されます。

このメッセージは、『ビュー名はテーブル名1に依存しています。』という意味です。

 

対処方法1

削除したいテーブルを使っているビューを先に消す

--削除したいテーブルを使っているビューを先に消す
DROP VIEW ビュー名;              -- ビューの削除
DROP TABLE 本来消したいテーブル名;  -- 削除したいテーブルの削除

 

対処方法2

DROP ・・・ CASCADE文を使用する

--削除したいテーブル名の後ろに「CASCADE」をつける
DROP TABLE 本来消したいテーブル名 CASCADE;  -- 削除したいテーブルの削除

※ CASCADEをつけた場合、関わる制約・参照を削除します。この場合はビューを自動で削除します。

 

 

余談:エラーにならないケース

次の場合は、テーブル削除してもエラーとならないケースです。

※ すべての場合を検証していませんので、一例ととらえて下さい。

 

1)削除したテーブルが関数から参照されていた時

関数で使用されているテーブルを削除してもエラーにはなりませんでした。

当然ですが、関数実行時にテーブルが存在しないエラーが発生します。

 

2)削除したテーブルがトリガーから参照されていた時

関数同様にエラーになりません。

また、トリガーで呼ばれる関数から参照されていても1.同様エラーにはなりません。

PostgreSQLのサービス開始、停止、再起動コマンド

PostgreSQLのサービス停止・再起動コマンドを紹介します。

大きく2つの方法(① pg_ctlを使用する、② net start / stopを使用する)があり、それぞれについて説明します。②はWindowsの機能になるため、安全に停止するなら①のpg_ctlの場合がよいかと。

 

あわせて読みたい

Linux(CentOS)の場合はこちらを参照

https://postgresweb.com/postgresql13-service-start

 

 

 

方法①:pg_ctlを使用

pg_ctlを使用しサービスの開始、停止、再起動を行うコマンド例です。

--PostgreSQLサービスの開始(-Dでdataフォルダを指定する)
pg_ctl.exe start -D "C:\Program Files\PostgreSQL\data"

--PostgreSQLサービスの停止
pg_ctl.exe stop -D "C:\Program Files\PostgreSQL\data"
--停止モードは3種類あり(詳細は下記参照)
pg_ctl.exe stop -m smart -D 省略     
pg_ctl.exe stop -m fast -D 省略
pg_ctl.exe stop -m immediate -D 省略

--PostgreSQLサービスの再起動
pg_ctl.exe reload -D "C:\Program Files\PostgreSQL\data"

--PostgreSQLサービスの状態を確認
pg_ctl.exe status -D "C:\Program Files\PostgreSQL\data"

 

停止方法(smart , fast , immediate)について

「ーm」で指定できる停止方法には、smart , fast , immediateの3パターンがあります。

・smart:すべてのクライアントが切断するのを待ってからデータベースを停止

・fast:実行中のトランザクションをすべてロールバック+クライアント接続を強制的切断し、停止

・immediate は、サーバプロセスを即座に停止(DB再起動時にリカバリ処理が実行)

尚、デフォルトはfastになっています。(PostgreSQL9.5以降)

 

 

方法②:net start / stopを使用

net start / net stopを使用し、PostgreSQLのサービスを開始、終了、再起動する例です。

※ コマンドプロンプト(もしくはPowerShell)を管理者権限で実行して下さい。

管理者権限のやり方:コマンドプロンプトを右クリック > その他 > 管理者として実行

--PostgreSQLサービスの開始
net start postgresql-XXXXX
net start postgresql-x64-12  --例:PostgreSQL12の場合

--PostgreSQLサービスの停止
net stop postgresql-XXXXX
net stop postgresql-x64-12   --例:PostgreSQL12の場合

--PostgreSQLサービスを再起動する(停止 → 起動の順番に実行する)
net stop postgresql-x64-12   --例:PostgreSQL12の場合
net start postgresql-x64-12

 

 

方法②の補足:サービス名の調べ方

上で紹介した 『postgresql-x64-12』の箇所はサービス名を指定しています。

サービス名は、次の手順で確認します。

 

① エクスプローラー上のPCを右クリック > 管理をクリック

 

② 左赤枠内のサービスアプリケーション > サービスから

「postgresql-XXXX」で始まるサービス名を右クリック > プロパティをクリックします。

 

③ 左赤枠内の表示されているものがサービス名です。

この場合は、「postgresql-x64-12」がサービス名だとわかります。

 

 

各バージョンのサポート期限

各バージョンのサポート期限(EOL=End Of Life)をまとめました。

期限は初期リリースから5年です。

 

※ その5年後にはメジャー版は修正を含む最後のマイナーリリースを1回だけ行い

その時点で廃版(EOL)とみなされサポートされなくなります。最新情報はこちら

 

バージョン 初期リリース サポート終了期限
16 2023年9月 2028年11月
15 2022年10月 2027年11月
14 2021年09月 2026年09月
13 2020年09月 2025年11月
12 2019年10月 2024年11月
11 2018年10月 2023年11月
10 2017年10月 2022年11月
9.6 2016年09月 2021年11月
9.5 2016年01月 2021年02月
9.4 2014年12月 2020年02月
9.3 2013年09月 2018年11月
9.2 2012年09月 2017年11月
9.1 2011年09月 2016年10月
9.0 2010年09月 2015年10月

 

 

各バージョンのインストール方法

PostgreSQL13のインストール

PostgreSQL12のインストール

PostgreSQL11のインストール

インストールせずにPostgreSQLを使用する(ポータブル版)

 

 

PostgreSQLのリリース頻度

約1年に1度、新機能を含む新しいメジャーバージョンのリリースと、

少なくとも3ヶ月に一度のマイナーバージョンアップが行われます。

 

メジャーバージョンアップでは、仕様の追加・変更、バグ修正とセキュリティ修正が行われ、

マイナーバージョンアップでは主にバグ修正が行われます。

 

※ メジャーバージョンアップは、基本的に互換性がないため注意

※ 重要なバグや、重要なセキュリティ修正の場合は例外あり

※ マイナーリリースのスケジュールについては、こちらのロードマップ(英語)をご覧下さい。

 

 

 

PostgreSQLのバージョン表記

PostgreSQLのバージョン表記は次のようになっています。

 

PostgreSQL 10以降のバージョン

メジャーバージョンがあがると、バージョンの最初の数字が増えます。

例:PostgreSQL10、PosgreSQL11

 

・マイナーバージョンは、2番目の数値で表記されます。

例:PostgreSQL12.3、PostgreSQL12.4

 

PostgreSQL 10より前のバージョン(9.6以前)

・メジャーバージョンは、1番目または2番目の数値が増えます。

例:PostgreSQL9.5、PosgreSQL9.6

 

・マイナーバージョンは、3番目の数値で表記されます。

例:PostgreSQL9.5.3、PostgreSQL9.5.4

 

 

 

メジャーバージョンごとに追加された主な機能

過去のバージョン含み、メジャーバージョンごとに追加された主な機能一覧を引用させてもらいました。

表1.1 メジャーバージョンごとに追加された主な機能

出典:PostgreSQLとは? 初めてデータベースに触る人のための『PostgreSQL徹底入門 第4版』から紹介(CodeZine)https://codezine.jp/article/detail/11753

 

シーケンス(データ型のserial)とは

シーケンスとはINSERTで値を入れなくとも、自動で採番される列で、CREATE SEQUENCE文で作成することができます。またテーブル作成時、データ型に「serial」を指定した場合も同じくシーケンスとなります。シーケンスは自動で1から採番され、+1ずつされます。また範囲は、1~2,147,483,647の間になります。尚、作成したシーケンスは、pgAdminの「シーケンス」で確認できます。

 

 

シーケンスを作成(CREATE SEQUENCE)

CREATE文での列定義は次のように使います。

-- シーケンスの作成
CREATE SEQUENCE seq_name;

-- シーケンスの作成(初期値を設定する場合)
CREATE SEQUENCE seq_name START 1;

 

 

CREATE TABLEでのシーケンス作成

シーケンスはCREATE TABLEする時に作成することもでき、列定義に「serial」を指定すると、シーケンスとなります。また、シーケンス名は自動で命名されます。

-- CREATE TABLE時にシーケンス列を作成する
CREATE TABLE テーブル名
(・・・
seq serial,  -- 列定義をserialにするとシーケンスとなる
・・・)

-- すでにあるテーブルにシーケンス列を追加する
-- シーケンス名は''で囲むため注意
alter table テーブル名 add column 列名 integer NOT NULL DEFAULT nextval('シーケンス名');
alter table test add column testcol integer NOT NULL DEFAULT nextval('seq_name')

 

テーブル作成後、SQL文を確認するとシーケンス名が確認できます。

 

 

シーケンス値を設定する

シーケンス値は、次のSELECT文を実行することで値を設定できます。

設定後はその値から再度カウントアップを始めます。

-- シーケンス「seq_name」を1に設定
-- "1"を変更すればその値から再度カウントアップします
select SETVAL('seq_name', 1 , false);

-- 3つ目の引数(true / false)の違い 
SELECT setval('seq_name', 1000, true);   --シーケンスは1001からカウントアップ
SELECT setval('seq_name', 1000, false);  --シーケンスは1000からカウントアップ 

 

 

 

シーケンス値の値を確認

シーケンス値をSELECT文すると、現在の値等を取得できます。last_valueの値が現在値です。

-- シーケンス「seq_name」の値を確認する
select * from seq_name; 

 

 

動作確認

実際の動きを確認してみます。

① CREATE文でテーブルを作成します

CREATE TABLE test
(
	seq serial NOT NULL,  -- serial列
	hoge character(10),
	CONSTRAINT pk_test PRIMARY KEY (seq)
);

 

 

② INSERT文でデータを登録する

serial列に値を設定しないデータ登録を実行します。

 

③ SELECT文で登録データを確認する

SELECT文を実行し、serial列の値を確認する。

serial列にデータが登録されていることが確認できました。

列挙型(ENUM)の書き方

PostgreSQLでの列挙型(ENUM型)の作り方・使い方を紹介します。

--列挙型を定義
CREATE TYPE 列挙型の名前 AS ENUM (値1 , 値2 , 値3, ・・・);

--例:色の列挙型を定義する
CREATE TYPE color AS ENUM ('RED', 'YELLOW', 'BLUE');

 

 

列挙型の使い方

上の定義で作成した列挙型の使用する方法です。

--例:色の列挙型を定義する
CREATE TYPE color AS ENUM ('RED', 'YELLOW', 'BLUE');

--テーブル作成時に列挙型を使用
CREATE TABLE car (
    code character varying(8),
    name character varying(120),
    colortype color
);

--データ登録
INSERT INTO car VALUES('001','A車','RED');   --成功
INSERT INTO car VALUES('002','B車','GREEN'); --GREENはないためエラーになる

 

 

列挙型の削除

列挙型を削除する方法です。

すでに列挙型をテーブルで使用している場合は、削除できないので注意して下さい。

削除するにはそのテーブルを削除、またはそのテーブルの列定義を変更する必要があります。

--列挙型を削除
DROP TYPE color; --すでに使われている場合はエラーとなる

psqlの起動画面

まずpsqlが起動した画面から説明します。

下の画面がpsqlが起動した画面ですが、この状態でSQLが入力できる状態になっています。

 

 

コマンドプロンプトでpsqlを実行する

手順は簡単です。

 

①コマンドプロンプトを開く

WINDOWボタン(キーボード左下のWINDOWSマークのボタン)を押しながらキーボードのR

⇒ 名前の所にCMDと入力し、OKボタンを押すと起動します。

 

 

②コマンドを入力

コマンド「psql -U ユーザー名 -d データベース名」を入力します。間には半角スペースを入れます。

# psql -h 接続先 -p ポート番号 -U ユーザー名 -d データベース名 を入力
psql -h localhost -p 5432 -U postgres -d dbname

ユーザー名はポスグレのユーザー名です(postgresなど)。

ポート番号はデフォルトで「5432」です。

データベース名は自身で作成したデータベースの名前です。

 

 

③パスワードを入力

「ユーザ XXXのパスワード:」と表示されたらパスワードを入力します。

パスワードはインストール時に設定したものです。忘れた場合はこちら

 

上で紹介した画面のように「データベース名=#」となれば起動が完了です。

 

 

'psql' は、内部コマンドまたは外部コマンド・・・のメッセージが表示される時

このエラーメッセージについてはこちらでまとめていますので参考にして下さい。

 

接続中のセッションを確認する

次のSELECT文を実行すると、現在接続中のセッションを取得できます。

select * from pg_stat_activity 
where datname = 'データベース名';

 

 

接続中のセッションを切断する

接続中のセッションを切断するSQLです。

セッションの切断ができると、実行結果にtrueが返ります

-- 設定するpidはこの上の『接続中のセッションを確認する』で取得する
SELECT pg_terminate_backend(pidを設定); 

ANALYZE(アナライズ)とは

ANALYZEは、データベース内のテーブルの内容に関する統計情報を更新するコマンドです

 

発行されたクエリは、プランナ(オプティマイザ)が統計情報を参照して実行計画を作成します。

この統計情報はプランナが最適な実行計画を決定するために活用されているものです。

※統計情報といっても、テーブルのアクセス頻度のようなDBの統計情報ではない点に注意して下さい。

 

そのため、統計情報が最新化されていない場合、実行計画は最適なものを作成できない場合があり、

夜間バッチなどで大量のデータ更新などが行われた時、手動でANALYZEすることをお勧めします。

尚、AUTOVACUUMの実行時には、ANALYZEも同時に実行されます。

 

 

 

ANALYZEコマンド

ANALYZEコマンドを紹介します。(pgAdminでも実行できます)

-- ANALYZEコマンド
ANALYZE;

-- テーブル指定のANALYZE
ANALYZE テーブル名;

 

 

関連記事

https://postgresweb.com/post-5194

https://postgresweb.com/autovacuum

https://postgresweb.com/post-4047