Thread: insert char(1) type by different ways.

Good day.

System Configuration
---------------------
  Architecture: Intel Pentium

  Operating System: 4.2-STABLE FreeBSD

  PostgreSQL version:   PostgreSQL-7.0.2

  Compiler used:  gcc 2.95.2

Description of problem
----------------------

It's some differences in results, then I inserted char(1) type. I introduce one
example:

create table ku(n_pp int4, col char(1));

insert into ku values(1, '\000');
insert into ku values(2, '\040');
insert into ku values(3, ichar(0));
insert into ku values(4, ichar(32));

select n_pp, ascii(col) from ku ;
 n_pp | ascii
------+-------
    1 |    32
    2 |    32
    3 |     0
    4 |    32
(4 rows)

Look at the strings where "n_pp" equal 1 and 2. "Col" column always equals to 32
(decimal), but I inserted as '\000' and '\040'. (It is first way).
  In second way, If I use ichar() function, then "col" column has another
values. This result is correct (I think).
Is it right ?
  Locales for "postgres" user adjust properly (if it's necessary).

ps. I use some function from list, which generated by "\df" command from psql
console. But I didn't found them in pgsql documentation. Is it possible that
this function will be removed in future version of postgresql ?
pps. Can I take description of this function ?
  sorry for my bad english ;)


  Regards..
--

"And. Andruikhanov" <andy@euinf.dp.ua> writes:
> insert into ku values(1, '\000');

This cannot work as you expect, because what comes out of the parser is
a string containing a single null character --- and that's fed to a
datatype input routine that expects a null-terminated string.  So the
char(n) input routine thinks you entered just '', which it blank-pads
to one character.

In general the Postgres I/O routines are not friendly to embedded nulls.
The char/varchar/text types could not support embedded nulls even
without the I/O problem, because they depend on C library routines like
strcoll(), and those routines don't support strings with embedded nulls.

            regards, tom lane

On Sat, 3 Feb 2001, Tom Lane wrote:

> "And. Andruikhanov" <andy@euinf.dp.ua> writes:
> > insert into ku values(1, '\000');
>
> This cannot work as you expect, because what comes out of the parser is
> a string containing a single null character --- and that's fed to a
> datatype input routine that expects a null-terminated string.  So the
> char(n) input routine thinks you entered just '', which it blank-pads
> to one character.
>
> In general the Postgres I/O routines are not friendly to embedded nulls.
> The char/varchar/text types could not support embedded nulls even
> without the I/O problem, because they depend on C library routines like
> strcoll(), and those routines don't support strings with embedded nulls.

 And how store \000 to DB to standard tuples without exotic LO?

 By the way, for example MySQL client lib has nice (simple) function
that make correction from "arbitrary-data" to string that is correct
for all MySQL FE/BE routines (function convert problematic chars to \Oct
format). It's good feature, because user not must yourself check all
strings and know how chars is right for used DB.

 x1 = "Boys don't cry";

 mysql_escape_string(x2, x1, strlen(x1));

 ... and x2 is "Boys don\'t cry"

 (I not sure if quote is good example, but for others problematic chars
is good tool.)

        Karel