Search code examples
oraclesystimestamp

Insert SYSTIMESTAMP for Timestamp field

I have three timestamps in my SQL Table.

Column Name     Data Type   Nullable Data_Default

STATUS_TIMSTM   TIMESTAMP(6)    No  (null)
CREATED_TIMSTM  TIMESTAMP(6)    No  SYSTIMESTAMP  
UPDATED_TIMSTM  TIMESTAMP(6)    No  (null)

INSERT INTO "TABLE_NAME" ("STATUS_TIMSTM","CREATED_TIMSTM","UPDATED_TIMSTM")
VALUES(TIMESTAMP '2020-12-10 00:00:00', TIMESTAMP '2020-06-15 00:00:00',TIMESTAMP '2020-06-15 00:00:00');

The above works correctly.

How do I insert the current systimestamp?

I've tried several options: curdate(), now(), systimestamp().

I usually get errors such as Error report - SQL Error: ORA-00904: "NOW": invalid identifier 00904. 00000 - "%s: invalid identifier"

Solution

  • Since you already have a DATA DEFAULT, only inserting data in below format must populate the CREATED_TIMSTM column with current TIMESTAMP.

    INSERT INTO "TABLE_NAME" ("STATUS_TIMSTM","UPDATED_TIMSTM")
VALUES(TIMESTAMP '2020-12-10 00:00:00', TIMESTAMP '2020-06-15 00:00:00');

    Here is a simplified DB fiddle demonstrating the same.