Re: How to store text files in the postgresql?

Thank you very much.

I`ve also worked out how to do this simply from bash

./bin/psql mypdb <<EOF
insert into pdb values ('`cat /file/name`');
EOF

SDA

On Sat, 2009-06-06 at 16:32 +0100, Raymond O'Donnell wrote:
> 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
> ------------------------------------------------------------------