PostgresSQL 10.6 - function substring & regexp_matches function does not exist

Question

I have installed PostgresSQL 10.6 installed on Windows and using DBeaver - I confirmed the version by "SELECT VERSION()".

For some reason, whenever I try to use function SUBSTRING, I receive the below error:

SQL Error [42883]: ERROR: function pg_catalog.substring(character varying, integer, character varying, integer) does not exist

Similarly for REGEXP_MATCHES

SQL Error [42883]: ERROR: function regexp_matches(character varying, text, integer) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.

The syntax I'm using I believe matches the docs, for example REGEXP_MATCHES(source_string, pattern, 1)

Any ideas why Postgres would throw these errors?

The syntax I'm using I believe matches the docs. for example? — klin
– klin, Commented Jan 16, 2019 at 3:06
Please edit your question and add the complete statement you are using. — user330315
– user330315, Commented Jan 16, 2019 at 6:43
It's regexp_matches(text,text,text), there is no regexp_matches(text,text,int). Same issue with substring(), though I don't know which one you were aiming for... Show us the code. — Nick Barnes
– Nick Barnes, Commented Jan 16, 2019 at 6:45
Yes, I was using the wrong format. Misread the docs. I understood it to be (text,text,int) with the int representing case insensitivity. Same for substring; incorrect use. Postgres was acknowledging there was no regexp_matches function that included (text,text,int) rather than just say that the function did not accept int — user3867061
– user3867061, Commented Jan 16, 2019 at 8:20

user3867061 · Accepted Answer · 2019-01-16 08:33:48Z

I was attempting to use the functions incorrectly which threw the "function doesn't exist". I guess it means that the function does exist if I want to use it with the type of parameters I tried to use.

REGEXP_MATCHES(source_string, pattern, 1) I understood to be a regex match with case insensitivty (1). As Nick Barnes pointed out in the comment, REGEXP_MATCHES takes (text,text,text).

I was shorterning the length of one text field to check if it existed within another column - rather than use SUBSTRING against col2, I used LEFT(col2,10) and instead of regexp_matches, I used (regexp_match(col1,col2))[1]

Collectives™ on Stack Overflow

PostgresSQL 10.6 - function substring & regexp_matches function does not exist

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related