Thread: Do it exist?

PostgreSQL Version 7.3.0

I hope I am not Cross Posting..with psql-novice..If so..sorry..

We have a situation where in our application the user provides the Order
Number for a delivery document.

When the Order Number is entered we need to determine if the Order Number
already exists and raise a flag if it does exist.

In another language I would do: find first Order where Order.Order_num = "1111". if avaialble Order then it_exists =
true.

In PostgreSQL ?:   SELECT DISTINCT Order_num from Order_header where Order_num = '1111';
   Is this close? If so, how to raise flage that row exists?


How we do somthing like this in SQL?


-- 
Hal Davison
Davison Consulting

> In another language I would do:
>   find first Order where Order.Order_num = "1111".
>   if avaialble Order then it_exists = true.
>
> In PostgreSQL ?:
>     SELECT DISTINCT Order_num from Order_header where Order_num = '1111';
>
>     Is this close? If so, how to raise flage that row exists?
>
>
> How we do somthing like this in SQL?

Since you don't care about a value, just pull TRUE and use LIMIT to
restrict to a single entry (much faster than DISTINCT, but not as
portable).

SELECT TRUE FROM Order_header where Order_num = '1111' LIMIT 1;

Do a count of the number of rows returned.  1 row means it exists, 0
rows means it does not exist.


It seems to me like you intend to insert a row with that number shortly
after?  You should be aware that this process will introduce a race
condition (may not exist during test, but does exist shortly after
during insert).

If Order_num is Distinct on that table (or another), just try the
insert.  If it fails, increment the number and try again. If you don't
mind gaps in the numbers, then a sequence will fare much better (easier
and faster).