Unknown database function 'date'

In SQL dialect for postgresql, the IDE does not recognize the date function(s). (For me this is postgresql 9.3).

This should be valid SQL for postgres:

SELECT date('2024/1/1');

and indeed, it does execute if/when Rider lets it be executed. But date here is red and says Unknown database function ‘date’ and in the query console it will not allow it to be executed because of this. It will execute it as an injected language block in my C# code.

I cannot find this date function documented, but I know it works, and I can find it in the list of server functions in my postgres database.

I can see that there are a bunch of functions there that I can use that Rider does not recognize. Some examples are: date_cmp, date_eq, date_ge, date_gt, date_out, date_larger, date_smaller, etc.

So I'm not sure what is happening here. Why are these functions undocumented and also not recognized by Rider? It could be a couple things, and I'm not sure:

  • Maybe these are third-party functions and I have installed some plugin or something to get them? I don't think this is the case. There could be plugins installed that I don't know about, but I doubt that it would be these kind of utility functions that you would get. But I don't know for sure, maybe it could be? It would at least explain why I can't find documentation for them.
  • Maybe these are considered super low-level functions? Maybe these are the underlying operations that are called by other more conventional things, like casting and converting and operators and so on? So you are not “meant” to call these? But that seems strange. If I can call them why shouldn't I call them? And why wouldn't Rider know about them if they are available?

So I'm not really sure. Ideally I think Rider should just know about these since they are available and they work and I can call them. Rider should/could even load their definitions right from the database. Or maybe it is supposed to and it is failing for some reason?

0
5 comments

Related to this, the following queries are similar, but not the same; they do work but they give wrong results and I'm not sure why.

SELECT date '2024/1/1';
SELECT '2024/1/1'::date;

In Rider these both return:

date
2023-12-31

If I run these in pgadmin I get the correct answer of 2024-1-1. Is this a driver issue or is there some reason I'm getting the wrong result here?

0

Also related, is there a way to “favorite” and “unfavorite” injectable languages from the default list?

0

I think I sort of figured out that you can use Settings → SQL Dialects to save SQL dialect choices by file or folder. Then you don't have to choose a dialect manually, you can just say SQL and it will know. (And then you can use language comments if there exceptions within a file.) So that basically answers the last question but not the first ones.

0

Another theory I have about the mysterious undocumented functions like the three date() overloads is that from what I've read these undocumented functions may be obsolete/deprecated and they are there only for backwards-compatibility or something like that. But I am not sure of this.

0

Hi Dave, 

I don't have a pg 9.3 data source, but it should be easy to spot if the built-in function is present in Database Explorer. You'd expect it to be listed under pg_catalog schema, so please ensure you have this schema selected for synchronization
 


 


The same goes for the rest of functions you mentioned
 


Speaking of the SQL dialects, we don't have a favorites option, but it should put the most used dialects on top of the list based on how frequently they are used.

0

Please sign in to leave a comment.