postgres, substring a subselect

Question

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?

Kamil Gosciminski · Accepted Answer · 2014-10-02 13:10:30Z

1

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.

answered Oct 2, 2014 at 13:10

Kamil Gosciminski

17.3k9 gold badges53 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgres, substring a subselect

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related