Re: pgsql vs mysql

# JanWieck(at)Yahoo(dot)com / 2006-07-11 12:04:07 -0400:
> On 6/30/2006 1:07 PM, Merlin Moncure wrote:
>
> >* mysql has a few features here and there which are nice...just to
> >name a few, flush tables with lock, multiple insert, etc

(...)

> The multiple insert stuff is not only non-standard, it also encourages
> the bad practice of using literal values directly in the SQL string
> versus prepared statements with place holders. It is bad practice
> because it introduces SQL injection risks since the responsibility of
> literal value escaping is with the application instead of the driver.
>
> Everything that teaches new developers bad things counts as a
> disadvantage in my book, so -1 on that for MySQL too.

Those "multiple inserts" are really inserts with Table Value
Constructors, which are "table literals", SQL:2003 F641 (see
http://www.postgresql.org/docs/8.1/static/unsupported-features-sql-standard.html).
TVCs are useful in many more contexts than just inserts. An email from a past
thread on this list:

: # mag(at)fbab(dot)net / 2005-09-20 20:45:21 +0200:
: > I was thinking if this was possible in some way..
: > I have this table where we have X and Y coordinates, and i need to
: > select several in one go.
: >
: > # select * from xy where (x = 1 and y = 2) or (x = 2 and y = 2);
: >
: > This works but are not so nice looking.
: > It would be nice to be able to do it like this:
: >
: > # select * from xy where (x, y) in ((1, 2), (2, 2));
: >
: > But that doesn't work.
: > A funny thing is that this works:
: >
: > # select * from xy where (x, y) = (1, 2);
: >
: > What's the most efficient way of doing these kind of selects?
:
: You'd need to write that as
:
: (x, y) IN VALUES (1, 2), (2, 2)
:
: Unfortunately, PostgreSQL's support for table value constructors
: is very weak.

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991