Thread: SQL list table names
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. thanks. alviN
If you start psql like so psql -E Then all the SQL behind the \d type commands is displayed for you. adam > 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. > > thanks. > > > > alviN > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.
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 psqlusing \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
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: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr
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 Group V_/_ USDA/FS - Southern Research Station E-Mail: rdm@srs.fs.usda.gov Voice: (828) 259-0518 Fax: (828) 257-4840 Web: http://www.srs.fs.fed.us/ =======================================
On Wed, Jan 08, 2003 at 09:02:47 -0500, "Randy D. McCracken" <rdm@srs.fs.usda.gov> 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.
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 Group V_/_ > > USDA/FS - Southern Research Station > > E-Mail: rdm@srs.fs.usda.gov > 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.
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 frommy 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. reedstrm@rice.edu Research Scientist phone: 713-348-6166 The Connexions Project http://cnx./rice.edu fax: 713-348-6182 Rice University MS-39 Houston, TX 77005
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. reedstrm@rice.edu > Research Scientist phone: 713-348-6166 > The Connexions Project http://cnx./rice.edu fax: 713-348-6182 > Rice University MS-39 > Houston, TX 77005 > >
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. reedstrm@rice.edu > > 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.
On Thu, Jan 09, 2003 at 11:00:32AM +0530, Rajesh Kumar Mallah. wrote: > > any anyone explain whats wrong with the replace based solution to this problem > which i posted earlier? > > did i misunderstood anything? Probably just overkill - I'm sure it would work, but, based on how the question was asked, I guessed that the original questioner was looking for a quick, onetime fix sort of thing, and wasn't real comfortable with SQL, let alone adding contrib extension products to the installation. Ross
On Thu, 9 Jan 2003, Ross J. Reedstrom wrote:
> On Thu, Jan 09, 2003 at 11:00:32AM +0530, Rajesh Kumar Mallah. wrote:
> >
> > any anyone explain whats wrong with the replace based solution to this problem
> > which i posted earlier?
> >
> > did i misunderstood anything?
>
> Probably just overkill - I'm sure it would work, but, based on how the
> question was asked, I guessed that the original questioner was looking
> for a quick, onetime fix sort of thing, and wasn't real comfortable with
> SQL, let alone adding contrib extension products to the installation.
>
> Ross
>
>
Hi Rajesh,
I don't know why but the example you gave me did not work. Here is what
happened when I tried:
==========
pubs_test=# UPDATE publications SET url = replace( 'www.srs.fs.fed.us' ,
'www.srs.fs.usda.gov' , url ) WHERE url ilike '%www.srs.fs.fed.us%';
ERROR: Function 'replace(unknown, unknown, text)' does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
==========
Obviously I am a newbie at PostgreSQL (I have also joined the Novice
mailing list to learn more) so I can not tell you why your example did not
work, perhaps someone else with more experience can add some information.
Best,
--rdm
On Thu, Jan 09, 2003 at 19:15:51 -0500, "Randy D. McCracken" <rdm@srs.fs.usda.gov> wrote: > > Hi Rajesh, > > I don't know why but the example you gave me did not work. Here is what > happened when I tried: > > ========== > > pubs_test=# UPDATE publications SET url = replace( 'www.srs.fs.fed.us' , > 'www.srs.fs.usda.gov' , url ) WHERE url ilike '%www.srs.fs.fed.us%'; > > ERROR: Function 'replace(unknown, unknown, text)' does not exist > Unable to identify a function that satisfies the given argument types > You may need to add explicit typecasts The arguments to replace were in the wrong order.