Re: How to store text files in the postgresql?

On 06/06/2009 14:37, DimitryASuplatov wrote:
> But then comes the problem because the only command I found to read in
> the file content is COPY but the following command would not work

Yes, this wont work here - COPY is intended for reading an entire table
to or from a disk file, not a single column. It's used for bulk
loading/exporting of data - pg_dump uses it, for example, when backing
up a database.

BTW, "index" is a reserved word, so you shouldn't use it as a column
name. If you *really* have to use it, you'll need to double-quote it
every time, which is a PITA.

> mypdb=# copy pdb (filecontent) from
> '/home/sda/Documents/Work/PVA_India/PGA_test_modeling/pdb/1gm9.pdb'
> where index=1;
> ERROR: syntax error at or near "where"
> LINE 1: ...ts/Work/PVA_India/PGA_test_modeling/pdb/1gm9.pdb' where
> inde...
>
>
> QUESTION: what is the command to read the content of a plain text file
> into a SPECIFIED table entry?

I don't think you can, directly in the database. Instead, write a short
script which reads the file contents into a string and then issues an
SQL query to INSERT a new row or UPDATE an existing one. In PHP, for
example (untested):

// Get the file contents.
$filename = 'my_file';
$h = fopen($filename, 'r');
$lines = fread($h, filesize($filename));
fclose($h);

// Write the contents to the database.
$sql = 'insert into pdb("index", filename, filecontents) values ($1, $2,
$3)';
$conn = pg_connect([..your connection string..]);
pg_query_params($sql, array(1, $filename, $lines));

HTH,

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod(at)iol(dot)ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------