Are either of these supported in YugabyteDB? If not, is there a workaround?
confirmed_at timestamptz GENERATED ALWAYS AS (LEAST (email_confirmed_at, phone_confirmed_at)) STORED,
email text generated always as (lower(identity_data->>‘email’)) stored
Thank you
STORED generated columns were introduced in Postgres 12. YugabyteDB is currently compatible with Postgres 11.2.
As a work around, you’ll have to rely on a trigger for this.
yugabyte=# CREATE TABLE t(email_confirmed_at TIMESTAMP, phone_confirmed_at TIMESTAMP, confirmed_at TIMESTAMP);
CREATE TABLE
yugabyte=# CREATE TRIGGER AFTER INSERT ON t
yugabyte=# CREATE OR REPLACE FUNCTION t_confirmed_at()
yugabyte-# RETURNS TRIGGER AS
yugabyte-# $$
yugabyte$# BEGIN
yugabyte$# NEW.confirmed_at = LEAST(NEW.email_confirmed_at, NEW.phone_confirmed_at);
yugabyte$# RETURN NEW;
yugabyte$# END;
yugabyte$# $$
yugabyte-# LANGUAGE 'plpgsql';
CREATE FUNCTION
yugabyte=# CREATE TRIGGER confirmed_at_default
yugabyte-# BEFORE INSERT ON t
yugabyte-# FOR EACH ROW
yugabyte-# EXECUTE FUNCTION t_confirmed_at();
CREATE TRIGGER
yugabyte=# INSERT INTO t(email_confirmed_at, phone_confirmed_at) VALUES (current_timestamp, current_timestamp + INTERVAL '1 DAY');
INSERT 0 1
yugabyte=# INSERT INTO t(email_confirmed_at, phone_confirmed_at) VALUES (current_timestamp + INTERVAL '1 DAY', current_timestamp);
INSERT 0 1
yugabyte=# SELECT * FROM t;
email_confirmed_at | phone_confirmed_at | confirmed_at
----------------------------+----------------------------+----------------------------
2024-02-19 15:57:23.860143 | 2024-02-20 15:57:23.860143 | 2024-02-19 15:57:23.860143
2024-02-20 15:57:47.912178 | 2024-02-19 15:57:47.912178 | 2024-02-19 15:57:47.912178
(2 rows)