[GENERAL] How to store text files in the postgresql?
Hello, I am very new to postgresql database. I`ve used a little of MySql previously. My task is to store a lot (10^5) of small ( <10 MB) text files in the database with the ability to restore them back to the hard drive on demand. That means that I need two functions. First - grab file from the directory, store it in the database and delete from the disk; second - recreate in back to the disk. 1/ Is it possible? 2/ Could you give me some quick tips on how to manage it from the start so that I knew what to look for in the manual? Thank you for your time. Dimitry -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 < 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
> r...@iol.ie
> Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
> --
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to store text files in the postgresql?
Thank you for answering. I`ve read a manual and now I have a more advanced question. 1/ I`ve created a table in the database mypdb=# create table pdb( index int, filename text, filecontent text ); 2/ Then I want to read a file into it First I insert metainfo mypdb=# insert into pdb (index , filename) values (1, '/home/sda/Documents/Work/PVA_India/PGA_test_modeling/pdb/1gm9.pdb'); INSERT 0 1 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 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? Thank you for your time. SDA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ERROR: row is too big: size 8176, maximum size 8160
Hello, I am using postgresql-8.3.7 and have recently got this error: org.postgresql.util.PSQLException: ERROR: row is too big: size 8168, maximum size 8160 How is that possible in version 8+? I`ve found some old version 7 manuals that suggest editing /src/include/config.h file but I did not find one for my sources. How could I overcome this problem? Do I need to recompile the database? Do I need to recreate the database already present? Thank you. SDA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
