【postgreSQL】誕生月に該当する人を抽出して、結果をCSV出力する
環境
EC-CUBE2系
PostgreSQL9.6くらい
すぐできると思ったけどいろいろ調べないといけなくてハマってしまいました
使用したSQLやpsqlコマンドたち
-- 使用するSELECT文
SELECT name01, name02, EXTRACT(year from age(birth)) as age, birth::DATE, line_id FROM dtb_customer WHERE to_char(birth, 'MM') = {2桁文字列型のmonth} AND line_id IS NOT NULL;
-- 例
# SELECT name01, name02, EXTRACT(year from age(birth)) as age, birth::DATE FROM dtb_customer WHERE to_char(birth, 'MM') = '10';
name01 | name02 | age | birth |
--------+--------+-----+------------+-------------
てすと | てすと | 29 | 1988-10-10 |
-- CSV出力するSQL文
COPY {SQL文} TO {出力先のファイルパス} WITH CSV DELIMITER ',' HEADER;
-- 例
COPY (SELECT name01, name02, EXTRACT(year from age(birth)) as age, birth::DATE FROM dtb_customer WHERE to_char(birth, 'MM') = '10') TO '/vagrant/customer_test.csv' WITH CSV DELIMITER ',' HEADER;
-- psqlコマンド
$ psql -U {ユーザ名} -d {データベース名} -c "SELECT name01, name02, EXTRACT(year from age(birth)) as age, birth::DATE, line_id FROM dtb_customer WHERE to_char(birth, 'MM') = {2桁の文字列month} AND line_id IS NOT NULL" -A -F, > {ファイル名}
それぞれを解説
SELECT name01, name02, EXTRACT(year from age(birth)) as age, birth::DATE, line_id FROM dtb_customer WHERE to_char(birth, 'MM') = {2桁文字列型のmonth} AND line_id IS NOT NULL;
名前、年齢、誕生日をある誕生月を条件に出力してほしいと言われたので上記SQLを作りました
EC-CUBEには年齢カラムは存在しないので変換をしました
EXTRACT(year from age(birth)) as age
postgresにはage()関数っていうのがあります、
指定のタイムスタンプから現在までの差分を取ってくれるという関数です
age()関数は返り値がinterval型（〇year △month □days）なので、intervalからさらに年のみを出力するために、EXTRACT関数を使っています
参考： 9.9. 日付/時刻関数と演算子
birth::DATE
birthカラムはtimestamp型ですが、今回の出力結果は年月日しかいらなくて、「00:00:00」は不要なのでDATE型に変換する必要があります。
CAST()関数で変換できますが、もっと簡単に「カラム::型」でも変換可能です。
参考： 4.2.9. 型キャスト
to_char(birth, 'MM') = {2桁文字列型のmonth}
dtb_customerのbirthカラムはtimestamp型です。
今回、ある誕生月で出力しないといけなかったのでtimestamp型を変換する必要がありました。
birthカラムを第1引数にいれて、第2引数に出力結果をMM、つまり2桁の月をchar型で出力するという意味です
参考： 9.8. データ型書式設定関数
-- CSV出力するSQL文
COPY {SQL文} TO {出力先のファイルパス} WITH CSV DELIMITER ',' HEADER;
COPYはテーブルとファイル間で入出力を可能にするやつです
今回は出力なので「TO」って書きましたけど、「FROM」って書いてしまうとファイルをテーブルにコピーしちゃうので要注意
「WHITH」以下でCOPYにオプションを付与することができます。
上記SQL文は、CSV形式で、DELIMITERはカンマ、ヘッダー付き、というオプションを付けています
postgreSQLにログインして行う処理なので、ファイルの出力先はフルパスです
参考： COPY
と、いうことがわかっていざ本番で行おうとしたら、
ファイルパスに含まれているディレクトリの権限の都合で出力できませんでした。
postgresSQLにログインせず行わないといけないようです
psqlコマンドでCSV出力を行う
$ psql -U {ユーザ名} -d {データベース名} -c "SQL文" -A -F, > {ファイル名}
psqlコマンドでCSV出力を行います
ユーザ名、データベース名を入れて、
オプション「-c」でpsqlコマンドからSQL文を呼び出すことができます
「-A」は位置揃えなしコマンド、「-F」は区切り文字の設定で、今回はカンマで区切っています（デフォルトは「|」）
不等号の記号は要注意です
DBから{ファイル名}への出力なので「>」です
psqlコマンドの詳細は
$ psql --help
で見れます
参考： psql
Discussion