pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWHERE clause?
10+ messages / 8 participants
[nested] [flat]
* WHERE clause?
@ 1999-07-14 05:17 Jim Rowan <jmr@computing.com>
0 siblings, 1 reply; 10+ messages in thread
From: Jim Rowan @ 1999-07-14 05:17 UTC (permalink / raw)
To: pgsql-sql@hub.org
I'm trying to create a log table to track changes to a table.. so I've
created a set of rules that look something like this:
CREATE RULE "m_log_change" AS ON UPDATE TO "machine"
do (
INSERT INTO machine_log (who, date, machnum, col, newval)
SELECT getpgusername(), 'now'::text, old.machnum,
'host', new.host
WHERE (new.host != old.host) or
(old.host IS NOT NULL and new.host IS NULL) or
(old.host IS NULL and new.host IS NOT NULL);
... more similar INSERT statements with different WHERE clauses..
);
My question is:
Is this the best way to code the WHERE? What I'm really after is "did the
value change?". I found that the tests to see if one-but-not-both of the
values is NULL are required to identify times when the value changed to or
from NULL.
Seems like a lot of extra work...
Jim Rowan DCSI DCE/DFS/Sysadmin Consulting
jmr@computing.com (512) 374-1143
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] WHERE clause?
@ 1999-07-14 14:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jim Rowan <jmr@computing.com>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 1999-07-14 14:00 UTC (permalink / raw)
To: Jim Rowan <jmr@computing.com>; +Cc: pgsql-sql@hub.org
Jim Rowan <jmr@computing.com> writes:
> WHERE (new.host != old.host) or
> (old.host IS NOT NULL and new.host IS NULL) or
> (old.host IS NULL and new.host IS NOT NULL);
> Is this the best way to code the WHERE? What I'm really after is "did the
> value change?". I found that the tests to see if one-but-not-both of the
> values is NULL are required to identify times when the value changed to or
> from NULL.
Yes, because any ordinary operator applied to NULL will produce NULL,
which WHERE interprets as FALSE. The only operations that actually
work on nulls are IS NULL/IS NOT NULL. In 6.5 you can use COALESCE
as a handy abbreviation for certain sorts of IS NOT NULL tests, but
offhand I don't see a good way to apply it here.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] WHERE clause?
@ 1999-07-14 15:50 Steven Bradley <sbradley@llnl.gov>
0 siblings, 0 replies; 10+ messages in thread
From: Steven Bradley @ 1999-07-14 15:50 UTC (permalink / raw)
To: Jim Rowan <jmr@computing.com>; +Cc: pgsql-sql
To the best of my knowledge, this is the way to do it; however, if you
wanted to save on coding, you could always create a function to do the
comparison for you and then call this function for each column. Another
thing I've done (under Oracle) is to generalize and normalize the audit log
tables so that they can be used for changes to ANY table in the database.
The three tables required for the would be
--
-- top level table, stores general info about the SQL command that caused a
-- record's data to change
--
create table audit_event
(
audit_id number(9) not null, /* unique
key */
dbms_operation char(1) null , /* (I)nsert,
(U)pdate, (D)elete */
table_name varchar2(30) null , /* table
being modifed */
username varchar2(30) null , /* user
making the modification */
audit_ts timestamp null , /* when the
change was made */
constraint pk_audit_event primary key (audit_id)
)
--
-- contains the column name/value pairs which unquely identify the record
being changed.
-- one-to-many relationship with audit_event table. Will contain multiple
records per
-- SQL statement for modification of records with multi-column PK
--
create table audit_key
(
audit_id number(9) not null, /* FK to
audit_event */
key_col_name varchar2(30) not null, /* PK
column name */
key_col_value varchar2(64) not null, /* PK value
*/
constraint pk_audit_key primary key (audit_id, key_col_name)
)
--
-- contains the column name/value pairs which for columns affected by the
SQL command.
-- For INSERT, this will contain all columns in the table. For UPDATE,
this will
-- contain only the columns which are modified. No columns exist in this
table for a
-- DELETE operation.
--
create table audit_column
(
audit_id number(9) not null,
column_name varchar2(30) not null,
old_value varchar2(2000) null ,
new_value varchar2(2000) null ,
constraint pk_audit_column primary key (audit_id, column_name)
)
Hope this gives some "food for thought" on how you can implement a
generalized audit capability for all of your tables.
Steven Bradley
Lawrence Livermore National Laboratory
PO Box 808
Livermore, California 94550
sbradley@llnl.gov
At 12:17 AM 7/14/99 -0500, you wrote:
>
>I'm trying to create a log table to track changes to a table.. so I've
>created a set of rules that look something like this:
>
>CREATE RULE "m_log_change" AS ON UPDATE TO "machine"
>do (
> INSERT INTO machine_log (who, date, machnum, col, newval)
> SELECT getpgusername(), 'now'::text, old.machnum,
> 'host', new.host
> WHERE (new.host != old.host) or
> (old.host IS NOT NULL and new.host IS NULL) or
> (old.host IS NULL and new.host IS NOT NULL);
>... more similar INSERT statements with different WHERE clauses..
>);
>
>My question is:
>
>Is this the best way to code the WHERE? What I'm really after is "did the
>value change?". I found that the tests to see if one-but-not-both of the
>values is NULL are required to identify times when the value changed to or
>from NULL.
>
>Seems like a lot of extra work...
>
>
>Jim Rowan DCSI DCE/DFS/Sysadmin Consulting
>jmr@computing.com (512) 374-1143
>
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Where clause
@ 2007-06-26 08:24 Michael Landin Hostbaek <mich@the-lab.org>
0 siblings, 2 replies; 10+ messages in thread
From: Michael Landin Hostbaek @ 2007-06-26 08:24 UTC (permalink / raw)
To: pgsql-sql
Hello,
I have a table called tracking, with a contactid varchar, click bool,
view bool and cid varchar.
I would like to put the following into one single query if possible:
// Number of clicks
select cid,count(distinct contactid) from tracking where click =
true group by cid;
// Number of views
select cid,count(distinct contactid) from tracking where view =
true group by cid;
I guess I have to put where (click = true or view = true) - and
differentiate them in the SELECT target.. ?
Many thanks for any input,
Mike
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Where clause
@ 2007-06-26 09:01 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Michael Landin Hostbaek <mich@the-lab.org>
1 sibling, 1 reply; 10+ messages in thread
From: A. Kretschmer @ 2007-06-26 09:01 UTC (permalink / raw)
To: pgsql-sql; +Cc: Michael Landin Hostbaek <mich@the-lab.org>
am Tue, dem 26.06.2007, um 10:24:05 +0200 mailte Michael Landin Hostbaek folgendes:
> Hello,
>
> I have a table called tracking, with a contactid varchar, click bool,
> view bool and cid varchar.
>
> I would like to put the following into one single query if possible:
>
> // Number of clicks
> select cid,count(distinct contactid) from tracking where click =
> true group by cid;
>
> // Number of views
> select cid,count(distinct contactid) from tracking where view =
> true group by cid;
>
> I guess I have to put where (click = true or view = true) - and
> differentiate them in the SELECT target.. ?
*untested*
select cid, sum(case when click = true then 1 else 0 end), sum(case when
view = true then 1 else 0 end) from ...
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Where clause
@ 2007-06-26 09:44 Michael Landin Hostbaek <mich@the-lab.org>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 2 replies; 10+ messages in thread
From: Michael Landin Hostbaek @ 2007-06-26 09:44 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; +Cc: pgsql-sql
A. Kretschmer (andreas.kretschmer) writes:
> *untested*
>
> select cid, sum(case when click = true then 1 else 0 end), sum(case when
> view = true then 1 else 0 end) from ...
>
Thanks, but I need the DISTINCT contactid - I don't want the same
contactid counted twice.
Mike
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Where clause
@ 2007-06-26 10:31 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Michael Landin Hostbaek <mich@the-lab.org>
1 sibling, 0 replies; 10+ messages in thread
From: Achilleas Mantzios @ 2007-06-26 10:31 UTC (permalink / raw)
To: Michael Landin Hostbaek <mich@the-lab.org>; A. Kretschmer <andreas.kretschmer@schollglas.com>; pgsql-sql
Óôéò Ôñßôç 26 Éïýíéïò 2007 12:44, ï/ç Michael Landin Hostbaek Ýãñáøå:
> A. Kretschmer (andreas.kretschmer) writes:
> > *untested*
> >
> > select cid, sum(case when click = true then 1 else 0 end), sum(case when
> > view = true then 1 else 0 end) from ...
>
> Thanks, but I need the DISTINCT contactid - I don't want the same
> contactid counted twice.
>
Something like
SELECT distinct cid,(select count(distinct t1.contactid) from tracking t1
where t1.view and t1.cid=tglob.cid) as countviews,(select count(distinct
t2.contactid) from tracking t2 where t2.click and t2.cid=tglob.cid) as
countclicks from tracking tglob;
?
> Mike
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
--
Achilleas Mantzios
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Where clause
@ 2007-06-26 10:45 Richard Huxton <dev@archonet.com>
parent: Michael Landin Hostbaek <mich@the-lab.org>
1 sibling, 0 replies; 10+ messages in thread
From: Richard Huxton @ 2007-06-26 10:45 UTC (permalink / raw)
To: Michael Landin Hostbaek <mich@the-lab.org>; A. Kretschmer <andreas.kretschmer@schollglas.com>; pgsql-sql
Michael Landin Hostbaek wrote:
> A. Kretschmer (andreas.kretschmer) writes:
>> *untested*
>>
>> select cid, sum(case when click = true then 1 else 0 end), sum(case when
>> view = true then 1 else 0 end) from ...
>>
>
> Thanks, but I need the DISTINCT contactid - I don't want the same
> contactid counted twice.
... GROUP BY cid
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Where clause
@ 2007-06-26 15:22 news.gmane.org <nis@superlativ.dk>
parent: Michael Landin Hostbaek <mich@the-lab.org>
1 sibling, 1 reply; 10+ messages in thread
From: news.gmane.org @ 2007-06-26 15:22 UTC (permalink / raw)
To: pgsql-sql
Michael Landin Hostbaek skrev:
> Hello,
>
> I have a table called tracking, with a contactid varchar, click bool,
> view bool and cid varchar.
>
> I would like to put the following into one single query if possible:
>
> // Number of clicks
> select cid,count(distinct contactid) from tracking where click =
> true group by cid;
>
> // Number of views
> select cid,count(distinct contactid) from tracking where view =
> true group by cid;
Untested, not the cleverest formulation, but something like this should
work:
SELECT * FROM
(
select cid,count(distinct contactid) from tracking where click =
true group by cid
) c1
FULL OUTER JOIN
(
select cid,count(distinct contactid) from tracking where view =
true group by cid
) c2
USING (cid);
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Where clause
@ 2007-06-27 08:12 Michael Landin Hostbaek <mich@the-lab.org>
parent: news.gmane.org <nis@superlativ.dk>
0 siblings, 0 replies; 10+ messages in thread
From: Michael Landin Hostbaek @ 2007-06-27 08:12 UTC (permalink / raw)
To: news.gmane.org <nis@superlativ.dk>; +Cc: pgsql-sql
news.gmane.org (nis) writes:
> SELECT * FROM
> (
> select cid,count(distinct contactid) from tracking where click =
> true group by cid
> ) c1
> FULL OUTER JOIN
> (
> select cid,count(distinct contactid) from tracking where view =
> true group by cid
> ) c2
> USING (cid);
That did the trick!
Many thanks,
Mike
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2007-06-27 08:12 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-07-14 05:17 WHERE clause? Jim Rowan <jmr@computing.com>
1999-07-14 14:00 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-07-14 15:50 Re: [SQL] WHERE clause? Steven Bradley <sbradley@llnl.gov>
2007-06-26 08:24 Where clause Michael Landin Hostbaek <mich@the-lab.org>
2007-06-26 09:01 ` Re: Where clause A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-06-26 09:44 ` Re: Where clause Michael Landin Hostbaek <mich@the-lab.org>
2007-06-26 10:31 ` Re: Where clause Achilleas Mantzios <achill@matrix.gatewaynet.com>
2007-06-26 10:45 ` Re: Where clause Richard Huxton <dev@archonet.com>
2007-06-26 15:22 ` Re: Where clause news.gmane.org <nis@superlativ.dk>
2007-06-27 08:12 ` Re: Where clause Michael Landin Hostbaek <mich@the-lab.org>
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