Re: [SQL] bytea or blobs?
On this subject, isn't it actually better to just store image names in the database and pull the image itself from a directory? That's what I do on my site because I didn't want to bloat up my database unnecessarily. Are there additional benefits to storing the image information in the database that I'm missing? Thanks, Jeremy -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Jan Wieck Sent: Tuesday, February 17, 2004 10:08 AM To: beyaNet Consultancy Cc: [EMAIL PROTECTED] Subject: Re: [SQL] bytea or blobs? beyaNet Consultancy wrote: > Hi, > what I am trying to do is to be able to store images in my database. > What I wanted to know is this: > > 1. Would it be better to have the image field type as a bytea or a > blob? I have heard it mentioned that bytea would be better as doing > data dumps would also insure that the image was saved as well! > > 2. Would it be better to make reference to mp3 files (i.e. storing the > address of the image /images/*.jpg) or is it feasible to store the mp3 > in the database as bytea or blobs as well? > > many thanks in adavance If you want the same access and data protection (including transactional semantics and network access) as for your other data, it has to be inside the database. Now unless you're going for video streams, I think most databases (even MySQL as of 4.0) can handle multi-megabyte columns just fine, and as long as they contain just some 7bit ascii you'll be absolutely portable. Storing the data in Postgres in regular tables will give you the least amount of backup etc. problems, as they just don't exist in that case. To achieve this, I'd recommend to let the application convert the binary data to and from base64, which is a well defined and not too bloated standard. It is reasonably fast too. That will let you easily embed any binary data into a text or varchar column. You don't even need to quote it any more when inserting it into the query string. To get the ultimate out of Postgres' storage capabilities then, I would create a data table with a bytea column, hidden behind a view and rewrite rules that use encode(data, 'base64') and decode(data, 'base64') when rewriting the queries. The bytea column of that table will be configured without toast compression if the intended data usually is compressed, like jpeg or mp3. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] CHAR(n) always trims trailing spaces in 7.4
Also, to make char(n) even more annoying, I had the one character value "K" stored in a column that was char(2). When I pulled it from the database and tried to compare it to a variable with a value of "K" it came out inequal. Of course in mysql, that was not a problem. Jeremy -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Richard Huxton Sent: Wednesday, February 18, 2004 4:40 AM To: Tom Lane; scott.marlowe Cc: elein; news.postgresql.org; [EMAIL PROTECTED] Subject: Re: [SQL] CHAR(n) always trims trailing spaces in 7.4 On Wednesday 18 February 2004 00:25, Tom Lane wrote: > "scott.marlowe" <[EMAIL PROTECTED]> writes: > > But then this: > > select 'x'||' '||'x' > > should produce xx, but it produces x x. > > No, because the imputed type of those literals is text. You'd have to > cast the middle guy to char(n) explicitly to make its trailing spaces go > away when it's reconverted to text. > > The real issue here is that trailing spaces in char(n) are semantically > insignificant according to the SQL spec. The spec is pretty vague about > which operations should actually honor that insignificance --- it's > clear that comparisons should, less clear about other things. I think > the 7.4 behavior is more consistent than what we had before, but I'm > willing to be persuaded to change it again if someone can give an > alternate definition that's more workable than this one. [rant on] I've never really understood the rationale behind char(n) in SQL databases (other than as backward compatibility with some old mainframe DB). Insignificant spaces? If it's not significant, why is it there? You could have a formatting rule that specifies left-aligned strings space-padded (as printf) but that's not the same as mucking about appending and trimming spaces. The only sensible definition of char(n) that I can see would be: A text value of type char(n) is always "n" characters in length. If you assign less than "n" characters, it is right-padded with spaces. In all other respects it behaves as any other text type of length "n" with right-trailing spaces. [rant off - ah, feel better for that :-] -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Inserting NULL into Integer column
Hi,
in mysql I was able to make an insert such as:
INSERT INTO TABLE (integervariable) VALUES ('')
and have it either insert that variable, or insert the default if it had
been assigned. In postgresql it gives and error every time that this is
attempted. Since I have so many queries that do this on my site already, is
there any way to set up a table so that it just accepts this sort of query?
Thanks,
Jeremy
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Inserting NULL into Integer column
Scott,
I understand that MySQL's adherence to the standards must be lazy as I am
running into frequent issues as I transfer my site. Unfortunately I have
over 2500 queries, and many more of them needed to be rewritten than I ever
would have imagined. I guess MySQL is the IE of open source DB, and
PostgreSQL is Netscape / Mozilla, in more ways than one.
I guess in some sense, since I relied on MySQL's laziness, my code also
became a bit lazy. There are many locations where I accept user input from
a form, and then have a process page. And on that process page I might have
hundreds of variables that look like:
$input = $_POST['input'];
and in the old days, if that was an empty value and inserted into a mysql
query, it would just revert to the default. Now it looks like I need to:
$input = $_POST['input'];
if (!$input) {
$input = DEFAULT;
}
over and over and over and over :) I guess I am just looking for a
shortcut since the site conversion has already taken a week and counting,
when I originally was misguided enough to think it would take hours.
Anyway, the help on this list is much appreciated..
Jeremy
-Original Message-
From: scott.marlowe [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 18, 2004 2:44 PM
To: Jeremy Smith
Cc: [EMAIL PROTECTED]
Subject: Re: [SQL] Inserting NULL into Integer column
On Wed, 18 Feb 2004, Jeremy Smith wrote:
> Hi,
>
> in mysql I was able to make an insert such as:
>
> INSERT INTO TABLE (integervariable) VALUES ('')
>
> and have it either insert that variable, or insert the default if it had
> been assigned. In postgresql it gives and error every time that this is
> attempted. Since I have so many queries that do this on my site already,
is
> there any way to set up a table so that it just accepts this sort of
query?
First off, the reason for this problem is that Postgresql adheres to the
SQL standard while MySQL heads off on their own, making it up as they go
along. This causes many problems for people migrating from MySQL to
almost ANY database.
Phew, now that that's out of the way, here's the standard ways of doing
it.
Use DEFAULT: If no default is it will insert a NULL, otherwise the
default will be inserted:
insert into table (integervar) values (DEFAULT);
OR
Leave it out of the list of vars to be inserted
insert into table (othervars, othervars2) values ('abc',123);
OR
Insert a NULL if that's what you want:
insert into table (integervar) values (NULL);
Note that NULL and DEFAULT are not quoted.
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Inserting NULL into Integer column
I've run into this kind of thing before. IT helps if you have an array of
all your fields like:
$fields = array("field1","field3","last_name");
and then you can foreach across the input:
foreach($fields as $f){
if (!$_POST[$f]){
$_POST[$f]='DEFAULT';
} else {
$_POST[$f] = "'".$_POST[$f]."'";
}
}
Wow, great idea. I will definitely do this, thanks alot.
Well, you might find yourself rewriting fair portions of your site, but
usually you wind up with better code and better checking, so it's a bit of
a trade off.
No doubt that this is true. Of course even without the better code and
error checking, the extra features like stored procedures and automatic row
locking was more than enough to make the switch worth it.
Thanks again!
Jeremy
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] DISTINCT ON troubles
Hi, I have a query that reads: SELECT DISTINCT ON (messageboard.threadid) messageboard.threadid, messageboard.topic, owner.ownerid, owner.username FROM messageboard, owner WHERE messageboard.ownerid=owner.ownerid AND messageboard.leagueid = '$leagueid' ORDER BY messageboard.messageid DESC LIMIT $entries_on_page OFFSET $beginThread" The purpose of this query is to retrieve unique threadid's in the order of the most recent posts that have been made to each thread. When I use this query I get an error that: "SELECT DISTINCT ON expressions must match initial ORDER BY expressions". Of course, if I put ORDER BY threadid in the query it would order it in the order that the thread was created, not in the last post made. I have tried using GROUP BY threadID, I get a similar order. Am I just approaching this all wrong and need to create a temporary table and draw from that, or is there a way to salvage this query? Thanks so much, Jeremy ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] DISTINCT ON troubles
Thanks Josh, I'll do that, I just wasn't sure if I was missing something obvious. Jeremy -Original Message- From: Josh Berkus [mailto:[EMAIL PROTECTED] Sent: Thursday, February 19, 2004 2:29 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [SQL] DISTINCT ON troubles Jeremy, > Am I just approaching this all wrong and need to create a temporary table > and draw from that, or is there a way to salvage this query? Think about using a subquery instead of the DISTINCT ON approach. I don't think you can get what you want with DISTINCT ON. A temporary table is not necessary. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
