pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedquery
15+ messages / 12 participants
[nested] [flat]
* query
@ 2001-12-29 15:46 QdlatY <qdlaty@wielun.dhs.org>
0 siblings, 0 replies; 15+ messages in thread
From: QdlatY @ 2001-12-29 15:46 UTC (permalink / raw)
To: pgsql-sql
BTW. I'm using Postgres 7.0.3
^ permalink raw reply [nested|flat] 15+ messages in thread
* Query
@ 2003-01-03 13:46 Pedro Igor <pedroigor@aip.com.br>
2003-01-03 15:57 ` Re: Query Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 15+ messages in thread
From: Pedro Igor @ 2003-01-03 13:46 UTC (permalink / raw)
To: pgsql-sql
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 ....
I have tried : check (select count(b.id) from B b where b.id = id) <> 0) .... but doesn´t work ......
I can use trigger here, but i don´t know if is the best solution .....
Thanks,
Pedro Igor
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Query
2003-01-03 13:46 Query Pedro Igor <pedroigor@aip.com.br>
@ 2003-01-03 15:57 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 15+ messages in thread
From: Stephan Szabo @ 2003-01-03 15:57 UTC (permalink / raw)
To: Pedro Igor <pedroigor@aip.com.br>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* query
@ 2005-03-17 10:57 Chandan_Kumaraiah <Chandan_Kumaraiah@satyam.com>
2005-03-21 14:33 ` Re: query Thomas F.O'Connell <tfo@sitening.com>
0 siblings, 1 reply; 15+ messages in thread
From: Chandan_Kumaraiah @ 2005-03-17 10:57 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: query
2005-03-17 10:57 query Chandan_Kumaraiah <Chandan_Kumaraiah@satyam.com>
@ 2005-03-21 14:33 ` Thomas F.O'Connell <tfo@sitening.com>
2005-03-21 15:17 ` Re: query Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 15+ messages in thread
From: Thomas F.O'Connell @ 2005-03-21 14:33 UTC (permalink / raw)
To: Chandan_Kumaraiah <Chandan_Kumaraiah@satyam.com>; +Cc: pgsql-sql
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,
>
> 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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: query
2005-03-17 10:57 query Chandan_Kumaraiah <Chandan_Kumaraiah@satyam.com>
2005-03-21 14:33 ` Re: query Thomas F.O'Connell <tfo@sitening.com>
@ 2005-03-21 15:17 ` Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 15+ messages in thread
From: Bruno Wolff III @ 2005-03-21 15:17 UTC (permalink / raw)
To: Thomas F.O'Connell <tfo@sitening.com>; +Cc: Chandan_Kumaraiah <Chandan_Kumaraiah@satyam.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* query
@ 2005-03-17 11:08 Chandan_Kumaraiah <Chandan_Kumaraiah@satyam.com>
2005-03-17 11:43 ` Re: query Terry Fielder <terry@ashtonwoodshomes.com>
0 siblings, 1 reply; 15+ messages in thread
From: Chandan_Kumaraiah @ 2005-03-17 11:08 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: query
2005-03-17 11:08 query Chandan_Kumaraiah <Chandan_Kumaraiah@satyam.com>
@ 2005-03-17 11:43 ` Terry Fielder <terry@ashtonwoodshomes.com>
2005-03-17 12:02 ` Re: query Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 15+ messages in thread
From: Terry Fielder @ 2005-03-17 11:43 UTC (permalink / raw)
To: Chandan_Kumaraiah <Chandan_Kumaraiah@satyam.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: query
2005-03-17 11:08 query Chandan_Kumaraiah <Chandan_Kumaraiah@satyam.com>
2005-03-17 11:43 ` Re: query Terry Fielder <terry@ashtonwoodshomes.com>
@ 2005-03-17 12:02 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 15+ messages in thread
From: Richard Huxton @ 2005-03-17 12:02 UTC (permalink / raw)
To: Terry Fielder <terry@ashtonwoodshomes.com>; +Cc: Chandan_Kumaraiah <Chandan_Kumaraiah@satyam.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* query
@ 2005-11-18 05:25 Aftab Alam <aalam@tatashare.com>
2005-11-20 07:01 ` Re: query JuanCri <juancri@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Aftab Alam @ 2005-11-18 05:25 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: query
2005-11-18 05:25 query Aftab Alam <aalam@tatashare.com>
@ 2005-11-20 07:01 ` JuanCri <juancri@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: JuanCri @ 2005-11-20 07:01 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* query
@ 2006-02-07 09:45 superboy143 (sent by Nabble.com) <lists@nabble.com>
2006-02-07 10:34 ` Re: query Richard Huxton <dev@archonet.com>
2006-02-10 06:11 ` Re: query Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 15+ messages in thread
From: superboy143 (sent by Nabble.com) @ 2006-02-07 09:45 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: query
2006-02-07 09:45 query superboy143 (sent by Nabble.com) <lists@nabble.com>
@ 2006-02-07 10:34 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 15+ messages in thread
From: Richard Huxton @ 2006-02-07 10:34 UTC (permalink / raw)
To: superboy143 <superboy143@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: query
2006-02-07 09:45 query superboy143 (sent by Nabble.com) <lists@nabble.com>
@ 2006-02-10 06:11 ` Bruno Wolff III <bruno@wolff.to>
2006-02-10 16:22 ` Re: query Ken Hill <ken@scottshill.com>
1 sibling, 1 reply; 15+ messages in thread
From: Bruno Wolff III @ 2006-02-10 06:11 UTC (permalink / raw)
To: superboy143 <superboy143@gmail.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: query
2006-02-07 09:45 query superboy143 (sent by Nabble.com) <lists@nabble.com>
2006-02-10 06:11 ` Re: query Bruno Wolff III <bruno@wolff.to>
@ 2006-02-10 16:22 ` Ken Hill <ken@scottshill.com>
0 siblings, 0 replies; 15+ messages in thread
From: Ken Hill @ 2006-02-10 16:22 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: superboy143 <superboy143@gmail.com>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2006-02-10 16:22 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-12-29 15:46 query QdlatY <qdlaty@wielun.dhs.org>
2003-01-03 13:46 Query Pedro Igor <pedroigor@aip.com.br>
2003-01-03 15:57 ` Re: Query Stephan Szabo <sszabo@megazone23.bigpanda.com>
2005-03-17 10:57 query Chandan_Kumaraiah <Chandan_Kumaraiah@satyam.com>
2005-03-21 14:33 ` Thomas F.O'Connell <tfo@sitening.com>
2005-03-21 15:17 ` Bruno Wolff III <bruno@wolff.to>
2005-03-17 11:08 query Chandan_Kumaraiah <Chandan_Kumaraiah@satyam.com>
2005-03-17 11:43 ` Terry Fielder <terry@ashtonwoodshomes.com>
2005-03-17 12:02 ` Richard Huxton <dev@archonet.com>
2005-11-18 05:25 query Aftab Alam <aalam@tatashare.com>
2005-11-20 07:01 ` JuanCri <juancri@gmail.com>
2006-02-07 09:45 query superboy143 (sent by Nabble.com) <lists@nabble.com>
2006-02-07 10:34 ` Richard Huxton <dev@archonet.com>
2006-02-10 06:11 ` Bruno Wolff III <bruno@wolff.to>
2006-02-10 16:22 ` Ken Hill <ken@scottshill.com>
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