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?

Why are you doing that to begin with? is it to support a wildcard search at the beginning? In that case you can simply create an index on the expression, rather than adding a new column. Or use a trigram index. — , Jan 27 '21 at 06:26
The original table was designed by someone else, I'm currently looking at keeping the table structure the same, just to keep everything working smoothly. — TejusN, Jan 27 '21 at 07:19

Akhilesh Mishra · Accepted Answer · 2021-01-27T05:47:01.453

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

score 1 · Answer 2 · answered Jan 27 '21 at 06:14

Do not store string twice (redundantly). It will be much cleaner and cheaper overall to store it once and produce the reverted copy on the fly. You can use a VIEW if you need a drop-in replacement for your table:

CREATE TABLE base_test(value varchar(200));
INSERT INTO base_test VALUES ('hello');
 
CREATE VIEW test AS
SELECT *, reverse(value) AS value_reverse
FROM   base_test;

db<>fiddle here

Computed / calculated / virtual / derived columns in PostgreSQL

Reverse a column in postgres

2 Answers2