Different cast behavior of TEXT and VARCHAR
-----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-----
Import Notes
Reply to msg id not found: 4DA3BD69.7070202@nycap.rr.comReference msg id not found: 20110411064834.GA20505@tomasReference msg id not found: 4DA3BD69.7070202@nycap.rr.com
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
-----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-----