2

I'm new to PostgreSQL, today I use php PDO to connect to Postgres and user var_dump print the results, it's astonishing that there are many redundant spaces in the char column of result set, my php code:

$sth = $this->db->prepare($sql);
$sth->execute($params);
$result = $st->fetch(PDO::FETCH_ASSOC);
var_dump($result);

The result like this:

array(4) {
  ["uid"]=>
  int(1)
  ["first_name"]=>
  string(32) "xiaobing                        "
  ["last_name"]=>
  string(32) "zhang                           "
  ["user_email"]=>
  string(32) "aa@gmail.com                    "
}

And I test how long does the column first_name:

echo "column first_name lenth: " . strlen($result['first_name'])."\n";

The result is :

column `first_name` length: 32

Background: I am used to use MySQL. Recently, I converted to Postgres. I am surprised that char columns have redundant spaces because MySQL is not like this. Does Postgres need to process the redundant spaces by application program? Maybe I'm wrong.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
windrain
  • 75
  • 2
  • 9
  • 2
    Are you saying the table fields are char instead of varchar? – Tony Hopkinson Dec 31 '14 at 09:59
  • Could you tell how the column is defined in database, what is the exact type? –  Dec 31 '14 at 10:00
  • use command: \d+ users, uid is integer and all of others are character(32) and the Storage column is extended – windrain Dec 31 '14 at 10:24
  • 4
    This is how char works in every database - it is a fixed length character string that will be silently padded with spaces when it comes short of the defined size. If this is a problem you should alter the type to varchar, this will accept strings of size up to the limit you set without any padding. – Lucas Dec 31 '14 at 11:33
  • 1
    From the manual: character(n), char(n) fixed-length, blank padded. You get what you ask for, that's how it works. MySQL has strange behavior and this behavior also depends on the many SQL modes you can use. http://www.postgresql.org/docs/current/interactive/datatype-character.html – Frank Heikens Dec 31 '14 at 11:51
  • Never use the blank-padded outdated data type `char`. Use `varchar` or `text` instead: http://stackoverflow.com/questions/17333196/compare-varchar-with-char/17333258#17333258, http://stackoverflow.com/questions/20326892/any-downsides-of-using-data-type-text-for-storing-strings/20334221#20334221 – Erwin Brandstetter Dec 31 '14 at 14:47

0 Answers0