To: pgsql-admin@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Re: Inserting .png file into bytea column
Date: Thu, 19 Nov 2015 20:32:38 +0100
Lines: 44
Message-ID: <n2l84p$jae$1@ger.gmane.org>
References: 
 <2BC18916DE4BF141AC1EFC55CE2CAE7D111B8C62@NAEANRFKXM01V.nadsusea.nads.navy.mil>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de; rv:1.8.1.21)
 Gecko/20090302 Thunderbird/2.0.0.21 Mnenhy/0.7.5.666
In-Reply-To: 
 <2BC18916DE4BF141AC1EFC55CE2CAE7D111B8C62@NAEANRFKXM01V.nadsusea.nads.navy.mil>
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Ferrell, Denise D CTR NSWCDD, H11 schrieb am 19.11.2015 um 15:07:
> Good Morning All,
>
> I am using PostgreSQL 9.3 on Linux Rehat...
>
> I'm trying to insert an image (.png format) into a table of flags.  I've tried the following but keep getting errors.
>
> CREATE TABLE FLAGS (country_code text, flag bytea);
>
> INSERT INTO flags VALUES ('AD', pg_read_file('/home/flags')::bytea);
>
> Get the following error:
> ERROR:   absolute path not allowed
> *********ERROR*************
> ERROR:  absolute path not allowed
> SQL State:  42501
>
> Any assistance would be greatly appreciated.
>
> Denise Ferrell
>

If the image is on the computer where the SQL client is running, pg_read_file() can not be used
because it is reading files on the server, not the client.

If you are open to a different SQL client, the one I am maintaining - SQL Workbench/J - can
do precisely what you want:

     INSERT INTO flags VALUES ('AD', {$blobfile=/home/flags});

This will read the file from the computer where SQL Workbench is running:

But again: this syntax *only* works with SQL Workbench/J

   http://www.sql-workbench.net/blob_example.html

You can also do this directly from within a result of a SELECT statement:

    http://www.sql-workbench.net/BlobDisplay_png.html

Full disclosure: I am the author of that tool.

Thomas


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin