pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDo it exist?
2+ messages / 2 participants
[nested] [flat]
* Do it exist?
@ 2003-08-16 09:38 Hal Davison <hal@faams.net>
0 siblings, 1 reply; 2+ messages in thread
From: Hal Davison @ 2003-08-16 09:38 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Do it exist?
@ 2003-08-17 02:47 Rod Taylor <rbt@rbt.ca>
parent: Hal Davison <hal@faams.net>
0 siblings, 0 replies; 2+ messages in thread
From: Rod Taylor @ 2003-08-17 02:47 UTC (permalink / raw)
To: Hal Davison <hal@faams.net>; +Cc: pgsql-sql
> 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).
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-08-17 02:47 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-16 09:38 Do it exist? Hal Davison <hal@faams.net>
2003-08-17 02:47 ` Rod Taylor <rbt@rbt.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox