CODEWITHSUNDEEP
postgresqlselect
user1443098
user1443098

Reputation: 7665

In PostgreSQL, which types can be cast with the type name first?

Reading the PostgreSQL docs, I see that you can cast a longish bit of text to xml like this:

SELECT xml '<long>long text, may span many lines</long>'

SELECT xml '...'

Curious, I found that I could do the same with JSON:

SELECT json '{"arg1":"val1", <more args spanning many lines>}'

(I couldn't find an official reference for this one. It just works!)

By contrast, this does not work:

SELECT float8 3.14159

I like this alternate syntax from a readability perspective. Now I'm looking for a reference listing which types may be specified up front like this. but I haven't found it yet.

Any pointers?

Upvotes: 2

Views: 576

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247545

The documentation says:

A constant of an arbitrary type can be entered using any one of the following notations:

type 'string'
'string'::type
CAST ( 'string' AS type )

The string constant's text is passed to the input conversion routine for the type called type. The result is a constant of the indicated type. The explicit type cast can be omitted if there is no ambiguity as to the type the constant must be (for example, when it is assigned directly to a table column), in which case it is automatically coerced.

The form you are asking about is the first one.

So this can be used for all PostgreSQL types.

Note that the data must be specified as a string literal (in single or dollar quotes) when you use that syntax.

Upvotes: 2

Related Questions