Re: [SQL] please help
Yes but the option NOWAIT say to the instruction SELECT ... FOR UPDATE to not wait the unlock but to return the information the lines can't be lock. (Must retry late). Peter Eisentraut wrote: [EMAIL PROTECTED]">Loïc Bourgeois writes: What is the equivalent of the oracle request: SELECT ... FOR UPDATENOWAIT, under PostGreSQL I don't know Oracle, but there doesn't seem to be such a command inPostgreSQL. If the table is already locked, the SELECT FOR UPDATE has towait.
Re: [SQL] Index on View ?
From: "Keith Gray" <[EMAIL PROTECTED]> > Richard Huxton wrote: > > > > Indexes on underlying tables should be used though. Difficult to suggest > > what indices you might need without knowing the view/tables/queries > > involved. > > As an example I may have an "Invoice" table with several thousand invoices. > I could design a query/view "Aged" to get all unpaid invoices > greater than 15 days old. > > I would often look for Invoices per Client and should have an index on > Invoice(ClientID). > > e.g. CREATE INDEX Invoice_ClientID ON Invoice(ClientID); OK - makes sense. > > Is there any advantage in having an index on ClientID for the Aged query? > > e.g. CREATE INDEX Aged_ClientID ON Aged(ClientID); > > Would this index be continually maintained by the RDBMS or only on lookup? You can't do this at all I'm afraid. You can only index actual data. In this case you already have an index on clientID so you're covered. For your 15-day query, if it looks something like: SELECT * FROM invoices WHERE status='UNPAID' AND inv_date < CURRENT_DATE-'15 days' you might want indexes on inv_date and status. This doesn't mean that they will definitely be used though - it depends on how many records you have and how many the query returns. It *is* possible to define an index on a function, so you could in theory write a quite_old(inv_date) function and index that, but I doubt it makes much sense in your case. - Richard Huxton ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] how to do this join ?
Hello there I have another SQL question. Please see the example : select *, (select a_typ from auftrag where a_nr=z_a_nr) as typ, (select a_t_definition_d from auftrags_typ where a_t_code=typ) as text from zylinder I have three tables that I need data from. I'd like to use the to temporary store the kind of auftrag and then use it to get the definition (clear text) from another table. The query returns that typ is not known . How can I do it ? Thanks ... jr PFISTER + PARTNER, SYSTEM - ENGINEERING AG Juerg Rietmann Grundstrasse 22a 6343 Rotkreuz Switzerland phone: +4141 790 4040 fax: +4141 790 2545 mobile: +4179 211 0315 ---(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] how to do this join ?
[EMAIL PROTECTED] writes: > Hello there > > I have another SQL question. Please see the example : > > select *, > (select a_typ from auftrag where a_nr=z_a_nr) as typ, > (select a_t_definition_d from auftrags_typ where a_t_code=typ) as text > from zylinder select zylinder.*, auftrag.a_typ (select a_t_definition_d from auftrags_typ where a_t_code = auftrag.a_typ) from zylinder, auftrag where auftrag.a_nr = zylinder.z_a_nr; or, using 7.1, maybe something like select zylinder.*, auftrag.a_typ, auftrags_typ.a_t_definition from (zylinder join auftrag on a_nr = z_a_nr) left join auftrags_typ on a_t_code = a_typ Other variations are possible, depending on the referential contraints you have between the tables. > > I have three tables that I need data from. I'd like to use the to > temporary store the kind of auftrag and then use it to get the > definition (clear text) from another table. > > The query returns that typ is not known . -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] how to do this join ?
[EMAIL PROTECTED] writes: > select *, > (select a_typ from auftrag where a_nr=z_a_nr) as typ, > (select a_t_definition_d from auftrags_typ where a_t_code=typ) as text > from zylinder Seems like a very non-SQLish way to proceed. Instead use joins: select zylinder.*, a_typ as typ, a_t_definition_d as text from zylinder, auftrag, auftrags_typ where a_nr = z_a_nr and a_t_code = a_typ If there were multiple matches in auftrag or auftrags_typ then this would yield multiple rows per zylinder row, which you might not want; but your subselect-based approach is already assuming there are not multiple matches. If there's a possibility of *no* matching row, then the first solution would emit NULLs for the missing auftrag and auftrag_typ values, whereas the second would emit nothing at all for that zylinder row. If that's not what you want, you need to use outer joins (new in 7.1): select zylinder.*, a_typ as typ, a_t_definition_d as text from (zylinder left join auftrag on (a_nr = z_a_nr)) left join auftrags_typ on (a_t_code = a_typ); regards, tom lane ---(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] Does pg_dump stable on v7.0
When I dump out my whole db with pg_dump -x dbname > dbname.out
then when I reload them, one of creation always failed
msg is relation 'urlinfo' is not exist,
so I dump out scheme first -- pg_dump -x -a dbname > dbname.out.s
reload them , samething happend, so I cut & paste the definition of
this table, it's OK. then load data is OK.
but how come??
foollowing is this table:
CREATE TABLE "urlinfo" (
"url" text NOT NULL,
"id" int4 NOT NULL,
"ratedby" character varying(32),
"ratedon" timestamp DEFAULT "timestamp"('now'::text),
"comments" text,
"list" int2,
"pidwsr" int4,
CONSTRAINT "host_ck" CHECK ((urlpart('host'::text,
(url)::"varchar") <> '*.com'::text) AND (urlpart('host'::text,
(url)::"varchar") <> '*.net'::text)) AND (urlpart('host'::text,
(url)::"varchar") <> '*.gov'::text)) AND (urlpart('host'::text,
(url)::"varchar") <> '*.*'::text)) AND (urlpart('host'::text,
(url)::"varchar") <> '*'::text))),
PRIMARY KEY ("id")
);
Jie LIANG
St. Bernard Software
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
[EMAIL PROTECTED]
www.stbernard.com
www.ipinc.com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] "row_count" reserved?
Tom, Stephan, Bruce, All: I just upgraded to 7.1 RC2. Two of my functions in the current development database used a variable called "row_count", which had no problems in 7.1 beta 3. They now bomb when called from PHP4, but not when run from PSQL. Huh? Changing the variable names fixed the problem, but I'd love to know what's going on here. -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 4: Don't 'kill -9' the postmaster
Re: [SQL] "row_count" reserved?
"Josh Berkus" <[EMAIL PROTECTED]> writes: > I just upgraded to 7.1 RC2. Two of my functions in the current > development database used a variable called "row_count", which had no > problems in 7.1 beta 3. They now bomb when called from PHP4, but not > when run from PSQL. Huh? row_count is a keyword in plpgsql ... not sure how long it's been a keyword ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Memory And Performance
> >The system that I'm developing, I have about 25000 (persons) x 8 >>(exams) >> x 15 (answers per exam) = 300 records to process and it is VERY SLOW. > >f you need to import large quantities of data, look at the copy >command, that tends to be faster. By way of example for the level of improvement COPY gives: a 300 row table ( 350Mb dump file -> 450Mb table ) can by loaded via copy in 7 minutes. To insert each row (say using a perl prog to read the file and DBD-Pg to insert, committing every 1 rows ) takes about 75minutes. I used a PII 266Mhz/192Mb and Postgresql 7.1b5 for these results. Postgresql 7.0.2 is slower ( 20-30% or so...), but should still display a similar level of improvement with copy. Good loading Mark ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
