Skip site navigation (1) Skip section navigation (2)

Re: BUG #6236: Query execution bug

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Boris Matkov" <borism(at)devart(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #6236: Query execution bug
Date: 2011-09-30 15:33:35
Message-ID: 7767.1317396815@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"Boris Matkov" <borism(at)devart(dot)com> writes:
> INSERT INTO btest0 (id, name) VALUES ((SELECT DISTINCT id FROM btest0 WHERE
> name = $1 LIMIT 1), $1) - was executed successfully

> INSERT INTO btest0 (name,id) VALUES ($1, (SELECT DISTINCT id FROM btest0
> WHERE name = $1 LIMIT 1)) - I got error:
> ---------------------------
> inconsistent types deduced for parameter $1
> ---------------------------

> Script for the btest0 table creating:

> CREATE TABLE btest0
> (
>   id integer NOT NULL,
>   name character varying(50) NOT NULL,
>   value double precision,
>   CONSTRAINT pk_btest0 PRIMARY KEY (id)
> )

It may be annoying, but it's not a bug.  The equality test uses the
"text = text" operator, so if that's seen first then the parameter gets
resolved as text.  You'd be better off to declare the type of the
parameter instead of assuming that it will always be resolved the way
you want ... but if you can't be bothered to do that, using text instead
of varchar as the column type would avoid most of the cases where you'll
see something like this.

			regards, tom lane

In response to

pgsql-bugs by date

Next:From: Tom LaneDate: 2011-09-30 17:35:08
Subject: Re: BUG #6235: Delete fails with ON DELETE rule on inherited table
Previous:From: Kevin GrittnerDate: 2011-09-30 14:40:55
Subject: Re: FW: PostGre compatible to RHEL 6.1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group