Re: How to store text files in the postgresql? - Mailing list pgsql-general

From Raymond O'Donnell
Subject Re: How to store text files in the postgresql?
Date
Msg-id 4A2A8C0C.7040206@iol.ie
Whole thread Raw
In response to How to store text files in the postgresql?  (DimitryASuplatov <genesup@gmail.com>)
Responses Re: How to store text files in the postgresql?
List pgsql-general
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
------------------------------------------------------------------

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Upgrade from Postgres 8.2.4 to 8.3.5
Next
From: DimitryASuplatov
Date:
Subject: Re: How to store text files in the postgresql?