[SQL] table listing queries
Hi, I know that questions like this have been asked in the past, but I can find no definitive answer to one particular part of my problem... Namely, in MySQL I can say: "SHOW TABLES FROM 'dbname'" to list tables in a database I'm not currently connected to. I can find no way of doing this in PgSQL. Is there a way, or is postgres not letting me list the tables until I have connected to the database for security reasons? Thanks in advance, D. -- Daniel Silverstone http://www.digital-scurf.org/ PGP mail accepted and encouraged.Key Id: 2BC8 4016 2068 7895 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] table listing queries
On 8/25/05 7:13 AM, "Daniel Silverstone" <[EMAIL PROTECTED]> wrote: > Hi, > > I know that questions like this have been asked in the past, but I can > find no definitive answer to one particular part of my problem... > > Namely, in MySQL I can say: "SHOW TABLES FROM 'dbname'" to list tables > in a database I'm not currently connected to. > > I can find no way of doing this in PgSQL. > > Is there a way, or is postgres not letting me list the tables until I > have connected to the database for security reasons? There are system catalogs which contain the information about tables. http://www.postgresql.org/docs/8.0/interactive/catalogs.html However, they are specific to each database. In other words, the storage of database-specific information is all WITHIN the given database, so you need to be physically accessing the given database to even see those tables (or do the query). Sean ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] table listing queries
Namely, in MySQL I can say: "SHOW TABLES FROM 'dbname'" to list tables in a database I'm not currently connected to. In MySQL, table definitions are replicated outside the database files, and might therefore be available without connecting to a specifc database. This probably happens because of the need for some kind of unified repository of objects pertaining to different database engines. Helder M. Vieira ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Number of rows in a cursor ?
On Thu, Aug 25, 2005 at 08:51:59 +0200, Bo Lorentsen <[EMAIL PROTECTED]> wrote: > Michael Fuhr wrote: > > >Right -- when you open a cursor PostgreSQL doesn't know how many > >rows it will return. PostgreSQL selects a query plan based on an > >*estimate* of how many rows the query will return, but until you > >fetch all the rows you can't know for sure how many rows there will > >be. > > > > > So if i make a but data set as result of a cursor I only "pay" for the > rows I actually fetch ? There is usually some overhead for set up, though I believe quick starting plans are favored for cursor queries. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How this query!
On Thu, Aug 25, 2005 at 18:44:00 +0700, Richard Susanto <[EMAIL PROTECTED]> wrote: > Folks, > would you like help me how to query this case. This question is off topic for the pgsql-admin list, it should have been asked on the pgsql-sql list because it was a question about SQL. (I probably wouldn't have said anything if it had been posted to the pgsql-general or pgsql-novice lists, but it is definitely not a question even remotely related to postgres administration.) > > I have tbl_a, the fields are : > item_record > item_product_name > item_qty > > > if the data are : > > item_record = 1,2,3,4,5,6 > item_product_name = a,b,a,c,d,c,... > item_qty = 10,5,15,10,20,5,... > > How the query to sum item_qty_total if i want the result : > item_product_name = a,b,c,d > item_qty_total = 25,5,15,20 Use GROUP BY item_product_name and sum(item_qty). Something like: SELECT item_product_name, sum(item_qty) AS item_qty_total FROM tbl_a GROUP BY item_product_name ORDER BY item_product_name ; ---(end of broadcast)--- TIP 1: 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] Can EXCEPT Be Used for To Solve This Problem?
On Aug 24, 2005, at 4:47 PM, Lane Van Ingen wrote: I want to select 2nd oldest transaction from foo (transaction 3). The solution below works, but I think there may be a better way. Does anyone else have a better idea? why not just select order by update_time desc limit 2 then discard the first row you fetch? Vivek Khera, Ph.D. +1-301-869-4449 x806 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Can EXCEPT Be Used for To Solve This Problem?
you can also do select ... order by update_time desc offset 1 limit 1 On Thursday 25 August 2005 10:47 am, Vivek Khera wrote: > On Aug 24, 2005, at 4:47 PM, Lane Van Ingen wrote: > > I want to select 2nd oldest transaction from foo (transaction 3). The > > solution below > > works, but I think there may be a better way. Does anyone else have > > a better > > idea? > > why not just select order by update_time desc limit 2 then discard > the first row you fetch? > > > Vivek Khera, Ph.D. > +1-301-869-4449 x806 > > > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match ---(end of broadcast)--- TIP 1: 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] Tidying values on variable instantiation
Folks, Preamble: * I can create a check constraint on a column or domain that enforces "no leading or trailing whitespace". Imagine that the domain is called "trimmed_varchar" * I can create plpgsql function/triggers that "tidy" up incoming varchars, trimming the offending whitespaces, on a column by column basis. * When creating a column based on a domain with the check constraint, I cannot "tidy it up" during a pre-insert/pre-update trigger. Fair enough. * I'm only asking about this because I am a long-in-the-tooth Oracle guy, and Pg seems to have many *very* nice features, and there might be an *elegant* way to achieve this that I cannot attempt in Oracle. Desired Outcome(s): * I would like to have the convenience of declaring a column that obeys a constraint (similar to using a domain), but allows a "tidy-up" as the value is created BEFORE asserting the constraint. This *might* be termed a "domain trigger". (Perhaps even a WORM is possible!). * I would like to able to declare columns as "trimmed_varchar(n)". * I'd like to be able to use the same approach for other "weak domains". Question(s): * Am I being realistic, or should I grit my teeth and clone code from trigger to trigger and column to column? * Is this something I should try and do using domains, types and cast functions from "text" or some horrible combination of them all? * Has anybody got a code sample that might do something similar. Apologies if I have missed something obvious in the manual, or if it is a well-known item in the wish-lists, but I am very new to serious Pg work, and have a tight schedule to do deliver a schema. *sigh* Thanks in advance -- David T. Bath [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Tidying values on variable instantiation
On Aug 26, 2005, at 12:04 PM, Bath, David wrote:
Desired Outcome(s):
* I would like to have the convenience of declaring a column that
obeys
a constraint (similar to using a domain), but allows a "tidy-up"
as the
value is created BEFORE asserting the constraint. This *might* be
termed a "domain trigger". (Perhaps even a WORM is possible!).
* I would like to able to declare columns as
"trimmed_varchar(n)".
* I'd like to be able to use the same approach for other "weak
domains".
Unfortuantely, I don't know of a way to obtain your desired outcomes,
but perhaps can offer a couple of ideas that you haven't mentioned
(though you may have already thought of them and discarded them as
undesireable. In that case, my apologies :)
Perhaps rather doing this with a trigger and having the table take
care of it, you could use pl functions to handle the inserts, so
instead of using INSERT directly, you could call the
insert_into_table_foo function. The insert_into_table_foo function
would clean up the input and then call INSERT. A disadvantage of this
is that you'll need to write one of these for each table, though
there are some who handle a lot of their inserts, updates, etc, via
pl functions rather than calling the INSERT and UPDATE commands
directly.
Another option would be to have a separate cleaning function (e.g.,
clean_foo() )for each "type" you want, and then call it with
something like INSERT INTO bar (baz, bat, foo) values (232,
'2005-02-20', clean_foo('protofoo')); This has the advantage that you
just need to write one function for each type (rather than each
table), but you'll have to remember to call it.
While I can understand your motivation, I personally think this kind
of operation is best left in the application layer (which includes
such insert functions) rather than the DDL.
Just my ¥2.
Michael Glaesemann
grzm myrealbox com
---(end of broadcast)---
TIP 6: explain analyze is your friend
