Re: PreparedStatement error for date parameter with overlaps

On 31/05/11 14:01, Simon Mitchell wrote:
> Bingo
>
> thanks guys, this works fine, obviously I had no idea what i was doing,
> no need to run the date function on a date.
> SELECT (CAST(? AS date), ?::date) OVERLAPS (date(?), date(?)) works fine.

Avoid using the type-as-function form like "date(varname)" or "date(?)".
It's a non-standard hack and a postgresql implementation quirk that
exists because the underlying cast implementation functions are named
after their data types. Don't rely on it.

Instead, use the SQL-standard "CAST(? AS date)" syntax.

> So from what I understand using the date function in jdbc stuff requires
> brackets like most functions do,

There *is* a DATE function, but you should avoid it in favour of
explicit CASTs.

> but the psql is happy for me to use the date function with or without
> brackets.

No. The difference is that via PgJDBC you were using prepared
statements. To compare PgJDBC prepared statement execution with psql,
you must use the psql 'PREPARE' and 'EXECUTE' commands, not just type
the SELECT in directly. See the example below.

> Postgresql docs examples seem to be all with out brackets.

The DATE literal prefix is only valid with *literals*, and the examples
you refer to show DATE being used with literals, where it is valid. If
you're using a variable or a placeholder, the DATE prefix to specify
data type is *not* *valid*.

Compare:

-- DATE used to specify type of a literal
--
regress=> SELECT DATE '2011-04-02';
date
------------
2011-04-02
(1 row)

-- DATE used to specify type of a literal in a prepared statement
--
regress=> PREPARE datetest AS SELECT DATE '2011-04-02';
PREPARE
regress=> EXECUTE datetest;
date
------------
2011-04-02
(1 row)

-- DATE used to (try to) specify the type of a placeholder
-- variable in a prepared statement.
--
regress=> PREPARE datetest2 AS SELECT DATE $1;
ERROR: syntax error at or near "$1"
LINE 1: PREPARE datetest2 AS SELECT DATE $1;

-- It is not understood, because the DATE prefix is not
-- valid for a non-literal. The CAST(value AS type) syntax
-- should be used instead:

regress=> PREPARE datetest2 AS SELECT CAST($1 AS date);
PREPARE
regress=> EXECUTE datetest2('2011-04-02');
date
------------
2011-04-02
(1 row)

-- ... and it's also legal (albeit unnecessary) to explicitly specify
-- the type of the literal you pass to EXECUTE, because you're using
-- the typename on a literal:

regress=> EXECUTE datetest2(DATE '2011-04-02');
date
------------
2011-04-02
(1 row)

Because it only applies to literals and it offers no advantage over a
CAST expression, it's often best to avoid the literal-type prefix.
Instead, use a CAST expression. PostgreSQL will use casts to obtain the
same information as a literal-type prefix when they are used on a
literal of unknown type, but unlike a type name prefix they'll also work
on variables and placeholders. So, instead of:

regress=> PREPARE datetest2 AS SELECT DATE $1;

write

regress=> PREPARE datetest2 AS SELECT CAST($1 AS date);

It's the same in JDBC, except that placeholders are "?" instead of "$n".

These rules don't just apply to placeholders in prepared statements. You
can't use a type name to cast a variable either:

SELECT DATE a FROM (SELECT TEXT '2011-04-02' a) AS x;
ERROR: column "date" does not exist
LINE 1: SELECT DATE a FROM (SELECT TEXT '2011-04-02' a) AS x;

even though this works because it's using DATE on a literal:

regress=> SELECT a FROM (SELECT DATE '2011-04-02' a) AS x;
a
------------
2011-04-02
(1 row)

--
Craig Ringer