From qdlaty@wielun.dhs.org Sat Dec  29 15:47:14 2001
Received: from josue.wielun.dhs.org (pb130.wielun.sdi.tpnet.pl
	[217.99.199.130])
	by postgresql.org (8.11.3/8.11.4) with ESMTP id fBTFlDm19147
	for <pgsql-sql@postgresql.org>; Sat, 29 Dec 2001 10:47:14 -0500 (EST)
	(envelope-from qdlaty@wielun.dhs.org)
Received: from QDLATY (pa73.wielun.sdi.tpnet.pl [213.76.196.73])
	by josue.wielun.dhs.org (8.9.3/8.9.3) with ESMTP id QAA00756
	for <pgsql-sql@postgresql.org>; Sat, 29 Dec 2001 16:20:28 +0100
Date: Sat, 29 Dec 2001 16:46:11 +0100
From: QdlatY <qdlaty@wielun.dhs.org>
X-Mailer: The Bat! (v1.53d) Personal
Reply-To: QdlatY <qdlaty@wielun.dhs.org>
X-Priority: 3 (Normal)
Message-ID: <784286770.20011229164611@wielun.dhs.org>
To: pgsql-sql@postgresql.org
Subject: query
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Archive-Number: 200112/263
X-Sequence-Number: 6085


BTW. I'm using Postgres 7.0.3




From pedroigor@aip.com.br Fri Jan  3 13:56:04 2003
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id E5CBB475B07
	for <pgsql-sql@postgresql.org>; Fri,  3 Jan 2003 08:56:04 -0500 (EST)
Received: from server45.aip.com.br (unknown [200.199.192.5])
	by postgresql.org (Postfix) with ESMTP id 69434474E61
	for <pgsql-sql@postgresql.org>; Fri,  3 Jan 2003 08:56:02 -0500 (EST)
Received: from PEDRO ([10.1.0.11]) by server45.aip.com.br with SMTP (Microsoft
	Exchange Internet Mail Service Version 5.5.2653.13)
	id CFPJ9QQL; Fri, 3 Jan 2003 11:57:40 -0300
Message-ID: <000d01c2b32e$8529eca0$0b00010a@aip.com.br>
From: "Pedro Igor" <pedroigor@aip.com.br>
To: <pgsql-sql@postgresql.org>
Subject: Query
Date: Fri, 3 Jan 2003 11:46:27 -0200
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_000A_01C2B31D.C00F3200"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200301/23
X-Sequence-Number: 10888

This is a multi-part message in MIME format.

------=_NextPart_000_000A_01C2B31D.C00F3200
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Regards .....

  I hope someone can help me in this query.
  Have a field in a table that needs to check if another table has the valu=
e that is being inserted.
  Ex:
    table A=20
      - id int constraint pkey_id primary key,
=20=20=20=20
=20=20=20=20
    table B
      - id int constraint fkey_A_B references A,

    Here comes my doubt:

    table C
      - id int constraint fkey_A_C references A check (if exists B.id =3D C=
.id)

    How can i build this expression so, when I insert a tupple in table C t=
he field will check in the table A(ok, because is a foreign key) and also i=
n table B ....
    I have tried : check (select count(b.id) from B b where b.id =3D id) <>=
 0) .... but doesn=B4t work ......=20
    I can use trigger here, but i don=B4t know if is the best solution .....
=20=20=20=20=20
   Thanks,

        Pedro Igor
=20=20=20
------=_NextPart_000_000A_01C2B31D.C00F3200
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1126" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Regards .....</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp; I hope someone can help me in this=
=20
query.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;Have a field in a table that n=
eeds to=20
check if another table has the value that is being inserted.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;Ex:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; table A </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - id int co=
nstraint=20
pkey_id primary key,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; table B</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - id int co=
nstraint=20
fkey_A_B references A,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; Here comes my=20
doubt:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; table C</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - id int co=
nstraint=20
fkey_A_C references A check (if exists B.id =3D C.id)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; How can i build this ex=
pression=20
so, when I insert a tupple in table C the field will check in the table A(o=
k,=20
because is a foreign key) and also in table B ....</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; I have tried : check (s=
elect=20
count(b.id) from B b where b.id =3D id) &lt;&gt; 0) .... but&nbsp;doesn=B4t=
 work=20
...... </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; I can use trigger here,=
 but i=20
don=B4t know if is the best solution .....</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;</FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp;Thanks,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; Pedr=
o=20
Igor</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp; </FONT></DIV></BODY></HTML>

------=_NextPart_000_000A_01C2B31D.C00F3200--




From sszabo@megazone23.bigpanda.com Fri Jan  3 15:57:42 2003
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 01A2A4762E7
	for <pgsql-sql@postgresql.org>; Fri,  3 Jan 2003 10:57:42 -0500 (EST)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178])
	by postgresql.org (Postfix) with ESMTP id E79BB476286
	for <pgsql-sql@postgresql.org>; Fri,  3 Jan 2003 10:57:40 -0500 (EST)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 69BB4D612; Fri,  3 Jan 2003 07:57:45 -0800 (PST)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 5F7485C04; Fri,  3 Jan 2003 07:57:45 -0800 (PST)
Date: Fri, 3 Jan 2003 07:57:45 -0800 (PST)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Pedro Igor <pedroigor@aip.com.br>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: Query
In-Reply-To: <000d01c2b32e$8529eca0$0b00010a@aip.com.br>
Message-ID: <20030103075235.Q3488-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200301/24
X-Sequence-Number: 10889


On Fri, 3 Jan 2003, Pedro Igor wrote:

