postgresqlで"select"した結果を変数に格納したい。

Question

【やりたいこと】
インプットデータのcsvファイルをpostgresqlのCOPY文で挿入し、
直前で挿入したデータのみ出力（select）したい。

【やってみてること】
１．DBに挿入する前に現在の時間を取得する。
２．DBに挿入する。
３．1で取得した日付以降のデータを出力(select)する。

【困っていること】
１で現在の時間を取得を変数に格納することができず、困っています。
ご教授頂けると助かります。

CREATE TABLE testDB
(ID SERIAL NOT NULL,
test1 INT4 NOT NULL,
test2 INT4 NOT NULL,
REG_DATE TIMESTAMP);

# 1 やってみたけどだめでした。select now()という文字と認識されてしまう。
\set zikan select now();
# 2
\COPY testDB(test1, test2, REG_DATE) FROM '/tmp/inputCSV/__test.csv' WITH CSV;
# 3
\COPY (SELECT * FROM testDB WHERE REG_DATE > zikan) TO '/tmp/testDB.csv' WITH CSV DELIMITER ',';

●CSVデータ
1,1,now()
2,2,now()
3,3,now()

Comments

[m.ts10806]

now()だけならそれ自体変数のようなのものなのであえてsetする必要はないような…？

[m.ts10806]

VIEWにするとどうでしょうか

[merci_children]

すいません。。。
無知で申し訳ないのですが、VIEWにするとはどういうことでしょうか。
(VIEWがわかってないです。)
また、now()を変数に格納したい理由は、selectで直前で挿入したデータのみを抽出したいからです。

[m.ts10806]

viewとは
https://thinkit.co.jp/free/article/0703/13/8/

[sazi]

現在時刻で挿入し、現在時刻以降を取得して、対象のデータはが存在するんですか？

[sazi]

COPY時点を記録して、その記録以降というなら分かりますが、その場合記録するのは変数じゃないですね。

[merci_children]

すいません。。。理解が追い付いてないのですが。
現在時刻で挿入し、現在時間以降を取得すれば直前で挿入した情報のみ取得できると考えたんですが、対象のデータは取得できませんか？
また、COPY時点を記録してその記録以降のデータを抽出するやり方がほかにあれば教えていただきたいです。

[m.ts10806]

csvにどのような情報があるのか提示いただいた方が良いかもしれませんね

[merci_children]

ありがとうございます。
CSVデータを追記いたしました。

Answer 1

CSV中にnow()を埋めてもコマンドみたいに実行されるわけではないですよ。単なる文字列です。
やるなら、テーブルの方に初期値を持たせます。

CREATE TABLE testDB
(ID SERIAL NOT NULL,
test1 INT4 NOT NULL,
test2 INT4 NOT NULL,
REG_DATE TIMESTAMP default current_timestamp
);

それから、その初期値の項目を含めないCSVにして、COPYすれば初期値が設定されます。

\COPY testDB(test1, test2) FROM '/tmp/inputCSV/__test.csv' WITH CSV;

その上で直前の時刻を知りたいのであれば、

select max(REG_DATE) from testDB

とすれば取得できます。
前回の質問の続きで、追加したものだけ取得したいということであれば以下で取得が可能です。

select * from testDB where REG_DATE=(select max(reg_date) from testDB)

追記

変数については以下を参考に。
クエリ結果をpsqlの変数やシェルの環境変数に格納

Comments

[merci_children]

いつも丁寧にご回答いただきありがとうございます。
とても助かっています。

試してみた結果うまく期待した結果を取得することができました。
ありがとうございました。

[sazi]

因みに、current_timestampはトランザクションの開始時刻なので、copyコマンドで処理された複数のデータは全て同じ時刻になっているから、=Max()で取得が可能です。

[merci_children]

追記ありがとうございます。
そうなのですね。
すべて同じ時刻になるのですね。
アドバイスありがとうございます。

[sazi]

そうです。リアルな時刻の場合は、clock_timestampを使用します。
なので、今回のような場合はcurrent_timestampを使用する事で、シェル側で前後の時間を取得してその期間内で取得するような面倒なことをしなくて済むわけです。

[merci_children]

なるほど。
ご丁寧にアドバイスをくださり、ありがとうございます。
引き続きがんばります。

Answer 2

COPY した時刻は処理が早ければ同時刻になる可能性があるので、そもそもそのやり方は無理があると思います。
ID SERIAL というフィールドがあるのであれば、max(ID) な列を取得すれば最後に COPY した列が取得できるのでは？

Comments

[merci_children]

ご回答ありがとうございます。
アドバイスの通り、そちらのほうがよそそうです。（固執していました。。。）

ただ、max(ID)を取得し、変数に格納する方法がわからないです。
教えていただきたいです。。。

[ssasaki]

こういうことを期待していますか？

select max(id) as reg_id from testDB \gset;
select :reg_id;

[merci_children]

ありがとうございます。
変数への格納はそのようにやるのですね。
勉強になります。