Skip site navigation (1) Skip section navigation (2)

Re: [SQL] system tables inquiry & db Link inquiry

From: Scott Marlowe <smarlowe(at)g2switchworks(dot)com>
To: Karthikeyan Sundaram <skarthi98(at)hotmail(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] system tables inquiry & db Link inquiry
Date: 2007-02-28 18:35:17
Message-ID: 1172687717.20651.156.camel@state.g2switchworks.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-sql
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.

In response to

pgsql-admin by date

Next:From: Karthikeyan SundaramDate: 2007-02-28 19:17:40
Subject: pg_dump error
Previous:From: Gary ChambersDate: 2007-02-28 18:29:43
Subject: Re: [SQL] system tables inquiry & db Link inquir

pgsql-sql by date

Next:From: Karthikeyan SundaramDate: 2007-02-28 19:17:40
Subject: pg_dump error
Previous:From: Gary ChambersDate: 2007-02-28 18:29:43
Subject: Re: [SQL] system tables inquiry & db Link inquir

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group