Re: [SQL] Identifying obsolete values
On Wed, 17 Oct 2001 17:17:44 METDST Haller Christoph wrote: > I use the second table to identify the actual resp. obsolete ones within the first >table. > > DELETE FROM advncd_tempreftime; > INSERT INTO advncd_tempreftime > SELECT timepoint,mid,lid,sid,MAX(entrancetime) FROM advncd_onfvalue > GROUP BY timepoint,mid,lid,sid ; > > SELECT o.sid,o.timepoint,o.lid,o.mid,o.value FROM advncd_onfvalue o > WHERE EXISTS > (SELECT * FROM advncd_tempreftime t WHERE > o.timepoint= t.timepointAND > o.mid = t.mid AND > o.lid = t.lid AND > o.sid = t.sid AND > o.entrancetime = t.entrancetime > ) ; Hi, It seems that a series of the operations can be unified. SELECT o.sid,o.timepoint,o.lid,o.mid,o.value FROM advncd_onfvalue as o WHERE EXISTS (SELECT t.timepoint, t.mid, t.lid, t.sid FROM advncd_onfvalue as t GROUP BY t.timepoint, t.mid, t.lid, t.sid HAVING o.timepoint= t.timepointAND o.mid = t.mid AND o.lid = t.lid AND o.sid = t.sid AND o.entrancetime = MAX(t.entrancetime) ) ; By the way, a mail server have been downed ? Regards, Masaru Sugawara ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] oid's in views.
Aasmund, Thank you for the clarification. Now that I know what you are doing, I went through exactly the same thing about a year ago ... which is how we discovered some additional problems with using OIDs in database design. I was trying to spare you the same dead end. > > If your problem is that you want to update VIEWs and aren't sure > what the > > PK for the view is, could you follow a standard like this: > > > > CREATE TABLE person (social_security CHAR(9), full_name TEXT); > > > > CREATE VIEW pers_view AS select social_security AS primkey, > > social_security, > > full_name); > > > > and know that you can always find the "primkey" field in the view > as one > > to use in where clauses for updates? This is more-or-less a correct approach. As it does not address the issue of different data types, let me tell you what I did: Each significant data table contains one column, the first column, called "usq", for "universal sequence". This usq field may or may not be the primary key for the table, but does have a unique index. The usq is populated by a single sequence "universal_sq" which is shared between tables, thus allowing all tables usq uniqueness between them. This strategy has allowed me to write a number of functions which are table-agnostic, needing only the usq to do their job (such as a function that creates modification hisotry). -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] cast
hi. I wanna to make a query on this table(postgres system catalog): Attribute | Type| Modifier --+---+-- relname | name | reltype | oid | relowner | integer | relam| oid | relpages | integer | reltuples| integer | . . ... . select relname from pg_class; How can I get a char or varchar cast for the name data type? because relname is a name data type and I want to get a char or varchar data type. bye . my english is no so good! ---(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] CREATE RULE ON UPDATE/DELETE
On Sat, 20 Oct 2001, Joel Burton wrote: > On Sat, 20 Oct 2001, Aasmund Midttun Godal wrote: > > > Can a rule see the where statement in a query which it has been > > triggered by? or is it simply ignored?? what happens? > > > > Looking over your question, I wanted to clarify the problem a bit, so: > (cleaned up example a bit from Aasmund) > drop view normal; > drop view dbl; > drop table raw; > > CREATE TABLE raw (id INT PRIMARY KEY, name TEXT ); > INSERT INTO raw VALUES(1, 'a'); > INSERT INTO raw VALUES(2, 'b'); > INSERT INTO raw VALUES(12, 'c'); > INSERT INTO raw VALUES(15, 'd'); > INSERT INTO raw VALUES(14, 'e'); > > > -- set up two views: "normal", a simple view, > -- and "dbl", which shows id * 2 > > -- create basic rules to allow update to both views > > CREATE VIEW normal AS SELECT * FROM raw; > > CREATE RULE normal_update AS ON UPDATE TO normal DO INSTEAD UPDATE raw SET > id = NEW.id, name = NEW.name WHERE OLD.id = id; > > CREATE VIEW dbl AS SELECT id * 2 as id, name FROM raw; > > CREATE RULE dbl_update AS ON UPDATE TO dbl DO INSTEAD UPDATE raw SET > id = NEW.id, name = NEW.name WHERE OLD.id = id; > The issue is that there are no IDs over 10 that have another ID that is > exactly their value, so the first update to "dbl" does nothing. > > The second time, w/o the ID>10 restriction, it finds 1(a), and double > that, 2(b), and adds 10; getting confused about which record to edit. > > Is this the best way to interpret this? Is this a bug? Don't think so. I think the rule doesn't make any sense. NEW.id and OLD.id are probably dbl values, so saying OLD.id=id (where id is raw.id since that's the update table) isn't correct. It probably should be OLD.id=id*2 (which seems to work for me, btw) It's editing a different row than the one that's being selected. ---(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] CREATE RULE ON UPDATE/DELETE
Yes, I agree perfectly... I never thought of that! I would really like it if some more info was added to the docs regarding info on rules and triggers. The section on update rules is quite good, but some more would never hurt. One point in the trigger vs rules section which at least to me is very important is the simple fact that you cannot have a trigger on a select... Ok I understand why - but it took some time... Thank you for answering my questions! regards, Aasmund. On Sun, 21 Oct 2001 12:47:41 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: > Joel Burton <[EMAIL PROTECTED]> writes: > > > Surely you'd need something like > > CREATE RULE dbl_update AS ON UPDATE TO dbl DO INSTEAD UPDATE raw SET > id = NEW.id / 2, name = NEW.name WHERE OLD.id = id * 2; > > (untested...) > > regards, tom lane Aasmund Midttun Godal [EMAIL PROTECTED] - http://www.godal.com/ +47 40 45 20 46 ---(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] CREATE RULE ON UPDATE/DELETE
Yes, I agree perfectly... I never thought of that! I would really like it if some more info was added to the docs regarding info on rules and triggers. The section on update rules is quite good, but some more would never hurt. One point in the trigger vs rules section which at least to me is very important is the simple fact that you cannot have a trigger on a select... Ok I understand why - but it took some time... Thank you for answering my questions! regards, Aasmund. On Sun, 21 Oct 2001 12:47:41 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: > Joel Burton <[EMAIL PROTECTED]> writes: > > > Surely you'd need something like > > CREATE RULE dbl_update AS ON UPDATE TO dbl DO INSTEAD UPDATE raw SET > id = NEW.id / 2, name = NEW.name WHERE OLD.id = id * 2; > > (untested...) > > regards, tom lane Aasmund Midttun Godal [EMAIL PROTECTED] - http://www.godal.com/ +47 40 45 20 46 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] CREATE RULE ON UPDATE/DELETE
Joel Burton <[EMAIL PROTECTED]> writes: > CREATE VIEW dbl AS SELECT id * 2 as id, name FROM raw; > CREATE RULE dbl_update AS ON UPDATE TO dbl DO INSTEAD UPDATE raw SET > id = NEW.id, name = NEW.name WHERE OLD.id = id; Surely you'd need something like CREATE RULE dbl_update AS ON UPDATE TO dbl DO INSTEAD UPDATE raw SET id = NEW.id / 2, name = NEW.name WHERE OLD.id = id * 2; (untested...) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] CREATE RULE ON UPDATE/DELETE
> Don't think so. I think the rule doesn't make any sense. > NEW.id and OLD.id are probably dbl values, so saying OLD.id=id (where id > is raw.id since that's the update table) isn't correct. It probably > should be OLD.id=id*2 (which seems to work for me, btw) It's editing > a different row than the one that's being selected. I forgot to mention in this that I needed to made an additional change in the rule to make the ids come out correct at the end :(. The update set id=NEW.id should be id=NEW.id/2 of course... Otherwise the +10 becomes a +20. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Index of a table is not used (in any case)
Hello PostgreSQl Users! PostSQL V 7.1.1: I have defined a table and the necessary indices. But the index is not used in every SELECT. (Therefore, the selects are *very* slow, due to seq scan on 20 million entries, which is a test setup up to now) The definitions can be seen in the annex. Does some body know the reason and how to circumvent the seq scan? Is the order of index creation relevant? I.e., should I create the indices before inserting entries or the other way around? Should a hashing index be used? (I tried this, but I got the known error "Out of overflow pages") (The docu on "create index" says : "Notes The Postgres query optimizer will consider using a btree index whenever an indexed attribute is involved in a comparison using one of: <, <=, =, >=, > The Postgres query optimizer will consider using an rtree index whenever an indexed attribute is involved in a comparison using one of: <<, &<, &>, >>, @, ~=, && The Postgres query optimizer will consider using a hash index whenever an indexed attribute is involved in a comparison using the = operator. " The table entry 'epoche' is used in two different indices. Should that be avoided? Any suggestions are welcome. Thank you in advance. Reiner -- Annex: == Table: -- \d wetter Table "wetter" Attribute | Type | Modifier ---+--+-- sensor_id | integer | not null epoche| timestamp with time zone | not null wert | real | not null Indices: wetter_epoche_idx, wetter_pkey \d wetter_epoche_idx Index "wetter_epoche_idx" Attribute | Type ---+-- epoche| timestamp with time zone btree \d wetter_pkey Index "wetter_pkey" Attribute | Type ---+-- sensor_id | integer epoche| timestamp with time zone unique btree (primary key) Select where index is used: explain select * from wetter order by epoche desc; NOTICE: QUERY PLAN: Index Scan Backward using wetter_epoche_idx on wetter (cost=0.00..3216018.59 rows=2034 width=16) EXPLAIN Select where the index is NOT used: === explain select * from wetter where epoche between '1970-01-01' and '1980-01-01' order by epoche asc; NOTICE: QUERY PLAN: Sort (cost=480705.74..480705.74 rows=203400 width=16) -> Seq Scan on wetter (cost=0.00..454852.00 rows=203400 width=16) EXPLAIN -- Mit freundlichen Gruessen / With best regards Reiner Dassing ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] pgsql embedded again!
hi. I still have the data type problem. I am working with the postgresql system catalog and I wanna get all relname of pg_class relation on a C variable. -> \d pg_class Attribute | Type| Modifier --+---+-- relname | name | reltype | oid | relowner | integer | relam| oid | . .. . ... .. C program. EXEC SQL BEGIN DECLARE SECTION; VARCHAR tabla[50];I also tried with char tabla[50]; .. . EXEC SQL END DECLARE SECTION; I used a cursor for results. EXEC SQL DECLARE T99 CURSOR FOR select relname from pg_class now I want to get the results of query... then: EXEC SQL OPEN T99; EXEC SQL FETCH IN T99 INTO :tabla; here is the problem, I can't to get on tabla variable results. I think that may be the data type . I don't know
Re: [SQL] cast
On Mon, 22 Oct 2001, Esteban Gutierrez Abarzua wrote: > > hi. > > I wanna to make a query on this table(postgres system catalog): > > Attribute | Type| Modifier > --+---+-- > relname | name | > reltype | oid | > relowner | integer | > relam| oid | > relpages | integer | > reltuples| integer | > . > . > ... > . > select relname from pg_class; > > How can I get a char or varchar cast for the name data type? > because relname is a name data type and I want to get a char or varchar > data type. In general, I think name and varchar() are pretty equivalent, but... select CAST(relname as varchar) AS relname from pg_class; should work. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] oid's in views.
On Mon, 22 Oct 2001, Josh Berkus wrote: > Each significant data table contains one column, the first column, > called "usq", for "universal sequence". This usq field may or may not > be the primary key for the table, but does have a unique index. The usq > is populated by a single sequence "universal_sq" which is shared between > tables, thus allowing all tables usq uniqueness between them. > > This strategy has allowed me to write a number of functions which are > table-agnostic, needing only the usq to do their job (such as a function > that creates modification hisotry). Josh -- Good example! I'll bet a lot of PG users may have never realized that you can use the same sequence across several tables. -- Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Independent Knowledge Management Consultant ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Index of a table is not used (in any case)
Reinier, For future notice, [SQL] is the correct list for this kind of inquiry. Please do not post it to [HACKERS]. And please don't cross-post ... it results in a lot of needless duplication of effort. > I have defined a table and the necessary indices. > Is the order of index creation relevant? I.e., should I create the > indices before inserting > entries or the other way around? Ummm ... not to be obvious, or anything, but did you VACCUUM ANALYZE after populating your table? There's also some special steps to take if you are regularly deleting large numbers of records. -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Index of a table is not used (in any case)
On Mon, 22 Oct 2001, Reiner Dassing wrote: > Hello PostgreSQl Users! > > PostSQL V 7.1.1: > > I have defined a table and the necessary indices. > But the index is not used in every SELECT. (Therefore, the selects are > *very* slow, due to seq scan on > 20 million entries, which is a test setup up to now) > > The definitions can be seen in the annex. > > Does some body know the reason and how to circumvent the seq scan? > > Is the order of index creation relevant? I.e., should I create the > indices before inserting > entries or the other way around? > Have you run a vacuum analyze to update the statistics after the data was loaded? ---(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] oid's in views.
That is what i did... Regards, Aasmund On Sat, 20 Oct 2001 23:34:44 -0400 (EDT), Joel Burton <[EMAIL PROTECTED]> wrote: > On Sun, 21 Oct 2001, Aasmund Midttun Godal wrote: > > > > Aasmund -- > > If your problem is that you want to update VIEWs and aren't sure what the > PK for the view is, could you follow a standard like this: > > CREATE TABLE person (social_security CHAR(9), full_name TEXT); > > CREATE VIEW pers_view AS select social_security AS primkey, > social_security, > full_name); > > and know that you can always find the "primkey" field in the view as one > to use in where clauses for updates? > > HTH, > -- > > Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton > Independent Knowledge Management Consultant > Aasmund Midttun Godal [EMAIL PROTECTED] - http://www.godal.com/ +47 40 45 20 46 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