> Regards .....
>
>   I hope someone can help me in this query.
>   Have a field in a table that needs to check if another table has the value that is being inserted.
>   Ex:
>     table A
>       - id int constraint pkey_id primary key,
>
>
>     table B
>       - id int constraint fkey_A_B references A,
>
>     Here comes my doubt:
>
>     table C
>       - id int constraint fkey_A_C references A check (if exists B.id
> = C.id)
>
>     How can i build this expression so, when I insert a tupple in
> table C the field will check in the table A(ok, because is a foreign
> key) and also in table B ....

If you only want insert(/update) time checks (ie that deleting from B
doesn't violate) then a trigger should work.  I think a check constraint
to a function that does the work will work as well.  Subsellects in check
constraints are a nasty problem that hasn't been solved yet (since those
do imply the inverse constraint).

Of course if you want two foreign keys you should be able to do that as
well.




From Chandan_Kumaraiah@satyam.com Thu Mar  17 10:57:53 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id DDBC8566D5
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Mar 2005 10:57:53 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 83832-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Mar 2005 10:57:50 +0000 (GMT)
Received: from njpmail.satyam.com (njpmail.satyam.com [204.179.188.132])
	by svr1.postgresql.org (Postfix) with ESMTP id C11DB56567
	for <pgsql-sql@postgresql.org>; Thu, 17 Mar 2005 10:57:46 +0000 (GMT)
Received: from che1.satyam.com (che1 [172.18.73.11])
	by njpmail.satyam.com (8.11.6/8.11.6) with ESMTP id j2HAqbI21240
	for <pgsql-sql@postgresql.org>; Thu, 17 Mar 2005 05:52:37 -0500
Received: from cscmsg002.corp.satyam.ad (localhost [127.0.0.1])
	by che1.satyam.com (8.11.6/8.11.6) with ESMTP id j2HAqdF04420
	for <pgsql-sql@postgresql.org>; Thu, 17 Mar 2005 16:22:40 +0530
X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----_=_NextPart_001_01C52AE0.1FE7FEA5"
Subject: query
Date: Thu, 17 Mar 2005 16:27:34 +0530
Message-ID: <2EB549AAAFEA264FA926269A5BD5E9EB146577@certsrv.satyam.com>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: query
Thread-Index: AcUq4BXnHuFTr8odT7SBiZ4aa7so0g==
From: "Chandan_Kumaraiah" <Chandan_Kumaraiah@satyam.com>
To: <pgsql-sql@postgresql.org>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.19 tagged_above=0 required=5 tests=HTML_90_100,
	HTML_MESSAGE
X-Spam-Level: 
X-Archive-Number: 200503/272
X-Sequence-Number: 20751

This is a multi-part message in MIME format.

------_=_NextPart_001_01C52AE0.1FE7FEA5
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi,

=20

In oracle we write sysdate-1

For example,we write a query (select * from table1 where
created_date>=3Dsysdate-1).Whats its equivalent in postgre?

=20

Chandan

=20


------_=_NextPart_001_01C52AE0.1FE7FEA5
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 11 (filtered medium)">
<style>
<!--
 /* Font Definitions */
 @font-face
	{font-family:Tahoma;
	panose-1:2 11 6 4 3 5 4 4 2 4;}
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:12.0pt;
	font-family:"Times New Roman";}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:Arial;
	color:windowtext;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
	{page:Section1;}
-->
</style>

</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

<p class=3DMsoNormal><b><font size=3D2 face=3DTahoma><span =
style=3D'font-size:10.0pt;
font-family:Tahoma;font-weight:bold'>Hi,<o:p></o:p></span></font></b></p>=


<p class=3DMsoNormal><b><font size=3D2 face=3DTahoma><span =
style=3D'font-size:10.0pt;
font-family:Tahoma;font-weight:bold'><o:p>&nbsp;</o:p></span></font></b><=
/p>

<p class=3DMsoNormal><b><font size=3D2 face=3DTahoma><span =
style=3D'font-size:10.0pt;
font-family:Tahoma;font-weight:bold'>In oracle we write =
sysdate-1<o:p></o:p></span></font></b></p>

<p class=3DMsoNormal><b><font size=3D2 face=3DTahoma><span =
style=3D'font-size:10.0pt;
font-family:Tahoma;font-weight:bold'>For example,we write a query =
(select *
from table1 where created_date&gt;=3Dsysdate-1).Whats its equivalent in =
postgre?<o:p></o:p></span></font></b></p>

<p class=3DMsoNormal><b><font size=3D2 face=3DTahoma><span =
style=3D'font-size:10.0pt;
font-family:Tahoma;font-weight:bold'><o:p>&nbsp;</o:p></span></font></b><=
/p>

<p class=3DMsoNormal><b><font size=3D2 face=3DTahoma><span =
style=3D'font-size:10.0pt;
font-family:Tahoma;font-weight:bold'>Chandan<o:p></o:p></span></font></b>=
</p>

<p class=3DMsoNormal><b><font size=3D2 face=3DTahoma><span =
style=3D'font-size:10.0pt;
font-family:Tahoma;font-weight:bold'><o:p>&nbsp;</o:p></span></font></b><=
/p>

</div>

</body>

</html>

------_=_NextPart_001_01C52AE0.1FE7FEA5--



From Chandan_Kumaraiah@satyam.com Thu Mar  17 11:08:38 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 3934956A13
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Mar 2005 11:08:38 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 86800-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Mar 2005 11:08:29 +0000 (GMT)
Received: from vvamail.satyam.com (vvamail.satyam.com [208.220.247.17])
	by svr1.postgresql.org (Postfix) with ESMTP id 8896156A12
	for <pgsql-sql@postgresql.org>; Thu, 17 Mar 2005 11:08:28 +0000 (GMT)
