PostgreSQLのnow()とstatement_timestamp()の違いについて

Question

PostgreSQLのバージョンは、12.1で、Windows Server 2019で動かしています。

MySQLのON UPDATE CURRENT_TIMESTAMPと同様の動き（データをアップデートした日時を記録する）
をPostgreSQLでも実現させたく、ファンクションとトリガーを使って実現しようとしています。

以下のような仕組みを考えています。

・テーブルを作成

CREATE TABLE aabbcc
(
    "id"                    integer DEFAULT nextval('aabbcc_id_seq'::regclass) NOT NULL,
    "cd"                    character varying(9) NOT NULL,
    "update_at"              timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    CONSTRAINT "aabbcc_pkey" PRIMARY KEY  ("id")  USING INDEX TABLESPACE "pg_default"
)
TABLESPACE "pg_default"
;

・ファンクションを作成

CREATE OR REPLACE FUNCTION fnc_update_at()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
  begin
    new.update_at := 'now()';
    return new;
  end;
$function$
;

・トリガーを作成

CREATE TRIGGER aabbcc_update_at
BEFORE UPDATE 
ON aabbcc
FOR EACH ROW
EXECUTE PROCEDURE fnc_update_at()
;

ファンクションの６行目が、「new.update_at := 'now()';」だとUPDATE時にupdate_atカラムが正しく更新されるのですが、
これだとトランザクションの開始日時になってしまうため、「new.update_at := 'statement_timestamp()';」
に変更して実行すると、データのアップデート時に以下のエラーになってしまいます。

ERROR:invalid input syntax for type timestamp with time zone:"statement_timestamp()"

now()もstatement_timestamp()も、戻り値の型はtimestamp with time zoneなので問題なく動くと
想定していましたが、エラーになってしまいました。

なぜエラーになってしまうのでしょうか？

Comments

[amura]

お答えになっていません（推測）
now()はクォートで括る事が出来るようです、'now'でも行けます。
new.update_at := (select 'now'::timestamp); の動きと同じの様です。　nowだけ特別？？
でもnow(),statement_timestamp()クォート取れば行けますけど

[new9]

ありがとうございます。
ご指摘頂いた通り、クォートを外して
「new.update_at := clock_timestamp();」で正しく動きました。

[sazi]

ここに回答と解決結果を書いても気付く人は少ないです。
ベストアンサーにでも、コメントとして書いておいた方が良いと思います。

Answer 1

「PostgreSQLのnow()とstatement_timestamp()の違いに関して」のみ
begin;するとわかりますが、end;までの間

now() 変わらない
transaction_timestamp()　変わらない当然
statement_timestamp() 変わる
clock_timestamp()　変わる
timeofday()　何故か変わる

があります。

マニュアルからすると

PostgreSQLでは利便性のために、下記に示されているような特別な日付/時刻入力値を
サポートしています。 infinityと-infinityの値は、特別にシステム内部で表現され、
変更されずに表示されます。 他のものは、単に簡略化された表記で、
読み込まれるときに通常の日付/時刻値に変換されます。
 （特にnowとその関連文字列は読み込まれるとすぐにその時点の値に変換されます。）
epoch
infinity
-infinity
now
today
tomorrow
yesterday
allballs

これらは’today()’でも動作しているので()までは見てないのでは？
'now' = 'now()' = 'now{}' = 'now$$'
利便性文字列の中で、nowだけ関数登録されていました、now()関数としても使えます。

Comments

[new9]

ありがとうございます。
私も実際に確認しました。
now()だと微妙に時間がずれていることがあったため、
実際にSQLを発行した時間に変更したいと思っております。

Answer 2

PostgreSQL 11のドキュメントから：

statement_timestamp()は現在の文の実行開始時刻を返すものです（より具体的にいうと、直前のコマンドメッセージをクライアントから受け取った時刻です）。 statement_timestamp()およびtransaction_timestamp()はトランザクションの最初のコマンドでは同じ値を返しますが、その後に引き続くコマンドでは異なる可能性があります。

（あくまで想像でしかないですが）明示的に対話的コマンド実行において使えるものであって、トリガーのような非対話的コマンド実行では使えないってことでしょうか？
コマンド実行時点の日時であって、トリガー実行時点の日時ではない、っていう。

おなじく：

clock_timestamp()は実際の現在時刻を返しますので、その値は単一のSQLコマンドであっても異なります。

なので、使うならclock_timestamp()でしょうか。

Comments

[new9]

ありがとうございます。
clock_timestamp()に変更して試してみましたが、
「ERROR:invalid input syntax for type timestamp with time zone:"clock_timestamp()"」
というエラーになってしまいました。

[退会済みユーザー]

AT TIME ZONE構文使って、timestamp without time zoneからtimestamp with time zoneに型変換したらいいと思います
https://www.postgresql.jp/document/11/html/functions-datetime.html

Answer 3

9.9.4. 現在の日付/時刻

statement_timestamp()は現在の文の実行開始時刻を返すものです（より具体的にいうと、直前のコマンドメッセージをクライアントから受け取った時刻です）

内容からして、**statement_timestamp()**をトリガー内で使用するのは不適切だと思います。
clock_timestampを使用してみて下さい。

Comments

[new9]

ありがとうございます。
同じ回答で恐縮ですが、
「ERROR:invalid input syntax for type timestamp with time zone:"clock_timestamp()"」
というエラーになってしまいました。