Catching unique_violation exception on specific column/index

Alexey Dokuchaev schrieb am 11.06.2018 um 12:10:

I have a table with several UNIQUE and CHECK constraints. One of these
UNIQUE constraints actually *can* be violated -- not on the table level,
of course, but on the application level -- meaning, if the entry with
particular foo_key is already in there, do not throw an exception, just
silently do nothing.

The usual approach ("EXCEPTION WHEN unique_violation THEN ... END") does
not really cut it because I want to catch unique_violation only when it
happens on "foo_key", and still rightfully complain on others. However,
there is no "EXCEPTION WHEN unique_violation ON (foo_key)" or something.
Is there a way to do this without using triggers and in a less ugly way
than the code below?

IF SQLERRM = 'duplicate key value violates unique constraint' ||
' "foo_key"' THEN
RAISE NOTICE '%: %', SQLSTATE, SQLERRM;
ELSE
RAISE EXCEPTION '%: %', SQLSTATE, SQLERRM;
END IF;

./danfe

What's wrong with:

INSERT ...
ON CONFLICT (foo_key) DO NOTHING

On Mon, Jun 11, 2018 at 05:10:33PM +0700, Alexey Dokuchaev wrote:

The usual approach ("EXCEPTION WHEN unique_violation THEN ... END") does
not really cut it because I want to catch unique_violation only when it
happens on "foo_key", and still rightfully complain on others. However,
there is no "EXCEPTION WHEN unique_violation ON (foo_key)" or something.
Is there a way to do this without using triggers and in a less ugly way
than the code below?

Oh, I completely forgot about rules:

CREATE OR REPLACE RULE ignore_duplicate_foo AS
ON INSERT TO table_bar WHERE EXISTS (
SELECT 1 FROM table_bar WHERE foo = NEW.foo)
DO INSTEAD NOTHING;

Sorry for the noise.

./danfe

Am 11.06.2018 um 12:58 schrieb Alexey Dokuchaev:

What's wrong with:

INSERT ...
ON CONFLICT (foo_key) DO NOTHING

Nothing I guess, except that it is available since 9.5 (right?), and I try
to stay compatible with 9.3. Sorry for not saying this in the first place.

./danfe

... 9.3 will be out of support soon

Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

On Mon, Jun 11, 2018 at 12:30:13PM +0200, Thomas Kellerer wrote:

Alexey Dokuchaev schrieb am 11.06.2018 um 12:10:

I have a table with several UNIQUE and CHECK constraints. One of these
UNIQUE constraints actually *can* be violated -- not on the table level,
of course, but on the application level -- meaning, if the entry with
particular foo_key is already in there, do not throw an exception, just
silently do nothing.

What's wrong with:

INSERT ...
ON CONFLICT (foo_key) DO NOTHING

Nothing I guess, except that it is available since 9.5 (right?), and I try
to stay compatible with 9.3. Sorry for not saying this in the first place.

./danfe

Alexey Dokuchaev schrieb am 11.06.2018 um 12:58:

I have a table with several UNIQUE and CHECK constraints. One of these
UNIQUE constraints actually *can* be violated -- not on the table level,
of course, but on the application level -- meaning, if the entry with
particular foo_key is already in there, do not throw an exception, just
silently do nothing.

What's wrong with:

INSERT ...
ON CONFLICT (foo_key) DO NOTHING

Nothing I guess, except that it is available since 9.5 (right?), and I try
to stay compatible with 9.3. Sorry for not saying this in the first place.

Andreas already mentioned that 9.3 will be EOL soon (3 months from now), but
the performance and efficiency for concurrent execution of ON CONFLICT is
much better than anything you can implement yourself.

If that functionality is an important part of your code, you should consider
upgrading to 10 (or 9.6 if your are really conservative) rather sooner
than later.

On Mon, Jun 11, 2018 at 01:26:16PM +0200, Thomas Kellerer wrote:

If that functionality is an important part of your code, you should
consider upgrading to 10 (or 9.6 if your are really conservative)
rather sooner than later.

Oh well, fair enough. As much as I'd love to stick to the lowest
supported (and sometimes even unsupported) versions, ON CONFLICT is
indeed very handy, esp. since I have a few UPSERT's implemented the
old way already (via catching the "unique_violation" exception).

Shall I update to 9.6/10, I have a bit off-topic (to the original
subject) question: right now, when I need to get the length of an
array (never multidimensional), I do this:

coalesce(array_length(foo, 1), 0);

In 9.4+, I can call cardinality(). I'm a bit hesitant: is doing so
semantically correct, or should I still do coalesce(..., 0) in this
case? This is not about the outcome, it's whether cardinality() is
semantically correct to obtain the number of the array items, or it
was introduced for other means?

./danfe

On 06/11/2018 11:21 AM, Alexey Dokuchaev wrote:

On Mon, Jun 11, 2018 at 01:26:16PM +0200, Thomas Kellerer wrote:

If that functionality is an important part of your code, you should
consider upgrading to 10 (or 9.6 if your are really conservative)
rather sooner than later.

Oh well, fair enough. As much as I'd love to stick to the lowest
supported (and sometimes even unsupported) versions, ON CONFLICT is
indeed very handy, esp. since I have a few UPSERT's implemented the
old way already (via catching the "unique_violation" exception).

Shall I update to 9.6/10, I have a bit off-topic (to the original
subject) question: right now, when I need to get the length of an
array (never multidimensional), I do this:

coalesce(array_length(foo, 1), 0);

In 9.4+, I can call cardinality(). I'm a bit hesitant: is doing so
semantically correct, or should I still do coalesce(..., 0) in this
case? This is not about the outcome, it's whether cardinality() is
semantically correct to obtain the number of the array items, or it
was introduced for other means?

https://www.postgresql.org/docs/10/static/arrays.html

8.15.3. Accessing Arrays

"... cardinality returns the total number of elements in an array across
all dimensions. It is effectively the number of rows a call to unnest
would yield: ..."

./danfe

--
Adrian Klaver
adrian.klaver@aklaver.com