Postgres Pro
Downloads
Home   >   mailing lists

Thread: Different cast behavior of TEXT and VARCHAR

Different cast behavior of TEXT and VARCHAR

From
tomas@tuxteam.de
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

When PREPARing statements, the type guessing machinery seems to behave
differently for VARCHAR and TEXT. Is this intentional?

This is all against PostgreSQL 8.4.5

Illustration:

 |  tomas@floh:~$ psql foo
 => psql (8.4.5)
 => Type "help" for help.
 |
 |  foo=# CREATE TABLE bar (
 |  foo(#   a TEXT,
 |  foo(#   b VARCHAR
 |  foo(# );
 => CREATE TABLE
 |  foo=# PREPARE s1 AS SELECT a, b FROM BAR WHERE a = $1;
 => PREPARE
 |  foo=# PREPARE s1 AS SELECT a, b FROM BAR WHERE b = $1;
 => ERROR:  could not determine data type of parameter $1

In the case of VARCHAR, I get no error if I put the placeholder in
quotes (i.e. '$1').

It's no big issue for me. I'll just switch to TEXT anyway, but it's a
bit surprising :-)

Thanks for any insights

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFNo/LUBcgs9XrR2kYRAtYVAJ4wpms/EuAhlfDmH2xKIgeFJWRS9wCfeBsY
ZdlA/GaNrFS4DAyekNlH0hM=
=cmap
-----END PGP SIGNATURE-----

Re: Different cast behavior of TEXT and VARCHAR

From
Tom Lane
Date:
tomas@tuxteam.de writes:
> When PREPARing statements, the type guessing machinery seems to behave
> differently for VARCHAR and TEXT. Is this intentional?

Your example works for me, in all branches back to 8.2:

regression=# create table foo(a text, b varchar);
CREATE TABLE
regression=# PREPARE s1 AS SELECT a, b FROM foo WHERE a = $1;
PREPARE
regression=# PREPARE s2 AS SELECT a, b FROM foo WHERE b = $1;
PREPARE

I wonder if you have some nondefault operators installed that are making
the query ambiguous.

            regards, tom lane

Re: Different cast behavior of TEXT and VARCHAR

From
tomas@tuxteam.de
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Tue, Apr 12, 2011 at 10:29:44AM -0400, Tom Lane wrote:
> tomas@tuxteam.de writes:
> > When PREPARing statements, the type guessing machinery seems to behave
> > differently for VARCHAR and TEXT. Is this intentional?
>
> Your example works for me, in all branches back to 8.2:
>
> regression=# create table foo(a text, b varchar);
> CREATE TABLE
> regression=# PREPARE s1 AS SELECT a, b FROM foo WHERE a = $1;
> PREPARE
> regression=# PREPARE s2 AS SELECT a, b FROM foo WHERE b = $1;
> PREPARE

Thanks!

> I wonder if you have some nondefault operators installed that are making
> the query ambiguous.

That would mean I managed to munge my template1. Yes, that'd be the most
plausible scenario :-/

Not at my machine at the moment, will check later. Thanks and sorry for
the noise.

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFNpGgLBcgs9XrR2kYRAgU5AJwJnOwC0LYHAiPUC9jWASI+al95owCdGhqX
EoRE7s265qjLz1yJBMERrQI=
=AnaI
-----END PGP SIGNATURE-----