pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRE: SQL Syntax
11+ messages / 9 participants
[nested] [flat]
* RE: SQL Syntax
@ 1999-02-08 20:49 Jackson, DeJuan <djackson@cpsgroup.com>
0 siblings, 0 replies; 11+ messages in thread
From: Jackson, DeJuan @ 1999-02-08 20:49 UTC (permalink / raw)
To: khj@cs.appstate.edu; +Cc: pgsql-sql
The error that you are running into is it appears you believe that || is
an OR operator. In Postgres (and I believe the SQL standard) the '||'
operator is used for string concatenation.
I believe the query you are seeking is:
select title from book where title ~* 'world|nation'
-DEJ;
> -----Original Message-----
> I am trying to use something like the following in 6.4.2:
>
> select title from book where title ~* ('world' || 'nation');
>
> Though one of your emails to the 'postgresql' list uses something
> similar, the above returns no matches (even though some records
> contain both strings).
>
> Any ideas?
>
> I'm trying to find a shorter form than
>
> select title from book where title ~* 'world' or title ~* 'nation';
>
>
> Thanks for any help,
>
> -Prof Kenneth H Jacker khj@cs.appstate.edu
> -Computer Science Dept www.cs.appstate.edu/~khj
> -Appalachian State Univ
> -Boone, NC 28608 USA
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* SQL syntax
@ 2002-07-31 13:16 Mindaugas Riauba <mind@bi.lt>
2002-07-31 15:57 ` Re: SQL syntax Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 11+ messages in thread
From: Mindaugas Riauba @ 2002-07-31 13:16 UTC (permalink / raw)
To: pgsql-sql
I have two similar tables with host and services availability
data (Nagios). And I want to find out services which are not OK
in first table and OK in second one. Query I used is:
select c.* from coll_servicestatus as c inner join
servicestatus as s on (c.service_description=s.service_description
and c.host_name=s.host_name) where c.service_status != 'OK' and
s.service_status = 'OK';
Results are fine. But how to write UPDATE statement if I want
to set those non-OK states in first table to OK if they are OK
in the second table?
Something like:
update coll_servicestatus set service_status = 'OK' from
coll_servicestatus as c, servicestatus as s where
c.service_description = s.service_description and c.host_name=s.host_name
and c.service_status != 'OK' and s.service_status = 'OK';
updates all rows not only required ones.
Mindaugas
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: SQL syntax
2002-07-31 13:16 SQL syntax Mindaugas Riauba <mind@bi.lt>
@ 2002-07-31 15:57 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 11+ messages in thread
From: Stephan Szabo @ 2002-07-31 15:57 UTC (permalink / raw)
To: Mindaugas Riauba <mind@bi.lt>; +Cc: pgsql-sql
On Wed, 31 Jul 2002, Mindaugas Riauba wrote:
>
> I have two similar tables with host and services availability
> data (Nagios). And I want to find out services which are not OK
> in first table and OK in second one. Query I used is:
>
> select c.* from coll_servicestatus as c inner join
> servicestatus as s on (c.service_description=s.service_description
> and c.host_name=s.host_name) where c.service_status != 'OK' and
> s.service_status = 'OK';
>
> Results are fine. But how to write UPDATE statement if I want
> to set those non-OK states in first table to OK if they are OK
> in the second table?
>
> Something like:
>
> update coll_servicestatus set service_status = 'OK' from
> coll_servicestatus as c, servicestatus as s where
> c.service_description = s.service_description and c.host_name=s.host_name
> and c.service_status != 'OK' and s.service_status = 'OK';
>
> updates all rows not only required ones.
There are two joins on coll_servicestatus in the above, the one in the
from and one with the updating table. You could probably remove the
coll_servicestatus as c, and change references to c to coll_servicestatus
or use a subselect, something like:
update coll_servicestatus set service_status='OK' where
service_status!='OK' and exists (select * from servicestatus as s where
coll_servicestatus.service_description=s.service_description and
coll_servicestatus.host_name=s.host_name and s.service_status='OK');
^ permalink raw reply [nested|flat] 11+ messages in thread
* SQL syntax
@ 2002-08-06 15:29 frederic massot <frederic@juliana-multimedia.com>
2002-08-06 16:48 ` Re: SQL syntax Tod McQuillin <devin@spamcop.net>
2002-08-06 17:10 ` Re: SQL syntax Cedric Dufour (Cogito Ergo Soft) <cedric.dufour@cogito-ergo-soft.com>
2002-08-06 17:38 ` Re: SQL syntax Jean-Luc Lachance <jllachan@nsd.ca>
0 siblings, 3 replies; 11+ messages in thread
From: frederic massot @ 2002-08-06 15:29 UTC (permalink / raw)
To: pgsql-sql
Hello,
I have a table containing the months of the year :
travail=> select * from mois order by id_mois;
id_mois|mois_fr |mois_en |mois_de
-------+---------+---------+---------
1|Janvier |January |Januar
2|Fevrier |February |Februar
3|Mars |March |Marz
4|Avril |April |April
5|Mai |May |Aber
6|Juin |June |Juni
7|Juillet |July |Juli
8|Aout |August |August
9|Septembre|September|September
10|Octobre |October |Oktober
11|Novembre |November |November
12|Decembre |December |Dezember
(12 rows)
And I try to obtain for one month given (for example August : 8) two
previous months, the current month, and nine next months :
id_mois|mois_fr |mois_en |mois_de
-------+---------+---------+---------
6|Juin |June |Juni
7|Juillet |July |Juli
8|Aout |August |August
9|Septembre|September|September
10|Octobre |October |Oktober
11|Novembre |November |November
12|Decembre |December |Dezember
1|Janvier |January |Januar
2|Fevrier |February |Februar
3|Mars |March |Marz
4|Avril |April |April
5|Mai |May |Aber
Do you see how making in a SQL request ?
Thank you.
--
==============================================
| FREDERIC MASSOT |
| http://www.juliana-multimedia.com |
| mailto:frederic@juliana-multimedia.com |
===========================Debian=GNU/Linux===
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: SQL syntax
2002-08-06 15:29 SQL syntax frederic massot <frederic@juliana-multimedia.com>
@ 2002-08-06 16:48 ` Tod McQuillin <devin@spamcop.net>
2 siblings, 0 replies; 11+ messages in thread
From: Tod McQuillin @ 2002-08-06 16:48 UTC (permalink / raw)
To: frederic massot <frederic@juliana-multimedia.com>; +Cc: pgsql-sql
On Tue, 6 Aug 2002, frederic massot wrote:
> And I try to obtain for one month given (for example August : 8) two
> previous months, the current month, and nine next months :
>
>
> id_mois|mois_fr |mois_en |mois_de
> -------+---------+---------+---------
> 6|Juin |June |Juni
> 7|Juillet |July |Juli
> 8|Aout |August |August
> 9|Septembre|September|September
> 10|Octobre |October |Oktober
> 11|Novembre |November |November
> 12|Decembre |December |Dezember
> 1|Janvier |January |Januar
> 2|Fevrier |February |Februar
> 3|Mars |March |Marz
> 4|Avril |April |April
> 5|Mai |May |Aber
>
> Do you see how making in a SQL request ?
given m = 8:
select * from mois
order by case when id_mois < (m + 10) % 12 then id_mois + 13 else id_mois
end;
--
Tod McQuillin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: SQL syntax
2002-08-06 15:29 SQL syntax frederic massot <frederic@juliana-multimedia.com>
@ 2002-08-06 17:10 ` Cedric Dufour (Cogito Ergo Soft) <cedric.dufour@cogito-ergo-soft.com>
2 siblings, 0 replies; 11+ messages in thread
From: Cedric Dufour (Cogito Ergo Soft) @ 2002-08-06 17:10 UTC (permalink / raw)
To: frederic massot <frederic@juliana-multimedia.com>; pgsql-sql
I would try something like
SELECT
(id_mois+12)%12
FROM
( SELECT id_mois-12 AS id_mois FROM mois
UNION
SELECT id_mois FROM mois
UNION
SELECT id_mois+12 AS id_mois FROM mois ) AS mois_etendu
WHERE
( mois_etendu.id_mois >= (mon_mois-2) )
AND
( mois_etendu.id_mois <= (mon_mois+9) )
where 'mon_mois' is your reference month (e.g. 8)
Cedric Dufour
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of frederic massot
> Sent: Tuesday, August 06, 2002 17:29
> To: pgsql-sql@postgresql.org
> Subject: [SQL] SQL syntax
>
>
> Hello,
>
> I have a table containing the months of the year :
>
>
> travail=> select * from mois order by id_mois;
>
> id_mois|mois_fr |mois_en |mois_de
> -------+---------+---------+---------
> 1|Janvier |January |Januar
> 2|Fevrier |February |Februar
> 3|Mars |March |Marz
> 4|Avril |April |April
> 5|Mai |May |Aber
> 6|Juin |June |Juni
> 7|Juillet |July |Juli
> 8|Aout |August |August
> 9|Septembre|September|September
> 10|Octobre |October |Oktober
> 11|Novembre |November |November
> 12|Decembre |December |Dezember
> (12 rows)
>
>
> And I try to obtain for one month given (for example August : 8) two
> previous months, the current month, and nine next months :
>
>
> id_mois|mois_fr |mois_en |mois_de
> -------+---------+---------+---------
> 6|Juin |June |Juni
> 7|Juillet |July |Juli
> 8|Aout |August |August
> 9|Septembre|September|September
> 10|Octobre |October |Oktober
> 11|Novembre |November |November
> 12|Decembre |December |Dezember
> 1|Janvier |January |Januar
> 2|Fevrier |February |Februar
> 3|Mars |March |Marz
> 4|Avril |April |April
> 5|Mai |May |Aber
>
>
> Do you see how making in a SQL request ?
>
> Thank you.
> --
> ==============================================
> | FREDERIC MASSOT |
> | http://www.juliana-multimedia.com |
> | mailto:frederic@juliana-multimedia.com |
> ===========================Debian=GNU/Linux===
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: SQL syntax
2002-08-06 15:29 SQL syntax frederic massot <frederic@juliana-multimedia.com>
@ 2002-08-06 17:38 ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-08-06 17:53 ` Re: SQL syntax Jean-Luc Lachance <jllachan@nsd.ca>
2 siblings, 1 reply; 11+ messages in thread
From: Jean-Luc Lachance @ 2002-08-06 17:38 UTC (permalink / raw)
To: frederic massot <frederic@juliana-multimedia.com>; +Cc: pgsql-sql
Try:
Order by mod( id_mois + 12 - <current_month>, 12)
frederic massot wrote:
>
> Hello,
>
> I have a table containing the months of the year :
>
> travail=> select * from mois order by id_mois;
>
> id_mois|mois_fr |mois_en |mois_de
> -------+---------+---------+---------
> 1|Janvier |January |Januar
> 2|Fevrier |February |Februar
> 3|Mars |March |Marz
> 4|Avril |April |April
> 5|Mai |May |Aber
> 6|Juin |June |Juni
> 7|Juillet |July |Juli
> 8|Aout |August |August
> 9|Septembre|September|September
> 10|Octobre |October |Oktober
> 11|Novembre |November |November
> 12|Decembre |December |Dezember
> (12 rows)
>
> And I try to obtain for one month given (for example August : 8) two
> previous months, the current month, and nine next months :
>
> id_mois|mois_fr |mois_en |mois_de
> -------+---------+---------+---------
> 6|Juin |June |Juni
> 7|Juillet |July |Juli
> 8|Aout |August |August
> 9|Septembre|September|September
> 10|Octobre |October |Oktober
> 11|Novembre |November |November
> 12|Decembre |December |Dezember
> 1|Janvier |January |Januar
> 2|Fevrier |February |Februar
> 3|Mars |March |Marz
> 4|Avril |April |April
> 5|Mai |May |Aber
>
> Do you see how making in a SQL request ?
>
> Thank you.
> --
> ==============================================
> | FREDERIC MASSOT |
> | http://www.juliana-multimedia.com |
> | mailto:frederic@juliana-multimedia.com |
> ===========================Debian=GNU/Linux===
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: SQL syntax
2002-08-06 15:29 SQL syntax frederic massot <frederic@juliana-multimedia.com>
2002-08-06 17:38 ` Re: SQL syntax Jean-Luc Lachance <jllachan@nsd.ca>
@ 2002-08-06 17:53 ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-08-07 03:31 ` Re: SQL syntax Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Jean-Luc Lachance @ 2002-08-06 17:53 UTC (permalink / raw)
To: frederic massot <frederic@juliana-multimedia.com>; pgsql-sql
Can someone *pleaaaaase* temporarely remove
"Bob Powell" <Bob@hotchkiss.org>
from the list so we do not get a vacation message for every message one
posts.
Jean-Luc Lachance wrote:
>
> Try:
>
> Order by mod( id_mois + 12 - <current_month>, 12)
>
> frederic massot wrote:
> >
> > Hello,
> >
> > I have a table containing the months of the year :
> >
> > travail=> select * from mois order by id_mois;
> >
> > id_mois|mois_fr |mois_en |mois_de
> > -------+---------+---------+---------
> > 1|Janvier |January |Januar
> > 2|Fevrier |February |Februar
> > 3|Mars |March |Marz
> > 4|Avril |April |April
> > 5|Mai |May |Aber
> > 6|Juin |June |Juni
> > 7|Juillet |July |Juli
> > 8|Aout |August |August
> > 9|Septembre|September|September
> > 10|Octobre |October |Oktober
> > 11|Novembre |November |November
> > 12|Decembre |December |Dezember
> > (12 rows)
> >
> > And I try to obtain for one month given (for example August : 8) two
> > previous months, the current month, and nine next months :
> >
> > id_mois|mois_fr |mois_en |mois_de
> > -------+---------+---------+---------
> > 6|Juin |June |Juni
> > 7|Juillet |July |Juli
> > 8|Aout |August |August
> > 9|Septembre|September|September
> > 10|Octobre |October |Oktober
> > 11|Novembre |November |November
> > 12|Decembre |December |Dezember
> > 1|Janvier |January |Januar
> > 2|Fevrier |February |Februar
> > 3|Mars |March |Marz
> > 4|Avril |April |April
> > 5|Mai |May |Aber
> >
> > Do you see how making in a SQL request ?
> >
> > Thank you.
> > --
> > ==============================================
> > | FREDERIC MASSOT |
> > | http://www.juliana-multimedia.com |
> > | mailto:frederic@juliana-multimedia.com |
> > ===========================Debian=GNU/Linux===
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: SQL syntax
2002-08-06 15:29 SQL syntax frederic massot <frederic@juliana-multimedia.com>
2002-08-06 17:38 ` Re: SQL syntax Jean-Luc Lachance <jllachan@nsd.ca>
2002-08-06 17:53 ` Re: SQL syntax Jean-Luc Lachance <jllachan@nsd.ca>
@ 2002-08-07 03:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-08-07 03:47 ` Re: SQL syntax Bruno Wolff III <bruno@wolff.to>
2002-08-09 21:36 ` Re: SQL syntax Jean-Luc Lachance <jllachan@nsd.ca>
0 siblings, 2 replies; 11+ messages in thread
From: Tom Lane @ 2002-08-07 03:31 UTC (permalink / raw)
To: Jean-Luc Lachance <jllachan@nsd.ca>; +Cc: frederic massot <frederic@juliana-multimedia.com>; pgsql-sql@postgresql.org, Marc Fournier <scrappy@hub.org>
Jean-Luc Lachance <jllachan@nsd.ca> writes:
> Can someone *pleaaaaase* temporarely remove
> "Bob Powell" <Bob@hotchkiss.org>
> from the list so we do not get a vacation message for every message one
> posts.
I complained to Marc about that a week or more ago, but I guess he
doesn't want to bounce Bob from the lists just for being incompetent
about configuring "vacation". I've set my own mailserver to deny
connections from hotchkiss.org ...
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: SQL syntax
2002-08-06 15:29 SQL syntax frederic massot <frederic@juliana-multimedia.com>
2002-08-06 17:38 ` Re: SQL syntax Jean-Luc Lachance <jllachan@nsd.ca>
2002-08-06 17:53 ` Re: SQL syntax Jean-Luc Lachance <jllachan@nsd.ca>
2002-08-07 03:31 ` Re: SQL syntax Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-08-07 03:47 ` Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 11+ messages in thread
From: Bruno Wolff III @ 2002-08-07 03:47 UTC (permalink / raw)
To: pgsql-sql
On Tue, Aug 06, 2002 at 23:31:49 -0400,
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jean-Luc Lachance <jllachan@nsd.ca> writes:
> > Can someone *pleaaaaase* temporarely remove
> > "Bob Powell" <Bob@hotchkiss.org>
> > from the list so we do not get a vacation message for every message one
> > posts.
>
> I complained to Marc about that a week or more ago, but I guess he
> doesn't want to bounce Bob from the lists just for being incompetent
> about configuring "vacation". I've set my own mailserver to deny
> connections from hotchkiss.org ...
He isn't using software appropiate as a vacation responder.
It is responding to the wrong address. Delivery status messages (such as
vacation messages) should go to the envelope sender address, not addresses
in the message headers.
The responder has replied to messages that didn't explicitly list his
email address in the recipient headers (as well as having several other
features indicating they were list messages rather than messages specifically
for him).
I also received several notifications from his bot over a couple of hours.
Normally vacation bots don't send notices more than about once every two weeks.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: SQL syntax
2002-08-06 15:29 SQL syntax frederic massot <frederic@juliana-multimedia.com>
2002-08-06 17:38 ` Re: SQL syntax Jean-Luc Lachance <jllachan@nsd.ca>
2002-08-06 17:53 ` Re: SQL syntax Jean-Luc Lachance <jllachan@nsd.ca>
2002-08-07 03:31 ` Re: SQL syntax Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-08-09 21:36 ` Jean-Luc Lachance <jllachan@nsd.ca>
1 sibling, 0 replies; 11+ messages in thread
From: Jean-Luc Lachance @ 2002-08-09 21:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: frederic massot <frederic@juliana-multimedia.com>; pgsql-sql@postgresql.org, Marc Fournier <scrappy@hub.org>
Well.... It's Friday and I am still geting vacation messages from
Bob....
Tom Lane wrote:
>
> Jean-Luc Lachance <jllachan@nsd.ca> writes:
> > Can someone *pleaaaaase* temporarely remove
> > "Bob Powell" <Bob@hotchkiss.org>
> > from the list so we do not get a vacation message for every message one
> > posts.
>
> I complained to Marc about that a week or more ago, but I guess he
> doesn't want to bounce Bob from the lists just for being incompetent
> about configuring "vacation". I've set my own mailserver to deny
> connections from hotchkiss.org ...
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2002-08-09 21:36 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-02-08 20:49 RE: SQL Syntax Jackson, DeJuan <djackson@cpsgroup.com>
2002-07-31 13:16 SQL syntax Mindaugas Riauba <mind@bi.lt>
2002-07-31 15:57 ` Re: SQL syntax Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-08-06 15:29 SQL syntax frederic massot <frederic@juliana-multimedia.com>
2002-08-06 16:48 ` Re: SQL syntax Tod McQuillin <devin@spamcop.net>
2002-08-06 17:10 ` Re: SQL syntax Cedric Dufour (Cogito Ergo Soft) <cedric.dufour@cogito-ergo-soft.com>
2002-08-06 17:38 ` Re: SQL syntax Jean-Luc Lachance <jllachan@nsd.ca>
2002-08-06 17:53 ` Re: SQL syntax Jean-Luc Lachance <jllachan@nsd.ca>
2002-08-07 03:31 ` Re: SQL syntax Tom Lane <tgl@sss.pgh.pa.us>
2002-08-07 03:47 ` Re: SQL syntax Bruno Wolff III <bruno@wolff.to>
2002-08-09 21:36 ` Re: SQL syntax Jean-Luc Lachance <jllachan@nsd.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