[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-admin
Subject: Re: [ADMIN] Data type for storing attached file
From: Bear Giles <bgiles () coyotesong ! com>
Date: 2017-07-11 18:01:40
Message-ID: CALBNtw5tzE1Wycc3z6w6_84oSAkE1zjNS=JoQAyQPW6mpkmnyg () mail ! gmail ! com
[Download RAW message or body]
If you want to get fancy you can create a "user defined type" for each type
of attachment. A very vanilla implementation would just make it a little
easier to figure out what you have but a RECORD type would allow you to
cache "interesting" information (e.g., the size of an image in pixels) and
a full implementation in C, java, etc., could compute that "interesting"
information from the raw data and even perform different types of
manipulation. E.g., you could have a "user defined function" that rescales
the image to a desired size.
The downsides are that few frameworks understand UDT. That's not a problem
if you're writing everything by hand or are able to use hand-written code
for specific functionality but could be a problem if you're trying to do
everything via a framework.
Another potential downside is if you don't host your own instance, e.g.,
you're using an AWS RDS instance. They won't allow you to use "C"
extensions. (You can still use pure SQL extensions.)
Finally if you want to be generic - you want "attachments" but don't want
to require explicit types, you can either use a generic UDT or create a
polymorphic one where the DB sees the UDT but you have UDF that allow you
to cast the type to a specific format if possible.
On Tue, Jul 11, 2017 at 9:31 AM, Ashish Tiwari <tashish786@gmail.com> wrote:
> Hi Ferrell ,
>
> I have encoded the files in base64 and stored binary data in text.this can
> be stored in bytea too.
>
> Thanks.
>
> On Jul 11, 2017 8:41 PM, "Ferrell, Denise D CTR NSWCDD, H11" <
> denise.ferrell.ctr@navy.mil> wrote:
>
>> Using PostgreSQL v9.3 on Linux...
>>
>> What data type would be used to store an attachment (file, could be .txt,
>> .doc, .xls, etc.) into a column?
>>
>> Thank you in advance,
>> Denise
>>
>>
>> --
>> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-admin
>>
>
[Attachment #3 (text/html)]
<div dir="ltr"><div class="gmail_default" \
style="font-family:tahoma,sans-serif;color:#000000">If you want to get fancy you can \
create a "user defined type" for each type of attachment. A very vanilla \
implementation would just make it a little easier to figure out what you have but a \
RECORD type would allow you to cache "interesting" information (e.g., the \
size of an image in pixels) and a full implementation in C, java, etc., could compute \
that "interesting" information from the raw data and even perform different \
types of manipulation. E.g., you could have a "user defined function" that \
rescales the image to a desired size.</div><div class="gmail_default" \
style="font-family:tahoma,sans-serif;color:#000000"><br></div><div \
class="gmail_default" style="font-family:tahoma,sans-serif;color:#000000">The \
downsides are that few frameworks understand UDT. That's not a problem if \
you're writing everything by hand or are able to use hand-written code for \
specific functionality but could be a problem if you're trying to do everything \
via a framework.</div><div class="gmail_default" \
style="font-family:tahoma,sans-serif;color:#000000"><br></div><div \
class="gmail_default" style="font-family:tahoma,sans-serif;color:#000000">Another \
potential downside is if you don't host your own instance, e.g., you're using \
an AWS RDS instance. They won't allow you to use "C" extensions. (You \
can still use pure SQL extensions.)</div><div class="gmail_default" \
style="font-family:tahoma,sans-serif;color:#000000"><br></div><div \
class="gmail_default" style="font-family:tahoma,sans-serif;color:#000000">Finally if \
you want to be generic - you want "attachments" but don't want to \
require explicit types, you can either use a generic UDT or create a polymorphic one \
where the DB sees the UDT but you have UDF that allow you to cast the type to a \
specific format if possible.</div></div><div class="gmail_extra"><br><div \
class="gmail_quote">On Tue, Jul 11, 2017 at 9:31 AM, Ashish Tiwari <span \
dir="ltr"><<a href="mailto:tashish786@gmail.com" \
target="_blank">tashish786@gmail.com</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div dir="auto">Hi Ferrell , <div dir="auto"><br></div><div \
dir="auto">I have encoded the files in base64 and stored binary data in text.this can \
be stored in bytea too.<div dir="auto"><br></div><div \
dir="auto">Thanks.</div></div></div><div class="HOEnZb"><div class="h5"><div \
class="gmail_extra"><br><div class="gmail_quote">On Jul 11, 2017 8:41 PM, \
"Ferrell, Denise D CTR NSWCDD, H11" <<a \
href="mailto:denise.ferrell.ctr@navy.mil" \
target="_blank">denise.ferrell.ctr@navy.mil</a>> wrote:<br \
type="attribution"><blockquote class="gmail_quote" style="margin:0 0 0 \
.8ex;border-left:1px #ccc solid;padding-left:1ex">Using PostgreSQL v9.3 on \
Linux...<br> <br>
What data type would be used to store an attachment (file, could be .txt, .doc, .xls, \
etc.) into a column?<br> <br>
Thank you in advance,<br>
Denise<br>
<br>
<br>
--<br>
Sent via pgsql-admin mailing list (<a href="mailto:pgsql-admin@postgresql.org" \
target="_blank">pgsql-admin@postgresql.org</a>)<br> To make changes to your \
subscription:<br> <a href="http://www.postgresql.org/mailpref/pgsql-admin" \
rel="noreferrer" target="_blank">http://www.postgresql.org/mail<wbr>pref/pgsql-admin</a><br>
</blockquote></div></div>
</div></div></blockquote></div><br></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic