Hi,

Sean Davis schrieb:

>> 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.
This is what I need to do.
>> 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
>>
OK, I forgot about DISTINCT.
> 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
Thanks for your hints. In my situation (many inserts and only a few
would break the unique clause) I think using a trigger is the way to get
a better performance.

Regards,
Verena



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

> Hi,
>
> Sean Davis schrieb:
>
>>> 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.
> This is what I need to do.
>>> 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
>>>
> OK, I forgot about DISTINCT.
>> 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
> Thanks for your hints. In my situation (many inserts and only a few
> would break the unique clause) I think using a trigger is the way to get
> a better performance.

Just keep in mind that the trigger runs on EVERY insert, even those for
which the unique clause is not violated. If that is the behavior you need,
then use the trigger. However, if you know that after you have clean data
in the table, you will not be inserting "duplicates" (I think this is the
typical case), then a trigger may not be the way to go.

Sean


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster