Postgres bytea column is returning string (char array) instead of byte array

Question

I have been using C# to write a concrete provider implementation for our product for different databases. W/out getting into details, one of the columns is of byte array type (bytea in postgres - due to the preferences bytea was chosen over blob). The only problem, is that it does not return same value that was inserted. When I insert Int32 ("0") I get 8 [92 and 8x 48] (instead of [0,0,0,0]). I need a performance wise solution, that will return pure bytes I have inserted, instead of ASCII representation of value "0" on 8 bytes.

I am using Npgsql to retrive data. If someone knows solution for c# I will be happy to learn it as well.

Edit: Postgres 9.0, .Net 3.5

Simplification

Command query: - inside it only does an insert statment

select InsertOrUpdateEntry(:nodeId, :timeStamp, :data)

Data parameter:

byte [] value = BitConverter.GetBytes((int)someValue);

Parameter is assigned as below

command.Parameters.Add(new NpgsqlParameter("data", NpgsqlDbType.Bytea) 
{ Value = value });

Select statments:

select * from Entries

Same byte array I have entered, I want to get back. I would really appreciate your help.

Input: 0 0 0 0
Current Output: 92 48 48 48 48 48 48 48 48
Expected Output: 0 0 0 0

Could you provide some code, especially how do you insert that Int32 ? — Grzegorz Szpetkowski, Aug 22 '11 at 19:55
Which version of Postgresql are you using? Which Npgsql version? Did you try using a prepared statement? Npgsql has optimizations for bytea transfers when using prepared statements. I hope it helps. — Francisco Junior, Aug 22 '11 at 21:49
For data retrival it does not matter if I use DataReader or DataAdapter. In both cases I get 8 bytes [92 and 7 x 48]. — Falcon, Aug 23 '11 at 09:23
How do you read the data? Do you use `DbDataReader.GetBytes`? — Branko Dimitrijevic, Aug 23 '11 at 10:52

score 6 · Accepted Answer · edited Aug 24 '11 at 10:47

6

In Npgsql there is NpgsqlDataReader class to retrieve inserted rows, e.g:

NpgsqlConnection conn = new NpgsqlConnection(connStr);
conn.Open();

NpgsqlCommand insertCmd =
    new NpgsqlCommand("INSERT INTO binaryData (data) VALUES(:dataParam)", conn);
NpgsqlParameter param = new NpgsqlParameter("dataParam", NpgsqlDbType.Bytea);

byte[] inputBytes = BitConverter.GetBytes((int)0);
Console.Write("Input:");
foreach (byte b in inputBytes)
    Console.Write(" {0}", b);
Console.WriteLine();

param.Value = inputBytes;
insertCmd.Parameters.Add(param);
insertCmd.ExecuteNonQuery();

NpgsqlCommand selectCmd = new NpgsqlCommand("SELECT data FROM binaryData", conn);
NpgsqlDataReader dr = selectCmd.ExecuteReader();
if(dr.Read())
{
    Console.Write("Output:");
    byte[] result = (byte[])dr[0];
    foreach(byte b in result)
        Console.Write(" {0}", b);
    Console.WriteLine();
}

conn.Close();

Result from C# app:

Input: 0 0 0 0
Output: 0 0 0 0

Result from pgAdmin:

"\000\000\000\000"

EDIT:

I found explanation why you getting:

92 48 48 48 48 48 48 48 48

I checked my code with previous version Npgsql2.0.10-bin-ms.net3.5sp1.zip and get above result (of course pgAdmin returns \000\000\000\000), so I think that best what you can do is to use another version without this bug.

ANSWER: User higher version of Npgsql than 2.0.10

edited Aug 24 '11 at 10:47

Falcon

650
1
8
24

answered Aug 23 '11 at 12:12

Grzegorz Szpetkowski

36,988
6
90
137

Very well Grzesiek. You code looks almost the same as mine. I will investigate any differences I see, and I will let you know about my conclusion. Btw - I did use NpgsqlDataReader (not SqlDataReader or DbDataReader). Thank you – Falcon Aug 23 '11 at 13:01
I cannot see any differences, and I am receiving same output as yours from pgAdmin. Is there a possibility that the server configuration is messing it up? – Falcon Aug 23 '11 at 14:31
@Falcon: There is `bytea_output` property (`SHOW bytea_output`;) and I have default `hex` value, but I think it has nothing to do with your issue. – Grzegorz Szpetkowski Aug 23 '11 at 15:24
I have hex in config file, but Show byte_output says escape. I restarted the service, but no change. – Falcon Aug 23 '11 at 16:31
@Falcon: If you have proper output from pgAdmin (you can check under psql as well), then there is something wrong with data retrieval code. I can't reproduce your output. – Grzegorz Szpetkowski Aug 23 '11 at 16:44
I have used exactly same lines as you did. Can Npgsql be causing this behavior? Because narrowing down, it is the ExecuteReader that returns DataReader fetching formated input. Going back to previous question - how to change byte_output - because from what I reed "hex format is compatible with a wide range of external applications and protocols, and it tends to be faster to convert than the escape format". – Falcon Aug 23 '11 at 16:50
1

@Falcon: You can always add anonymous command like `new NpgsqlCommand("SET bytea_output TO 'hex'", conn).ExecuteNonQuery();` and check its value by `Console.WriteLine(new NpgsqlCommand("SHOW bytea_output", conn).ExecuteScalar());`. – Grzegorz Szpetkowski Aug 23 '11 at 17:06
@Falcon: Try another way with `NpgsqlCommand selectCmd = new NpgsqlCommand("SELECT data FROM binaryData", conn); byte[] result = (byte[])selectCmd.ExecuteScalar(); foreach (byte b in result) Console.Write("{0} ", b);`. That should return `0 0 0 0`. – Grzegorz Szpetkowski Aug 23 '11 at 17:30
Changing to hex did not make a difference. Your newest solution did not work neither. I will try to investigate problem deeper. If you have any ideas I will be happy to check them out. – Falcon Aug 24 '11 at 08:35
1

@Falcon: Edited answer, it looks like there is some bug in version Npgsql2.0.10. – Grzegorz Szpetkowski Aug 24 '11 at 09:31

score 3 · Answer 2 · answered Jun 17 '13 at 16:23

Ran the same problem, but managed to solve the problem without having to resort to changing drivers.

PHP documentation has a good description of what's happening, Postgres is returning escaped data. Check your output against an ASCII table, when you see 92 48 ... it's the text lead in to an octal escape sequence, \0xx, just like PHP describes.

Postgres's binary data type explains the output escaped octets. Fret not, there are code examples.

The solution is to tell Postgres how to bytea output is escaped, which can be either escape or hex. In this case issue the following to Postgres via psql to match your data:

ALTER DATABASE yourdb SET BYTEA_OUTPUT TO 'escape';

Postgres bytea column is returning string (char array) instead of byte array

2 Answers2

Linked