Received: from che1.satyam.com (che1.satyam.com [172.18.73.11])
	by vvamail.satyam.com (8.11.6/8.11.6) with ESMTP id j2HAngR10272
	for <pgsql-sql@postgresql.org>; Thu, 17 Mar 2005 05:49:43 -0500
Received: from cscmsg002.corp.satyam.ad (localhost [127.0.0.1])
	by che1.satyam.com (8.11.6/8.11.6) with ESMTP id j2HB3LE11215
	for <pgsql-sql@postgresql.org>; Thu, 17 Mar 2005 16:33:21 +0530
X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----_=_NextPart_001_01C52AE1.9DC1D91B"
Subject: query
Date: Thu, 17 Mar 2005 16:38:15 +0530
Message-ID: <2EB549AAAFEA264FA926269A5BD5E9EB146579@certsrv.satyam.com>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: query
Thread-Index: AcUq4BXnHuFTr8odT7SBiZ4aa7so0gAAVvew
From: "Chandan_Kumaraiah" <Chandan_Kumaraiah@satyam.com>
To: <pgsql-sql@postgresql.org>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=1.15 tagged_above=0 required=5 tests=AWL, HTML_90_100,
	HTML_MESSAGE
X-Spam-Level: *
X-Archive-Number: 200503/235
X-Sequence-Number: 20714

This is a multi-part message in MIME format.

------_=_NextPart_001_01C52AE1.9DC1D91B
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

=20

Hi,

=20

In oracle we write sysdate-1

For example,we write a query (select * from table1 where
created_date>=3Dsysdate-1).Whats its equivalent in postgre?

=20

Chandan

=20


------_=_NextPart_001_01C52AE1.9DC1D91B
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 11 (filtered medium)">
<style>
<!--
 /* Font Definitions */
 @font-face
	{font-family:Tahoma;
	panose-1:2 11 6 4 3 5 4 4 2 4;}
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:12.0pt;
	font-family:"Times New Roman";}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{mso-style-type:personal;
	font-family:Arial;
	color:windowtext;}
span.EmailStyle18
	{mso-style-type:personal-reply;
	font-family:Arial;
	color:navy;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
	{page:Section1;}
-->
</style>

</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

<p class=3DMsoNormal><font size=3D3 face=3D"Times New Roman"><span =
style=3D'font-size:
12.0pt'><o:p>&nbsp;</o:p></span></font></p>

<p class=3DMsoNormal><b><font size=3D2 face=3DTahoma><span =
style=3D'font-size:10.0pt;
font-family:Tahoma;font-weight:bold'>Hi,<o:p></o:p></span></font></b></p>=


<p class=3DMsoNormal><b><font size=3D2 face=3DTahoma><span =
style=3D'font-size:10.0pt;
font-family:Tahoma;font-weight:bold'><o:p>&nbsp;</o:p></span></font></b><=
/p>

<p class=3DMsoNormal><b><font size=3D2 face=3DTahoma><span =
style=3D'font-size:10.0pt;
font-family:Tahoma;font-weight:bold'>In oracle we write =
sysdate-1<o:p></o:p></span></font></b></p>

<p class=3DMsoNormal><b><font size=3D2 face=3DTahoma><span =
style=3D'font-size:10.0pt;
font-family:Tahoma;font-weight:bold'>For example,we write a query =
(select *
from table1 where created_date&gt;=3Dsysdate-1).Whats its equivalent in =
postgre?<o:p></o:p></span></font></b></p>

<p class=3DMsoNormal><b><font size=3D2 face=3DTahoma><span =
style=3D'font-size:10.0pt;
font-family:Tahoma;font-weight:bold'><o:p>&nbsp;</o:p></span></font></b><=
/p>

<p class=3DMsoNormal><b><font size=3D2 face=3DTahoma><span =
style=3D'font-size:10.0pt;
font-family:Tahoma;font-weight:bold'>Chandan<o:p></o:p></span></font></b>=
</p>

<p class=3DMsoNormal><b><font size=3D2 face=3DTahoma><span =
style=3D'font-size:10.0pt;
font-family:Tahoma;font-weight:bold'><o:p>&nbsp;</o:p></span></font></b><=
/p>

</div>

</body>

</html>

------_=_NextPart_001_01C52AE1.9DC1D91B--



From terry@ashtonwoodshomes.com Thu Mar  17 11:43:14 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 393FF56A1D
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Mar 2005 11:43:14 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 98949-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Mar 2005 11:43:03 +0000 (GMT)
Received: from mail.greatgulfhomes.com (mail.greatgulfhomes.com
	[216.191.52.66])
	by svr1.postgresql.org (Postfix) with ESMTP id B096A56A19
	for <pgsql-sql@postgresql.org>; Thu, 17 Mar 2005 11:43:01 +0000 (GMT)
Received: from [10.243.102.72] ([216.191.52.74]) (authenticated bits=0)
	by mail.greatgulfhomes.com (8.12.8/8.12.8) with ESMTP id j2HBxwah015077;
	Thu, 17 Mar 2005 06:59:58 -0500
Message-ID: <42396D61.6070002@ashtonwoodshomes.com>
Date: Thu, 17 Mar 2005 06:43:29 -0500
From: Terry Fielder <terry@ashtonwoodshomes.com>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Chandan_Kumaraiah <Chandan_Kumaraiah@satyam.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: query
References: <2EB549AAAFEA264FA926269A5BD5E9EB146579@certsrv.satyam.com>
In-Reply-To: <2EB549AAAFEA264FA926269A5BD5E9EB146579@certsrv.satyam.com>
Content-Type: multipart/alternative;
	boundary="------------000503030000040703070200"
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.09 tagged_above=0 required=5 tests=AWL, HTML_MESSAGE
X-Spam-Level: 
X-Archive-Number: 200503/237
X-Sequence-Number: 20716

This is a multi-part message in MIME format.
--------------000503030000040703070200
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

now() returns a timestamp.

Cast it to a date and then you can subtract days. e.g.

select now()::date -1

Terry

Chandan_Kumaraiah wrote:

>  
>
> *Hi,*
>
> * *
>
> *In oracle we write sysdate-1*
>
> *For example,we write a query (select * from table1 where 
> created_date>=sysdate-1).Whats its equivalent in postgre?*
>
> * *
>
> *Chandan*
>
> * *
>

-- 
Terry Fielder
terry@greatgulfhomes.com
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085


--------------000503030000040703070200
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>now() returns a timestamp.<br>
<br>
Cast it to a date and then you can subtract days. e.g.<br>
<br>
select now()::date -1<br>
<br>
Terry<br>
</tt><br>
Chandan_Kumaraiah wrote:
<blockquote
 cite="mid2EB549AAAFEA264FA926269A5BD5E9EB146579@certsrv.satyam.com"
 type="cite">
  <meta http-equiv="Content-Type" content="text/html; ">
  <meta name="Generator" content="Microsoft Word 11 (filtered medium)">
  <style>
<!--
 /* Font Definitions */
 @font-face
	{font-family:Tahoma;
	panose-1:2 11 6 4 3 5 4 4 2 4;}
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:12.0pt;
	font-family:"Times New Roman";}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{mso-style-type:personal;
	font-family:Arial;
	color:windowtext;}
