psqlでパスワードを省略する

psqlの実行時にパスワードを聞いてくるのを省略する手順です。

方法は、実行するコマンドの前に「set PGPASSWORD=パスワード」を実行するだけです。

 

コマンドを外部ファイルで実行する場合は、ファイルの一行目に入れておきます。実例はこちら

-- この1行を先に実行します。
-- xxxxxの箇所は実際のパスワードに変更して下さい。
set PGPASSWORD=xxxxx

-- ログインする
-- psql -h 接続先 -p ポート番号 -U ユーザー名 -d データベース名
psql -h localhost -p 5432 -U postgres -d dbname

月ごとに集計するSQL(date型、timestamp型の列)

date型、timestamp型の列で月ごとに集計するSQLです。

 

3パターン用意してみましたので。ご参考に。

1:月ごとの合計値の集計と並び順を指定する例

2:月ごとの合計値の集計と並び順+抽出条件を指定する例

3:月ごとの合計値の集計と並び順+抽出条件+集計後の結果でさらに絞る例

 

※ 次の例はdate型で実行していますが、timestampでもSQLは全く同じです。

年で集計するには、「'YYYYMM'」となっているところを「'YYYY'」にして実行します。

--用意したテーブル
CREATE TABLE precipitation         -- 降水量を保持するテーブル
(
    observation_date1 date,        -- 日付(date型の列)
    amount numeric,                -- 降水量を登録しておく列
    CONSTRAINT pk_precipitation1 PRIMARY KEY (observation_date1)
);


--1.月ごとに集計する例(合計値と並び順を指定)
select 
   to_char(observation_date1,'YYYYMM') as observation_month -- 年月に変換
   , sum(amount)             -- (降水量の)合計値
from precipitation
group by observation_month   -- グループ化
order by observation_month;  -- 年月の順番にする


--2.月ごとに集計する例(合計値と並び順、抽出条件を指定する)
select 
   to_char(observation_date1,'YYYYMM') as observation_month
   , sum(amount)                       
from precipitation
where observation_date1 >= '20190401'  -- 20190401以降を対象にする
group by observation_month           
order by observation_month;          


--3.月ごとに集計する例(合計値と並び順、抽出条件、集計後の結果でさらに絞る)
select 
   to_char(observation_date1,'YYYYMM') as observation_month
   , sum(amount)                     
from precipitation
where observation_date1 > '20190331'
group by observation_month          
having sum(amount) > 200   -- 集計結果(ここでは合計値)で抽出する
order by observation_month;        

 

あわせて読みたい

上のSQLで使用しているto_charはこちらで紹介しています

https://postgresweb.com/post-4975

 

 

 

月ごとに集計するSQL(文字列型の列)

文字型の列で月ごとに集計するSQLです。

 

3パターン用意してみましたので。ご参考に。

1:月ごとの合計値の集計と並び順を指定する例

2:月ごとの合計値の集計と並び順+抽出条件を指定する例

3:月ごとの合計値の集計と並び順+抽出条件+集計後の結果でさらに絞る例

 

年で集計するには、「'YYYYMM'」となっているところを「'YYYY'」にして実行します。

--用意したテーブル
CREATE TABLE precipitation                    -- 降水量を保持するテーブル
(
    observation_date2 character varying(10),  -- 日付(文字型の列)YYYY/MM/DD形式
    amount numeric,                           -- 降水量を登録しておく列
    CONSTRAINT pk_precipitation1 PRIMARY KEY (observation_date2)
);


--1.月ごとに集計する例(合計値と並び順を指定)
select 
   -- 一度日付型に変換し、年月に変換
   to_char(cast(observation_date2 as date),'YYYYMM') as observation_month
   , sum(amount)             -- (降水量の)合計値
from precipitation
group by observation_month   -- 年月でグループ化
order by observation_month;  -- 年月の順番にする


--2.月ごとに集計する例(合計値と並び順、抽出条件を指定する)
select 
   to_char(cast(observation_date2 as date),'YYYYMM') as observation_month
   , sum(amount)                       
from precipitation
-- 20190401以降を対象にする
where cast(observation_date2 as date) >= cast('20190401' as date)
group by observation_month           
order by observation_month;          


