CODEWITHSUNDEEP
phppostgresqlencodingcharacter-encoding
VikR
VikR

Reputation: 5142

How to store and retrieve PHP serialize() in PostgreSQL?

I just converted to PostgreSQL from MySQL. In PostgreSQL my field (jobInfo) is a blob in (bytea) format. 

SELECT folderName, jobInfo
FROM jobs

In mySQL, that field came in as a string that could be unserialized. However, when I read the data in from postgres, it comes in looking like this:

\x613a31353a7b733a31373a2266696c65466f726d617456657273696f6e223b733a333a22342e30223b733a373a226a6f62 [.....]

Is there a way, in PHP or postgres, to decode that back to the serialized string of text characters?

UPDATE Per request, here is the code used to initially create the mySQL Blob field:

$theJobInfo = serialize($theJobInfo);

And SQL query:

UPDATE `jobs` SET `jobInfo` = theJobInfo
WHERE `folderName` = 'myFolderName'

Upvotes: 2

Views: 4588

Answers (2)

Evan Carroll
Evan Carroll

Reputation: 1

You can easily convert to and from bytea and text, like this

SELECT convert_from('foo'::bytea, 'UTF8');

However, I don't think that's relevant.

You're using the serailize() function of php

Note that this is a binary string which may include null bytes, and needs to be stored and handled as such. For example, serialize() output should generally be stored in a BLOB field in a database, rather than a CHAR or TEXT field.

That function returns binary data. And likewise, unserialize() expects binary data. If you don't want to use binary data in the php, you can make jobs.jobinfo a jsonb type and send the result of json_encode() to it, and then json_decode() from it..

Upvotes: 2

VikR
VikR

Reputation: 5142

This code retrieved the serialized string from the contents of the postgres ByteA field:

$serializedString = pg_unescape_bytea($contentsOfPostGresByteAField);

Thanks to Sergey Pashkov for the tip!

Upvotes: 0

Related Questions