DEFAULT TIMESTAMP - Sybase SQL Anywhere to PostgreSQL Migration

In Sybase SQL Anywhere, you can specify the DEFAULT TIMESTAMP clause for a table column, and its value will auto-update with the current date and time whenever the row is updated.

In PostgreSQL, you can use DEFAULT CLOCK_TIMESTAMP() to set the default value on insert, and a trigger to update it on every update.

Sybase SQL Anywhere:

  -- Sample table
  CREATE TABLE orders 
  (
    name VARCHAR(30),
    items INTEGER,
    created TIMESTAMP DEFAULT CURRENT TIMESTAMP,
    modified TIMESTAMP DEFAULT TIMESTAMP
  );  
 
  -- Insert a sample row
  INSERT INTO orders (name, items) VALUES ('Apple', 3);
 
  SELECT * FROM orders;
  /* Apple   3   2026-03-12 17:20:29   2026-03-12 17:20:29 */
 
  -- Update the order later
  UPDATE orders SET items = 7 WHERE name = 'Apple';
 
  -- 'modified' column was automatically updated  
  SELECT * FROM orders;
  /* Apple   7   2026-03-12 17:20:29   2026-03-12 17:27:59 */

PostgreSQL:

   -- Sample table
  CREATE TABLE orders 
  (
    name VARCHAR(30),
    items INTEGER,
    created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    modified TIMESTAMP DEFAULT CLOCK_TIMESTAMP()
  );  
 
  -- Trigger function
  CREATE OR REPLACE FUNCTION orders_modified_tr_func() RETURNS TRIGGER 
  AS $$
  BEGIN
      NEW.modified = CLOCK_TIMESTAMP();
      RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;
 
  -- Trigger
  CREATE TRIGGER orders_modified_tr BEFORE UPDATE ON orders
    FOR EACH ROW EXECUTE FUNCTION orders_modified_tr_func();
 
  -- Insert a sample row
  INSERT INTO orders (name, items) VALUES ('Apple', 3);
 
  SELECT * FROM orders;
  /* Apple   3   2026-03-12 17:56:02   2026-03-12 17:56:02 */
 
  -- Update the order later
  UPDATE orders SET items = 7 WHERE name = 'Apple';
 
  -- 'modified' column was automatically updated  
  SELECT * FROM orders;
  /* Apple   7   2026-03-12 17:56:02   2026-03-12 17:59:42 */

For more information, see Sybase SQL Anywhere to PostgreSQL Migration.