How to insert a text file into a field in PostgreSQL?

Question

How to insert a text file into a field in PostgreSQL?

I'd like to insert a row with fields from a local or remote text file.

I'd expect a function like gettext() or geturl() in order to do the following:

% INSERT INTO collection(id, path, content) VALUES(1, '/etc/motd', gettext('/etc/motd'));

-S.

Answer 1

The easiest method would be to use one of the embeddable scripting languages. Here's an example using plpythonu:

CREATE FUNCTION gettext(url TEXT) RETURNS TEXT
AS $$
import urllib2
try:
  f = urllib2.urlopen(url)
  return ''.join(f.readlines())
except Exception:
  return ""
$$ LANGUAGE plpythonu;

One drawback to this example function is its reliance on urllib2 means you have to use "file:///" URLs to access local files, like this:

select gettext('file:///etc/motd');

Answer 2

Thanks for the tips. I've found another answer with a built in function. You need to have super user rights in order to execute that!

-- 1. Create a function to load a doc
-- DROP FUNCTION get_text_document(CHARACTER VARYING);
CREATE OR REPLACE FUNCTION get_text_document(p_filename CHARACTER VARYING)
  RETURNS TEXT AS $$
  -- Set the end read to some big number because we are too lazy to grab the length
  -- and it will cut of at the EOF anyway
  SELECT CAST(pg_read_file(E'mydocuments/' || $1 ,0, 100000000) AS TEXT);
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;
ALTER FUNCTION get_text_document(CHARACTER VARYING) OWNER TO postgres;

-- 2. Determine the location of your cluster by running as super user:
SELECT name, setting FROM pg_settings WHERE name='data_directory'; 

-- 3. Copy the files you want to import into <data_directory>/mydocuments/
--    and test it: 
SELECT get_text_document('file1.txt');

-- 4. Now do the import (HINT: File must be UTF-8) 
INSERT INTO mytable(file, content)
  VALUES ('file1.txt', get_text_document('file1.txt'));

Answer 3

Postgres's COPY command is exactly for this.

My advice is to upload it to a temporary table, and then transfer the data across to your main table when you're happy with the formatting. e.g.

CREATE table text_data (text varchar)
COPY text_data FROM 'C:\mytempfolder\textdata.txt';
INSERT INTO main_table (value) 
  SELECT string_agg(text, chr(10)) FROM text_data;
DROP TABLE text_data; 

Also see this question.

Answer 4

You can't. You need to write a program that will read file content's (or URL's) and store it into the desired field.

Answer 5

Use COPY instead of INSERT

reference: http://www.commandprompt.com/ppbook/x5504#AEN5631