span.EmailStyle18
	{mso-style-type:personal-reply;
	font-family:Arial;
	color:navy;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
	{page:Section1;}
-->
  </style>
  <div class="Section1">
  <p class="MsoNormal"><font face="Times New Roman" size="3"><span
 style="font-size: 12pt;"><o:p>&nbsp;</o:p></span></font></p>
  <p class="MsoNormal"><b><font face="Tahoma" size="2"><span
 style="font-size: 10pt; font-family: Tahoma; font-weight: bold;">Hi,<o:p></o:p></span></font></b></p>
  <p class="MsoNormal"><b><font face="Tahoma" size="2"><span
 style="font-size: 10pt; font-family: Tahoma; font-weight: bold;"><o:p>&nbsp;</o:p></span></font></b></p>
  <p class="MsoNormal"><b><font face="Tahoma" size="2"><span
 style="font-size: 10pt; font-family: Tahoma; font-weight: bold;">In
oracle we write sysdate-1<o:p></o:p></span></font></b></p>
  <p class="MsoNormal"><b><font face="Tahoma" size="2"><span
 style="font-size: 10pt; font-family: Tahoma; font-weight: bold;">For
example,we write a query (select *
from table1 where created_date&gt;=sysdate-1).Whats its equivalent in
postgre?<o:p></o:p></span></font></b></p>
  <p class="MsoNormal"><b><font face="Tahoma" size="2"><span
 style="font-size: 10pt; font-family: Tahoma; font-weight: bold;"><o:p>&nbsp;</o:p></span></font></b></p>
  <p class="MsoNormal"><b><font face="Tahoma" size="2"><span
 style="font-size: 10pt; font-family: Tahoma; font-weight: bold;">Chandan<o:p></o:p></span></font></b></p>
  <p class="MsoNormal"><b><font face="Tahoma" size="2"><span
 style="font-size: 10pt; font-family: Tahoma; font-weight: bold;"><o:p>&nbsp;</o:p></span></font></b></p>
  </div>
</blockquote>
<br>
<pre class="moz-signature" cols="72">-- 
Terry Fielder
<a class="moz-txt-link-abbreviated" href="mailto:terry@greatgulfhomes.com">terry@greatgulfhomes.com</a>
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085</pre>
</body>
</html>

--------------000503030000040703070200--



From dev@archonet.com Thu Mar  17 12:02:24 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id EE38756A0F
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Mar 2005 12:02:24 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 07159-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Mar 2005 12:02:16 +0000 (GMT)
Received: from anchor-post-31.mail.demon.net (anchor-post-31.mail.demon.net
	[194.217.242.89])
	by svr1.postgresql.org (Postfix) with ESMTP id 109F056A28
	for <pgsql-sql@postgresql.org>; Thu, 17 Mar 2005 12:02:15 +0000 (GMT)
Received: from mwynhau.demon.co.uk ([193.237.186.96]
 helo=mainbox.archonet.com)
	by anchor-post-31.mail.demon.net with esmtp (Exim 4.42)
	id 1DBtfb-000Agx-4N; Thu, 17 Mar 2005 12:00:08 +0000
Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17])
	by mainbox.archonet.com (Postfix) with ESMTP
	id C538F17496; Thu, 17 Mar 2005 12:02:14 +0000 (GMT)
Message-ID: <423971C6.1020706@archonet.com>
Date: Thu, 17 Mar 2005 12:02:14 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Terry Fielder <terry@ashtonwoodshomes.com>
Cc: Chandan_Kumaraiah <Chandan_Kumaraiah@satyam.com>, pgsql-sql@postgresql.org
Subject: Re: query
References: <2EB549AAAFEA264FA926269A5BD5E9EB146579@certsrv.satyam.com>
	<42396D61.6070002@ashtonwoodshomes.com>
