Format argument unsupported for TO_CHAR/TO_VARCHAR function

[sunrutcon]

When processing some query (insert .. as select) (12k characters), I'm getting few of the messages from Title.
As I can see there is no error in processing.

Could you explain what does it mean?

[georgesittas]

That's a warning that even though there's a format argument in the AST node corresponding to a TO_CHAR call, it's unsupported in the targeted dialect.

What's the input/output dialect in your case? Can you show a small example of how you're trying to use SQLGlot?

[sunrutcon]

I have INSERT .. as SELECT from Postgres database, generated from ODI (oracle data integrator). There are TO_CHAR functions on some lines.
I'm using parse_one(query) function without providing dialect.
Also after modifying query I'm using .sql funciton to output SQL also without providing dialect.

Would it help to provide Postgres dialect to parse_one?

[georgesittas]

Yes, you should provide both the intended read and the intended write dialects so that the parsing / SQL generation work correctly for them. So, if you're parsing a Postgres query you should do parse_one(..., dialect="postgres") and if you're generating postgres, do parsed_expression.sql(dialect="postgres").

[sunrutcon]

Tnx a lot!

[sunrutcon]

What is default read/write dialect if not provided?
I'm getting different results on write when changing read/write dialects:

• when no dialect provided on read/write: TO_CHAR translates to CAST(... AS TEXT)
• when postgres dialect provided only on read: TO_CHAR translates to TIME_TO_STR
• when postgres dialect provided on read and write or just write: TO_CHAR remains unchanged

[georgesittas]

What is default read/write dialect if not provided?

The default dialect is "SQLGlot", which can be viewed as a more general / lenient SQL dialect. Its syntax is defined by the base Parser class and generates SQL according to the base Generator class.

[sunrutcon]

Please check this example, there is coalesce, and cast on the write, and should not be:

`from sqlglot import parse_one

query = """
(
CASE
WHEN SIP_TURN_CUSTOMER.value_date IS NULL
OR EXTRACT(
YEAR
FROM
SIP_TURN_CUSTOMER.value_date
) = '9999' THEN '9999-10-01 00:00:00'
ELSE concat(
to_char(SIP_TURN_CUSTOMER.value_date, 'YYYY-MM-DD'),
' 00:00:00'
)
END
) AS VALUE_DATE
"""

parsed = parse_one(query, dialect = 'postgres')

unparsed = parsed.sql(pretty=True, dialect='postgres')`

[sunrutcon]

There are no problems on output when processing same query in Java using JSqlParser.

[georgesittas]

This is expected behavior, SQLGlot parses the above SQL in a way that will 1) produce a Postgres output that yields the same result and 2) enable it to transpile it to other dialects. The COALESCE and CAST are added at parse time because Postgres has additional implicit semantics that need to be realized and preserved when targeting different dialects.

[sunrutcon]

Tnx!
No problems when not providing dialect on read.