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

Question

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'

If that bytea field represents a PHP Object, how do you know it will make sense as a string of characters? — Evan Carroll
– Evan Carroll, Commented Dec 28, 2016 at 23:41
Show the code you use to set the field in mysql/php originally. — Evan Carroll
– Evan Carroll, Commented Dec 28, 2016 at 23:41
Because it's serialized. I have saved and retrieved this data thousands of times from mySQL, so for that reason I do believe it makes sense as a string of characters. I have updated the original post with the PHP/SQL code per your request. — VikR
– VikR, Commented Dec 29, 2016 at 0:01

Evan Carroll · Accepted Answer · 2019-02-28 12:52:34Z

2

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..

edited Feb 28, 2019 at 12:52

answered Dec 28, 2016 at 23:53

Evan Carroll

1

Sign up to request clarification or add additional context in comments.

12 Comments

VikR Over a year ago

I do want to use binary data. That's why I'm saving it to a Blob field in mySQL.

VikR Over a year ago

The question is, once I get it into a postgres ByteA field, how do I retrieve it as a serialized string that I can then unserialize?

Evan Carroll Over a year ago

You don't retrieve it as as a text string. You retrieve it as a "binary string", or "scalar" if php wasn't dumb af. Then you feed the scalar (with its binary contents) to unserialize().

VikR Over a year ago

json has difficulty with PHP objects. It's possible to json encode PHP objects via JsonSerializable, but decoding them back to the original objects (not stdClass objects) is slow. That's why I'm seeking to find out if it's possible to use the serialized PHP objects.

VikR Over a year ago

Our comments crossed.

|

VikR · Accepted Answer · 2016-12-31 22:10:17Z

0

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!

answered Dec 31, 2016 at 22:10

VikR

5,16410 gold badges58 silver badges113 bronze badges

1 Comment

crmpicco Over a year ago

Is this a custom module?

Collectives™ on Stack Overflow

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

2 Answers 2

12 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related