--3.月ごとに集計する例(合計値と並び順、抽出条件、集計後の結果でさらに絞る)
select 
   to_char(cast(observation_date2 as date),'YYYYMM') as observation_month
   , sum(amount)                     
from precipitation
where cast(observation_date2 as date) >= cast('20190401' as date)
group by observation_month          
having sum(amount) > 200  -- 集計結果(ここでは合計値)で抽出する
order by observation_month;            

中央値を取得する構文

中央値を取得する構文を紹介します。

--中央値を取得する
--※使用する時は、「列名」と「テーブル名」を変更して下さい
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY 列名) FROM テーブル名;

テーブルにあるデータが奇数個の時は、中央値のデータを取得し、

偶数個の時は、中央の二つの値の平均を中央値とします。

 

例:

テーブルのデータ数が奇数個(1,2,3,4,5)の時: 中央値 = 3

テーブルのデータ数が奇数個(1,2,3,4)の時 : 中央値 = (2+3) ÷ 2 = 2.5

 

補足

・パーセンタイルとは

簡単に言うと100個の測定値を値の順に並べて、

小さい方から90番目の値を「90パーセンタイル」あるいは「90%ile」と表現する。

中央値=50パーセンタイル。

 

・ PERCENTILE_CONTとは

ソート項目でソートした結果から、指定されたパーセント値の行の値を返します。

指定されたパーセント値が行と行の間に位置する場合、前後の行の平均値を返します。

 

timestampのwithout time zoneとwith time zoneの違い

PostgreSQLのtimestamp型には、「without time zone」と「with time zone」があり、これらの違いはずばり「タイムゾーン情報を含むか、含まないのか」です。※ タイムゾーン情報を含むのは「with time zone」で、含まないのは「without time zone」です。

 

使い分けるには、タイムゾーンの異なるクライアントから参照される可能性があるなら「with time zone」に、一方タイムゾーンを気にしなくてもよい場合は「without time zone」にするのがおすすめ。日本だけのサービスなら「without time zone」でよい様子。ご参考まで。

 

 

タイムゾーン(Time Zone)とは

日本語に訳すと時間帯のことで、同じ標準時(Standard Time/スタンダードタイム)を用いる地域全体を指す。日本の標準時は「日本標準時」で、協定世界時(UTC)より9時間進んでいるため「UTC+9」とも表記されます。英語では「Japan Standard Time」、略称を「JST」と表記します。

 

 

関連記事(日付操作)

現在日時、システム日時の取得

日時、時刻の書式設定をする(yyyymmdd形式)

日付の加算、週の加算、月の加算

2つの日付の差を計算する

誕生日から年齢を計算する

合計行を作る方法

合計行を作る方法は、ROLLUPを使います。

ここではその使い方と、表示結果を調整する方法を、実例で紹介します。

 

ROLLUPの構文

ROLLUPの構文は以下の形になります。

簡単にいうと、GROUP BYの時の構文「GROUP BY 列名』を

『GROUP BY ROLLUP(列名)』に変えるだけです。

--合計行を一緒に取得するSELECT文
select 列名1 , sum(列名2)
From テーブル名
group by ROLLUP(列名1);

--複数列でGROUP BYする時
select 列名1 , 列名2 , sum(列名3)
From テーブル名
group by ROLLUP(列名1 , 列名2);

 

実例で見てみます。こんなデータを用意しました(参考程度に)。

イメージは、学生と教科ごとの点数のデータです。

 

用意したテーブルはこんな感じ。

CREATE TABLE test_score
(
    student_code character varying(10) NOT NULL,  -- 学生コード
    student_name character varying(50) ,          -- 学生名
    subject_code character varying(10) NOT NULL,  -- 教科コード
    subject_name character varying(50) ,          -- 教科名
    score integer,                                -- 点数
    CONSTRAINT pk_test_score PRIMARY KEY (student_code, subject_code)
)

 

合計行を含めて取得するSQLはこうなります。

select 
	student_code               -- 学生コード
  , max(student_name)          -- 学生名
  , sum(score)                 -- 合計点
from test_score 
group by rollup(student_code)  -- rollupで合計行をだす
order by student_code;

 

結果の画像です。

合計行が4行目に表示されたのがわかります。

 

上の例では少し見た目が悪いのでこのようにSQLを変えてみます。

