PostgreSQL SQL Statement to generate custom blob storage table

Question

I need to use the awesome-looking django-storages Database storage backend for a current project, but:

It can be used to map filenames to database blobs: so you have to use it with a special additional table created manually. The table should contain a pk-column for filenames (better to use the same type that FileField uses: nvarchar(100)), blob field (image type for example) and size field (bigint).

I'll admit it. ORM's have spoiled me. Its been a while since I've written an SQL statement, and I'm not at all familiar with PostgreSQL's flavor of SQL & BLOBs.

So, could someone pretty please help me out with a Postgres CREATE TABLE statement with three columns mentioned above (FILE_NAME, BLOB, FILE_SIZE), along with the appropriate column types?

If anyone's wondering. I ended up not using Database storage backend because its basically incompatible with Postgresql, has licensing issues, and didn't give me a good impression. I wrote my own custom storage backend that uses Text fields and base64 encoding as recommended here: http://wiki.postgresql.org/wiki/BinaryFilesInDB, which is awesome. I'd be happy to share if you reach out to me. — B Robster, Jul 12 '12 at 18:23

score 7 · Accepted Answer · edited May 23 '17 at 12:27

You generally use a column of type bytea for blobs in PostgreSQL. Your statement could look like this:

CREATE TABLE (
  file_name text PRIMARY KEY
 ,blob bytea
 ,file_size bigint
);

There is no nvarchar in PostgreSQL like in SQL Server. You could use varchar(100) but I would advise to just use text without modifier instead.

There are other options to store blobs. Refer to this related answer for more.

PostgreSQL SQL Statement to generate custom blob storage table

1 Answers1