sql - Reverse a column in postgres

Question

So I have a table in SQL server that is defined as such

create table test(value varchar(200), Reverse(value) as valueReverse);

now when I insert something in this table lets say I insert the string hello, it will store the value in the table as such.

value | valueReverse
--------------------
hello | olleh

I am trying to convert the table into PostgreSQL however the reverse() function is not working and it's giving me errors. What is the correct way to create this table in postgres?

question from:https://stackoverflow.com/questions/65913162/reverse-a-column-in-postgres

Answer 1

For PostgreSQL 12 and above

If you are using Postgres 12 or higher then you can use GENERATED ALWAYS AS for the column valueReverse like below: Manual

create table test(value varchar(200),
valueReverse varchar(200) generated always as (reverse(value)) STORED );

DEMO

For PostgreSQL 11 or below

For earlier version you can use Triggers like below.

Creating Trigger Function

create or replace function trig_reverse() returns trigger as
$$
begin
new.valueReverse=reverse(new.value);
return new;
end;
$$
language plpgsql

Creating Trigger

create trigger trig_rev 
before insert or update on test 
for each row 
execute procedure trig_reverse();

DEMO