From: Rod Taylor <rbt@rbt.ca>
To: Hal Davison <hal@faams.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Do it exist?
Date: Sat, 16 Aug 2003 22:47:22 -0400
Message-ID: <1061088440.39706.93.camel@jester> (raw)
In-Reply-To: <3f3ca9e7_1@newsfeed>
References: <3f3ca9e7_1@newsfeed>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rbt@rbt.ca, hal@faams.net
Subject: Re: Do it exist?
In-Reply-To: <1061088440.39706.93.camel@jester>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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