I think that simplest way is to use that --hex-blob
switch on mysqldump
and restore by psql, with decode(string text, type text)
. However it's not that simple, because you need to change a little produced dump (sed, awk), adding that decode function. For example:
mysqldump -u root -p --skip-quote-names --hex-blob --skip-triggers \
--compact --no-create-info mysql samples > prepg.dump
sed "s/0x\([0-9,A-F]*\))/decode('\1','hex'))/g" prepg.dump > pg.dump
psql session:
CREATE TABLE samples
(
file_id integer PRIMARY KEY,
file bytea
);
\i 'path/to/pg.dump'
You are forming an ad-hoc row type (effectively an anonymous record) with this expression:
(media_files.position, media_files.token, media_files.title)
in your aggregate function call:
ARRAY_AGG((media_files.position, media_files.token, media_files.title)
ORDER BY media_files.position) AS media_files
Arrays types can only be built upon well-known types. Your option is to announce such a type to the system and cast the record to it before forming the array.
Create a well-known composite type:
CREATE TYPE my_type AS (
position int -- data type?
,token text
,title text
)
I am guessing data types for lack of information here. Fill in your actual types.
Creating a table has the same effect: It announces a well known composite type to the system indirectly, as well. For this reason, you can (ab-)use a temporary table to register a composite type for the duration of the session:
CREATE TEMP TABLE my_type AS (
position int -- data type?
,token text
,title text
)
Either way, you can then cast your record:
ARRAY_AGG((media_files.position, media_files.token, media_files.title)::my_type
ORDER BY media_files.position) AS media_files
Then you can reference elements of the (now well-known) type by name:
SELECT media_files[1].position, media_files[1].token
FROM (
...
,ARRAY_AGG((media_files.position, media_files.token, media_files.title)::my_type
ORDER BY media_files.position) AS media_files
...
FROM ....
GROUP BY ...
) sub;
Now, Postgres can use these names for building a JSON value. Voilá.
Best Answer
You can use the operator
~
that uses the full power of regular expressionsA stricter version of the regex would be
'^#\d{4}$'
, so that it matches only strings that start with#
then have 4, and only 4 numbers, and then there is the end of the string.