大文字を小文字に変換する

大文字を小文字に変換するには、lowerを使用します。

-- 大文字を小文字に変換する
lower( 変換したい文字列 )

 

 

実行例

lowerの使用例です。※下の例はそのまま実行できます

-- 使用例 
select lower('ABC');     --"abc"
select lower('aBc');     --"abc"
select lower('ＡＢＣ');  --"ａｂｃ"

 

 

注意点

全角大文字が小文字にならない時、ロケールの設定が「C」になっている可能性があります。（ロケールが「C」の時は、全角大文字がlowerされません。）

その場合は、translateを使用して1文字ずつ変換すれば解決できます。

-- ロケールが「C」の時は、全角小文字がupperされない
select lower('ＡＢＣ');  --"ＡＢＣ"

-- translateを使用することで変換ができるようになります。
select translate('ＡＢＣ', 'ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ', 'ａｂｃｄｅｆｇｈｉｊｋｌｍｎｏｐｑｒｓｔｕｖｗｘｙｚ');   --"ａｂｃ"

データベースを削除する

データベースを削除するSQLです。

-- データベース名「testdb」を削除する構文
DROP DATABASE testdb;

-- IF EXSTSをつけると存在している場合のみ削除される
-- 存在していない場合は、エラーにはならずスキップされる
DROP DATABASE IF EXISTS testdb;

 

 

データをインデックス順に並び替える（CLUSTER）

CLUSTERを使うと、テーブルのデータをインデックス順に並び替えることができます。

並び替え最中はテーブルへアクセスすることはできません。

-- 構文
cluster テーブル名;
cluster テーブル名 using インデックス名;

-- verboseをつけることで処理内容の詳細が表示されます
cluster verbose テーブル名; 
cluster verbose テーブル名 using インデックス名;  

-- 例
cluster company;
cluster company using company_pk;
cluster verbose company;
cluster verbose company using company_pk;

 

INDEXを再構築する（REINDEX）

REINDEXとは、インデックスを再構築するものです。

REINDEXすることで、運用中にインデックス内にできてしまった空の領域が存在する状態を解消します。

-- インデックスを再構築する
reindex index インデックス名;

-- テーブルに対して再構築する（指定したテーブルの全インデックスを再作成します）
reindex table テーブル名;

-- データベースに対して再構築する（データベースのすべてのインデックスを再作成します）
-- 現在オープンしているデータベースのみインデックスを再構築できます
reindex database データベース名;

 

テーブルの列一覧を取得する

テーブルにある列一覧を取得するSQLです。

--'テーブル名'に存在する列一覧を取得
select * from information_schema.columns
where table_name='テーブル名' order by ordinal_position;

 

あわせて読みたい

その他のテーブル操作方法はこちらでも紹介しています。

https://postgresweb.com/summary-of-database-and-table-information-operations

 

 

テーブルの列一覧を取得する（コマンド）

テーブルの列一覧を取得するには、次のコマンドを実行します。

※ psqlでログイン後に「\d テーブル名」と実行します。

\d テーブル名

\d city      # テーブル「city」の列一覧を取得
\d company   # テーブル「company」の列一覧を取得

 

ALTE ROLE（＝ALTER USER）

ユーザー情報（パスワード、パスワード期限、権限）の変更をするSQLです（psql、pgAdminからも実行できます）。ALTER ROLEで例を書いていますが、ALTER USERとしても動作は同じです。

-- パスワードを変更
ALTER ROLE user1 with PASSWORD 'pass';  -- パスワード設定する
ALTER ROLE user1 with PASSWORD NULL;    -- パスワードなしにする

-- パスワードの期限を2021年の終わりまで有効とする
ALTER ROLE user1 WITH PASSWORD 'pass' VALID UNTIL 'Jan 1 2022';
ALTER ROLE user1 VALID UNTIL 'infinity';  -- 有効期限を無効にする

-- DBの作成権限
ALTER ROLE user1 CREATEDB;      -- 作成権限あり
ALTER ROLE user1 CREATEDB;      -- 作成権限なし

-- ユーザーの作成権限
ALTER ROLE user1 CREATEROLE;    -- 作成権限あり
ALTER ROLE user1 NOCREATEROLE;  -- 作成権限なし

-- ログイン権限
ALTER ROLE user1 LOGIN;         -- 権限あり
ALTER ROLE user1 NOLOGIN;       -- 権限なし

以上、ユーザー情報を変更する方法でした。

 

 

こちらも参考に

あわせて読みたい

