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@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------