Understanding the behaviour of substring in postgresql

Question

I am new to postgresql guys. I tried to use substring function, while trying some examples I've noticed "awkward" behaviour that I didn't understand. Consider the following scenarios:

select substring('123456',1,3) returns 123 (which is logical for me)

select substring('123456',0,1) returns nothing (I don't know why!)

select substring('123456',-1,2) returns nothing too!

Question: Can someone explain how does postgresql substring function behave when we give it 0 or negative values for position.

What does select substring('123456', -1, 3) return, out of interest? I suspect the fact that the indexes are 1-based is relevant here... maybe anything "before" position 1 is just assumed not to exist? — Jon Skeet
– Jon Skeet, Commented Apr 8, 2018 at 9:29
It definitely doesn't work like Oracle's SUBSTR : Refer discussion here postgresql.org/message-id/12803.1168804636%40sss.pgh.pa.us — Kaushik Nayak
– Kaushik Nayak, Commented Apr 8, 2018 at 9:47

404 · Accepted Answer · 2018-04-08 09:50:46Z

3

The first position in a string is 1. If you do SUBSTR('abc', 1, 1) you get a. If you do SUBSTR('abc', 0, 1), you get an empty string because there's nothing at position 0. (SUBSTR doesn't raise an exception if the position doesn't exist.) Same for a position of -1, or 10.

These both return a: SUBSTR('abc', 0, 2), SUBSTR('abc', -1, 3), which should show pretty much how SUBSTR works in postgres. (I'm using SUBSTR but it's the same as SUBSTRING.)

It's worth noting also that arrays in postgres are 1-based rather than 0.

answered Apr 8, 2018 at 9:50

404

8,7622 gold badges34 silver badges53 bronze badges

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

4 Comments

ziMtyth Over a year ago

in your example SUBSTR('abc', 0, 1) you say there is nothing at position 0 I would add something, after my tests I can say that it is more appropriate to say there is nothing at position 0 with length 1 because if you try SUBSTR('abc', 0, 2) you get a as a result so I think that postgresql does accept 0 and negative positions because the length defines the result. it would be nice if someone can provide official documentation for my explication (if it is right ofc), I searched and I found nothing.

404 Over a year ago

Yes well I thought that went without saying given that the length arg used in my example is 1, and a length of 2 from 0 gets the first character. I don't know about any documentation for it but as I said, how it works is quite straightforward if you play with a few edge cases.

ziMtyth Over a year ago

playing some tests gave me almost a clear vision about how it works, I'm just looking for official documentation to confirm my comprehesion, because imagine that we pass a parameter to this function from the application and some value could give unexpected result just because I didn't understand fully how this function works , that's why I need official source =)

ZhengguanLi Over a year ago

PG will not returns empty because there is no 0 position. It will assume you have, but returns nothing, so SUBSTR('abc', 0, 2) equals SUBSTR('abc', 1, 1) which returns a, SUBSTR('abc', 0, 3) equals SUBSTR('abc', 1, 2) which returns ab

Collectives™ on Stack Overflow

Understanding the behaviour of substring in postgresql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related