Thread: zero char is returned as space
Hi,
could you help me understand if this is an intended behaviour, or I'm incorrectly querying a "char" field? I have simple table with column declared as:
c_tinyint char NOT NULL
The column contains tiny integers in range 0-10. When I query the column from my app using libpq values 1-10 are returned correctly as 0x1-0x10. But value of zero is returned as 0x20 (expected 0x0).
The pgAdmin displays result of the query
SELECT c_tinyint, ascii(c_tinyint) FROM tbl
as shown below:
| c_tinyint | ascii |
| character(1) | integer |
| | 0 |
| | 1 |
| | 2 |
| | 3 |
| | 4 |
Thank you
K
Konstantin Izmailov <pgfizm@gmail.com> writes:
> could you help me understand if this is an intended behaviour, or I'm
> incorrectly querying a "char" field?
We do not support '\0' as an element of a string value. You didn't
show how you're trying to insert this value, but I suspect that
Postgres saw it as an empty string which it then space-padded to
length 1 because that's what char(1) does.
Don't use a string field to store an integer. What with the need
for a length header, you wouldn't be saving any space compared to
"smallint" even if there weren't any semantic issues.
regards, tom lane
Tom,
thank you very much! It makes sense now.
K
On Tue, Apr 5, 2022 at 10:08 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Konstantin Izmailov <pgfizm@gmail.com> writes:
> could you help me understand if this is an intended behaviour, or I'm
> incorrectly querying a "char" field?
We do not support '\0' as an element of a string value. You didn't
show how you're trying to insert this value, but I suspect that
Postgres saw it as an empty string which it then space-padded to
length 1 because that's what char(1) does.
Don't use a string field to store an integer. What with the need
for a length header, you wouldn't be saving any space compared to
"smallint" even if there weren't any semantic issues.
regards, tom lane