Postgres Pro
Downloads
Home   >   mailing lists

Thread: SQL99 IGNORE

SQL99 IGNORE

From
Jackson Miller
Date:
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.

Thanks,
-Jackson

Re: SQL99 IGNORE

From
Stephan Szabo
Date:
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.

Re: SQL99 IGNORE

From
Joe Conway
Date:
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

Re: SQL99 IGNORE

From
Jeff Davis
Date:
> > 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

Re: SQL99 IGNORE

From
Tom Lane
Date:
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