postgresql 如何将 Postgres bytea 列下载为文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6730729/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to download Postgres bytea column as file
提问by Chong Hsiung
Currently, i have a number of files stored in postgres 8.4 as bytea. The file types are .doc, .odt, .pdf, .txt and etc.
目前，我有许多文件作为 bytea 存储在 postgres 8.4 中。文件类型为 .doc、.odt、.pdf、.txt 等。
May i know how to download all the file stored in Postgres because i need to to do a backup. I need them in their original file type instead of bytea format.
我可以知道如何下载存储在 Postgres 中的所有文件，因为我需要进行备份。我需要它们的原始文件类型而不是 bytea 格式。
Thanks!
谢谢！
回答by Grzegorz Szpetkowski
One simple option is to use COPYcommand with encodeto hex format and then apply xxdshell command (with -p continuous hexdump styleswitch). For example let's say I have jpg image in bytea column in samples table:
一种简单的选择是使用十六进制格式的COPY命令，encode然后应用xxdshell 命令（使用 -p连续十六进制转储样式开关）。例如，假设我在示例表的 bytea 列中有 jpg 图像：
\copy (SELECT encode(file, 'hex') FROM samples LIMIT 1) TO
'/home/grzegorz/Desktop/image.hex'
$ xxd -p -r image.hex > image.jpg
As I checked it works in practice.
当我检查它在实践中有效。
回答by paramecium
Try this:
尝试这个：
COPY (SELECT yourbyteacolumn FROM yourtable WHERE <add your clauses here> ...) TO 'youroutputfile' (FORMAT binary)
回答by brighters
If you have a lot of data to download then you can get the lines first and then iterate through each one writing the bytea field to file.
如果您有大量数据要下载，那么您可以先获取行，然后遍历每个将 bytea 字段写入文件的行。
$resource = pg_connect('host=localhost port=5432 dbname=website user=super password=************');
// grab all the user IDs
$userResponse = pg_query('select distinct(r.id) from resource r
join connection c on r.id = c.resource_id_from
join resource rfile on c.resource_id_to = rfile.id and rfile.resource_type_id = 10
join file f on rfile.id = f.resource_id
join file_type ft on f.file_type_id = ft.id
where r.resource_type_id = 38');
// need to work through one by one to handle data
while($user = pg_fetch_array($userResponse)){
$user_id = $user['id'];
$query = 'select r.id, f.data, rfile.resource_type_id, ft.extension from resource r
join connection c on r.id = c.resource_id_from
join resource rfile on c.resource_id_to = rfile.id and rfile.resource_type_id = 10
join file f on rfile.id = f.resource_id
join file_type ft on f.file_type_id = ft.id
where r.resource_type_id = 38 and r.id = ' . $user_id;
$fileResponse = pg_query($query);
$fileData = pg_fetch_array($fileResponse);
$data = pg_unescape_bytea($fileData['data']);
$extension = $fileData['extension'];
$fileId = $fileData['id'];
$filename = $fileId . '.' . $extension;
$fileHandle = fopen($filename, 'w');
fwrite($fileHandle, $data);
fclose($fileHandle);
}
回答by Denis de Bernardy
Best I'm aware, bytea to file needs to be done at the app level.
最好的我知道， bytea 到文件需要在应用程序级别完成。
(9.1 might change this with the filesystem data wrapper contrib. There's also a lo_export function, but it is not applicable here.)
（9.1 可能会通过文件系统数据包装器 contrib 改变这一点。还有一个 lo_export 函数，但它在这里不适用。）
回答by dmitriy
DO $$
DECLARE
l_lob_id OID;
r record; BEGIN
for r in
select data, filename from bytea_table
LOOP
l_lob_id:=lo_from_bytea(0,r.data);
PERFORM lo_export(l_lob_id,'/home/...'||r.filename);
PERFORM lo_unlink(l_lob_id);
END LOOP;
END; $$
回答by Chris Cogdon
Here's the simplest thing I could come up with:
这是我能想到的最简单的事情：
psql -qAt "select encode(file,'base64') from files limit 1" | base64 -d
The -qAtis important as it strips off any formatting of the output. These options are available inside the psqlshell, too.
这-qAt很重要，因为它剥离了输出的任何格式。这些选项在psqlshell 中也可用。
