Hello,

sorry for answering so late, ...
> I do not know if there is an specific error handler built in postgresql (besides error-codes).
>
> If you break the unique constraint an error will be thrown. A
> workaround may be to launch a trigger BEFORE the insertion and do whatever is necessary within the trigger.
>
This is possibillity I was thinking of, too. But if there are many
inserts and (probably) only a few of them would break the unique
constraint, there would be a great overhead. This was what I'd like to
avoid with a custom error handler.

Regards,
Verena



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On 5/10/06 9:35 AM, "Verena Ruff" <lists@triosolutions.at> wrote:

> Hello,
>
> sorry for answering so late, ...
>> I do not know if there is an specific error handler built in postgresql
>> (besides error-codes).
>>
>> If you break the unique constraint an error will be thrown. A
>> workaround may be to launch a trigger BEFORE the insertion and do whatever is
>> necessary within the trigger.
>>
> This is possibillity I was thinking of, too. But if there are many
> inserts and (probably) only a few of them would break the unique
> constraint, there would be a great overhead. This was what I'd like to
> avoid with a custom error handler.

Probably the simplest way to do this is to load the data into a temporary
table without the unique constraint then use SQL to insert a "clean" version
into the new table. Alternatively, you could use savepoints. If an insert
fails, just rollback to that savepoint. If it succeeds, commit that
savepoint.

Sean


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Hi,

Sean Davis schrieb:
> Probably the simplest way to do this is to load the data into a temporary
> table without the unique constraint then use SQL to insert a "clean" version
> into the new table. Alternatively, you could use savepoints. If an insert
> fails, just rollback to that savepoint. If it succeeds, commit that
> savepoint.
>
But isn't that more overhead than using the trigger? And while inserting
into the clean table, I would have to test if the value is existing or
not. I don't see the advantage of this compared to the script which was
posted a few weeks ago.

Regards,
Verena

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On 5/10/06 9:09 AM, "Verena Ruff" <lists@triosolutions.at> wrote:

> Hi,
>
> Sean Davis schrieb:
>> Probably the simplest way to do this is to load the data into a temporary
>> table without the unique constraint then use SQL to insert a "clean" version
>> into the new table. Alternatively, you could use savepoints. If an insert
>> fails, just rollback to that savepoint. If it succeeds, commit that
>> savepoint.
>>
> But isn't that more overhead than using the trigger? And while inserting
> into the clean table, I would have to test if the value is existing or
> not.

If you are copying bulk data into the table only once, then cleaning the
data up front will not impact your actual use down the road. If you are
saying that you will be inserting non-unique values and need to catch that,
a trigger is the better way to go. As for testing if the value is existing
or not, you can avoid that by using SQL functions (like the postgresql
specific distinct on) to select from the temporary table only those values
that are unique. See here in the docs:

http://www.postgresql.org/docs/8.1/i...l#SQL-DISTINCT

> I don't see the advantage of this compared to the script which was
> posted a few weeks ago.

I should have pointed out that the solution depends on your needs. If you
don't see an advantage, it is likely because there isn't one for your
particular needs, so feel free to use some other option.

Sean


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq