[SQL] drop view even with dependencies?
Is there a way to drop a VIEW in postgres without the need to drop all the dependencies? VIEW 3 | VIEW 2 | VIEW 1 In my case, VIEW 3 depends on VIEW 2, and VIEW 2 depends on VIEW 1. Is there a way to drop VIEW 3 without dropping VIEW 1 and 2? I tried CREATE OR REPLACE VIEW but replace will only work if they have the same number of fields. Also with tables, can I drop a table even if there are multiple views that depend on it? It's such a pain in the neck to have to drop all the dependencies and recreate them all over again. Any help would greatly appreciated. I am using Postgres 7.3.2 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] best way to auto-update a field when row is updated
I have the following table FIELD_A| FIELD_B | TSTAMP x y 2005-03-10 14:56:47.456431 TSTAMP = not null default now() What's the best way to always auto-update TSTAMP to it's default value whenever the row gets updated? (e.g. update TABLENAME set FIELD_A='zzz' where FIELD_A='x'; should automatically set TSTAMP to now) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Breakdown results by month
I have the ff table: id |total| effective|end_date john 6 01-01-200502-28-2005 john 8 03-01-200506-30-2005 How can I return: id |total| effective|end_date john 6 01-01-200501-31-2005 john 6 02-01-200502-28-2005 john 8 03-01-200503-31-2005 john 8 04-01-200504-30-2005 john 8 05-01-200505-31-2005 john 8 06-01-200506-30-2005 Any help would be appreciated. Thanks ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] sum but not grouped by?
I have the ff data: id | date | hours AAA07-01-2005 3 AAA07-02-2005 4 BBB07-01-2005 6 BBB07-02-2005 2 BBB07-03-2005 7 Would it be possible to get the ff: id | date | hours | id_total AAA07-01-2005 3 7 AAA07-02-2005 4 7 BBB07-01-2005 6 15 BBB07-02-2005 2 15 BBB07-03-2005 7 15 So it's like SUM OF, but not Grouped By? Is this possible at all? Thank you for any help. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] sum but not grouped by?
Is it possible at all to do this without any joins or subselect? On 8/5/05, Mischa Sandberg <[EMAIL PROTECTED]> wrote: > Quoting Henry Ortega <[EMAIL PROTECTED]>: > > > I have the ff data: > > > > id | date | hours > > AAA07-01-2005 3 > > AAA07-02-2005 4 > > BBB07-01-2005 6 > > BBB07-02-2005 2 > > BBB07-03-2005 7 > > > > Would it be possible to get the ff: > > > > id | date | hours | id_total > > AAA07-01-2005 3 7 > > AAA07-02-2005 4 7 > > BBB07-01-2005 6 15 > > BBB07-02-2005 2 15 > > BBB07-03-2005 7 15 > > > > So it's like SUM OF, but not Grouped By? Is this possible at all? > > Thank you for any help. > > You're really joining two sets: > > select FFDATA.id, FFDATA.date, FFDATA.hours, FFSUM.id_total > fromFFDATA > join (select id, sum(hours) as id_total > from FFDATA group by id > ) as FFSUM using(id) > > > > ---(end of broadcast)--- > TIP 1: 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 > ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] insert only if conditions are met?
Is there a way to insert a record only if a certain
condition is met?
Something like:
insert into employee values('lastname','firstname',8) where
(condition here.. select sum(ofsomething) from xx where sum(ofsomething)>0 )
Is this possible at all with just plain SQL?
Re: [SQL] insert only if conditions are met?
What I am trying to do is
* Insert a record for EMPLOYEE A to TABLE A
IF
the sum of the hours worked by EMPLOYEE A on TABLE A
is not equal to N
Is this possible?
On 8/31/05, Michael Fuhr <[EMAIL PROTECTED]> wrote:
On Wed, Aug 31, 2005 at 11:09:54AM -0400, Henry Ortega wrote:> Is there a way to insert a record only if a certain> condition is met?>> Something like:> insert into employee values('lastname','firstname',8) where
> (condition here.. select sum(ofsomething) from xx where sum(ofsomething)>0 )See the INSERT documentation; it mentions inserting values from a subquery.INSERT INTO employeeSELECT 'lastname', 'firstname', 8
WHERE ;--Michael Fuhr
Re: [SQL] insert only if conditions are met?
Ok. Here's TABLE A emp date hours type JSMITH 08-15-2005 5 WORK JSMITH 08-15-2005 3 WORK JSMITH 08-25-2005 6 WORK I want to insert the ff: 1.) JSMITH 08-15-2005 8 VAC 2.) DOE 08-16-2005 8 VAC #1 should fail because there is already 8 hours entered as being Worked on 08-15-2005 (same date). Any suggestions? On 8/31/05, Ragnar Hafstað <[EMAIL PROTECTED]> wrote: On Wed, 2005-08-31 at 11:49 -0400, Henry Ortega wrote:> What I am trying to do is> * Insert a record for EMPLOYEE A to TABLE A> IF> the sum of the hours worked by EMPLOYEE A on TABLE A> is not equal to N >> Is this possible?Sure, given a suitable schemaIt is not clear to me, if the hours worked areto be found in the same table you want to insertinto, or not.gnari
Re: [SQL] insert only if conditions are met?
Thanks for all your answers. Very helpful. What if after adding all those hours in one long transaction, I want to send a query to check the MONTHLY TOTAL HOURS (including those just entered) and if they exceed N number of hours, all those records added should *ROLLBACK*? BEGIN; insert.. insert. if sum(hours)>N then ROLLBACK END; Is that possible? Maybe with just plain SQL? (and one transaction) On 8/31/05, Philip Hallstrom <[EMAIL PROTECTED]> wrote: > On Wed, 2005-08-31 at 12:49 -0400, Henry Ortega wrote:>> Ok. Here's TABLE A>>>> empdate hours type>> JSMITH 08-15-2005 5 WORK>> JSMITH 08-15-2005 3 WORK>> JSMITH 08-25-2005 6 WORK>>>> I want to insert the ff:>> 1.) JSMITH08-15-20058VAC>> 2.) DOE08-16-20058VAC>>>> #1 should fail because there is already 8 hours entered as being >> Worked on 08-15-2005 (same date).>> sorry, did not notice the duplicates before my previous reply.>> you could do something like> insert into A select 'JSMITH','08-15-2005',8,'VAC' >where> 8 != (select sum(hours) FROM A>WHERE emp = 'JSMITH'>AND date = '8-15-2005');Wouldn't that fail if JSMITH had only worked 7 hours on 8-15? I'mguessing he'd still want it to fail since adding that 8 hours ov VAC wouldresult in a 15 hour day... so maybe something like? insert into A select 'JSMITH','08-15-2005',8,'VAC'WHERE8 >= 8 + (select sum(hours) FROM A WHERE emp = 'JSMITH' AND date = '8-15-2005');?
[SQL] Given 02-01-2006 to 02-28-2006, output all days.
Is there a real quick way to do a query that will show me all the dates given a startdate and an end date?Given: 02-01-2006 and 02-28-2006it should give me:02-01-200602-02-2006..02-27-2006 02-28-2006Can this be done by a built-in function perhaps?
Re: [SQL] Given 02-01-2006 to 02-28-2006, output all days.
I was able to find a suitable 7.3.2 plpgsql.so and now plpgsql works. (supposedly)I am trying out some really basic function creation such as this:create function dng2(start_date DATE) returns setof date as $$
declareaa date:=start_date;But I always get thisERROR: parser: parse error at or near "DATE" at character 33before I can even finish.Any idea why this happens?
On 2/17/06, Owen Jacobson <[EMAIL PROTECTED]> wrote:
That usually indicates that, for whatever reason, plpgsql.so is from a different version of PostgreSQL than the database server. If you installed PostgreSQL from source, make sure you configured the server to look in the same lib dir as its libs were installed to; if you've installed from package management of some kind (RPM?) make sure you have the same versions of all postgres-related packages.
You should also upgrade, if possible. 7.3 is effectively obsolete (37 releases old); there are a number of bugfixes and performance improvements in more recent versions.-Owen-Original Message-----
From: Henry Ortega [mailto:[EMAIL PROTECTED]]Sent: Friday, February 17, 2006 2:06 PMTo: Owen JacobsonSubject: Re: [SQL] Given 02-01-2006 to 02-28-2006, output all days.
This sounds good. I don't have plpgsql loaded though.I am trying to load plpgsql and it's giving me:ERROR: Load of file /usr/lib/pgsql/plpgsql.so failed: /usr/lib/pgsql/plpgsql.so: undefined symbol: xlateSqlType
createlang: language installation failedI have pgsql 7.3.2I am googling and can't seem to find the answer. Any help would be appreciated.On 2/17/06, Owen Jacobson <
[EMAIL PROTECTED]> wrote:Henry Ortega wrote:(question about set of all days between two dates)I don't know of a builtin way to do it off the top of my head, but it's a pretty simple function to write:
create function days (start date, finish date) returns setof date as $$declare curdate date;begin curdate := start; while (curdate <= finish) loopreturn next curdate;curdate := curdate + 1;
end loop; return;end;$$ language plpgsql;# select * from days ('2006-02-01', '2006-02-07');days2006-02-012006-02-022006-02-032006-02-042006-02-052006-02-06
2006-02-07(7 rows)---(end of broadcast)---TIP 5: don't forget to increase your free space map settings
[SQL] Best way to do this query..
I have the following:name effective tstamp rateJohn 01-01-2006 2005-12-07 13:39:07.614945 115.00John 01-16-2006 2006-01-07 13:39:07.614945 125.00 John 01-16-2006 2006-01-09 15:13:04.416935 1885.00 I want the output to be:name effective end_date rate John 01-01-2006 01-15-2006 115.00 John 01-16-2006 1885.00What is the best way to do this? This is on a huge table and what Ihave right now is quite slow. Any ideas?
Re: [SQL] Best way to do this query..
Yes the data does not change once it is logged.I am quite new to this whole thing, do you mind elaborating moreabout the OLAP data model you mentioned about?On 8/25/06, Richard Broersma Jr <[EMAIL PROTECTED]> wrote: I would assume that your data does not change after it is logged. If this is the case, maybe thispart of your data model would actually be a good canidate for an OLAP data model.If this is not the direction your are enterested in moving, you could also reduce your query processing time by limiting the data ranges for your existing query to something for reasonable.Regards,Richard Broersma Jr.
[SQL] Trigger on Insert to Update only newly inserted fields?
I have a On Insert Trigger that updates one of the columns in that same table.Is there a way for the trigger to run only for the newly inserted records? Instead of all records in the database?E.g.:ID Start_Date End_Date 001 08-01-2006 002 08-02-2006On Insert/Update, Update End_Date=now().I want that to run only on new records.or the updatedrecord. How can I do this?Thank you so much.
Re: [SQL] Trigger on Insert to Update only newly inserted fields?
Here's what I am doing:I have this table:employee payrate effective tstamp end_date (to be updated by trigger)jdoe 1000 04-01-2006 2006-03-10 13:39: 07.614945jdoe 1500 04-01-2006 2006-03-12 15:43:14.423325jdoe 1555 04-16-2006 2006-03-15 12:14:15.112444 peter 500 04-1-2006 2006-03-25 08:13:35.152166 peter 900 04-16-2006 2006-03-28 09:22:14.456221 After the trigger runs, I want to have this:employee payrate effective tstamp end_date (to be updated by trigger) jdoe 1000 04-01-2006 2006-03-10 13:39:07.614945 04-15-2006 jdoe 1500 04-01-2006 2006-03-12 15:43:14.423325 04-15-2006 jdoe 1555 04-16-2006 2006-03-15 12:14:15.112444 NULL peter 500 04-1-2006 2006-03-25 08:13:35.152166 04-15-2006 peter 900 04-16-2006 2006-03-28 09:22:14.456221 NULLThe reason some of the end_date is NULL is because it is the latest record intable for that particular employee.My Trigger: CREATE FUNCTION updated_end_date() RETURNS trigger AS 'BEGIN update table set end_date=(select effective-1 from table t2 where t2.employee=table.employee and t2.effective>table.effective order by t2.effective limit 1);RETURN NEW;END;' LANGUAGE 'plpgsql'; That updates ALL of the records in the table which takes so long.Should I be doing things like this? Or is the update query on my trigger function so wrong?On 8/28/06, Michael Fuhr <[EMAIL PROTECTED]> wrote: On Mon, Aug 28, 2006 at 10:02:32AM -0400, Henry Ortega wrote:> I have a On Insert Trigger that updates one of the columns in that same> table.>> Is there a way for the trigger to run only for the newly inserted records? > Instead of all records in the database?Row-level INSERT and UPDATE triggers run only for the rows beinginserted or updated. What are you doing that suggests otherwise?> E.g.:> ID Start_Date End_Date > 001 08-01-2006> 002 08-02-2006>> On Insert/Update, Update End_Date=now().> I want that to run only on new records.or the updated> record. How can I do this?Row-level BEFORE triggers can modify the row they're processing -- is that what you're looking for? Something like this?CREATE FUNCTION trigfunc() RETURNS trigger AS $$BEGINNEW.end_date := current_date;RETURN NEW;END;$$ LANGUAGE plpgsql;If that's not what you mean then please elaborate. --Michael Fuhr
[SQL] Fastest way to get max tstamp
This is my table: name | program | effective | tstamp | rate --+-+++-- jdoe | AAA | 2006-07-01 | 2006-07-16 23:42:13.809214 | 20 jdoe | BBB | 2006-07-01 | 2006-07-16 23:42:13.809214 | 20 jdoe | AAA | 2006-08-16 | 2006-08-25 11:56:50.380575 | 20 jdoe | BBB | 2006-08-16 | 2006-08-25 11:56:50.380575 | 20 jdoe | AAA | 2006-08-16 | 2006-08-25 11:57: 17.394854 | 20 jdoe | BBB | 2006-08-16 | 2006-08-25 11:57:17.394854 | 20 jdoe | AAA | 2006-09-01 | 2006-08-28 12:38:42.486513 | 20 jdoe | BBB | 2006-09-01 | 2006-08-28 12:38:42.486513 | 20 I want to get: name | program | effective | tstamp | rate --+-+++-- jdoe | AAA | 2006-07-01 | 2006-07-16 23:42: 13.809214 | 20 jdoe | BBB | 2006-07-01 | 2006-07-16 23:42:13.809214 | 20 jdoe | AAA | 2006-08-16 | 2006-08-25 11:57:17.394854 | 20 jdoe | BBB | 2006-08-16 | 2006-08-25 11:57:17.394854 | 20 jdoe | AAA | 2006-09-01 | 2006-08-28 12:38:42.486513 | 20 jdoe | BBB | 2006-09-01 | 2006-08-28 12:38:42.486513 | 20Basically, for effective='08-16-2006', it only gets the latest insertedrecord (using tstamp) for that effective date, which is 2006-08-25 11:57: 17.394854.So what is the quickest way to do this?I can always do:Select * from Table t where tstamp=(select max(tstamp) from Table t2 where t2.name=t.name and t2.effective=t.effective )but it takes so long since this is a huge table.Any suggestions?
[SQL] Make Trigger run after completion of ENTIRE transaction
This maybe more of a theoretical question, can you actually make a Trigger run
after completion of the entire transaction?
Here's what I have:
LOG
user | startdate | enddate
enddate is getting updated by a trigger (on insert or update).
I have the following transaction:
BEGIN;
Insert into LOG(user,startdate) values('jdoe','2006-08-13');
Insert into LOG(user,startdate) values('jdoe','2006-08-14');
Insert into LOG(user,startdate) values('jdoe','2006-08-15');
Insert into LOG(user,startdate) values('jdoe','2006-08-16');
Insert into LOG(user,startdate) values('jdoe','2006-08-17');
... another 20-30 more inserts..
COMMIT;
The trigger actually runs on each Insert and therefore slows down the
Insert quite a bit.
My question is, can you tell the trigger to run after Commit?
