Re: [SQL] Unique Constraint Based on Date Range
Andrew Milne wrote: ... create table rates ( effective_date AS timestamp, expiry_date AS timestamp, cost AS numeric (12,2), access_time AS integer (in minutes) ); So for a given cost, there may not be a record where the effective date of one record overlaps the expiry date of another (and vice versa). Example record set (effective date, expiry date, cost, access_time): 2003-01-01 | 2003-01-15 | 5.00 | 60 2003-01-15 | infinity | 5.00 | 120 2003-01-01 | infinity | 1.00 | 10 An attempt to insert another 5.00 rate effective now would fail, because a 5.00 rate exists that doesn't expire (i.e. the expiry date would have to be updated to the effective date of the new record minus 1 second). I can enforce this from the front end, but a db constraint would be great. I don't know that a CHECK constraint would allow you to do this. But, you could create a function to perform the check, and fire a trigger on INSERT or UPDATE to execute the function. For example, something like this might do the trick. CREATE FUNCTION "check_record" () RETURNS TRIGGER AS ' DECLARE result RECORD; BEGIN SELECT INTO result * FROM table_rates WHERE effective_date >= NEW.effective_date AND expiry_date <= NEW.expiry_date AND cost = NEW.cost; IF FOUND THEN RAISE EXCEPTION ''record overlaps with existing record''; END IF; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER "tg_check_record" BEFORE INSERT OR UPDATE ON table_rates FOR EACH ROW EXECUTE PROCEDURE "check_record" (); Kevin ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Simple concatenation in select query
Hello Everybpdy, The concatination in select query u can write the query like this. select c.status||' this '||' is '||' the '||' status ' from course_master as c; This works fine Regards Yogesh Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software
Re: [SQL] Reg: Firing Trigger when a particular column value get changed
Thilak babu wrote: I have a scnerio as to fire a trigger when i update a particular column in a table. Please do help me out in getting thro this. The trigger function can use logic to exclude cases where a particular column does not change. For example: CREATE FUNCTION "column_update" () RETURNS TRIGGER AS ' BEGIN IF ( NEW.column <> OLD.column ) THEN do-your-stuff-here; END IF; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER "tg_column_update" BEFORE UPDATE ON "table_name" FOR EACH ROW EXECUTE PROCEDURE "column_update" (); The trigger fires on every update, but the procedure doesn't do anything unless the particular column changes. I don't think a trigger can be defined to fire on anything more granular than a table operation. Kevin ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Cross joining table with itself
I've been stumped trying to solve this problem via SQL. I have a table ID,
member1, member2, member3. In any row, any member field may have a value
or be null. I want to tally up how many times each member appears in the
table as a whole. For example, if two entries were ('a', '', 'c') and
('b', 'c', ''), I'd like my final result to be:
a, 1
b, 1
c, 2
Is this feasible? Thank you for any help.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Cross joining table with itself
On Sun, Sep 21, 2003 at 14:38:23 +,
Bob Hutzel <[EMAIL PROTECTED]> wrote:
> I've been stumped trying to solve this problem via SQL. I have a table ID,
> member1, member2, member3. In any row, any member field may have a value
> or be null. I want to tally up how many times each member appears in the
> table as a whole. For example, if two entries were ('a', '', 'c') and
> ('b', 'c', ''), I'd like my final result to be:
>
> a, 1
> b, 1
> c, 2
>
> Is this feasible? Thank you for any help.
You can 'union all' selects of each of the three columns where the appropiate
member value is not null and use group by and count on the result.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] auto_increment
On Saturday 20 September 2003 10:23, you wrote: > On Sat, 2003-09-20 at 06:10, Muhyiddin A.M Hayat wrote: > > How to Create auto_increment field in PostreSQL. > > Can I create them using Trigger. > > Use the SERIAL datatype. See also the functions nextval(), currval() > and setval(). I believe it is better to EXPLICITLY declare a SEQUENCE and set the default value nextval() to the field. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Reg: Firing Trigger when a particular column value get changed
On Sunday 21 September 2003 02:38, you wrote: > Thilak babu wrote: > > I have a scnerio as to fire a trigger when i update a particular column > > in a table. Please do help me out in getting thro this. > > The trigger function can use logic to exclude cases where a particular > column does not change. For example: > > CREATE FUNCTION "column_update" () RETURNS TRIGGER AS ' >BEGIN > IF ( NEW.column <> OLD.column ) THEN >do-your-stuff-here; > END IF; >RETURN NEW; > END; ' LANGUAGE 'plpgsql'; this trigger will not execute "your-stuff-here" if NEW.column or OLD.column will be null. you need to add this case to the logic statement in the "IF" (NEW.column <> OLD.column) OR (NEW.column IS NULL <> OLD.column. IS NULL) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] selecting duplicate records
Hi I would like to know the following 1. How to select duplicate records only from a single table using a select query. Thanks in advance Regards naveen - Original Message - From: yogesh selukar To: [EMAIL PROTECTED] ; [EMAIL PROTECTED] Sent: Saturday, September 20, 2003 10:35 PM Subject: Re: [SQL] Simple concatenation in select query Hello Everybpdy, The concatination in select query u can write the query like this. select c.status||' this '||' is '||' the '||' status ' from course_master as c; This works fine Regards Yogesh Do you Yahoo!?Yahoo! SiteBuilder - Free, easy-to-use web site design software
