Q:

postgres, substring a subselect

I have a query that uses a subselect like this
SELECT "columnA","columnB", (SELECT column1 FROM tableB WHERE id=1 LIMIT 1) as text
FROM tableA WHERE id=1

Now i would like to only get the last 3 chars from my "as text" column. I have tried to apply the substring or right around my subselect but that returns an error, can anyone explain why and how to do this properly?

A:

You need to use internal function substring matching POSIX regular expression
SELECT "columnA","columnB", (SELECT substring(column1::TEXT from '...$') FROM tableB WHERE id=1 LIMIT 1) as text
FROM tableA WHERE id=1

Please keep in mind that this way, if you have more than 1 record in tableA that matches your WHERE criteria, you will still be getting the same value in variable text for this query.

