2

I am trying to figure out what Postgres does when comparing varchar with char.

Here is one of my tests:

test=# select 'a'::character varying = 'a     '::character;
?column?
----------
 t

test=# select 'ab'::character varying = 'ab     '::character;
?column?
----------
 f

This looks like a bug to me. Does anyone know what is going on here? Are there good documents on this topic?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Xin
  • 737
  • 3
  • 10
  • 27

1 Answers1

5

Not a bug at all.
Casting the string literal 'ab ' to character, this is what you get:

a

Per documentation:

character without length specifier is equivalent to character(1).

'a'::character(1) will then be coerced to varchar (character varying) to test for equality with 'a'::varchar or 'ab'::varchar and yield TRUE or FALSE respectively.

Basically, there is hardly any good reason to use character at all. It's a legacy type that has outlived its usefulness. Just use text or varchar.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • "Basically, there is hardly any good reason to use" --- aren't fixed length rows more efficient (for lookups)? – zerkms Jun 27 '13 at 01:59
  • 3
    @zerkms not for Postgres. "While character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. " http://www.postgresql.org/docs/9.2/static/datatype-character.html – Xin Jun 27 '13 at 02:02