oka wrote:
> I have a question.
>
> There are the following data.
>
> create table chartbl
> (
> caseno int,
> varchar5 varchar(5)
> );
> insert into chartbl values(1, ' ');
> insert into chartbl values(2, '');
>
> The same result with the following two queries is obtained.
> select * from chartbl where varchar5 = ' '::bpchar -- ::bpchar
EXPLAIN VERBOSE SELECT * FROM chartbl WHERE varchar5 = ' '::bpchar;
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on laurenz.chartbl (cost=0.00..27.50 rows=7 width=28)
Output: caseno, varchar5
Filter: ((chartbl.varchar5)::bpchar = ' '::bpchar)
(3 rows)
"Character varying" is cast to "character" in this case, that's
why you get this result.
There are two operators "=" for string types: one comparing
"text" with "text", and one comparing "character" with "character".
So there has to be some casting if you compare "character varying"
with "character".
Because of rule 3)b) in
http://www.postgresql.org/docs/9.3/static/typeconv-oper.html
the operator chosen is the one that matches one of the argument
types.
> select * from chartbl where varchar5 = N' ' -- N prefix
That is because an N'...' literal is always of type "character":
SELECT pg_typeof(N' ');
pg_typeof
-----------
character
(1 row)
The rest of the argumentation is like in the previous case.
> Is this specification?
Are you asking if this is according to the SQL standard or
if it is working as documented?
I am not sure concerning the standard, but reading Syntax Rules 3) iii)
of chapter 9.3 of ISO/IEC 9075-2 I get the impression that PostgreSQL
does not follow the standard here.
The behaviour of your first query is well documented, but there is
no documentation of N'...' literals, and I personally think that
it violates the principle of least astonishment that they are
interpreted as "character" (different from E'...').
> Does it continue not to change?
Since that would break user applications, it will not
change without a very good reason.
Yours,
Laurenz Albe