具体的には①合計行は学生コードがnullになっているので、わかりやすく「合計」と表示、

②学生名はmaxで取得している加減があり名前が表示されているので、nullにします。

select 
  --1列目:学生コードがnullなら「合計」と表示、それ以外ならそのまま表示
  case when student_code is null then '合計' 
  else student_code end as student_code

  --2列目:学生名は合計行ならnullにする
  , case when student_code is null then null 
    else max(student_name) end as student_name

  --3列名:合計行
  , sum(score) 
from test_score group by rollup(student_code) 
order by student_code;

 

結果の画像です。

合計と表示され、名前がnullになりうまくいきました。

 

integer型

integerとは

integerは、整数値を保存できる型です。(小数値は保存不可)

 

integerは4バイト(=32bit)のため、2の32乗 - 1 の数を表すことができます。

2の32乗 -1  =  4,294,967,296 - 1  =  4,294,967,295

※ -1するのは0からカウントするためです。

また+とーの値をとるため、最小値は-2,147,483,648。最大値は2,147,483,647となります。

 

実際に動作を確認(PostgreSQL12)してみると以下の結果になります。

・小数を登録しようとした時、エラーにはならず小数点以下は四捨五入される

・文字列の整数を登録しようとした時、整数は問題なし、小数はエラーとなる

・範囲外の数を登録しようとした時、エラーとなる

・数式で登録しようとした時、特に問題なし

-- 動作確認。(select文のため、そのままコピーしすぐ実行できるようになっています)

-- 1.小数を登録しようとした時、小数点以下は四捨五入される
select cast(2.4 as integer);               -- 結果: 2
select cast(2.5 as integer);               -- 結果: 3

-- 2.文字列の整数を登録しようとした時、整数は問題なし、小数はエラーとなる
select cast('2' as integer);               -- 結果: 2
select cast('2.4' as integer);             -- 結果: エラー

-- 3.範囲外の数を登録しようとした時、エラーとなる
select cast(-2147483649 as integer);       -- 結果: エラー
select cast(2147483648 as integer);        -- 結果: エラー

-- 4.数式で登録しようとした時、特に問題なし
select cast(2 + 3 as integer);             -- 結果: 5
select cast(2 + 3.5 as integer);           -- 結果: 6

 

 

smallint型、bigint型

smallint、bigintとは

smallint、bigintは、integerと同じく整数値を保存できる型です。(小数値は保存不可)

 

smallintは2バイト(16bit)のため、2の16乗 - 1 の数を表すことができます。

2の16乗 -1  =  65,536 - 1  =  65,535

※ -1するのは0からカウントするためです。

また+とーの値をとるため、最小値は-32,768。最大値は32,767となります。

 

bigintは8バイト(64bit)のため、2の64乗 - 1 の数を表すことができます。

2の64乗 -1  =  18,446,744,073,709,551,616 - 1  =  18,446,744,073,709,551,615 (1,844京)

※ -1するのは0からカウントするためです。

また+とーの値をとるため、最小値は-9,223,372,036,854,775,808

最大値は9,223,372,036,854,775,807となります。

 

PostgreSQL文書には、smallint型は通常はディスク容量に制限が付いている場合に使用し、

bigint型はintegerの範囲を超える場合に使用します。みたいなことが書いてありましたが、

ほぼほぼintegerで事足りるので、登場する機会はあまりないように感じます。

 

念のため実際に動作を確認(PostgreSQL12)してみます。

※ 最小値、最大値の値が変わる以外は動作に変わりはありません。

-- 動作確認(smallint)
-- select文のため、そのままコピーしすぐ実行できるようになっています

-- 1.小数を登録しようとした時、小数点以下は四捨五入される
select cast(2.4 as smallint);               -- 結果: 2
select cast(2.5 as smallint);               -- 結果: 3

-- 2.文字列の整数を登録しようとした時、整数は問題なし、小数はエラーとなる
select cast('2' as smallint);               -- 結果: 2
select cast('2.4' as smallint);             -- 結果: エラー

-- 3.範囲外の数を登録しようとした時、エラーとなる
select cast(-32769 as smallint);            -- 結果: エラー
select cast(32768 as smallint);             -- 結果: エラー

