Re: [SQL] Trigger calling a function HELP ME! (2)
--- Richard Huxton wrote: > On Wednesday 21 April 2004 16:16, abief_ag_-postgresql(AT)yahoo.com > wrote: > > > CREATE OR REPLACE FUNCTION public.imp_test_to_out_test(imp_test) > > RETURNS imp_test AS > > 'begin > >return $1; > > end;' > > LANGUAGE 'plpgsql' STABLE; > > > > CREATE OR REPLACE FUNCTION public.imp_test_trigger() > > RETURNS trigger AS > > 'begin > > return imp_test_to_out_test(new); > > end;' > > LANGUAGE 'plpgsql' STABLE; > > This is your problem. NEW is a special variable, and I don't think > you can > pass it into another function (other than as NEW.col1, NEW.col2, > NEW.col3 > etc). > > You can however use TG_NAME or TG_RELNAME to see what trigger/table > called > you. I find that's helpful. > -- > Richard Huxton > Archonet Ltd > > ---(end of > broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org One other issue, as I understand it, NEW (and OLD) is a variable of type record, so you should be able to pass it to a function and get it as a return value, but it seems that there is a bug reported for version 7.2.1 that inhibits the use of record as type to be passed to a function. any suggestion? = Riccardo G. Facchini ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Python connection
a) this is the wrong mailing list for your question b) obviously, it doesn't like the "," part (and possibly the "host" part but that remains to be seen), try separating by ":" or just space or whatever libpq docs say c) any particular reason you don't use the Python DB API ? Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Own opclass and LIKE problem again!
Hi! Thank you very much the answers for my previous 'Multi ordered select and indexing' question! I tried your suggestions, and those are working well. We found a problem when used '(-col2)' instead of 'col2 DESC'. This solution working as a functional index and in our experience when the planner evaluates the cost of using this functional index, it uses 0.5% of the table's size. Usually this estimate is bad, and the query is slow. Why is it working such? Preferably should I ask this on the HACKERS or PERFORMANCE list? But my main question how can I force the LIKE operator for using my own operator class. I can create own LIKE operator, but it won't use my reverse order operator class (and its indexes). How can I exchange the standard LIKE operator with my own, which use my special reverse order indexes? For examle: (~~ means LIKE) col ~~ 'asd%' working as ((col >= 'asd'::text) AND (col < 'ase'::text)) I'd like to see the next: col /~~ 'asd%'working as ((col />= 'asd'::text) AND (col /< 'ase'::text)) Can somebody help us? Thanks in advance. Antal Attila ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] SQL Query Timeouts
I have a problem with a select statement that I am using in a web search engine. I have the following SQL: SELECT da_records.TITLE_EN AS TITLE, da_records.AUTHOR_EN AS AUTHOR, da_records.DESCRIPTION_EN AS DESCRIPTION, da_records.PUBLISHER_EN AS PUBLISHER, da_records.URL_EN AS URL, da_records.RECORD_ID, da_records.KEYWORD_LIST_ID, da_records.LANGUAGE, da_records.CONTRIBUTOR_NAME, da_records.CONTRIBUTOR_EMAIL, da_records.CONTRIBUTOR_ORGANISATION, da_records.CONTRIBUTOR_CREDIT, da_records.DEWEY_LIST_ID, da_records.LISTING_PRIORITY, da_records.SUBMITTED_DATE, da_records.LAST_EDIT_DATE, da_records.STATUS FROM da_records, lu_dewey, da_dewey_list WHERE da_records.RECORD_ID = da_dewey_list.RECORD_ID AND lu_dewey.DEWEY_ID = da_dewey_list.DEWEY_ID AND lu_dewey.DEWEY_LANG = 'en' AND lu_dewey.DEWEY_TYPE = 't' AND da_records.DESCRIPTION_EN like '%nasty%' OR da_records.TITLE_EN like '%nasty%' "nasty" is obviously the search term and if I search for a word which is unlikely to be found, the query returns 0 results in a fairly short time, as would be expected. However, if I search for a common word, the query times out (process gets killed if running it from the pgsql commandline). Its as if the query is returning a resultset which is too big for a buffer or something. I'm afraid I don't know Postgres that well yet so I can't tell you how big the DBs are but in MySQL they were under 10MB total size, so even a complete resultset shouldn't be too much for the machine. Incidentally the hardware I'm running on is: Dual 2GHz Opteron, 2GB RAM, SCSI. Hardly a slow system! Any pointers at either, more efficiend SQL or ways to tweak Postgres will be gladly recieved. Many thanks in Advance -- Dan Field <[EMAIL PROTECTED]> - Support Programmer: Cymru ar y we cy_GB: http://www.cymruarywe.org en_GB: http://www.walesontheweb.org
Re: [SQL] Join issue on a maximum value
On Wed, Apr 21, 2004 at 14:29:34 -0400, Heflin wrote: > > SELECT auction.auction_id, image.image_id, image.image_descr FROM > auction JOIN image ON auction.auction_id = image.auction_id WHERE > auction.auction_owner = 'Mabel'; > In my opinion there are 2 problem: how can you make the query and how many rows is in the result (performace)? Usually when you have more rows in the result you can use the LIMIT and OFFSET. So you can reach the result to unfold more pages. So I bult in these LIMIT and OFFSET into the queries. 2 new possibilities: SELECT IDSEL.*, (SELECT image_descr FROM image WHERE IDSEL.image_id=image.image_id) FROM ( SELECT auction.auction_id, max(image.image_id) AS image_id FROM auction JOIN image USING (auction_id) WHERE auction_owner = 'Mabel' GROUP BY auction.auction_id ORDER BY auction.auction_id LIMIT 10 OFFSET 0 ) AS IDSEL; or SELECT DISTINCT ON (image.auction_id) image.auction_id, image.image_id, image.image_descr FROM auction JOIN image USING (auction_id) WHERE auction.auction_owner = 'Mabel' ORDER BY image.auction_id, (-image.image_id) LIMIT 10 OFFSET 0; Index suggestions: CREATE INDEX auction_auction_owner on auction(auction_owner); CREATE INDEX image_auction_id on image(auction_id); CREATE INDEX image_auction_id_neg_image_id on image(auction_id, (-image_id)); -- Specially for the second solution The second solution build on Bruno Wolff III's ideas: http://archives.postgresql.org/pgsql-sql/2004-04/msg00211.php and http://archives.postgresql.org/pgsql-sql/2004-04/msg00262.php . You can see more solutions for your problem. You have to select the best performance solution for your specific databse. Use the EXPLAIN! Regards, Antal Attila ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Own opclass and LIKE problem again!
Hi! Thank you very much the answers for my previous 'Multi ordered select and indexing' question! I tried your suggestions, and those are working well. We found a problem when used '(-col2)' instead of 'col2 DESC'. This solution working as a functional index and in our experience when the planner evaluates the cost of using this functional index, it uses 0.5% of the table's size. Usually this estimate is bad, and the query is slow. Why is it working such? Preferably should I ask this on the HACKERS or PERFORMANCE list? But my main question how can I force the LIKE operator for using my own operator class. I can create own LIKE operator, but it won't use my reverse order operator class (and its indexes). How can I exchange the standard LIKE operator with my own, which use my special reverse order indexes? For examle: (~~ means LIKE) col ~~ 'asd%' working as ((col >= 'asd'::text) AND (col < 'ase'::text)) I'd like to see the next: col /~~ 'asd%'working as ((col />= 'asd'::text) AND (col /< 'ase'::text)) Can somebody help us? Thanks in advance. Antal Attila ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Postgres DB
Hi All, Our application is using the Postgres 7.3.4-RH database that is packaged with the AS3.0 for CCM. There is a variable type called refcursor that is being used by CAR Functions in Postgres database. This variable works fine when we execute the postgres Function from the database i.e through psql. When we try to execute the Function from java code, it uses pg73jdbc3.jar which is bundled with the Postgres 7.3.4, and pg73jdbc3.jar does not support this variable type refcursor. It works fine with pg74jdbc3.jar which comes along with Postgres 7.4. Is this a bug? Regards, Sumita ---(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
[SQL] Postgres backend to backup system
Hello, I don't know if this is the forum for this but here goes. I am interested in using Postgres as the backend to a backup system. Does anyone have any experiences or ideas on this? I have a project in mind to develop a web based backup system using PHP that stores backup information in Postgres. This is the typical types of information that I feel need to be stored in postgres: Tape ID Location of tape in autoloader magazine Directory file sizes Total Archives on tape Total bytes in archive Archive location of a file or directory on a tape Total bytes on tape Date archive was written to tape Server associated with an archive Absolute path to file or directory on tape My log files are generated by using the "v" option of the "tar" command. These create daily log files that are 6-8 mb that list every file that is backed up. This comes out to 75,000 lines per day. If you had an autoloader that you cycled through with 10 tapes for example, that could contain 750,000 entries. My system backups up anything that can run rsync. For me right now that is Linux servers, Novell servers, MAC running OSX, and Windows servers running rsync. Because there are many types of servers the database should be able to store which server, archive number a file or directory is in. If you were to search in the database for a file or directory, it would return a list that gave you the tape(s), date(s), archive(s) number on tape, etc. Any suggestions would be appreciated. -- Kent L. Nasveschuk <[EMAIL PROTECTED]> ---(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
[SQL] Multi ordered select and indexing
Hi! What is the simplest solution for this query type: SELECT * FROM tablename ORDER BY col1 ASC, col2 DESC; In our experience, postgres cannot use a multi-colum index on (col1, col2) in this situation. Is custom operator class the easiest solution, which can solve the reverse indexing on col2? Our problem with this solution, is that we have to replace "DESC" with "USING myoperator". Is it possible, that postgres can recognize "myoperator" without replacing "DESC"? We made new operators on int4 type starting with letter "/": CREATE OPERATOR CLASS int4_reverse_order_ops FOR TYPE int4 USING btree AS OPERATOR1 /< , OPERATOR2 /<= , OPERATOR3 /= , OPERATOR4 />= , OPERATOR5 /> , FUNCTION1 int4_reverse_order_cmp(int4, int4); Create an index: CREATE INDEX idx_test ON tablename (col1, col2 int4_reverse_order_ops); Postgres use this index in this query: EXPLAIN SELECT * FROM tablename ORDER BY col1 ASC, col2 USING /< limit 10; QUERY PLAN Limit (cost=0.00..0.52 rows=10 width=8) -> Index Scan using idx_test on tablename (cost=0.00..52.00 rows=1000 width=8) Another problem: we have to replace the operators in WHERE conditions, if that contains condition on col2. EXPLAIN SELECT * FROM tablename WHERE col1 < 10 and col2 < 10 ORDER BY col1, col2 using /< limit 10; QUERY PLAN --- Limit (cost=0.00..4.14 rows=10 width=8) -> Index Scan using idx_test on tablename (cost=0.00..46.33 rows=112 width=8) Index Cond: (col1 < 10) Filter: (col2 < 10) You can see, it use filtering on col2, but in the next case it can indexing on col2 condition: EXPLAIN SELECT * FROM tablename WHERE col1 < 10 and col2 /< 10 ORDER BY col1, col2 using /< limit 10; QUERY PLAN --- Limit (cost=0.00..3.82 rows=10 width=8) -> Index Scan using idx_test on tablename (cost=0.00..42.78 rows=112 width=8) Index Cond: ((col1 < 10) AND (col2 /< 10)) Can we do this easier? If can, how? After that, we have an other unsolved problem, if the col2's type is TEXT, and we try to use a LIKE operator on it. We coludn't replace the LIKE with own operator, because postgres exchange the "LIKE" with an expression which contains ">=" and "<". We made own like operator: "/~~", but we cannot tell postgres to use our own "/>=" and '/<' operators instead of "/~~". CREATE OPERATOR /~~ ( leftarg = text, rightarg = text, procedure = textlike, commutator = /~~ , negator = !~~ , restrict = scalarltsel, join = scalarltjoinsel ); Thanks in advance. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] transaction
Hi, You can achieve this by: 1. Create a new table 2. Insert the data in this. 3. Write a trigger on this table 4. In trigger issue UPDATE and check whether it updated any records. If NO, fire INSERT. ( here, i am updating first and inserting.. just reverse ) The code looks like: update tempxitag set qty = qty + nqty where ccod = cccod GET DIAGNOSTICS nFound = ROW_COUNT; If nFound = 0 then insert into tempxitag( ccod, qty) values (cccod, nqty ); End if; HTH Denis - Original Message - From: Bruno Wolff III <[EMAIL PROTECTED]> To: H.J. Sanders <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, April 21, 2004 7:30 AM Subject: Re: [SQL] transaction > On Tue, Apr 20, 2004 at 21:14:48 +0200, > "H.J. Sanders" <[EMAIL PROTECTED]> wrote: > > > > Hello list. > > > > We are migrating from Informix to PSQL. > > > > In Informix we used to do: > > > > - BEGIN WORK > > > > - INSERT ROW > > > > - IF FAILED THEN UPDATE ROW > > > > - COMMIT WORK > > > > > > In PSQL this does not seem to work because after the first error (the > > insert) > > everything is omitted. > > > > Has someone found a solution for this (we have 1000's of this type). > > This question has come up a few times over the last year and there isn't > a great answer. Locking the table is a simple solution, but can lead to > problems because of contention. > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Syntax for cmd to EXEC...how many quotes?
Try (to solve string terminating error ): sql_string := ''INSERT INTO temp_table ( view_name, row_count ) SELECT '' || r_rec.viewname || '', count(*) FROM '' || r_rec.viewname || '' ; '' ; BUT, you will be needing to put view_name in Quote too... try it yourself... HTH Denis - Original Message - From: David B <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, April 21, 2004 4:54 AM Subject: [SQL] Syntax for cmd to EXEC...how many quotes? > Folks, > > This is driving me crazy...I'm sure it's possible but that I am getting the > #quotes wrong in some way... > I keep getting unterminated string errors...now matter how many quotes I > use. > > I have a FN that I want to loop through all views and populate a table with > a count(*) from each views. > > To do it I'm doing a LOOP around all views...something like: > > FOR r_rec IN SELECT viewname from pg_views > LOOP > > sql_string := 'INSERT INTO temp_table ( view_name, row_count ) SELECT > ''' || r_rec.viewname || ''', count(*) FROM ' || r_rec.viewname || ' ; ' ; > > EXEC sql_string ; > > END LOOP ; > > END ; > > > Building that sql_string is the problem. > Any thoughts folks? > > -D > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.648 / Virus Database: 415 - Release Date: 3/31/2004 > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Order by YYYY MM DD in reverse chrono order trouble
Hi,
If you want to SORT descending considering multiple column, you need to
spefify DESC after each column.
Default is ASC.
So, your present sorting is ASC, ASC and DESC
You can specify
1 DESC, 2 DESC, 3 DESC
HTH
Denis
- Original Message -
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, April 21, 2004 4:00 PM
Subject: [SQL] Order by MM DD in reverse chrono order trouble
> Hello,
>
> I am trying to select distinct dates and order them in the reverse
> chronological order. Although the column type is TIMESTAMP, in this
> case I want only , MM, and DD back.
>
> I am using the following query, but it's not returning dates back in
> the reverse chronological order:
>
> SELECT DISTINCT
> date_part('year', uu.add_date), date_part('month', uu.add_date),
> date_part('day', uu.add_date)
>
> FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON
> ui.id=uu.user_id
> WHERE uus.x_id=1
>
> ORDER BY
> date_part('year', uu.add_date), date_part('month', uu.add_date),
> date_part('day', uu.add_date) DESC;
>
>
> This is what the above query returns:
>
> date_part | date_part | date_part
> ---+---+---
> 2004 | 2 | 6
> 2004 | 4 |20
> (2 rows)
>
>
> I am trying to get back something like this:
> 2004 4 20
> 2004 4 19
> 2004 2 6
> ...
>
> My query is obviously wrong, but I can't see the mistake. I was
> wondering if anyone else can see it. Just changing DESC to ASC, did
> not work.
>
> Thank you!
> Otis
>
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Can someone tell me why this statement is failing?
Hi..
Your END_TIME_MINUTES condition fails..
1082377320 <= 1082375100
HTH.
Denis
- Original Message -
From: P A <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, April 20, 2004 2:01 AM
Subject: [SQL] Can someone tell me why this statement is failing?
> Can anyone tell me why this SQL statement is not
> matching with the row from the table below? I think
> I'm going mad!
>
> #
> SQL Statement
> #
>
> SELECT * FROM t_bell_schedule WHERE calendar_day =
> '2004-04-12' AND start_time_minutes >= '1082374200'
> AND end_time_minutes <= '1082375100';
>
> #
> Current DB Data to match
> #
>
> calendar_day | period_letter | start_time_minutes |
> end_time_minutes
> --+---++--
> 2004-04-12 | B | 1082374440 |
> 1082377320
>
> #
> DB Structure
> #
>
>Column |Type |
>Modifiers
> +-+---
-
> bell_schedule_uid | integer |
> not null default
> nextval('public.t_bell_schedule_bell_schedule_uid_seq'::text)
> calendar_day | date|
> period_letter | character varying(4)|
> period | character varying(4)|
> start_time | time without time zone |
> end_time | time without time zone |
> total_minutes | integer |
> activestatus | integer |
> datecreated| timestamp without time zone |
> datemodified | timestamp without time zone |
> start_time_minutes | integer |
> end_time_minutes | integer |
>
>
> Cheers,
> Pete
>
>
>
>
>
> __
> Do you Yahoo!?
> Yahoo! Photos: High-quality 4x6 digital prints for 25¢
> http://photos.yahoo.com/ph/print_splash
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
---(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
[SQL] Cascade delete question
Hi All,
I am using a cascade delete on the following table:
vidvnameparentname
1 [EMAIL PROTECTED]
2 [EMAIL PROTECTED] [EMAIL PROTECTED]
3 [EMAIL PROTECTED] [EMAIL PROTECTED]
4 [EMAIL PROTECTED] [EMAIL PROTECTED]
5 [EMAIL PROTECTED] [EMAIL PROTECTED]
So the delete cascade states if I am deleting a row whose vname matches
the parentname delete those rows and it works fine.
I just changed the values of parentname (but the vname values stay the same)
vidvnameparentname
1 [EMAIL PROTECTED]
2 [EMAIL PROTECTED] n1
3 [EMAIL PROTECTED] n1
4 [EMAIL PROTECTED] n1
5 [EMAIL PROTECTED] n3
Is there a way to do the same cascade delete with these values? I can
select the correct info from vname in a query:
select substring(vname, from 0 for position('@' in vname)) from table1;
This works, so I tried to put that in the cascade but it failed.
Is there any way to accomplish this?
Thanks
Glenn MacGregor
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Join issue on a maximum value
On Wed, Apr 21, 2004 at 02:29:34PM -0400, Heflin wrote: > OK, it's been a while since I've had to do anything remotely complex in > SQL, so this may just be a pure brain block on my part. > ... > > So a basic JOIN gets this: > > SELECT auction.auction_id, image.image_id, image.image_descr > FROM auction JOIN image ON auction.auction_id = image.auction_id > WHERE auction.auction_owner = 'Mabel'; > > auction_id | image_id | image_descr > +--+- > 1 |1 | image 1 > 1 |2 | image 2 > 2 |3 | image 3 > 3 |4 | image 4 > 3 |5 | image 5 > 3 |7 | image 8 > (6 rows) > > Now the problem: I can't seem to remember how to get only the max value > for the image_id for each auction_id so that the result set would be: > > auction_id | image_id | image_descr > +--+- > 1 |2 | image 2 > 2 |3 | image 3 > 3 |7 | image 8 > > Playing with the max() aggregate seems to be the correct path, but for > the life of me I can't seem to get the syntax to the point that it > produces what I need. Any help would be greatly appreciated! A simple way to write this is to use DISTINCT ON combined with ORDER BY. In this case, these clauses can substitute for the MAX aggregate: select distinct on (auction.auction_id) auction.auction_id, image.image_id, image.image_descr from auction join image using (auction_id) where auction.auction_owner = 'Mabel' order by auction.auction_id, image.image_id desc; (I haven't tested this.) I think you need a subselect in there if you want to use the MAX aggregate. - Jeremy ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Which SQL command creates ExclusiveLock?
Hi everyone! I have a web application that uses Postgresql on backend. The application performs selects, updates, inserts, and deletes by using Hibernate. Tables contain indexed fields. When I run the following query, SELECT * FROM pg_locks, it shows that some transactions place ExclusiveLock: relation | database | transaction | pid | mode | granted--+--+-+---+-+- 16757 | 16976 | | 22770 | AccessShareLock | t | | 17965163 | 22770 | ExclusiveLock | t According to postgres documentation, all update operations place ROW EXCLUSIVE MODE locks, and EXCLUSIVE MODE is not automatically acquired by any postgres SQL command. So, which command places ExclusiveLock??? I suspect that indexes can account for this behaviour, but couldn't find anything in the docs. I am also wondering why there is nothing shown in "relation" column. I think it is supposed to display a table or index id or any other object that is being locked. Thanks for help! Dennis
[SQL] Trigger calling a function HELP ME! (2)
Sorry. I realize I slipped an error in my code: the code is: --- CREATE TABLE public.imp_test ( id int8, value text ) WITHOUT OIDS; CREATE OR REPLACE FUNCTION public.imp_test_to_out_test(imp_test) RETURNS imp_test AS 'begin return $1; end;' LANGUAGE 'plpgsql' STABLE; CREATE OR REPLACE FUNCTION public.imp_test_trigger() RETURNS trigger AS 'begin return imp_test_to_out_test(new); end;' LANGUAGE 'plpgsql' STABLE; CREATE TRIGGER imp_test_trigger_001 BEFORE INSERT OR UPDATE ON public.imp_test FOR EACH ROW EXECUTE PROCEDURE public.imp_test_trigger(); --- regards, = Riccardo G. Facchini ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] problem with slow select
hi I have two tables: table: idcode varchar(15) -->references (idcode) table latitude float longitude float time timestamp p_a char(1) barcode address idprog serial... -->primary key flag boolean table idfact numeric(7,0) --->references . idcode varchar(15) --->primary key name varchar(20) I want to exctract the last operation of table for each idcode of table where idfact=123 I HAVE THIS QUERY BUT IT IS TOO SLOW (10 SECONDS): select .*,.name from , join (select .idcode,max(.tempo) as tempo from , where .idfact=123 and .idcode=.idcode group by .idcode) temptable on (temptable.tempo=.tempo and temptable.idcode=.idcode) where .idfact=123 and .idcode=.idcode order by .name; PLEASE HELP ME -- francescosaf Posted via http://www.webservertalk.com View this thread: http://www.webservertalk.com/message189539.html ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Proper SQL syntax requested
Wondering if anyone can help me with the proper syntax for an ORDER BY clause with a subquery. What I have is a table column named make in one table. The make column contains a Serial ID of the name which is stored in an alternate table named sections. I am trying to be able to ORDER by on make, but as you can guess it is ORDERING by the ID rather than the actual Name. Select * from floors Where system = 1 ORDER by make This sorts by the Serial ID's in column make I would like to be able to sort by the actual Names associated back from the ID's. Anyway of doing this?? Thanks All for the Help! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] problem with slow select
On Wed, 2004-04-21 at 11:00, francescosaf wrote: > hi > > I have two tables: Please send results of EXPLAIN ANALYZE for the query in question. Thanks ---(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
[SQL] Logical comparison on Strings
Dear Friends, Postgres 7.3.2 on Linux 7. I want to compare to columns and get the logical result as follows. C1 is 'YNYNY' . C2 is 'NNYYY'. I want to compare like AND and OR operators. C1 AND C2 should give result like NNYNY. C1 OR C2 should give result like YNYYY. Please shed some light. Thanks Kumar
