SQL 在 postgresql 数据库中插入图片
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22288898/
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
Insert an image in postgresql database
提问by Sarra
I would like to know How can I insert an image "bytea" into a table of my postgreSql database? I've been searching forums for hours and have seen the same question posted dozens of times, but yet to find a single answer. All I see is how to insert .jpeg's into an old column which isn't what I need.
我想知道如何将图像“bytea”插入到我的 postgreSql 数据库表中？我已经在论坛上搜索了几个小时，看到同样的问题发布了几十次，但还没有找到一个答案。我所看到的只是如何将 .jpeg 插入到我不需要的旧列中。
Here's the database table:
这是数据库表：
create table category (
"id_category" SERIAL,
"category_name" TEXT,
"category_image" bytea,
constraint id_cat_pkey primary key ("id_category"))without oids;
and when I add a new line, it doesn't work :
当我添加新行时，它不起作用：
insert into category(category_name,category_image) values('tablette', lo_import('D:\image.jpg'));
回答by Ravi
insert into category(category_name,category_image) values('tablette', bytea('D:\image.jpg'));
The above solution works if column type is bytea
如果列类型为 bytea，则上述解决方案有效
insert into category(category_name,category_image) values('tablette', lo_import('D:\image.jpg'));
The above solution works if column type is oid i.e., Blob
如果列类型为 oid 即 Blob，则上述解决方案有效
insert into category(category_name,category_image) values('tablette',decode('HexStringOfImage',hex));
The above decode function take two parameters. First parameter is HexString of Image.The second parameter is hex by default.Decode function coverts the hexString to bytes and store in bytea datatype column in postgres.
上面的解码函数有两个参数。第一个参数是Image的HexString。第二个参数默认是hex。Decode函数将hexString转换为bytes，存储在postgres的bytea数据类型列中。
回答by im_infamous
Something like this function (slightly adapted from here) could work out.
像这个函数（稍微改编自here）可以解决这个问题。
create or replace function img_import(filename text)
returns void
volatile
as $$
declare
content_ bytea;
loid oid;
lfd integer;
lsize integer;
begin
loid := lo_import(filename);
lfd := lo_open(loid,131072);
lsize := lo_lseek(lfd,0,2);
perform lo_lseek(lfd,0,0);
content_ := loread(lfd,lsize);
perform lo_close(lfd);
perform lo_unlink(loid);
insert into category values
('tablette',
content_);
end;
$$ language plpgsql
Use it like select * from img_import('D:\image.jpg');or rewrite to procedure if feeling like it.
select * from img_import('D:\image.jpg');如果喜欢，可以使用它或重写程序。
回答by Govind Gupta
create below function:
创建以下功能：
create or replace function bytea_import(p_path text, p_result out bytea)
language plpgsql as $$
declare
l_oid oid;
begin
select lo_import(p_path) into l_oid;
select lo_get(l_oid) INTO p_result;
perform lo_unlink(l_oid);
end;$$;
and use like this:
并像这样使用：
insert into table values(bytea_import('C:.png'));
回答by MorticusMaximus
Use SQL workbench - Database explorer - insert a row and follow the dialogue...
使用 SQL 工作台 - 数据库资源管理器 - 插入一行并按照对话...
