On Thu, Nov 19, 2015 at 6:19 AM, Achilleas Mantzios <achill@xxxxxxxxxxxxxxxxxxxxx> wrote:
On 19/11/2015 16:07, Ferrell, Denise D CTR NSWCDD, H11 wrote:
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.
http://www.postgresql.org/docs/9.2/static/functions-admin.html#FUNCTIONS-ADMIN-GENFILE
usepg_read_binary_file(filename text [, offset bigint, length bigint])
as documented
The documentation you linked to explains the problem:
The functions shown in Table 9-66 provide native access to files on the machine hosting the server. Only files within the database cluster directory and the log_directory can be accessed. Use a relative path for files in the cluster directory, and a path matching the log_directoryconfiguration setting for log files. Use of these functions is restricted to superusers.
The easiest way to do this on the client side is to read the file into a client-side variable and then use a prepared statement. In Perl, something like this:
use DBI;
my $dbh = $dbi->connect(...);
my $photo;open(PHOTO, "/home/flags");binmode PHOTO;while (<PHOTO>) {$photo .= $_;}my $sth = $dbh->prepare("insert into flags(country_code, flag) values(?, ?)");$sth->execute('AD', $photo);
Craig
Denise Ferrell
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
---------------------------------
Craig A. James
Chief Technology OfficerCraig A. James