Subject: Re: Do it exist?
From: Rod Taylor <rbt@rbt.ca>
To: Hal Davison <hal@faams.net>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <3f3ca9e7_1@newsfeed>
References: <3f3ca9e7_1@newsfeed>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-xhnCNPaXfvUAgobF2cZl"
Message-Id: <1061088440.39706.93.camel@jester>
Mime-Version: 1.0
Date: Sat, 16 Aug 2003 22:47:22 -0400

--=-xhnCNPaXfvUAgobF2cZl
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

> In another language I would do:
>   find first Order where Order.Order_num =3D "1111".
>   if avaialble Order then it_exists =3D true.
>=20
> In PostgreSQL ?:
>     SELECT DISTINCT Order_num from Order_header where Order_num =3D '1111=
';
>=20
>     Is this close? If so, how to raise flage that row exists?
>=20
>=20
> 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 =3D '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).

--=-xhnCNPaXfvUAgobF2cZl
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (FreeBSD)

iD8DBQA/Puy46DETLow6vwwRAnMMAJ4z/1GNWWUBayrvc+s5imLR3ul6KACdEL5U
RrwhnxdE/qB5jNuDDf2QTjU=
=6pLd
-----END PGP SIGNATURE-----

--=-xhnCNPaXfvUAgobF2cZl--