https://postgresweb.com/createuser

 

あわせて読みたい

https://postgresweb.com/dropuser

 

あわせて読みたい

https://postgresweb.com/dropuser

 

ユーザーの削除

ユーザーを削除するには大きく2つの方法があり、それぞれの方法について紹介します。

① SQLでDROP USERを実行

② OSのコマンドラインからDROPUSERコマンドを実行

 

 

あわせて読みたい

ユーザーを作成する方法はこちらで紹介しています。

https://postgresweb.com/createuser

 

 

① SQLでDROP USERを実行

ユーザーを削除するSQL文です。

CREATE ROLEで作ったロールでも、DROP USERで削除できます。

-- ユーザー名「user1」を削除する構文
DROP USER user1;

-- IF EXSTSをつけると存在している場合のみ削除される
-- 存在していない場合は、エラーにはならずスキップされる
DROP USER IF EXISTS user1;

 

 

② OSのコマンドラインからDROPUSERコマンドを実行

ユーザーを作成するには、「dropuser」コマンドを実行します。

Windows機を例にしますが、dropuserコマンドは「bin\」配下のdropuser.exeを実行しています。

また画像をみるとわかるのですが、droproleというものはありません。

（コマンドでdroproleと実行してもエラーとなります）

 

dropuserコマンド

dropuserコマンドは次のようになります。

コマンドプロンプトから実行します。

dropuser -h サーバー名 -p ポート番号 -U 実行するユーザー名 削除するユーザー名

dropuser -h localhost -p 5432 -U postgres user1
dropuser -h 192.168.10.10 -p 5432 -U postgres user2
dropuser -h 192.168.10.10 -p 5432 -U postgres --if-exists user3
dropuser -U postgres user4  -- ローカル環境の場合

以上、ユーザーを削除する方法でした。

 

ユーザーの作成

最初にですが、ユーザーを作成するには大きく2つの方法があります。

① SQLでCREATE USERを実行

② OSのコマンドラインからCREATEUSERコマンドを実行

 

そうだねと思う人はよいのですが、私は最初この「CREATE USER（間にスペースあり）」と、「CREATEUSER（間にスペースなし）」2つの違いがよくわかりませんでした。同じ状況の人もいるのではないかと思いそれぞれについて、この記事で説明しようと思います。

 

※わかりづらいと感じるユーザーとロールの違いについてはこちらで説明してます。

記事自体は長くないので、お時間がある時に見てもらえると嬉しいです。

 

あわせて読みたい

https://postgresweb.com/difference-between-create-user-and-create-role

 

 

 

① SQLでCREATE USERを実行（ユーザーを作成するSQL）

ユーザーを作成する一つ目の方法は、SQL文を発行してユーザーを作成する方法です。

SQLなので構文と具体例を下記に紹介します。psqlやpgAdmin上から可能です。

※下の例は「CREATE USER」としていますが「CREATE ROLE」でも実行できます。

-- ユーザー名「user1」を作成する構文
CREATE USER user1;

-- ユーザー名「user1」をパスワード「pass」で作成する
CREATE USER user1 WITH PASSWORD 'pass';

-- ユーザー名「user1」をパスワード「pass」で作成し、
-- パスワードの期限を2021年の終わりまで有効とする（指定しない場合は永遠に有効）
CREATE USER user1 WITH PASSWORD 'pass' VALID UNTIL 'Jan 1 2022';

-- ユーザー名「user1」をパスワード「pass」で作成し、
-- スーパーユーザー権限とする
CREATE USER user5 WITH PASSWORD 'pass' SUPERUSER;

-- ユーザー名「user1」をパスワード「pass」で作成し、
-- 権限にデータベース作成権限、ユーザー作成権限を加える（スーパーユーザーではない）
CREATE USER user1 WITH PASSWORD 'pass' CREATEDB CREATEROLE;

 

オプションについての補足

・ PASSWORD 'パスワードの値' / PASSWORD NULL

パスワードのパスワードを消すにはNULLを指定。パスワード認証がONでパスワードをNULLにしていると、認証は常に失敗する。

 

・ VALID UNTIL 'timestamp'

ユーザーのパスワードが無効になる日時を設定。指定なしの場合、パスワードは永遠に有効。

 

・ SUPERUSER / NOSUPERUSER

新しいユーザーが「スーパーユーザ」となるかどうかの設定。指定なしの場合は、「NOSUPERUSER」となる。

 

・ CREATEDB / NOCREATEDB