In-Reply-To: <42396D61.6070002@ashtonwoodshomes.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.061 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO
X-Spam-Level: 
X-Archive-Number: 200503/238
X-Sequence-Number: 20717

Terry Fielder wrote:
> now() returns a timestamp.
> 
> Cast it to a date and then you can subtract days. e.g.
> 
> select now()::date -1

Or CURRENT_DATE - 1

>>
>> *In oracle we write sysdate-1*
>>
>> *For example,we write a query (select * from table1 where 
>> created_date>=sysdate-1).Whats its equivalent in postgre?*

--
   Richard Huxton
   Archonet Ltd



From tfo@sitening.com Mon Mar  21 14:34:03 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id E939456580
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 21 Mar 2005 14:34:03 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 07311-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 21 Mar 2005 14:33:41 +0000 (GMT)
Received: from window.monsterlabs.com (window.monsterlabs.com
	[216.183.105.176])
	by svr1.postgresql.org (Postfix) with SMTP id 828B656467
	for <pgsql-sql@postgresql.org>; Mon, 21 Mar 2005 14:33:39 +0000 (GMT)
Received: (qmail 507 invoked from network); 21 Mar 2005 14:33:43 -0000
Received: from a202088.n1.vanderbilt.edu (HELO ?129.59.202.88?)
 (129.59.202.88)
	by 0 with SMTP; 21 Mar 2005 14:33:43 -0000
In-Reply-To: <2EB549AAAFEA264FA926269A5BD5E9EB146577@certsrv.satyam.com>
References: <2EB549AAAFEA264FA926269A5BD5E9EB146577@certsrv.satyam.com>
Mime-Version: 1.0 (Apple Message framework v619.2)
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Message-Id: <d2ac5d5caa72f051e6360166c9fbe40a@sitening.com>
Content-Transfer-Encoding: quoted-printable
Cc: <pgsql-sql@postgresql.org>
From: Thomas F.O'Connell <tfo@sitening.com>
Subject: Re: query
Date: Mon, 21 Mar 2005 08:33:43 -0600
To: "Chandan_Kumaraiah" <Chandan_Kumaraiah@satyam.com>
X-Mailer: Apple Mail (2.619.2)
X-Virus-Scanned: by amavisd-new at hub.org
X-Archive-Number: 200503/285
X-Sequence-Number: 20764

You should be able to use the CURRENT_DATE function in place of sysdate.

You might need to cast the 1 explicitly to an interval.

As in:

CURRENT_DATE - '1 day'::interval

-tfo

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005

On Mar 17, 2005, at 4:57 AM, Chandan_Kumaraiah wrote:

> Hi,=A0
>
> In oracle we write sysdate-1
>
> For example,we write a query (select * from table1 where=20
> created_date>=3Dsysdate-1).Whats its equivalent in postgre?
>
> Chandan




From bruno@wolff.to Mon Mar  21 15:01:22 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 2D03B56410
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 21 Mar 2005 15:01:22 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 17491-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 21 Mar 2005 15:01:12 +0000 (GMT)
Received: from wolff.to (wolff.to [66.93.249.74])
	by svr1.postgresql.org (Postfix) with SMTP id 15B0456580
	for <pgsql-sql@postgresql.org>; Mon, 21 Mar 2005 15:01:11 +0000 (GMT)
Received: (qmail 21182 invoked by uid 500); 21 Mar 2005 15:17:35 -0000
Date: Mon, 21 Mar 2005 09:17:35 -0600
From: Bruno Wolff III <bruno@wolff.to>
To: "Thomas F.O'Connell" <tfo@sitening.com>
Cc: Chandan_Kumaraiah <Chandan_Kumaraiah@satyam.com>, pgsql-sql@postgresql.org
Subject: Re: query
Message-ID: <20050321151735.GA20261@wolff.to>
Mail-Followup-To: Bruno Wolff III <bruno@wolff.to>,
	"Thomas F.O'Connell" <tfo@sitening.com>,
	Chandan_Kumaraiah <Chandan_Kumaraiah@satyam.com>,
	pgsql-sql@postgresql.org
References: <2EB549AAAFEA264FA926269A5BD5E9EB146577@certsrv.satyam.com>
	<d2ac5d5caa72f051e6360166c9fbe40a@sitening.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <d2ac5d5caa72f051e6360166c9fbe40a@sitening.com>
User-Agent: Mutt/1.5.6i
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.01 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200503/287
X-Sequence-Number: 20766

On Mon, Mar 21, 2005 at 08:33:43 -0600,
  "Thomas F.O'Connell" <tfo@sitening.com> wrote:
> You should be able to use the CURRENT_DATE function in place of sysdate.
> 
> You might need to cast the 1 explicitly to an interval.
> 
> As in:
> 
> CURRENT_DATE - '1 day'::interval

I don't think you want that. That is going to force a conversion from date
to a timestamp. You are going to be better off just using:
current_date - 1



From aalam@tatashare.com Fri Nov  18 08:45:09 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id CD30CDB4F9
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 18 Nov 2005 04:45:09 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 62489-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 18 Nov 2005 08:45:10 +0000 (GMT)
X-Greylist: delayed 03:14:25.261445 by SQLgrey-
Received: from svr2.postgresql.org (svr2.postgresql.org [65.19.161.25])
	by svr1.postgresql.org (Postfix) with ESMTP id 30242D7817
	for <pgsql-sql@postgresql.org>; Fri, 18 Nov 2005 04:45:06 -0400 (AST)
