SQL99 IGNORE

On Fri, 13 Feb 2004, Jackson Miller wrote:

I notice that postgres does not support IGNORE.

I am currently migrating an app from a MySQL datastore to Postgres, and I
would really like to use IGNORE.

I am curious if there is a recommended work-araound.

What does it do? All I find in SQL99 Part 2 related to the word is that
it's reserved.

Stephan Szabo wrote:

On Fri, 13 Feb 2004, Jackson Miller wrote:

I notice that postgres does not support IGNORE.

What does it do? All I find in SQL99 Part 2 related to the word is that
it's reserved.

AFAICS it isn't listed at all in SQL2003...

Joe

I notice that postgres does not support IGNORE.

I am currently migrating an app from a MySQL datastore to Postgres, and I
would really like to use IGNORE.

I am curious if there is a recommended work-araound.

What does it do? All I find in SQL99 Part 2 related to the word is that
it's reserved.

If you insert from one table into another via a subselect, there's the
possibility that you may violate a unique contraint, simply because many
rows are being inserted at once.

The IGNORE keyword tells MySQL to simply ignore the duplicates if they
exist, not throw an error, and continue inserting the distinct, new
records.

Of course, there are much more logical ways of avoiding that problem,
like simply using a NOT IN on the subquery, or a NOT EXISTS, or maybe
even rethink the schema.

So, it surprises me that IGNORE is part of the spec, if it is. I don't
think I've ever run into a problem where something like this would fit.

Regards,
Jeff

Jeff Davis <jdavis-pgsql@empires.org> writes:

So, it surprises me that IGNORE is part of the spec, if it is.

It is not. It's listed as a keyword reserved for future use, but no
syntax or semantics are assigned to it. (While the SQL99 authors may
have had some specific future plan in mind, I see no particular reason
to assume that whatever they might've had in mind matches what MySQL is
doing with the keyword...)

regards, tom lane