Oracle function to_char(character varying, unknown) does not exist

PostgreSQL does have a function to_char(). Here is the documentation page: https://www.postgresql.org/docs/current/functions-formatting.html

But it does not have a signature for the function to accept a varchar as the first argument.

Looking at the error, I notice it suggests the remedy:

You might need to add explicit type casts.

I tried this:

postgres=# select to_char(date_of_birth::timestamp, 'fmMM') from tokens; to_char --------- 9 (1 row) 

Voilà!

This is necessary because you apparently defined the date_of_birth column as a varchar. It is recommended to use a temporal type when you define a column for temporal values. If you had used date or timestamp for that column, then you wouldn't need the explicit type cast.

This has nothing to do with what class you use to store the value in Java (e.g. java.util.Date). It's about the data type in PostgreSQL.

Why

PostgreSQL's error message

ERROR: function to_char(character varying, unknown) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.

Isn't as clear as one could hope. It could be that no function exists with that name, or that it exists with that name but not with those parameters types. Additionally you could be tempted to think the culprit is the bizarre unknown.

However, one thing is sure: it sees its first parameter (s.date_of_birth) as a character varying, that is, a varchar(n).
You probably have stored your dates as strings, in ISO-8601 (YYYY-MM-DD) or a national format that makes your string instantly human-readable (well, for those that recognize that format), but not well adapted to PostgreSQL's very rational way of doing (PostgreSQL has a very clean way of avoiding all forms of implicit, unspecified behaviours).

But let's go back to our original problem of unblocking to_char().

Looking for a candidate

So first thing to do, if pretty confident that to_char() exists in PostgreSQL, is to know what parameters the function would accept.
So let's ask:

SELECT format('%I(%s)', proname, oidvectortypes(proargtypes)) FROM pg_proc WHERE proname = 'to_char'; -- Note that in PostgreSQL, names are standardized in lowercase, so don't look for TO_CHAR, look for to_char. 

So we see that there exists to_char(timestamp [with or without timezone], text) in those well documented functions.

As a first try, we could take advice from the Hint: You might need to add explicit type casts:

to_char(s.date_of_birth::timestamp, 'fmMM'::text) 

and in fact… it works! PostgreSQL is quite tolerant in what formats it can understand as dates, so, be it ISO-8601 or a national format, there's a good chance that it works.

But wait wait wait! Don't use this quick solution, instead ask yourself what SQL type would be the more appropriate for your column.

date!

Given its name, and its use as a timestamp, you probably want it to be stored as a one of PostgreSQL time types: timestamp or its little sister date.

You'll gain:

• storage (4 bytes instead of 10)
• efficiency (you do not convert to timestamp then (through to_char) back to string, you will just do the second conversion, directly from timestamp / date to string)
• and a whole lot of helper functions dedicated to dates

What about Oracle?

In fact, be it in Oracle or in PostgreSQL, TO_CHAR() takes a date as its first parameter.

If you try the same in Oracle (having a varchar2 column and to_char()ing it), as you can see in this little demo you'll get either:

• ORA-01722: unable to convert string value containing '-' to a number
• or ORA-01481: invalid number format model

The query you gave on your question only worked in Oracle if the column was a date (in the hereabove demo fiddle, the query succeeds only after the column has been converted to a date (all the ALTER TABLE DMLs)).

So your Oracle database was rightly designed; but when migrating from Oracle to PostgreSQL, your date_of_birth was inadvertently transformed from a date to a text.

But we can remedy that.

How?

On the PostgreSQL side

If you read the Oracle fiddle, you saw a complicated way of having date_of_birth converted from varchar2 to date: renaming it, creating a new column that we populate with TO_DATE() from the renamed column, then drop the old, varchar2-typed column.

In PostgreSQL, you can in one operation both re-type the column and convert the data without loosing it, thanks to the USING keyword where you mention how your date-formatted strings will be converted on the fly to real dates:

ALTER TABLE tokens ALTER COLUMN date_of_birth TYPE date USING TO_DATE(date_of_birth, 'YYYY-MM-DD'); 

(of course the 'YYYY-MM-DD' depends on the format of the current data in your column)

You can see the whole process in this PostgreSQL db<>fiddle.

And you know what? Now that the column is a date, implicitly convertible to a timestamp, you can run your original SELECT as is, as PostgreSQL will use the right version of to_char(timestamp, text).

SELECT s.id, to_char(s.date_of_birth, 'fmMM') dobMonth, to_char(s.date_of_birth, 'fmYYYY') dobYear, to_char(s.date_of_birth, 'fmDD') dobDay, s.type FROM tokens s WHERE s.token_id = :profileId; 

On the Java side

If your Java uses String to represent date_of_birth, you are relying on Java transcribing your dates to their display form using the current locale (client or server? I'm not skilled enough to say).

If it works (and you only need it for display) you can keep it as is, however using a java.time.LocalDate is probably a good idea (thanks @Mark Rotteveel for the tip! But also this SO answer or this JDBC / PostgreSQL cheatsheet),
to have a consistent chain that uses the most machine oriented type as far as possible until display (for the same reasons as in database: performance + access to java.time.LocalDate's rich API).