I am using PostgreSQL. I am new to PostgreSQL, I used MySQL, in MySQL while creating table we will use ON UPDATE CURRENT_TIME STAMP. Sample query for MySQL
CREATE TABLE `city` (
`CITY_ID` BIGINT(20) NOT NULL,
`STATE_ID` BIGINT(20) NOT NULL,
`NAME` VARCHAR(55) NOT NULL,
`CITY_DESC` VARCHAR(45) NULL DEFAULT NULL,
`CREATED_DTTM` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`MODIFIED_DTTM` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`CITY_ID`),
INDEX `FK_CITY_STATE_ID` (`STATE_ID`),
CONSTRAINT `FK_CITY_STATE_ID` FOREIGN KEY (`STATE_ID`) REFERENCES `state` (`STATE_ID`)
)
But in PostgreSQL when i tried to use ON UPDATE now() its showing error My PostgreSQL query
CREATE TABLE TAX_RATE_CUSTOMER (
TAX_RATE_CUSTOMER_ID BIGINT PRIMARY KEY,
CUSTOMER_ID BIGINT ,
CREATED_DTTM TIMESTAMP DEFAULT NOW(),
MODIFIED_DTTM TIMESTAMP NOT NULL ON UPDATE NOW()
)
Its showing error as
ERROR: syntax error at or near "ON"
LINE 5: MODIFIED_DTTM TIMESTAMP NOT NULL ON UPDATE NOW()
^
********** Error **********
ERROR: syntax error at or near "ON"
SQL state: 42601
Character: 173
How to solve this I want to set current time automatically while updating. Thanks in advance