-- 4.数式で登録しようとした時、特に問題なし
select cast(2 + 3 as smallint);             -- 結果: 5
select cast(2 + 3.5 as smallint);           -- 結果: 6


-- 動作確認(bigint)
-- 1.小数を登録しようとした時、小数点以下は四捨五入される
select cast(2.4 as bigint);               -- 結果: 2
select cast(2.5 as bigint);               -- 結果: 3

-- 2.文字列の整数を登録しようとした時、整数は問題なし、小数はエラーとなる
select cast('2' as bigint);               -- 結果: 2
select cast('2.4' as bigint);             -- 結果: エラー

-- 3.範囲外の数を登録しようとした時、エラーとなる
select cast(-9223372036854775809 as bigint); -- 結果: エラー
select cast(9223372036854775808 as bigint);  -- 結果: エラー

-- 4.数式で登録しようとした時、特に問題なし
select cast(2 + 3 as bigint);             -- 結果: 5
select cast(2 + 3.5 as bigint);           -- 結果: 6

 

 

numeric型、decimal型

numeric、decimalとは

numericとdecimalは小数も可能な数値型です。

PostgreSQL文書を参照すると、「decimalとnumeric型は等価です。」とあるように、

全く意味が同じものです。なのでどちらを使っても同じです。

※ 心配性な方は「decimal numeric 違い」と調べてみてもわかると思います。

 

また+とーの値をとり、表現できる桁数は、

「小数点より上は131072桁まで、小数点より下は16383桁まで」と事実上、無限です。

超えるように検証するのはここでは諦めます。

 

また、numericはかっこ()をつける書き方と、かっこをつけない書き方があります。

かっこをつけると整数、小数点の桁数が指定でき、かっこをつけなければ最大まで格納できます。

-- numeric(X,Y)は全体でX桁で表示するうち、小数点はY桁となる
select cast(12.345 as numeric(3,1));       -- 結果: 12.3
select cast(1.2345 as numeric(3,2));       -- 結果: 1.23
-- 動作確認(numeric)
-- select文のため、そのままコピーしすぐ実行できるようになっています

-- 1.小数を登録しようとした時、小数点以下は四捨五入される
select cast(2.4 as numeric);               -- 結果: 2.4
select cast(2.5 as numeric);               -- 結果: 2.5

-- 2.文字列の整数を登録しようとした時、特に問題なし
select cast('2' as numeric);               -- 結果: 2
select cast('2.4' as numeric);             -- 結果: 2.4

-- 3.範囲外の数を登録しようとした時、特に問題なし
select cast(-32769 as numeric);            -- 結果: -32769
select cast(32768 as numeric);             -- 結果: 32768

-- 4.数式で登録しようとした時、特に問題なし
select cast(2 + 3 as numeric);             -- 結果: 5
select cast(2 + 3.5 as numeric);           -- 結果: 5.5

 

syntax error(SQL 状態: 42601)とは

syntax errorとは「構文エラー」のことです。

「42601」はPostgreSQLのエラーコードで、この番号も「構文エラー」を示しています。

 

構文エラーのためどこかに単純な間違いがある可能性が高いです。

(テーブル名を間違えた等なら別のエラーとなるため)

 

例えば

・ FROMやWHEREのつづり間違い

・ FROMやWHERE、HAVINGの順番の間違い

・ ()やシングルクオーテーションが閉じられていない

などが考えられます。ご参考まで。

boolean(論理型)

booleanとは、真偽値を保存できる型です。

 

ざっくりいうと以下のような特徴があります。

・ booleanは3つの値(true , false , null )のみをとる

・ true、falseに対応する数値や文字の値がある

※ 文字の場合、前後のスペースは無視される。 大文字小文字の区別はなし、全角はエラーとなる。

 trueに対応 … TRUE  , 't' , 'true'  , 'y' , 'yes' , 'on' , '1'

 falseに対応 … FALSE , 'f' , 'false' , 'n' , 'no' , 'off' , '0'

・ true , falseに対応できない値を代入しようとした時は、エラーが発生する

・ 次の動作検証でも触れるますが、上の値以外でもtrue , false , nullと判断されるものもある

 

動作検証

次の動作検証は、true、falseに対応する値が他にもあるか検証してみました。

castした結果がエラーとならなければ、それは「true or false or nullと判定できた」ということになります。

 

