How to insert a file from a folder in postgresql?

 insert into test(idserial, contenttext) values (4, pg_read_file('ex02.xml')); 

Two points: pg_read_file allowed to be used only by the superuser for security reasons. If you understand what you are doing, then this restriction can be circumvented, for example, by creating a new function on behalf of the superuser and marked as the security definer. From the superuser function declaration:

 CREATE OR REPLACE FUNCTION get_text_document(p_filename CHARACTER VARYING) RETURNS TEXT AS $$ SELECT pg_read_file($1); $$ LANGUAGE sql VOLATILE SECURITY DEFINER; 

Then you can use it from a regular user.

 insert into test(idserial, contenttext) values (4, get_text_document('ex02.xml')); 

The second point is more problematic: you can only read this from the directory where the base itself or the logs itself is located. The relative path up the hierarchy is also forbidden. Therefore, it is necessary either to transfer the file to the right place, or use the symlink to the directory (although it would be necessary to check if it suddenly refuses to walk on the symlink) or to use another method.

There is an option through a large object , again stored from the superuser:

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

The original function returns bytea, did not change it in the function itself. You can recode like this:

 insert into test(idserial, contenttext) values (4, convert_from(bytea_import('/etc/fstab'), 'utf8')); 

You can use a separate extension with basic I / O functions.

Or you can refer again to stored languages, but marked as unsafe: pl/perlu or pl/pythonu . They are unsafe just because they can access the file system, including without hindrance, on behalf of the database user. For example :

 CREATE FUNCTION gettext(url TEXT) RETURNS TEXT AS $$ import urllib2 try: f = urllib2.urlopen(url) return ''.join(f.readlines()) except Exception: return "" $$ LANGUAGE plpythonu; insert into test(idserial, contenttext) values (4, gettext('file://D:\html\ex02.xml'));