From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Karthikeyan Sundaram <skarthi98@hotmail.com>
Cc: pgsql-admin@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] system tables inquiry & db Link inquiry
Date: Wed, 28 Feb 2007 12:35:17 -0600
Message-ID: <1172687717.20651.156.camel@state.g2switchworks.com> (raw)
In-Reply-To: <BAY131-F14526BA01BB66AA5D4DB75B0810@phx.gbl>
References: <BAY131-F14526BA01BB66AA5D4DB75B0810@phx.gbl>
On Wed, 2007-02-28 at 12:19, Karthikeyan Sundaram wrote:
> Hi,
>
> We are using Postgres 8.1.0
Stop. Do not pass go, do not collect $200. Update your postgresql
installation now to 8.1.8. There were a lot of bugs fixed between 8.1.0
and 8.1.8.
After that...
> Question No 1:
> =========
> There are lots of system tables that are available in postgres. For
> example pg_tables will have all the information about the tables that are
> present in a given schema. pg_views will have all the information about the
> views for the given schema.
>
> I want to find all the sequences. What is the system tables that have
> the information about all the sequences?
In the future, you can use this trick to find those things out:
psql -E template1
\? (command to list all the backslash commands from psql)
\ds (<- command for listing sequences from psql)
Tada, you now get the sql that psql used to make that display.
For 8.2.3 that's:
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as
"Type",
r.rolname as "Owner"
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('S','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
> Question No 2:
> =========
>
> I have 2 postgres instance located in two different servers. I want
> to create a DBlink (like in Oracle) between these 2. What are the steps
> involved to create this.
>
> Any examples? Please advise.
I'm pretty sure there's some examples in the contrib/dblink/doc
directory in the source file to do that. It's pretty simple, I had it
working about 5 minutes after installing dblink.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: smarlowe@g2switchworks.com, skarthi98@hotmail.com, pgsql-admin@postgresql.org
Subject: Re: [SQL] system tables inquiry & db Link inquiry
In-Reply-To: <1172687717.20651.156.camel@state.g2switchworks.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox