PostgresSQL 10.6 - function substring & regexp_matches function does not exist

0
votes

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?

1
sqlpostgresql
The syntax I'm using I believe matches the docs. for example? – klin
updated with example – user3867061
Please edit your question and add the complete statement you are using. – a_horse_with_no_name
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
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

1 Answers

0
votes

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]