データベースの作成権限。指定なし場合のデフォルトは「NOCREATEDB」。

 

・LOGIN / NOLOGIN

ログイン可能かの権限。デフォルトはCREATE USERの場合は「LOGIN」、CREATE ROLEの場合は「NOLOGIN」となる。

 

 

② OSのコマンドラインからCREATEUSERコマンドを実行

ユーザーを作成するには、「createuser」コマンドを実行します。

Windows機を例にしますが、createuserコマンドはbinのcreateuser.exeを実行しています。

また画像をみるとわかるのですが、createroleというものはありません。

（コマンドでcreateroleと実行してもエラーとなります）

 

createuserコマンド

createuserコマンドは次のようになります。

コマンドプロンプトから実行します。

createuser -h サーバー名 -p ポート番号 -U 実行するユーザー名 -d -r -s 作成するユーザー名

createuser -h localhost -p 5432 -U postgres -d -l -r -s -P user1
createuser -h 192.168.10.10 -p 5432 -U postgres -d -l -r -s -P user2
createuser -U postgres -d -l -r -s -P user3  -- ローカル環境の場合

 

オプション

設定できるオプションは、小文字=許可、大文字=禁止

h　…　サーバーのホスト名やIPを指定

p　…　ポート番号を指定

U　…　createuserを実行するユーザー

d　…　データベースの作成権限、D　…　データベースの作成できない権限

l 　…　ログイン権限、L　…　ログイン不可権限

r　…　ユーザー作成の権限、R　…　ユーザー作成ができない権限

s　…　スーパーユーザー権限の付与、S　…　スーパーユーザーでない権限の付与

P　…　パスワード設定する（厳密には実行時に、パスワード設定メッセージが表示される）

 

実行してみる

実行した様子は次のとおり。特に成功しました等のメッセージは出ません。

（エラーになればメッセージがでます）

以上、ユーザーを作成する方法でした。

 

 

関連記事

・ユーザー（ロール）の作成、削除の方法

・CREATE USERとCREATE ROLEの違い

・ユーザー情報を変更する（ALTER ROLE、ALTER USER）

・ユーザーを削除する（DROP USER文、DROPUSER）

nullif関数

nullif関数とは、2つの文字列が等しい時nullを返す関数です。

間違えやすい「文字列がNullの時、別の文字列に置換する」とは全く異なるので注意して下さい。

-- 文字列Aと文字列Bが等しい時nullを返す
-- AとBが異なる場合は、文字列Aを返します
nullif(A , B)

 

あわせて読みたい

https://postgresweb.com/post-1593

 

 

使用例

nullifを使った例を紹介します。そのまま実行できますので試してみて下さい。

--AとBが等しいためnullが返る
select nullif('abc','abc');   -- null
select nullif('','');         -- null

--AとBが等しくないためAが返る
select nullif('abc','ABC');   -- 'abc'
select nullif('abc',null);    -- 'abc'

構文

日付や時刻を指定のところ（精度といいます）で切り捨てるには、date_trunc関数を使います。

-- date_trunc（日付の切り捨て）の構文
date_trunc( 精度 , 日付・時刻 );

 

精度には'year'、'month'、'day'等を指定します。次のように実例を示すとわかりやすいです。

select cast('2021/2/14 01:23:45.678912' as timestamp); 
-- "2021-02-14 01:23:45.678912"を使って、date_truncを実行してみます

-- 1.年で切り捨て（精度に'year'を指定）
select date_trunc('year', cast('2021/2/14 01:23:45.678912' as timestamp));
-- "2021-01-01 00:00:00"


-- 2.月で切り捨て（精度に'month'を指定）
select date_trunc('month', cast('2021/2/14 01:23:45.678912' as timestamp));
-- "2021-02-01 00:00:00"


-- 3.日で切り捨て（精度に'day'を指定）
select date_trunc('day', cast('2021/2/14 01:23:45.678912' as timestamp));
-- "2021-02-14 00:00:00"


-- 4.時で切り捨て（精度に'hour'を指定）
select date_trunc('hour', cast('2021/2/14 01:23:45.678912' as timestamp));
-- "2021-02-14 01:00:00"


-- 5.分で切り捨て（精度に'minute'を指定）
select date_trunc('minute', cast('2021/2/14 01:23:45.678912' as timestamp));
-- "2021-02-14 01:23:00"


-- 6.秒で切り捨て（精度に'second'を指定）
select date_trunc('second', cast('2021/2/14 01:23:45.678912' as timestamp));
-- "2021-02-14 01:23:45"