Received: from smtp1.netmagicians.com (smtp1.netmagicians.com [202.87.39.91])
	by svr2.postgresql.org (Postfix) with ESMTP id 27F87F0C49
	for <pgsql-sql@postgresql.org>; Fri, 18 Nov 2005 05:30:39 +0000 (GMT)
Received: from mailserver.tatashare.com (unknown [202.149.193.242])
	by smtp1.netmagicians.com (Postfix) with ESMTP id 7D73D3749BF
	for <pgsql-sql@postgresql.org>; Fri, 18 Nov 2005 11:00:25 +0530 (IST)
Received: from aftabn463 (unknown [172.16.16.236])
	by mailserver.tatashare.com (Postfix) with SMTP id D6BB73E5E04
	for <pgsql-sql@postgresql.org>; Fri, 18 Nov 2005 10:57:26 +0530 (IST)
Reply-To: <aalam@tatashare.com>
From: "Aftab Alam" <aalam@tatashare.com>
To: <pgsql-sql@postgresql.org>
Subject: query
Date: Fri, 18 Nov 2005 10:55:24 +0530
Message-ID: <001301c5ec00$7b25b0f0$ec1010ac@aftabn463>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0014_01C5EC2E.94DDECF0"
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook CWS, Build 9.0.2416 (9.0.2910.0)
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Importance: Normal
X-NetMagic-MailScanner-Information: Powered By NetMagic Mail Protection v1.0
X-NetMagic-MailScanner: Found to be clean
X-NetMagic-MailScanner-From: aalam@tatashare.com
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.001 required=5 tests=[HTML_MESSAGE=0.001]
X-Spam-Score: 0.001
X-Spam-Level: 
X-Archive-Number: 200511/145
X-Sequence-Number: 23396

This is a multi-part message in MIME format.

------=_NextPart_000_0014_01C5EC2E.94DDECF0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

Hello,

I want to create a trigger in PostgresSQL

In trigger, Before inserting the record, if data is already in the table,
the trigger fire the mesaage that data is already there, and after that
trigger ckeck for next insert statement.

How can I do this , Please reply.

Regards,

_____

Aftab Alam


------=_NextPart_000_0014_01C5EC2E.94DDECF0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">


<META content=3D"MSHTML 6.00.2600.0" name=3DGENERATOR></HEAD>
<BODY>
<P>Hello,</P>
<P>I want to create a trigger in PostgresSQL </P>
<P><SPAN class=3D921122105-18112005>In trigger, Before inserting the =
record, if=20
data is already in the table, the trigger fire the mesaage that data is =
already=20
there, and after that trigger ckeck for next insert =
statement.</SPAN></P>
<P><SPAN class=3D921122105-18112005></SPAN><SPAN =
class=3D921122105-18112005>How can=20
I do this , </SPAN>Please reply.</P>
<P>Regards, </P>
<P>_____ </P>
<P>Aftab Alam </P></BODY></HTML>

------=_NextPart_000_0014_01C5EC2E.94DDECF0--




From juancri@gmail.com Sun Nov  20 07:02:07 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id A9AC0DBC29
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 20 Nov 2005 03:02:07 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 78391-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 20 Nov 2005 07:02:06 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2])
	by svr1.postgresql.org (Postfix) with ESMTP id BDC05DBC5D
	for <pgsql-sql@postgresql.org>; Sun, 20 Nov 2005 03:02:04 -0400 (AST)
Received: by floppy.pyrenet.fr (Postfix, from userid 106)
	id 54B9D31059; Sun, 20 Nov 2005 08:02:05 +0100 (MET)
From: "JuanCri" <juancri@gmail.com>
X-Newsgroups: pgsql.sql
Subject: Re: query
Date: 19 Nov 2005 23:01:57 -0800
Organization: http://groups.google.com
Lines: 14
Message-ID: <1132470117.345048.64260@o13g2000cwo.googlegroups.com>
References: <001301c5ec00$7b25b0f0$ec1010ac@aftabn463>
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Complaints-To: groups-abuse@google.com
In-Reply-To: <001301c5ec00$7b25b0f0$ec1010ac@aftabn463>
User-Agent: G2/0.2
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.7.12) Gecko/20050915 Firefox/1.0.7,gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: o13g2000cwo.googlegroups.com; posting-host=201.215.213.22;
	posting-account=7tws-Q0AAADpX36azeU8JBvtMMi8oYJs
To: pgsql-sql@postgresql.org
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[none]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200511/252
X-Sequence-Number: 23503

First, INSERT statements are 1 to 1 with table rows... (unless you use
a insert/select).

Second, if you want to probe that.. it is better to create an UNIQUE
index.. isn't it?

Ok, if that's not enough for you... you can create a trigger that
perform a SELECT COUNT(*) with the criteria and if it's > 0, you rise
an exception (sorry, I don't have access to a databae now to check the
code).

Juan C. Olivares
www.juancri.com




From lists@nabble.com Tue Feb  7 09:45:51 2006
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 456469DC881
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  7 Feb 2006 05:45:51 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 97807-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  7 Feb 2006 05:45:52 -0400 (AST)
Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159])
	by postgresql.org (Postfix) with ESMTP id 20EF59DC83C
	for <pgsql-sql@postgresql.org>; Tue,  7 Feb 2006 05:45:49 -0400 (AST)
Received: from talk.nabble.com (www.nabble.com [72.21.53.35])
	by svr4.postgresql.org (Postfix) with ESMTP id B76AF5AF03D
	for <pgsql-sql@postgresql.org>; Tue,  7 Feb 2006 09:45:51 +0000 (GMT)
Received: from localhost ([127.0.0.1] helo=talk.nabble.com)
	by talk.nabble.com with esmtp (Exim 4.50) id 1F6PPy-0005UO-1A
	for pgsql-sql@postgresql.org; Tue, 07 Feb 2006 01:45:50 -0800
