Re: [ADMIN] Welcome to the pgsql-admin list!
Hi All, I have a simple table below and I want to be able to traverse it using SQL. Can somebody please advise me how I can do this in postgreSQL. CREATE TABLE grouping ( grouping_id int4 NOT NULL, version int4, parent_grouping_id int4, topic_topic_id int4, CONSTRAINT grouping_pkey PRIMARY KEY (grouping_id), CONSTRAINT fk1e2e9d036852722d FOREIGN KEY (topic_topic_id) REFERENCES topic (topic_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT fk1e2e9d03a1bfbfe FOREIGN KEY (topic_topic_id) REFERENCES topic (topic_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT fk1e2e9d03cdaf7222 FOREIGN KEY (parent_grouping_id) REFERENCES grouping (grouping_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) WITHOUT OIDS; ALTER TABLE grouping OWNER TO napdba; Thanks In Advance. Gibson. On Wed, Jul 23, 2008 at 11:12 AM, [EMAIL PROTECTED] wrote: Welcome to the pgsql-admin mailing list! Your password at postgresql.org is QiFiDx To leave this mailing list, send the following command in the body of a message to [EMAIL PROTECTED]: approve QiFiDx unsubscribe pgsql-admin [EMAIL PROTECTED] This command will work even if your address changes. For that reason, among others, it is important that you keep a copy of this message. To post a message to the mailing list, send it to pgsql-admin@postgresql.org If you need help or have questions about the mailing list, please contact the people who manage the list by sending a message to [EMAIL PROTECTED] You can manage your subscription by visiting the following WWW location: http://mail.postgresql.org/mj/mj_wwwusr/domain=postgresql.org/gchimhamhiwa%40gmail.com Put the text of the welcome message here. -- Gibson Chimhamhiwa CSIR - Meraka Institute P O Box 395, Pretoria 0001 South Africa www.csir.co.za / www.meraka.org.za Office: +27 12 841 2758 Mobile: +27 78 447 2879 Fax: +27 12 841 4720 E-mail:[EMAIL PROTECTED] [EMAIL PROTECTED]
Re: [ADMIN] Welcome to the pgsql-admin list!
Gibson Chimhamhiwa [EMAIL PROTECTED] wrote: I have a simple table below and I want to be able to traverse it using SQL. Can somebody please advise me how I can do this in postgreSQL. CREATE TABLE grouping SELECT * FROM grouping; If that isn't what you're looking for, we need more information to be able to help. If you follow up on this, please start a new thread with a subject line appropriate to the discussion; it will help the right people notice your mail. -Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] Query a list of tables
What is the SQL to do the following: 1) Return a list of all possible SCHEMA names within a database. 2) For a given SCHEMA return the list of all table names. Thanks, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu http://webservices.uiuc.edu/
Re: [ADMIN] Query a list of tables
On Wed, Jul 23, 2008 at 8:56 AM, Campbell, Lance [EMAIL PROTECTED] wrote: What is the SQL to do the following: 1) Return a list of all possible SCHEMA names within a database. 2) For a given SCHEMA return the list of all table names. You can either do it with the INFORMATION_SCHEMA, or the system catalogs. To see how to do it with the system catalogs, then start psql with the -E switch and issue \dn and set search_path='schemaname' and \d -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Query a list of tables
Hi All, I have a postgreSQl database and just wan to know how I can get to its data dictionary.
Re: [ADMIN] Query a list of tables
Is there a list somewhere of the system catalog tables and examples of querying it for administrative reasons? -Original Message- From: Scott Marlowe [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 23, 2008 10:02 AM To: Campbell, Lance Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Query a list of tables On Wed, Jul 23, 2008 at 8:56 AM, Campbell, Lance [EMAIL PROTECTED] wrote: What is the SQL to do the following: 1) Return a list of all possible SCHEMA names within a database. 2) For a given SCHEMA return the list of all table names. You can either do it with the INFORMATION_SCHEMA, or the system catalogs. To see how to do it with the system catalogs, then start psql with the -E switch and issue \dn and set search_path='schemaname' and \d -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Query a list of tables
Hi, Under Schema I have; information_schema pg_catalog pg_toast pg_toast_temp_1 pg_temp_1 and then public which contains the tables with data for my database. On Wed, Jul 23, 2008 at 5:11 PM, Campbell, Lance [EMAIL PROTECTED] wrote: Is there a list somewhere of the system catalog tables and examples of querying it for administrative reasons? -Original Message- From: Scott Marlowe [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 23, 2008 10:02 AM To: Campbell, Lance Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Query a list of tables On Wed, Jul 23, 2008 at 8:56 AM, Campbell, Lance [EMAIL PROTECTED] wrote: What is the SQL to do the following: 1) Return a list of all possible SCHEMA names within a database. 2) For a given SCHEMA return the list of all table names. You can either do it with the INFORMATION_SCHEMA, or the system catalogs. To see how to do it with the system catalogs, then start psql with the -E switch and issue \dn and set search_path='schemaname' and \d -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin -- Gibson Chimhamhiwa CSIR - Meraka Institute P O Box 395, Pretoria 0001 South Africa www.csir.co.za / www.meraka.org.za Office: +27 12 841 2758 Mobile: +27 78 447 2879 Fax: +27 12 841 4720 E-mail:[EMAIL PROTECTED] [EMAIL PROTECTED]
[ADMIN] Catalog
Hi for all, is possible to get PostgreSQL Catalog EER diagram? where can I get it? regards Sergio Gabriel Rodriguez -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Query a list of tables
Campbell, Lance написа: Is there a list somewhere of the system catalog tables and examples of querying it for administrative reasons? [...] http://www.postgresql.org/docs/current/static/information-schema.html http://www.postgresql.org/docs/current/static/catalogs.html This mostly means you need to acquaint yourself with the fine manual. -- Milen A. Radev -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Query a list of tables
Solution:
I found that by doing the following query you can get a list of all
schemas or namespaces:
select distinct schemaname from pg_tables where schemaname not in
('information_schema', 'pg_catalog');
In order to select all of the tables within a schema or namespace:
select tablename from pg_tables where schemaname='name_of_schema';
Thanks for the responses.
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Gibson
Chimhamhiwa
Sent: Wednesday, July 23, 2008 10:22 AM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Query a list of tables
Hi,
Under Schema I have;
information_schema
pg_catalog
pg_toast
pg_toast_temp_1
pg_temp_1
and then public which contains the tables with data for my database.
On Wed, Jul 23, 2008 at 5:11 PM, Campbell, Lance [EMAIL PROTECTED]
wrote:
Is there a list somewhere of the system catalog tables and examples of
querying it for administrative reasons?
-Original Message-
From: Scott Marlowe [mailto:[EMAIL PROTECTED]
Sent: Wednesday, July 23, 2008 10:02 AM
To: Campbell, Lance
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Query a list of tables
On Wed, Jul 23, 2008 at 8:56 AM, Campbell, Lance [EMAIL PROTECTED]
wrote:
What is the SQL to do the following:
1) Return a list of all possible SCHEMA names within a database.
2) For a given SCHEMA return the list of all table names.
You can either do it with the INFORMATION_SCHEMA, or the system
catalogs. To see how to do it with the system catalogs, then start
psql with the -E switch and issue \dn and set search_path='schemaname'
and \d
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
--
Gibson Chimhamhiwa
CSIR - Meraka Institute
P O Box 395, Pretoria 0001
South Africa
www.csir.co.za / www.meraka.org.za
Office: +27 12 841 2758
Mobile: +27 78 447 2879
Fax: +27 12 841 4720
E-mail:[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]
Re: [ADMIN] Catalog
There are tools that allow you to reverse engineer schemas.. or to generate ERD's .. visio comes to mind. It works on odbc connections. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Sergio Gabriel Rodriguez Sent: Wednesday, July 23, 2008 10:32 AM To: pgsql-admin@postgresql.org Subject: [ADMIN] Catalog Hi for all, is possible to get PostgreSQL Catalog EER diagram? where can I get it? regards Sergio Gabriel Rodriguez -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin ___ The information in this email or in any file attached hereto is intended only for the personal and confiden- tial use of the individual or entity to which it is addressed and may contain information that is propri- etary and confidential. If you are not the intended recipient of this message you are hereby notified that any review, dissemination, distribution or copying of this message is strictly prohibited. This communica- tion is for information purposes only and should not be regarded as an offer to sell or as a solicitation of an offer to buy any financial product. Email trans- mission cannot be guaranteed to be secure or error- free. P6070214 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] conditional logging based on client
Rajesh Kumar Mallah wrote: On Tue, Jul 22, 2008 at 5:54 PM, Achilleas Mantzios The original objective is that we should be able to know what all hand made SQLs have been executed in past (which can date back as long as 1 year even) You have to adjust log_statement in your postgresql.conf however you will have to bear in mind the performance implications of this. Normal production sites dont do this. You can enable/disable this kind of logging any time by killing -HUP thanks for reply. any hit on performance is not desired. is it possible to alter logging behaviour just for the psql session ? Not quite but the opposite could be a path to look down. The config specifies logging statements that is applied to all sessions. Your app could then be setup to alter this at run time. I haven't tried this - it is only a theory - I would think that you could create a function to turn off logging but you would also need to look into restricting access to only your app (your app provides some sort of code maybe). The biggest problem here will be preventing users from performing this command by hand in psql. This is where the function would allow a point that must be 'authenticated' by your app. http://www.postgresql.org/docs/8.3/interactive/functions-admin.html and http://www.postgresql.org/docs/8.3/interactive/view-pg-settings.html as well as SECURITY DEFINER which is the reason for the function - it must be run as a superuser. http://www.postgresql.org/docs/8.3/interactive/sql-createfunction.html Another option would be to have them use a different user name when using psql. Probably not an option unless the app runs with it's own username. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] conditional logging based on client
2008/7/22 Rajesh Kumar Mallah [EMAIL PROTECTED]: On Tue, Jul 22, 2008 at 5:54 PM, Achilleas Mantzios [EMAIL PROTECTED] wrote: Στις Tuesday 22 July 2008 15:17:42 ο/η Rajesh Kumar Mallah έγραψε: Hi, We want to log all SQLs that has been executed by using psql client. we do not want to use .psql_history as it is distributed and may be deleted by users . The original objective is that we should be able to know what all hand made SQLs have been executed in past (which can date back as long as 1 year even) You have to adjust log_statement in your postgresql.conf however you will have to bear in mind the performance implications of this. Normal production sites dont do this. You can enable/disable this kind of logging any time by killing -HUP thanks for reply. any hit on performance is not desired. is it possible to alter logging behaviour just for the psql session ? Set log_statement='ddl' in your postgresql.conf and pgsql will log all ddl that hits the database. It won't log other stuff, just the ddl. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Query a list of tables
First you get to Postgresql docs, from there you get anywhere you want. Igor From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Gibson Chimhamhiwa Sent: Wednesday, July 23, 2008 11:10 AM To: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Query a list of tables Hi All, I have a postgreSQl database and just wan to know how I can get to its data dictionary.
Re: [ADMIN] conditional logging based on client
Scott Marlowe [EMAIL PROTECTED] writes: 2008/7/22 Rajesh Kumar Mallah [EMAIL PROTECTED]: any hit on performance is not desired. is it possible to alter logging behaviour just for the psql session ? Set log_statement='ddl' in your postgresql.conf and pgsql will log all ddl that hits the database. It won't log other stuff, just the ddl. I think the OP didn't want *all* sessions doing this but just selected sessions; so ALTER USER ... SET log_statement, or possibly ALTER DATABASE ... SET, would perhaps serve better. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] -O to remove owner
I am currently running these to commands in scripts to backup the postgresql databases. /usr/bin/pg_dumpall -ch 127.0.0.1 -U postgres | gzip $backup_dir/postgresql-all-$timeslot-databases.gz /usr/bin/pg_dump $i -ch 127.0.0.1 -U postgres | gzip $backup_dir/postgresql-$i-$timeslot-database.gz I would like to remove all owners from all objects so when I do a restore all objects are owned by postgres. Can I just tack on -O after -U postgres? /usr/bin/pg_dumpall -ch 127.0.0.1 -U postgres -O | gzip $backup_dir/postgresql-all-$timeslot-databases.gz Marc -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
