Início / dba / Perguntas / 1742
Accepted
SabreWolfy
Asked: 2011-03-15 12:38:14 +0800 CST

Como inserir dados (arquivo) em uma coluna de bytea do PostgreSQL?

  • 772

Esta questão não é sobre bytea v. oid v. blobs v. objetos grandes, etc.

Eu tenho uma tabela contendo um campo de chave primária integere um byteacampo. Eu gostaria de inserir dados no byteacampo. Isso pode, presumivelmente, ser feito por um dos PL/idiomas, e posso pensar em fazer isso PL/Pythonno futuro.

Como ainda estou testando e experimentando, gostaria simplesmente de inserir dados de um arquivo (no servidor) usando instruções SQL "padrão". Estou ciente de que apenas administradores com permissão de gravação no servidor poderão inserir dados da maneira que eu gostaria. Não estou preocupado com isso neste estágio, pois os usuários não estariam inserindo byteadados no momento. Pesquisei em vários sites do StackExchange, nos arquivos do PostgreSQL e na Internet em geral, mas não consegui encontrar uma resposta.

Edit: Esta discussão de 2008 implica que o que eu quero fazer não é possível. Como os byteacampos são usados ​​então?

Edit: Esta pergunta semelhante de 2005 permanece sem resposta.

Resolvido: Os detalhes fornecidos aqui no psycopgsite forneceram a base para uma solução que escrevi em Python. Também pode ser possível inserir dados binários em uma byteacoluna usando PL/Python. Não sei se isso é possível usando SQL "puro".

postgresql plpgsql

6 respostas

  1. Usar pg_read_file('location_of file')::bytea.

    Por exemplo,

    create table test(id int, image bytea);
insert into test values (1, pg_read_file('/home/xyz')::bytea);

    Manual

    • 33
  2. Best Answer

    como superusuário:

    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;$$;

    lo_getfoi introduzido em 9.4, então para versões mais antigas você precisaria:

    create or replace function bytea_import(p_path text, p_result out bytea) 
                   language plpgsql as $$
declare
  l_oid oid;
  r record;
begin
  p_result := '';
  select lo_import(p_path) into l_oid;
  for r in ( select data 
             from pg_largeobject 
             where loid = l_oid 
             order by pageno ) loop
    p_result = p_result || r.data;
  end loop;
  perform lo_unlink(l_oid);
end;$$;

    então:

    insert into my_table(bytea_data) select bytea_import('/my/file.name');
    • 31

  3. Essa solução não é exatamente eficiente em termos de tempo de execução, mas é trivialmente fácil em comparação com a criação de seus próprios cabeçalhos para COPY BINARY. Além disso, não requer bibliotecas ou linguagens de script fora do bash.

    Primeiro, converta o arquivo em um hexdump, dobrando o tamanho do arquivo. xxd -pnos aproxima bastante, mas lança algumas novas linhas irritantes que temos que cuidar:

    xxd -p /path/file.bin | tr -d '\n' > /path/file.hex

    Em seguida, importe os dados no PostgreSQL como um textcampo muito grande. Esse tipo contém até um GB por valor de campo, portanto, devemos ficar bem para a maioria dos propósitos:

    CREATE TABLE hexdump (hex text); COPY hexdump FROM '/path/file.hex';

    Agora que nossos dados são uma string hexadecimal gratuita grande, usamos PostgresQL's decodepara colocá-los em um byteatipo:

    CREATE TABLE bindump AS SELECT decode(hex, 'hex') FROM hexdump;
    • 16

  4. A resposta com xxd é boa e, para arquivos pequenos, muito rápida. Abaixo está um script de exemplo que estou usando.

    xxd  -p /home/user/myimage.png | tr -d '\n' > /tmp/image.hex
echo "
    -- CREATE TABLE hexdump (hex text);
    DELETE FROM hexdump;
    COPY hexdump FROM '/tmp/image.hex';

    -- CREATE TABLE bindump (binarydump bytea);
    DELETE FROM bindump;

    INSERT INTO bindump (binarydump)  
    (SELECT decode(hex, 'hex') FROM hexdump limit 1);

    UPDATE users 
    SET image= 
    (
        SELECT decode(hex, 'hex') 
        FROM hexdump LIMIT 1
    )  
    WHERE id=15489 ;
    " | psql mydatabase
    • 5

  5. Aqui está como fazer isso sem privilégios de superusuário (como no Heroku).

    \lo_import '/cygdrive/c/Users/Chloe/Downloads/Contract.pdf'
update contracts set contract = lo_get(:LASTOID) where id = 77;

    Você pode usar \lo_listpara ver os objetos grandes e \lo_unlinkexcluí-los. O contractcampo no meu exemplo é bytea.

         Column      |            Type             |
contract         | bytea                       |
    • 0

  6. Use a função COPY BINARY do Postgres . Isso é amplamente equivalente às tabelas externas da Oracle .

    • -1

relate perguntas