Message-ID: <2798161.post@talk.nabble.com>
Date: Tue, 7 Feb 2006 01:45:50 -0800 (PST)
From: "superboy143 (sent by Nabble.com)" <lists@nabble.com>
Reply-To: superboy143 <superboy143@gmail.com>
To: pgsql-sql@postgresql.org
Subject: query
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_10597_18974862.1139305550030"
X-Nabble-Sender: Nabble Forums <lists@nabble.com>
X-Nabble-From: superboy143 <superboy143@gmail.com>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.247 required=5 tests=[AWL=0.246,
 HTML_MESSAGE=0.001]
X-Spam-Score: 0.247
X-Spam-Level: 
X-Archive-Number: 200602/54
X-Sequence-Number: 24064

------=_Part_10597_18974862.1139305550030
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit


Hello,

I have a table in which I have a field with format like 100101. It has many values like 100101, 100102, 100103, 100201, 100202, 100301. I have to write a query such that I have to get only distinct values such that they contain only the substring I need. If I give 10 as substring, then it should return only 100101 or 100102 but not both i.e if the last two characters are not same it should not return both of them. It should return only values starting with 10 the middle two values should be distinct and the last two characters may be anything.
--
View this message in context: http://www.nabble.com/query-t1074932.html#a2798161
Sent from the PostgreSQL - sql forum at Nabble.com.

------=_Part_10597_18974862.1139305550030
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit


Hello,
<br>
<br>I have a table in which I have a field with format like 100101. It has many values like 100101, 100102, 100103, 100201, 100202, 100301. I have to write a query such that I have to get only distinct values such that they contain only the substring I need. If I give 10 as substring, then it should return only 100101 or 100102 but not both i.e if the last two characters are not same it should not return both of them. It should return only values starting with 10 the middle two values should be distinct and the last two characters may be anything.
<br><hr align="left" width="300">
View this message in context: <a href="http://www.nabble.com/query-t1074932.html#a2798161">query</a><br>
Sent from the <a href="http://www.nabble.com/PostgreSQL---sql-f777.html">PostgreSQL - sql</a> forum at Nabble.com.

------=_Part_10597_18974862.1139305550030--




From dev@archonet.com Tue Feb  7 10:38:31 2006
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 8F3239DCAD6
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  7 Feb 2006 06:38:31 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 08216-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  7 Feb 2006 06:38:33 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75])
	by postgresql.org (Postfix) with ESMTP id 67C2C9DCA16
	for <pgsql-sql@postgresql.org>; Tue,  7 Feb 2006 06:38:29 -0400 (AST)
Received: from mainbox.archonet.com
	(84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99])
	by smtp.metronet.co.uk (MetroNet Mail) with ESMTP
	id 99616426559; Tue,  7 Feb 2006 10:35:00 +0000 (GMT)
Received: from [192.168.1.17] (client17.office.archonet.com [192.168.1.17])
	by mainbox.archonet.com (Postfix) with ESMTP id EBCA915EA4;
	Tue,  7 Feb 2006 10:34:59 +0000 (GMT)
Message-ID: <43E877D3.9090509@archonet.com>
Date: Tue, 07 Feb 2006 10:34:59 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 1.5 (X11/20051201)
MIME-Version: 1.0
To: superboy143 <superboy143@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: query
References: <2798161.post@talk.nabble.com>
In-Reply-To: <2798161.post@talk.nabble.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.116 required=5 tests=[AWL=0.116]
X-Spam-Score: 0.116
X-Spam-Level: 
X-Archive-Number: 200602/55
X-Sequence-Number: 24065

superboy143 (sent by Nabble.com) wrote:
> Hello,
> 
> I have a table in which I have a field with format like 100101. It
> has many values like 100101, 100102, 100103, 100201, 100202, 100301.

OK - so they look like numbers but aren't.

> I have to write a query such that I have to get only distinct values
> such that they contain only the substring I need. If I give 10 as
> substring, then it should return only 100101 or 100102 but not both
> i.e if the last two characters are not same it should not return both
> of them. 

The statement of the rule and the example contradict each other. Surely 
100101 and 100102 are distinct values. Surely they contain "10" as a 
substring. So according to your rule the should be returned.

 > It should return only values starting with 10 the middle two
> values should be distinct and the last two characters may be
> anything. 

This seems to agree with your example and not your rule.

So:
1. You don't want distinct values of your field, you want distinct 
substrings of your field?
2. You don't want to match a substring, you want to match the start of 
the string?
3. You want only one of 100101 or 100102 but you don't care which?

Is this correct?

By the way - it looks to me like you are trying to store multiple values 
in one column. If you split the values into their own columns I'd guess 
your query would be much easier.

-- 
   Richard Huxton
   Archonet Ltd



From bruno@wolff.to Fri Feb  10 06:02:47 2006
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 5CE999DC84A
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 10 Feb 2006 02:02:47 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 37490-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 10 Feb 2006 02:02:46 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from wolff.to (wolff.to [66.93.197.194])
	by postgresql.org (Postfix) with SMTP id A5C499DC818
	for <pgsql-sql@postgresql.org>; Fri, 10 Feb 2006 02:02:44 -0400 (AST)
Received: (qmail 26497 invoked by uid 500); 10 Feb 2006 06:11:29 -0000
Date: Fri, 10 Feb 2006 00:11:29 -0600
From: Bruno Wolff III <bruno@wolff.to>
To: superboy143 <superboy143@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: query
Message-ID: <20060210061129.GA26002@wolff.to>
Mail-Followup-To: Bruno Wolff III <bruno@wolff.to>,
	superboy143 <superboy143@gmail.com>, pgsql-sql@postgresql.org
