Re: [SQL] SQL list table names
alviN wrote: is it possible to execute an sql query to be able to list the tables's names? well, you can do it on psql using \dt. but im talking about the SQL statement, because i want to execute that query from a script. Oh, even *I* know the answer to this one! Run psql with the -E argument to see the SQL used to run \dt. Look at man psql for for info for just: psql -E template1 Alan Gutierrez ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SQL list table names
On Wed, 8 Jan 2003, Alan Gutierrez wrote: alviN wrote: is it possible to execute an sql query to be able to list the tables's names? well, you can do it on psql using \dt. but im talking about the SQL statement, because i want to execute that query from a script. Oh, even *I* know the answer to this one! Run psql with the -E argument to see the SQL used to run \dt. Look at man psql for for info for just: psql -E template1 or SELECT tablename from pg_tables where tablename NOT LIKE 'pg%'; Alan Gutierrez ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] full join in view
I'm making a report in Crystal Reports, which makes use of full join. As Crystal Reports does not support full join natively, I created a view which contains the join and based my report on that view. The report has also a parameter to filter only subset of rows from view. My problem is, that when selecting from this view, optimizer never uses indexes of neither of two tables. I understand, that optimizing the filter to the bottom of query tree may not always give the same result with full join (although it should in my case). Alternative could be to use function returning a table, but I doubt I could use this function from Crystal Reports. What other options I have? Tambet ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] full join in view
Tambet Matiisen wrote: I'm making a report in Crystal Reports, which makes use of full join. As Crystal Reports does not support full join natively, I created a view which contains the join and based my report on that view. The report has also a parameter to filter only subset of rows from view. My problem is, that when selecting from this view, optimizer never uses indexes of neither of two tables. I understand, that optimizing the filter to the bottom of query tree may not always give the same result with full join (although it should in my case). Alternative could be to use function returning a table, but I doubt I could use this function from Crystal Reports. What other options I have? Tambet Can you add some sql examples - table index definition, view definition? If your view doesn't contain other views or sub-selects, postgres should use indexes. Tomasz Myrta ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [PERFORM] 7.3.1 index use / performance
On Tue, 7 Jan 2003, Tom Lane wrote: Rod Taylor [EMAIL PROTECTED] writes: I am wondering about a compiler bug, or some other peculiarity on your platform. Can anyone else using FreeBSD try the above experiment and see if they get different results from mine on 7.3.* (or CVS tip)? On FreeBSD 4.7 I received the exact same results as Tom using the statements shown by Tom. On looking at the code, I do see part of a possible mechanism for this behavior: cost_index calculates the estimated cost for qual-clause evaluation like this: This bizarre index decreased cost (when adding conditions) behaviour maybe was due to some vacuums. (i cant remember how many reloads and vacuums i did to the database in the period petween the two emails). However my linux machine with the same pgsql 7.3.1, with a full clean installation also gives the same symptoms: Choosing the slow index, and after some (random) vacuums choosing the right index, and then after some vacuums chooses the bad index again. regards, tom lane == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [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] Search and Replace
I apologize for having to resort to sending what is most likely a simple tech support question regarding PostgreSQL to this list but I have not been able to find the answer in the documentation. I am responsible for managing a database containing over 6,000 records of US Forest Service Research publications (http://www.srs.fs.usda.gov/pubs/search.jsp) and I need to do a simple search and replace in one of the columns. In these records we have a field for URLs of the location the research publications and I need to change the base URL from www.srs.fs.fed.us to www.srs.fs.usda.gov. It seems like this search and replace would be a simple thing to do with an UPDATE command but I am having great difficulty making this work. The table definition I am trying to update is: url. At first I thought about using a simple UPDATE command like this: UPDATE publications SET url = 'www.srs.fs.usda.gov' WHERE url = 'www.srs.fs.fed.us'; Of course that would work fine but www.srs.fs.fed.us is only part of a complete URL such as: http://www.srs.fs.fed.us/pubs/rp/rp_se263.pdf. My problem (among other things!) is that I don't know how to pass along wildcards so that I do not change the other parts of the complete URL. I have tried substituting like for = and trying to use the wildcard of % but to no avail. I am really just guessing here. Any help would be greatly appreciated! Best, --rdm === Randy D. McCracken (0 Web Guy //\ Communications GroupV_/_ USDA/FS - Southern Research Station E-Mail: [EMAIL PROTECTED] Voice: (828) 259-0518 Fax:(828) 257-4840 Web:http://www.srs.fs.fed.us/ === ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Search and Replace
On Wed, Jan 08, 2003 at 09:02:47 -0500, Randy D. McCracken [EMAIL PROTECTED] wrote: UPDATE publications SET url = 'www.srs.fs.usda.gov' WHERE url = 'www.srs.fs.fed.us'; For simple cases you can do this with available string functions. For more complicated cases, you can write a simple application that selects records (which means you will need a unique key returned) based on a regular expression and then the application will issue updates for the matching records. ---(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] to_date() confusion
I'm confused. How do I massage the integer 10102 into the date
2002-01-01?
cmi= select to_date('010102','MMDDYY');
to_date
2002-01-01
(1 row)
cmi= select to_char(10102,'00');
to_char
-
010102
(1 row)
cmi= select to_date(to_char(10102,'00'),'MMDDYY');
to_date
2010-01-10
(1 row)
TIA!
--
Richard Rowell [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
Re: [SQL] to_date() confusion
On Wed, Jan 08, 2003 at 08:49:00 -0600, Richard Rowell [EMAIL PROTECTED] wrote: I'm confused. How do I massage the integer 10102 into the date 2002-01-01? cmi= select to_char(10102,'00'); to_char - 010102 (1 row) Note the leading space in the returned row. That is probably what is making the difference. You probably want to use to_char(10102,'FM00'). ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Search and Replace
REPLACE is the right function for you avaliable as an addon. install it in postgresql installation using the source at: http://www.brasileiro.net:8080/postgres/cookbook/view-one-recipe.adp?recipe_id=23 if you face problem please lemme know once this function is installed you could update like: -- UPDATE publications SET url = replace( 'www.srs.fs.fed.us' , 'www.srs.fs.usda.gov' , url ) WHERE url ilike '%www.srs.fs.fed.us%'; -- regds mallah. On Wednesday 08 January 2003 07:32 pm, Randy D. McCracken wrote: I apologize for having to resort to sending what is most likely a simple tech support question regarding PostgreSQL to this list but I have not been able to find the answer in the documentation. I am responsible for managing a database containing over 6,000 records of US Forest Service Research publications (http://www.srs.fs.usda.gov/pubs/search.jsp) and I need to do a simple search and replace in one of the columns. In these records we have a field for URLs of the location the research publications and I need to change the base URL from www.srs.fs.fed.us to www.srs.fs.usda.gov. It seems like this search and replace would be a simple thing to do with an UPDATE command but I am having great difficulty making this work. The table definition I am trying to update is: url. At first I thought about using a simple UPDATE command like this: UPDATE publications SET url = 'www.srs.fs.usda.gov' WHERE url = 'www.srs.fs.fed.us'; Of course that would work fine but www.srs.fs.fed.us is only part of a complete URL such as: http://www.srs.fs.fed.us/pubs/rp/rp_se263.pdf. My problem (among other things!) is that I don't know how to pass along wildcards so that I do not change the other parts of the complete URL. I have tried substituting like for = and trying to use the wildcard of % but to no avail. I am really just guessing here. Any help would be greatly appreciated! Best, --rdm === Randy D. McCracken (0 Web Guy //\ Communications GroupV_/_ USDA/FS - Southern Research Station E-Mail: [EMAIL PROTECTED] Voice:(828) 259-0518 Fax: (828) 257-4840 Web: http://www.srs.fs.fed.us/ === ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] count(*) optimization
Hi I have trouble executing count(*) statement. On a large dataset it takes quite a while to return result. Is there any other way to get number of tupples in relation or is the only way tooptimize count() function to indexit? thanks Nikola
Re: [SQL] count(*) optimization
On Wed, Jan 08, 2003 at 17:04:40 +0100, Nikola Ivacic [EMAIL PROTECTED] wrote: Hi I have trouble executing count(*) statement. On a large dataset it takes quite a while to return result. Is there any other way to get number of tupples in relation or is the only way to optimize count() function to index it? Indexes won't help count. If you retrieve counts a lot more than you do updates that will change their value, then you may want to keep the counts in another table and use triggers to keep them updated. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] count(*) optimization
The problem is in other software I use, witch uses count(*) function to display relation properties pgAdmin II phpPgAdmin Nikola - Original Message - From: Bruno Wolff III [EMAIL PROTECTED] To: Nikola Ivacic [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, January 08, 2003 5:22 PM Subject: Re: [SQL] count(*) optimization On Wed, Jan 08, 2003 at 17:04:40 +0100, Nikola Ivacic [EMAIL PROTECTED] wrote: Hi I have trouble executing count(*) statement. On a large dataset it takes quite a while to return result. Is there any other way to get number of tupples in relation or is the only way to optimize count() function to index it? Indexes won't help count. If you retrieve counts a lot more than you do updates that will change their value, then you may want to keep the counts in another table and use triggers to keep them updated. ---(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] Search and Replace
On Wed, Jan 08, 2003 at 09:02:47AM -0500, Randy D. McCracken wrote: I apologize for having to resort to sending what is most likely a simple tech support question regarding PostgreSQL to this list but I have not been able to find the answer in the documentation. Hey, this is Open Source: that's what the mailing lists are for. The only concern would be is this the right list? I'd suggest that this should probably be over in NOVICE, but at least you didn't post to HACKERS ;-) snip description of needing a simple string replace As you've discovered, standard SQL text processing functions are a bit primitive - usually you break out to the application language for that sort of thing. However, if you know for sure that there's only one instance of the replace string, and it's a fixed length string, you can get away with something like this: test=# select * from pubs; id | url + 1 | http://www.srs.fs.fed.us/pub/1 2 | http://www.srs.fs.fed.us/pub/2 3 | http://www.srs.fs.fed.us/pub/3 (3 rows) test=# update pubs set url= substr(url,0,strpos(url,'www.srs.fs.fed.us'))||'www.srs.fs.usda.gov'||substr(url,strpos(url,'www.srs.fs.fed.us')+17); UPDATE 3 test=# select * from pubs; id | url +-- 1 | http://www.srs.fs.usda.gov/pub/1 2 | http://www.srs.fs.usda.gov/pub/2 3 | http://www.srs.fs.usda.gov/pub/3 (3 rows) You can figure out how it works by playing with SELECTing different substr() ans strpos() directly, like this excerpt from my query history: select strpos(url,'www.srs.fs.usda.gov') from pubs; select substr(url,strpos(url,'www.srs.fs.usda.gov')) from pubs; select substr(url,0,strpos(url,'www.srs.fs.usda.gov')) from pubs; select substr(url,1,strpos(url,'www.srs.fs.usda.gov')) from pubs; select substr(url,-1,strpos(url,'www.srs.fs.usda.gov')) from pubs; select substr(url,0,strpos(url,'www.srs.fs.usda.gov')) from pubs; select substr(url,strpos(url,'www.srs.fs.usda.gov')+19) from pubs; select substr(url,strpos(url,'www.srs.fs.usda.gov')+18) from pubs; select substr(url,strpos(url,'www.srs.fs.usda.gov')+19) from pubs; Hope this helps, Ross -- Ross Reedstrom, Ph.D. [EMAIL PROTECTED] Research Scientist phone: 713-348-6166 The Connexions Project http://cnx./rice.edu fax: 713-348-6182 Rice University MS-39 Houston, TX 77005 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] What benefits can I expect from schemas ?
Hi, I could not find much documentation about SQL 92 schemas that version 7.3 now supports. I understood it was a structure to group various objects, which allows faster privilege management, namespaces definition. Besides that, I don't see any advantages. I'd be glad if someone could point them out for me, or give doc links. Thanks. _ GRAND JEU SMS : Pour gagner un NOKIA 7650, envoyez le mot IF au 61321 (prix d'un SMS + 0.35 euro). Un SMS vous dira si vous avez gagné. Règlement : http://www.ifrance.com/_reloc/sign.sms ---(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] count(*) optimization
I am not sure pgAdmin uses count(*) and I didn't have time to check phpPgAdmin also, I presumed it from similarly slow response I get in psql Nikola - Original Message - From: Ian Harding [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, January 08, 2003 5:20 PM Subject: Re: [SQL] count(*) optimization Bummer. I seem to remember PGAccess using the numbers generated by ANALYZE to show the number of records. I noticed it was inaccurate until you ran a vaccum. I guess pgAdmin chose accuracy over speed in this case. Ian Harding Programmer/Analyst II Tacoma-Pierce County Health Department [EMAIL PROTECTED] (253) 798-3549 Nikola Ivacic [EMAIL PROTECTED] 01/08/03 08:15AM The problem is in other software I use, witch uses count(*) function to display relation properties pgAdmin II phpPgAdmin Nikola - Original Message - From: Bruno Wolff III [EMAIL PROTECTED] To: Nikola Ivacic [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, January 08, 2003 5:22 PM Subject: Re: [SQL] count(*) optimization On Wed, Jan 08, 2003 at 17:04:40 +0100, Nikola Ivacic [EMAIL PROTECTED] wrote: Hi I have trouble executing count(*) statement. On a large dataset it takes quite a while to return result. Is there any other way to get number of tupples in relation or is the only way to optimize count() function to index it? Indexes won't help count. If you retrieve counts a lot more than you do updates that will change their value, then you may want to keep the counts in another table and use triggers to keep them updated. ---(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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] What benefits can I expect from schemas ?
Bob Lapique wrote: Hi, I could not find much documentation about SQL 92 schemas that version 7.3 now supports. I understood it was a structure to group various objects, which allows faster privilege management, namespaces definition. Besides that, I don't see any advantages. I'd be glad if someone could point them out for me, or give doc links. That's pretty much it. You can give each user their own namespace so they can all create tables with the same name, or put applications in their own namespace so they don't conflict with other applications. You can also control object creation easier by restricting privs on the public namespace. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [PERFORM] 7.3.1 index use / performance
Just to close off the thread, here is the end-result of investigating
Achilleus Mantzios' problem.
--- Forwarded Message
Date:Wed, 08 Jan 2003 11:54:36 -0500
From:Tom Lane [EMAIL PROTECTED]
To: Achilleus Mantzios [EMAIL PROTECTED]
Subject: Re: [SQL] [PERFORM] 7.3.1 index use / performance
I believe I see what's going on. You have a number of silly outlier
values in the report_date column --- quite a few instances of '10007-06-09'
for example. Depending on whether ANALYZE's random sample happens to
include one of these, the histogram generated by ANALYZE might look like
this (it took about half a dozen tries with ANALYZE to get this result):
dynacom=# analyze noon;
ANALYZE
dynacom=# select histogram_bounds from pg_stats where attname = 'report_date';
histogram_bounds
-
{1969-06-26,1994-09-24,1996-04-05,1997-07-21,1998-08-27,1999-03-13,1999-11-11,2000-08-18,2001-04-18,2002-01-04,10007-06-09}
(1 row)
in which case we get this:
dynacom=# EXPLAIN select * from noon where
dynacom-# report_date between '2002-01-07' and '2003-01-07';
QUERY PLAN
-
Index Scan using noonf_date on noon (cost=0.00..4.08 rows=1 width=1975)
Index Cond: ((report_date = '2002-01-07'::date) AND (report_date =
'2003-01-07'::date))
(2 rows)
Seeing this histogram, the planner assumes that one-tenth of the table
is uniformly distributed between 2002-01-04 and 10007-06-09, which leads
it to the conclusion that the range between 2002-01-07 and 2003-01-07
probably contains only about one row, which causes it to prefer a scan
on report_date rather than on v_code.
The reason the problem comes and goes is that any given ANALYZE run
might or might not happen across one of the outliers. When it doesn't,
you get a histogram that leads to reasonably accurate estimates.
There are a couple of things you could do about this. One is to
increase the statistics target for report_date (see ALTER TABLE SET
STATISTICS) so that a finer-grained histogram is generated for the
report_date column. The other thing, which is more work but probably
the best answer in the long run, is to fix the outliers, which I imagine
must be incorrect entries.
You could perhaps put a constraint on report_date to prevent bogus
entries from sneaking in in future.
It looks like increasing the stats target would be worth doing also,
if you make many queries using ranges of report_date.
regards, tom lane
--- End of Forwarded Message
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] count(*) optimization
Nikola, I am not sure pgAdmin uses count(*) and I didn't have time to check phpPgAdmin also, I presumed it from similarly slow response I get in psql Well, pgAdmin and phpPgAdmin have their own mailing lists. I'd suggest posting there. Ideally, these programs should have an option that lets you use the Analyze stats instead of a real count if you want speed over accuracy. If nobody requests it, though, neither development team is likely to consider it. Of course, it's also possible that these interfaces are slow for lots of other reasons. For example, I've found pgAdminII to be sluggish on a large, busy network because MS-ODBC is quite vulnerable to traffic conflicts. And phpPgAdmin, at least several versions ago, had a tendency to issue a seperate query for each single bit of information, mySQL-style (they may have fixed this, though). -Josh Berkus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] insert rule doesn't see id field
On Tue, Jan 07, 2003 at 11:01:08AM -0500, Tom Lane wrote: I thought that the idea behind noup was to protect single columns from update. However, when I apply the noup trigger as above, I can't update /any/ column. Is this the intended behaviour? Idly looking at the source code for contrib/noupdate/noup.c, I don't believe that it has ever worked as advertised: it seems to reject any non-null value for the target column, independently of whether the value is the same as before (which is what I'd have thought it should do). Is anyone interested in fixing it? Or should we just remove it? If it's been there since 6.4 and you're the first person to try to use it, as seems to be the case, then I'd have to say that it's a waste of space in the distribution. I'm going to see if I can create this function. The issue I face is that I'm allowing certain clients to access parts of a PostgreSQL database on MS Access via ODBC. This means I can't really control how people may try to edit the data. Well, I could, by using MS Access security features, but I'd rather do what I can on the back end. If someone changes an ID field, then as long as foreign key contraints on other related tables are set to cascade or whatever, that won't be a problem. But what if someone updates an ID field to something higher than my current sequence? Then when the sequence hits that ID, it will crap out. Maybe just try again, but what if that happened to a bunch of records? Could be a pain. So that's the problem I'd like to prevent, for which I think this function would be useful. So I'll hack at it and see what I come up with. Might not happen immediately, though.. -- Ron Peterson -o) Network Systems Manager /\\ Mount Holyoke College_\_v http://www.mtholyoke.edu/~rpeterso ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Search and Replace
Just to close off another thread and to give a tad more information... I was not clear enough in my initial question to the list because not all of the URLs I am trying to change begin with http://www.srs.fs.fed.us/ so what I was really looking for was the syntax for replacing www.srs.fs.fed.us with www.srs.fs.usda.gov and not touching any records do not contain www.srs.fs.fed.us Ross Reedstrom was kind enough to give me some additional help that worked perfectly and after doing a few tests I am happy to share his SQL statement with the list. update pubs set url=substr(url,0,strpos(url,'www.srs.fs.fed.us'))||'www.srs.fs.usda.gov'||substr(url,strpos(url,'www.srs.fs.fed.us')+17) where url ~ 'www.srs.fs.fed.us' Thanks Ross! --rdm On Wed, 8 Jan 2003, Ross J. Reedstrom wrote: snip description of needing a simple string replace As you've discovered, standard SQL text processing functions are a bit primitive - usually you break out to the application language for that sort of thing. However, if you know for sure that there's only one instance of the replace string, and it's a fixed length string, you can get away with something like this: test=# select * from pubs; id | url + 1 | http://www.srs.fs.fed.us/pub/1 2 | http://www.srs.fs.fed.us/pub/2 3 | http://www.srs.fs.fed.us/pub/3 (3 rows) test=# update pubs set url= substr(url,0,strpos(url,'www.srs.fs.fed.us'))||'www.srs.fs.usda.gov'||substr(url,strpos(url,'www.srs.fs.fed.us')+17); UPDATE 3 test=# select * from pubs; id | url +-- 1 | http://www.srs.fs.usda.gov/pub/1 2 | http://www.srs.fs.usda.gov/pub/2 3 | http://www.srs.fs.usda.gov/pub/3 (3 rows) You can figure out how it works by playing with SELECTing different substr() ans strpos() directly, like this excerpt from my query history: select strpos(url,'www.srs.fs.usda.gov') from pubs; select substr(url,strpos(url,'www.srs.fs.usda.gov')) from pubs; select substr(url,0,strpos(url,'www.srs.fs.usda.gov')) from pubs; select substr(url,1,strpos(url,'www.srs.fs.usda.gov')) from pubs; select substr(url,-1,strpos(url,'www.srs.fs.usda.gov')) from pubs; select substr(url,0,strpos(url,'www.srs.fs.usda.gov')) from pubs; select substr(url,strpos(url,'www.srs.fs.usda.gov')+19) from pubs; select substr(url,strpos(url,'www.srs.fs.usda.gov')+18) from pubs; select substr(url,strpos(url,'www.srs.fs.usda.gov')+19) from pubs; Hope this helps, Ross -- Ross Reedstrom, Ph.D. [EMAIL PROTECTED] Research Scientist phone: 713-348-6166 The Connexions Project http://cnx./rice.edu fax: 713-348-6182 Rice University MS-39 Houston, TX 77005 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] What benefits can I expect from schemas ?
I could not find much documentation about SQL 92 schemas that 7.3 now supports. I understood it was a structure to group various objects, which allows faster privilege management, namespaces definition. Besides that, I don't see any advantages. I'd be glad if someone could point them out for me, or give doc links. That's pretty much it. You can give each user their own namespace so they can all create tables with the same name, or put applications in their own namespace so they don't conflict with other applications. You can also control object creation easier by restricting privs on the public namespace. Can you copy by schema? __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] What benefits can I expect from schemas ?
chester c young wrote: I could not find much documentation about SQL 92 schemas that 7.3 now supports. I understood it was a structure to group various objects, which allows faster privilege management, namespaces definition. Besides that, I don't see any advantages. I'd be glad if someone could point them out for me, or give doc links. That's pretty much it. You can give each user their own namespace so they can all create tables with the same name, or put applications in their own namespace so they don't conflict with other applications. You can also control object creation easier by restricting privs on the public namespace. Can you copy by schema? No. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] Postgresql Bug List?
Is there a bugzilla kind of thing for Postgresql? I would like to help out on the development, but have no idea where to start... Thanks Wei ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Postgresql Bug List?
No bugzilla, but do have a TODO list. See the developers FAQ. --- Wei Weng wrote: Is there a bugzilla kind of thing for Postgresql? I would like to help out on the development, but have no idea where to start... Thanks Wei ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Search and Replace
any anyone explain whats wrong with the replace based solution to this problem which i posted earlier? did i misunderstood anything? regds mallah. On Thursday 09 January 2003 01:48 am, Randy D. McCracken wrote: Just to close off another thread and to give a tad more information... I was not clear enough in my initial question to the list because not all of the URLs I am trying to change begin with http://www.srs.fs.fed.us/ so what I was really looking for was the syntax for replacing www.srs.fs.fed.us with www.srs.fs.usda.gov and not touching any records do not contain www.srs.fs.fed.us Ross Reedstrom was kind enough to give me some additional help that worked perfectly and after doing a few tests I am happy to share his SQL statement with the list. update pubs set url=substr(url,0,strpos(url,'www.srs.fs.fed.us'))||'www.srs.fs.usda.gov'||s ubstr(url,strpos(url,'www.srs.fs.fed.us')+17) where url ~ 'www.srs.fs.fed.us' Thanks Ross! --rdm On Wed, 8 Jan 2003, Ross J. Reedstrom wrote: snip description of needing a simple string replace As you've discovered, standard SQL text processing functions are a bit primitive - usually you break out to the application language for that sort of thing. However, if you know for sure that there's only one instance of the replace string, and it's a fixed length string, you can get away with something like this: test=# select * from pubs; id | url + 1 | http://www.srs.fs.fed.us/pub/1 2 | http://www.srs.fs.fed.us/pub/2 3 | http://www.srs.fs.fed.us/pub/3 (3 rows) test=# update pubs set url= substr(url,0,strpos(url,'www.srs.fs.fed.us'))||'www.srs.fs.usda.gov'||sub str(url,strpos(url,'www.srs.fs.fed.us')+17); UPDATE 3 test=# select * from pubs; id | url +-- 1 | http://www.srs.fs.usda.gov/pub/1 2 | http://www.srs.fs.usda.gov/pub/2 3 | http://www.srs.fs.usda.gov/pub/3 (3 rows) You can figure out how it works by playing with SELECTing different substr() ans strpos() directly, like this excerpt from my query history: select strpos(url,'www.srs.fs.usda.gov') from pubs; select substr(url,strpos(url,'www.srs.fs.usda.gov')) from pubs; select substr(url,0,strpos(url,'www.srs.fs.usda.gov')) from pubs; select substr(url,1,strpos(url,'www.srs.fs.usda.gov')) from pubs; select substr(url,-1,strpos(url,'www.srs.fs.usda.gov')) from pubs; select substr(url,0,strpos(url,'www.srs.fs.usda.gov')) from pubs; select substr(url,strpos(url,'www.srs.fs.usda.gov')+19) from pubs; select substr(url,strpos(url,'www.srs.fs.usda.gov')+18) from pubs; select substr(url,strpos(url,'www.srs.fs.usda.gov')+19) from pubs; Hope this helps, Ross -- Ross Reedstrom, Ph.D. [EMAIL PROTECTED] Research Scientist phone: 713-348-6166 The Connexions Project http://cnx./rice.edu fax: 713-348-6182 Rice University MS-39 Houston, TX 77005 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
