[SQL] Equality operators on NULL values
(in)Equality operators evaluates to NULL when any of their
arguments are NULL as summarized bellow.
NULL=NULL ==> NULL
1=NULL==> NULL
1=1 ==> t
1=2 ==> f
I have a case where I need to delete rows from table A for records
that are designated in table B. One of the joined colum can have NULL
values. Therefore I need an operator behaving as follows:
NULL=NULL ==> t
1=NULL==> NULL (or anything else as long as it is not t)
1=1 ==> t
1=2 ==> f
I replaced the use of the = operator with this expression:
nullif(tabA.col_with_nulls, tabB.col_with_nulls) IS NULL
It works correctly but the intent is not that clear.
Is there a stock replacement operator that would behave like
the second truth table?
Regards,
Bertrand.
--
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Equality operators on NULL values
Bertrand Petit writes: > NULL=NULL ==> t > 1=NULL==> NULL (or anything else as long as it is not t) > 1=1 ==> t > 1=2 ==> f > Is there a stock replacement operator that would behave like > the second truth table? No, but why not write (a = b) or (a is null and b is null) -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Auto-update a field when record is changed
> On Fri, 22 Aug 2003, Stuart wrote: > > > Folks, > > > > I was wandering if there was a feasible way to automatically update a field > > in a table, say 'revision_date' in a record whenever any other field in the > > record is changed. My attempts to use a trigger caused repeating loops and > > bombed with error. I would like to be able to update such a field > > automatically anytime a record was updated. Any help would be appreciated. Unless I'm misunderstanding you, this is really easy. Here's what I use in nearly every database I build: create or replace function timestamp_fn() returns opaque as ' begin NEW.moddate = now(); return NEW; end ' language 'plpgsql'; create table blah ( ... createdate timestamp default now(), moddate timestamp, create trigger blah_timestamp_tr before insert or update on blah for each row execute procedure timestamp_fn(); Make the obvious changes for only doing this on updates. Or am I misunderstanding your goal? -j -- Jamie Lawrence[EMAIL PROTECTED] "One of the great things about books is that sometimes there are some fantastic pictures." - George H. W. Bush ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Porting from PL/SQL to PLPGSQL
Hi.. In postgres, you have two options.. >From PSQL prompt, you can use " \i filename " to process the file.. OR from PSQL prompt, you can directly instruct to execute that file like.. psql ... --filename <> Basically, when "&&" is specified in PL/SQL scripts, it prompts the user to enter its value. In Postgres, we do not have such option (so far i know). So, you can replace those with your desired values directly. Postgres supports Array type too. Although i haven't used it anyday.. you can try it out. HTH Denis - Original Message - From: "Jomon Skariah" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, August 20, 2003 3:15 PM Subject: [SQL] Porting from PL/SQL to PLPGSQL > > Hi All, > > We are in the process of migrating of our application from Oracle to > PostGreSQL. > > > we are facing a few problems with PL/SQL Code.. > > > 1) In Oracle sqlplus we can run sql script files as @script_name; > How do we do the same in PostGres. > Also is there any replacement for "&&" in PostGres ? > > > Eg: > CREATE USER CATALOG > IDENTIFIED BY &ORA_PASSWORD > DEFAULT TABLESPACE &DFLT_TABLESPACE > TEMPORARY TABLESPACE &TEMP_TABLESPACE > > > 2) In PostGres a function can not take more 16 arguments.We have some > procedures which are taking more than > 16 arguements.So how can we convert them into PostGres. > > > > Can anyone give some valuable suggestions.. > > > Regards > > Joe. > > > > > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] "SELECT IN" Still Broken in 7.4b
Mike Winter <[EMAIL PROTECTED]> writes: > Basically, queries of the form SELECT FROM WHERE IN > () take forever for high numbers of rows in the IN clause. > We've done timing on 7.3 and 7.4b and there is no speed improvement on > these queries. > Does anyone know what the status of this bug is? Try it in CVS tip ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