結果的には、't'だけでなく、'tr'でも'tru'でも'true'でもcastしてエラーにならないため、他にもありそうだということがわかりました。

ただし、統一されていないと可読性の面でも悪いと思うので、プロジェクト単位等では、いずれかの値に統一していたほうがいいと思います。

下の例はそのままコピーして実行できるので試してみて下さい。

--====================================
--booleanにtrueとして代入できる値を検証します
--※エラーになった場合は、trueとならない値だということ
--====================================
select cast(TRUE as boolean);     -- true

--文字列
--※下の例の場合、'o'以外はすべてtrueと同じ扱い
--結果がtrueとなればその文字は'true'と同じ意味となったということ
select cast('t' as boolean);      -- true
select cast('tr' as boolean);     -- true
select cast('tru' as boolean);    -- true
select cast('true' as boolean);   -- true
select cast('y' as boolean);      -- true
select cast('ye' as boolean);     -- true
select cast('yes' as boolean);    -- true
select cast(' yes ' as boolean);  -- true
select cast('o' as boolean);      -- エラー
select cast('on' as boolean);     -- true

--数値、数値の文字列
select cast(1 as boolean);        -- true(0:false、それ以外:true)
select cast(-999 as boolean);     -- true
select cast('1' as boolean);      -- true
select cast('2' as boolean);      -- エラー
select cast('-1' as boolean);     -- エラー

--====================================
--booleanにfalseとして代入できる値を検証します
--※エラーになった場合は、falseとならない値だということ
--====================================
select cast(FALSE as boolean);    -- false

--文字列
select cast('f' as boolean);      -- false
select cast('fa' as boolean);     -- false
select cast('fal' as boolean);    -- false
select cast('fals' as boolean);   -- false
select cast('false' as boolean);  -- false
select cast('n' as boolean);      -- false
select cast('no' as boolean);     -- false

--数値
select cast(0 as boolean);        -- true(0:false、それ以外:true)

--====================================
--空文字、nullの場合を検証
--====================================
select cast('' as boolean);       -- エラー
select cast(null as boolean);     -- null

 

PostgreSQLでのdual表の使い方

タイトルは「dual表の使い方」としていますが、PostgreSQLではdual表はありません

ではどうするかですが、そもそもselect文ではfromなしでの実行が可能です。

例えば次のようなselect文はそのまま実行可能です。

--次の5つのselect文は、from句がないですがそのまま実行可能です。
select '1';
select 'aa' as col1,'BB' as col2,'123' as col3;
select now();
select current_date;
select cast(1234.56 as integer);

--関数もfromなしで呼び出しが可能です。
select 関数名();
select 関数名(引数1 , 引数2 , ・・・);

--from句ありですが、この書き方もOKです。
select * from now();
select * from current_date;
select * from cast(1234.56 as integer);

当然ですが通常のテーブルから取得するには『select * From テーブル名』とします。

Oracleで使うように動作確認する時の参考にしてみて下さい。

pgAdmin4が起動しない時の対処方法

原因は不明ですが、突如としてpgAdmin4が「読み込み中」のまま進まない

という現象が発生するようになり、解決できたので紹介します。

 

解決方法の結論からいうと、レジストリの修正が必要でした。

PostgreSQLの再インストール等試しましたがうまくいかず

結局のところこの方法でうまくいきました。

 

ここからは手順になりますがレジストリをいじるので自己責任で。

(といってもそんなに難しくないので、お困りの際は参考にして下さい。)

 

① Windowsを押しながらRボタンを押す

② ファイル名を指定して実行より「regedit」入力してOKボタン

③ レジストリエディタが起動する

④ 「\HKEY_CLASSES_ROOT\.js\Content Type」までたどります

 

⑤ 「Content Type」を右クリック「修正」を押します。

⑥ 値のデータを「text/plain」から「text/javascript」に変更しOKを押します。(下の画像)

※ 変更前の値の「text/plain」は、メモ帳等に貼り付け戻せるようにしておくと安心です。

 

■変更前

 

■変更後

 

⑦ 最後にPCを再起動しpgAdminを再度起動する

※ ⑥の後にPostgreSQLのサービスを再起動しましたがうまくいかなかったため、

PCを再起動したところうまくいきました。同じ現象になった場合是非お試しを。