References: <2798161.post@talk.nabble.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <2798161.post@talk.nabble.com>
User-Agent: Mutt/1.4.2.1i
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.092 required=5 tests=[AWL=0.092]
X-Spam-Score: 0.092
X-Spam-Level: 
X-Archive-Number: 200602/110
X-Sequence-Number: 24120

On Tue, Feb 07, 2006 at 01:45:50 -0800,
  "superboy143 (sent by Nabble.com)" <lists@nabble.com> wrote:
> 
> I have a table in which I have a field with format like 100101. It has many values like 100101, 100102, 100103, 100201, 100202, 100301. I have to write a query such that I have to get only distinct values such that they contain only the substring I need. If I give 10 as substring, then it should return only 100101 or 100102 but not both i.e if the last two characters are not same it should not return both of them. It should return only values starting with 10 the middle two values should be distinct and the last two characters may be anything.

You can probably use the Postgres extension DISTINCT ON to do what you want.



From ken@scottshill.com Fri Feb  10 16:21:42 2006
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 5DCAA9DC898
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 10 Feb 2006 12:21:42 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 11229-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 10 Feb 2006 12:21:40 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from rwcrmhc14.comcast.net (rwcrmhc14.comcast.net [216.148.227.154])
	by postgresql.org (Postfix) with ESMTP id CC3959DC891
	for <pgsql-sql@postgresql.org>; Fri, 10 Feb 2006 12:21:38 -0400 (AST)
Received: from [10.0.1.5] (c-24-20-199-254.hsd1.or.comcast.net[24.20.199.254])
	by comcast.net (rwcrmhc14) with SMTP
	id <20060210162137m1400dh58pe>; Fri, 10 Feb 2006 16:21:37 +0000
Subject: Re: query
From: Ken Hill <ken@scottshill.com>
Reply-To: ken@scottshill.com
To: Bruno Wolff III <bruno@wolff.to>
Cc: superboy143 <superboy143@gmail.com>, pgsql-sql@postgresql.org
In-Reply-To: <20060210061129.GA26002@wolff.to>
References: <2798161.post@talk.nabble.com> <20060210061129.GA26002@wolff.to>
Content-Type: multipart/alternative; boundary="=-UUju+pOqUEQ86skafFAs"
Organization: Scotts Hill Associates, Inc.
Date: Fri, 10 Feb 2006 08:22:03 -0800
Message-Id: <1139588523.29808.10.camel@localhost.localdomain>
Mime-Version: 1.0
X-Mailer: Evolution 2.4.1 
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.066 required=5 tests=[AWL=0.065,
 HTML_MESSAGE=0.001]
X-Spam-Score: 0.066
X-Spam-Level: 
X-Archive-Number: 200602/114
X-Sequence-Number: 24124


--=-UUju+pOqUEQ86skafFAs
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

On Fri, 2006-02-10 at 00:11 -0600, Bruno Wolff III wrote:

> On Tue, Feb 07, 2006 at 01:45:50 -0800,
>   "superboy143 (sent by Nabble.com)" <lists@nabble.com> wrote:
> > 
> > I have a table in which I have a field with format like 100101. It has many values like 100101, 100102, 100103, 100201, 100202, 100301. I have to write a query such that I have to get only distinct values such that they contain only the substring I need. If I give 10 as substring, then it should return only 100101 or 100102 but not both i.e if the last two characters are not same it should not return both of them. It should return only values starting with 10 the middle two values should be distinct and the last two characters may be anything.
> 
> You can probably use the Postgres extension DISTINCT ON to do what you want.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq


Try substring(column,1,4). That should return values of 1001, 1002, 1003
when grouped.

--=-UUju+pOqUEQ86skafFAs
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.8.1">
</HEAD>
<BODY>
On Fri, 2006-02-10 at 00:11 -0600, Bruno Wolff III wrote:
<BLOCKQUOTE TYPE=CITE>
<PRE>
<FONT COLOR="#000000">On Tue, Feb 07, 2006 at 01:45:50 -0800,</FONT>
<FONT COLOR="#000000">  &quot;superboy143 (sent by Nabble.com)&quot; &lt;<A HREF="mailto:lists@nabble.com">lists@nabble.com</A>&gt; wrote:</FONT>
<FONT COLOR="#000000">&gt; </FONT>
<FONT COLOR="#000000">&gt; I have a table in which I have a field with format like 100101. It has many values like 100101, 100102, 100103, 100201, 100202, 100301. I have to write a query such that I have to get only distinct values such that they contain only the substring I need. If I give 10 as substring, then it should return only 100101 or 100102 but not both i.e if the last two characters are not same it should not return both of them. It should return only values starting with 10 the middle two values should be distinct and the last two characters may be anything.</FONT>

<FONT COLOR="#000000">You can probably use the Postgres extension DISTINCT ON to do what you want.</FONT>

<FONT COLOR="#000000">---------------------------(end of broadcast)---------------------------</FONT>
<FONT COLOR="#000000">TIP 3: Have you checked our extensive FAQ?</FONT>

<FONT COLOR="#000000">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <A HREF="http://www.postgresql.org/docs/faq">http://www.postgresq</FONT>l.org/docs/faq</A>
</PRE>
</BLOCKQUOTE>
<BR>
Try substring(column,1,4). That should return values of 1001, 1002, 1003 when grouped.
</BODY>
</HTML>

--=-UUju+pOqUEQ86skafFAs--