「.sql」ファイルをコマンドプロンプトから実行する例です。
※ パスワードを聞いてくるのを省略するには、こちらを参照して下さい。
psql -f sqlファイルのパス -U ユーザー名 -d データベース名 -h 接続先 -p ポート
-- 実際の例
-- sqlファイルのパスを「"C:\sql\test.sql"」とします。
psql -f C:\script\test.sql -U postgres -d schooldb -h localhost -p 5432
psqlの実行時にパスワードを聞いてくるのを省略する手順です。
方法は、実行するコマンドの前に「set PGPASSWORD=パスワード」を実行するだけです。
コマンドを外部ファイルで実行する場合は、ファイルの一行目に入れておきます。実例はこちら。
-- この1行を先に実行します。
-- xxxxxの箇所は実際のパスワードに変更して下さい。
set PGPASSWORD=xxxxx
-- ログインする
-- psql -h 接続先 -p ポート番号 -U ユーザー名 -d データベース名
psql -h localhost -p 5432 -U postgres -d dbname
date型、timestamp型の列で月ごとに集計するSQLです。
3パターン用意してみましたので。ご参考に。
１：月ごとの合計値の集計と並び順を指定する例
２：月ごとの合計値の集計と並び順＋抽出条件を指定する例
３：月ごとの合計値の集計と並び順＋抽出条件＋集計後の結果でさらに絞る例
※ 次の例はdate型で実行していますが、timestampでもSQLは全く同じです。
※ 年で集計するには、「'YYYYMM'」となっているところを「'YYYY'」にして実行します。
--用意したテーブル
CREATE TABLE precipitation -- 降水量を保持するテーブル
(
observation_date1 date, -- 日付（date型の列）
amount numeric, -- 降水量を登録しておく列
CONSTRAINT pk_precipitation1 PRIMARY KEY (observation_date1)
);
--１．月ごとに集計する例（合計値と並び順を指定）
select
to_char(observation_date1,'YYYYMM') as observation_month -- 年月に変換
, sum(amount) -- （降水量の）合計値
from precipitation
group by observation_month -- グループ化
order by observation_month; -- 年月の順番にする
--２．月ごとに集計する例（合計値と並び順、抽出条件を指定する）
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;
--３．月ごとに集計する例（合計値と並び順、抽出条件、集計後の結果でさらに絞る）
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です。
3パターン用意してみましたので。ご参考に。
１：月ごとの合計値の集計と並び順を指定する例
２：月ごとの合計値の集計と並び順＋抽出条件を指定する例
３：月ごとの合計値の集計と並び順＋抽出条件＋集計後の結果でさらに絞る例
※ 年で集計するには、「'YYYYMM'」となっているところを「'YYYY'」にして実行します。
--用意したテーブル
CREATE TABLE precipitation -- 降水量を保持するテーブル
(
observation_date2 character varying(10), -- 日付（文字型の列）YYYY/MM/DD形式
amount numeric, -- 降水量を登録しておく列
CONSTRAINT pk_precipitation1 PRIMARY KEY (observation_date2)
);
--１．月ごとに集計する例（合計値と並び順を指定）
select
-- 一度日付型に変換し、年月に変換
to_char(cast(observation_date2 as date),'YYYYMM') as observation_month
, sum(amount) -- （降水量の）合計値
from precipitation
group by observation_month -- 年月でグループ化
order by observation_month; -- 年月の順番にする
--２．月ごとに集計する例（合計値と並び順、抽出条件を指定する）
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;
--３．月ごとに集計する例（合計値と並び順、抽出条件、集計後の結果でさらに絞る）
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」と表現する。
中央値＝５０パーセンタイル。
・ PERCENTILE_CONTとは
ソート項目でソートした結果から、指定されたパーセント値の行の値を返します。
指定されたパーセント値が行と行の間に位置する場合、前後の行の平均値を返します。
PostgreSQLのtimestamp型には、「without time zone」と「with time zone」があり、これらの違いはずばり「タイムゾーン情報を含むか、含まないのか」です。※ タイムゾーン情報を含むのは「with time zone」で、含まないのは「without time zone」です。
使い分けるには、タイムゾーンの異なるクライアントから参照される可能性があるなら「with time zone」に、一方タイムゾーンを気にしなくてもよい場合は「without time zone」にするのがおすすめ。日本だけのサービスなら「without time zone」でよい様子。ご参考まで。
日本語に訳すと時間帯のことで、同じ標準時（Standard Time/スタンダードタイム）を用いる地域全体を指す。日本の標準時は「日本標準時」で、協定世界時（UTC）より9時間進んでいるため「UTC+9」とも表記されます。英語では「Japan Standard Time」、略称を「JST」と表記します。
合計行を作る方法は、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は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は、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は小数も可能な数値型です。
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とは「構文エラー」のことです。
「42601」はPostgreSQLのエラーコードで、この番号も「構文エラー」を示しています。
構文エラーのためどこかに単純な間違いがある可能性が高いです。
（テーブル名を間違えた等なら別のエラーとなるため）
例えば
・ FROMやWHEREのつづり間違い
・ FROMやWHERE、HAVINGの順番の間違い
・ ()やシングルクオーテーションが閉じられていない
などが考えられます。ご参考まで。
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
タイトルは「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で使うように動作確認する時の参考にしてみて下さい。