Sam Mason wrote:
On Tue, Jan 13, 2009 at 03:28:18PM -0600, Jason Long wrote:
Steve Atkins wrote:
On Jan 13, 2009, at 10:34 AM, Jason Long wrote:
I would like to use PSQLFS(http://www.edlsystems.com/psqlfs/)
to store 100 GB of images in PostgreSQL.
Is there a better way to load 20,000 plus files reliably into Postgres?
That would imply that they're around 5MB on average? If they're all
under, say, 20MB (or maybe even much more) you should be able to handle
it by doing the most naive things possible.
This is correct. They are all around 5 MB.I just want an easy way to load the files into the DB and their original
path they were loaded from.
Is possible through SQL to load a file into a bytea column?
You'd need to generate the SQL somehow; if you know python it's probably
a pretty easy 20 or 30 lines of code to get this working. psycopg seems
to be the recommend way of accessing PG with python and you basically
want to be doing something like:
import psycopg2; filename = "myimage.jpeg" conn = psycopg2.connect(""); conn.cursor().execute( "INSERT INTO pictures (filename,data) VALUES (%s,%s);", [filename,psycopg2.Binary(open(filename,"rb").read())]); conn.commit();
This seems to do the right thing for me, and obviously needs to be put
into a loop of some sort. But it'll hopefully get you started.
Sam
Never used Python or Perl. I use primarily Java. I was thinking of doing something like
INSERT INTO pictures (filename,data) VALUES ('filename','/path/to/my/image/img0009.jpg');
But, this syntax doesn't seem to be supported.
Maybe I can use a custom C function to get the contents of the file. Then do something like
INSERT INTO pictures (filename,data) VALUES ('/path/to/my/image/img0009.jpg',getBinaryFileContents('/path/to/my/image/img0009.jpg'));
Is there some postgres contrib for something like this?