[SQL] 'show databases' in psql way?
Hi list, how can I list the databases in a postgresish way? I know about the '-l' switch of 'psql' but is there a DBI/SQL-query way? I don't want to call an external program only to list the databases. I've googled about this problem but I only found the '-l'-way to this this. Ideas? Solutions? -- So long... Fuzz ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] 'show databases' in psql way?
At 07:13 AM 11/1/04, Erik Wasser wrote: how can I list the databases in a postgresish way? I know about the '-l' switch of 'psql' but is there a DBI/SQL-query way? I don't want to call an external program only to list the databases. I've googled about this problem but I only found the '-l'-way to this this. The -e switch of psql will display queries behind commands. So use this option, the use 'l' as a command instead of a switch. Frank ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] 'show databases' in psql way?
On Mon, 1 Nov 2004 13:13:10 +0100, Erik Wasser <[EMAIL PROTECTED]> wrote: > Hi list, > > how can I list the databases in a postgresish way? I know about the '-l' > switch of 'psql' but is there a DBI/SQL-query way? I don't want to call > an external program only to list the databases. I've googled about this > problem but I only found the '-l'-way to this this. > > Ideas? Solutions? Start psql with the -E switch, and it shows the SQL used to generate the output from psql's slash commands. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] 'show databases' in psql way?
On Monday 01 November 2004 13:26, Ian Barwick wrote: > Start psql with the -E switch, and it shows the SQL used to generate > the output from psql's slash commands. Thanks for the quick answer. I will remember this switch! B-) -- So long... Fuzz ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Join Table
Hello, I have created the following join table: the two FKs are the PK of the table. Typically, I will need to select rows for a given ITEM_FK. Question: is it necessary/advisable to create an index for the ITEM_FK column? Or is this redundantbecause this column is already one of the PK columns? CREATE TABLE SUPPLY ( ITEM_FK integer NOT NULL, CONTACT_FK integer NOT NULL, COST numeric (7,2), PRIMARY KEY (ITEM_FK,CONTACT_FK) ); -- Regards/Gruß, Tarlika Elisabeth Schmitz ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Join Table
On Mon, Nov 01, 2004 at 04:34:32PM +, T E Schmitz wrote: > > I have created the following join table: the two FKs are the PK of the > table. Typically, I will need to select rows for a given ITEM_FK. > > Question: is it necessary/advisable to create an index for the ITEM_FK > column? Or is this redundantbecause this column is already one of the PK > columns? Here's an excerpt from the documentation for CREATE TABLE: "PostgreSQL automatically creates an index for each unique constraint and primary key constraint to enforce the uniqueness. Thus, it is not necessary to create an explicit index for primary key columns." However, read the "Multicolumn Indexes" section in the "Indexes" chapter to be sure you understand when the index will be used and when it won't be: http://www.postgresql.org/docs/7.4/static/indexes-multicolumn.html > CREATE TABLE SUPPLY > ( > ITEM_FK integer NOT NULL, > CONTACT_FK integer NOT NULL, > COST numeric (7,2), > PRIMARY KEY (ITEM_FK,CONTACT_FK) > ); If ITEM_FK and CONTACT_FK are foreign keys, then you might want to add foreign key constraints to ensure referential integrity. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Join Table
T E Schmitz <[EMAIL PROTECTED]> writes: > CREATE TABLE SUPPLY > ( > ITEM_FK integer NOT NULL, > CONTACT_FK integer NOT NULL, > COST numeric (7,2), > PRIMARY KEY (ITEM_FK,CONTACT_FK) > ); > Question: is it necessary/advisable to create an index for the ITEM_FK > column? Or is this redundantbecause this column is already one of the PK > columns? The PK index will be usable for searches on ITEM_FK alone (though *not* for searches on CONTACT_FK alone --- a moment's thought about the index sort order should convince you why). It would be marginally less efficient for this purpose than a dedicated index on ITEM_FK. But unless your use of this table is almost all searches and hardly any modifications, adding a dedicated index is probably a net loss due to the added update costs. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Join Table
T E Schmitz wrote: Hello, I have created the following join table: the two FKs are the PK of the table. Typically, I will need to select rows for a given ITEM_FK. Question: is it necessary/advisable to create an index for the ITEM_FK column? Or is this redundantbecause this column is already one of the PK columns? CREATE TABLE SUPPLY ( ITEM_FK integer NOT NULL, CONTACT_FK integer NOT NULL, COST numeric (7,2), PRIMARY KEY (ITEM_FK,CONTACT_FK) ); The primary-key index can be used for ITEM_FK but not CONTACT_FK, so you might want an index on that column. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Join Table
Hello Mike/Tom/Richard, Thank you for your replies. Michael Fuhr wrote: On Mon, Nov 01, 2004 at 04:34:32PM +, T E Schmitz wrote: Question: is it necessary/advisable to create an index for the ITEM_FK column? Or is this redundantbecause this column is already one of the PK columns? However, read the "Multicolumn Indexes" section in the "Indexes" chapter to be sure you understand when the index will be used and when it won't be: http://www.postgresql.org/docs/7.4/static/indexes-multicolumn.html I see. If using a multi-column PK, the order matters. So, if I want to access the table both via the 1st and 2nd PK column, I would have to define an index for the 2nd column to avoid a full table scan. Let's ask the question the other way round: I remember seeing a discussion (re Oracle) whether to use a multi-column PK or a unique constraint in such a situation - I got the impression it is one of these "religious" discussions ;-). What are the pros and cons? I have a few join tables. In the example I gave earlier the rows might be updated and selected via either of the two FKs. I have other join tables the rows of which will never be updated. In any case, the FK/FK combination has to be unique. If using a multi-column unique constraint, I presume the order matters just as it does with multi-column PKs? If ITEM_FK and CONTACT_FK are foreign keys, then you might want to add foreign key constraints to ensure referential integrity. Thank you for the tip. I had done that further down in my init file via an ALTER TABLE...ADD CONSTRAINT. -- Regards/Gruß, Tarlika Elisabeth Schmitz ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] 'show databases' in psql way?
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
> how can I list the databases in a postgresish way? I know
> about the '-l' switch of 'psql' but is there a DBI/SQL-query way?
Yes, the DBI way is to use the "data_sources" function. Works
just fine for Postgres:
my @dbs = DBI->data_sources('Pg');
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200411012240
-BEGIN PGP SIGNATURE-
iD8DBQFBhwIHvJuQZxSWSsgRArd1AJ9hKXD+cSaM2L3RUXQdabuRofNFjwCfaHT0
+bRPuYhuED0mnlp1FRtvQQw=
=tsqe
-END PGP SIGNATURE-
---(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] Join Table
On Mon, Nov 01, 2004 at 06:12:02PM +, T E Schmitz wrote: > I see. If using a multi-column PK, the order matters. > So, if I want to access the table both via the 1st and 2nd PK column, I > would have to define an index for the 2nd column to avoid a full table scan. If you want to use an index scan when querying by the 2nd column alone then you'd need to create an index on it. Queries using the 1st column alone or the 1st column with the 2nd column will use the primary key index. You can use EXPLAIN ANALYZE to see which index, if any, the planner uses. > Let's ask the question the other way round: I remember seeing a > discussion (re Oracle) whether to use a multi-column PK or a unique > constraint in such a situation - I got the impression it is one of these > "religious" discussions ;-). > What are the pros and cons? Here's an excerpt from PostgreSQL's CREATE TABLE documentation: Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL, but identifying a set of columns as primary key also provides metadata about the design of the schema, as a primary key implies that other tables may rely on this set of columns as a unique identifier for rows. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
