Re: [SQL] About primary keys.

[Tim Andersen]

I'm new to PostgreSQL but I am familiar with DB2,
Oracle and Sybase.  I must say, I am impressed with
PostgreSQL so far!

In order to compare databases across DBMS platforms,
we need to create a view that queries from the system
catalog tables.  This view returns all of the columns
in the database listed by schema, table, and
columnname with some additional information about the
column (such as a primary key indicator).

These are the columns in the view:
creator (schema), tname (tablename), cname
(columnname), coltype (datatype), nulls (nullable),
length, syslength (precision), in_primary_key, colno
(columnumber), default_value, comments

I looked in the archives at postgresql.com, and I
found someone else with the same problem that I had
but no solution was posted.

I have made some good progress on creating a view that
selects from system catalog tables, but I am having
trouble with the in_primary_key and the
length/precision columns.  Many of our tables have
complex primary keys.

The query I have so far only gets columns that are
part of a primary key.  I need to return all of the
columns listed and a Y/N indicator for whether or not
the column is a part of the tables primary key.
Here's what I have:
/*---//
// This view shows all rows that //
// are part of a primary key://
//---*/
select upper(pgt1.schemaname) as "creator",
   upper(pgt1.tablename) as "tname",
   upper(pga1.attname) as "cname",
   case smmtsys.v_datatype.typname
 when 'bpchar' then 'char'
 else smmtsys.v_datatype.typname
   end as "coltype",
   case pga1.attnotnull
 when true then 'N'
 when false then 'Y'
   end as "nulls",
   i.indisprimary as "in_primary_key",
   pga1.atttypmod as "length",
   pga1.attndims as "syslength",
   pga1.attnum as "colno"
  from pg_tables pgt1,
   pg_class pgc1,
   pg_attribute pga1,
   pg_attribute pga2,
   pg_type,
   smmtsys.v_datatype,
   pg_index i,
   pg_namespace n
 where pgc1.relname = pgt1.tablename
   and pg_type.typname = pgt1.tablename
   and pga1.attrelid = pgc1.relfilenode
   and  pga1.attnum > 0
   and pga1.atttypid = smmtsys.v_datatype.oid
   and pgc1.oid = i.indrelid
   and i.indisprimary = 't'
   and n.oid = pgc1.relnamespace
   and pgt1.tablename = pgc1.relname
   and pga2.attrelid = i.indexrelid
   and pga1.attrelid = i.indrelid
   and pga1.attnum = i.indkey[pga2.attnum-1];

/*---//
// this is a quick and dirty //
// view to get the datatypes //
// used in the above query:  //
//---*/ 
create view smmtsys.v_datatype as (
select oid, typname from pg_type)
;


__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] About primary keys.

[Tim Andersen]

I looked in the info.c on line 2891 of the
psqlodbc-7.2.5 to find this SQL logic (courtesy of Tom
Lane)
select ta.attname, ia.attnum
 from pg_attribute ta, pg_attribute ia, pg_class c,
pg_index i, pg_namespace n
 where c.oid = i.indrelid
 AND n.oid = c.relnamespace
 AND i.indisprimary = 't'
 AND ia.attrelid = i.indexrelid
 AND ta.attrelid = i.indrelid
 AND ta.attnum = i.indkey[ia.attnum-1];

The above SQL retrieves each and every column in the
database that is a part of a complex primary key.
I need to join this to a list of all of the columns in
the database so I can have the primary key indicator. 


Here's another variation of the above SQL that shows
schema, table, column, colum_num, and a primary key
indicator:

select pg_tables.schemaname, pg_tables.tablename,
ta.attname, ia.attnum, i.indisprimary
from pg_attribute ta, pg_attribute ia, pg_class c,
pg_index i, pg_namespace n , pg_tables
where c.oid = i.indrelid
AND n.oid = c.relnamespace
AND i.indisprimary = 't'
AND ia.attrelid = i.indexrelid
AND ta.attrelid = i.indrelid
AND ta.attnum = i.indkey[ia.attnum-1]
AND pg_tables.tablename = c.relname;

so, shouldn't there be an easy way to retrieve all of
the columns for all tables with a primary key
indicator using this strategy?

If creating another view will simplify syntax, that's
fine too.


__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

---(end of broadcast)---
TIP 3: 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] About primary keys -- made some progress

[Tim Andersen]

This might not be the cleanest solution, but it runs
fast and it retrieved the information I need.
I broke it down into pieces and created several views
to query from to simplify it for myself.
The first four statements are views and the last one
is the query I was originally trying to get.  (note
that smmtsys is a schema I created, everything else is
dealing with system catalog tables)
Here's the SQL:
---
create view smmtsys.v_datatype as (
select oid, typname from pg_type)
;

create view smmtsys.v_primarykeys as(
select pg_tables.schemaname, pg_tables.tablename,
ta.attname, ta.attrelid, ia.attnum, i.indisprimary
from pg_attribute ta, pg_attribute ia, pg_class c,
pg_index i, pg_namespace n , pg_tables
where c.oid = i.indrelid
AND n.oid = c.relnamespace
AND i.indisprimary = 't'
AND ia.attrelid = i.indexrelid
AND ta.attrelid = i.indrelid
AND ta.attnum = i.indkey[ia.attnum-1]
AND pg_tables.tablename = c.relname
AND (pg_tables.schemaname = 'summit' or 
pg_tables.schemaname = 'uhelp' or 
pg_tables.schemaname = 'smmtsys' or 
pg_tables.schemaname = 'smmtsec' or 
pg_tables.schemaname = 'smmtccon' )
and ta.attname > 0
)
;

create view smmtsys.v_allcolumns as (
select pg_tables.schemaname,
   pg_tables.tablename,
   pg_attribute.attname
from pg_tables, 
 pg_class, 
 pg_attribute, 
 smmtsys.v_datatype 
where (schemaname = 'smmtccon' or 
   schemaname = 'smmtsec' or 
   schemaname = 'smmtsys' or 
   schemaname = 'summit' or 
   schemaname = 'uhelp' ) and 
  pg_class.relname = pg_tables.tablename and 
  pg_type.typname = pg_tables.tablename and 
  pg_attribute.attrelid = pg_class.relfilenode and

  pg_attribute.attnum > 0 and 
  pg_attribute.atttypid = smmtsys.v_datatype.oid
)
;

create view smmtsys.v_primarykeyind as (
select cols.schemaname ,
   cols.tablename ,
   cols.attname,
   case pks.indisprimary
 when true then 'Y'
 else 'N'
   end as in_primary_key
from smmtsys.v_allcolumns cols left outer join
smmtsys.v_primarykeys pks
on (cols.schemaname = pks.schemaname
and cols.tablename = pks.tablename
and cols.attname= pks.attname)
);

select upper(tbls.schemaname) as "creator", 
   upper(tbls.tablename) as "tname", 
   upper(cols.attname) as "cname", 
   case smmtsys.v_datatype.typname
 when 'bpchar' then 'char'
 else smmtsys.v_datatype.typname
   end as "coltype", 
   case cols.attnotnull
 when true then 'N'
 when false then 'Y'
   end as "nulls",
   length(cols.attrelid) as "length",  
   cols.attndims as "syslength", 
   vpk.in_primary_key,
   cols.attnum as "colno"
from pg_tables tbls, 
 pg_class, 
 pg_attribute cols, 
 pg_type, 
 smmtsys.v_datatype,
 smmtsys.v_primarykeyind vpk
where (tbls.schemaname = 'smmtccon'
   or tbls.schemaname = 'smmtsec'
   or tbls.schemaname = 'smmtsys'
   or tbls.schemaname = 'summit'
   or tbls.schemaname = 'uhelp')
  and pg_class.relname = tbls.tablename
  and pg_type.typname = tbls.tablename
  and cols.attrelid = pg_class.relfilenode
  and cols.attnum > 0
  and cols.atttypid = smmtsys.v_datatype.oid
  and vpk.schemaname = tbls.schemaname
  and vpk.tablename = tbls.tablename
  and vpk.attname = cols.attname
;

This retrieves all of the columns and shows a primary
key indicator for each column.  If someone could put
this logic all into one SQL query, I'd really like to
see it!

I still have a question about how to get the
information about length and precision of a column
from pg_attributes.atttypmod.  are there built-in
functions for PostgreSQL to extract this information?
Additionally, I need to get the column default value
and the comments on the column, but I think I can
figure that out with a little more time.



__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

---(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] Migration from db2 to postgres'

[Tim Andersen]

> I wanted to know how can I migrate all my data
> including the schema and their definitions,from
> IBM's db2 to the postgres.
> 

I have done this with other DBMSs before by writing a
program (using PowerBuilder).  The core concept is
this:

two ODBC datasources (source, target)

for (loop through source's schemas){
  for (loop through source's tables){
for(loop through source's rows){
  "select [row] from [source table]"
  "insert into [target table]"
}
  }
}

The key is this:  I have a view in both databases to
compare system catalog tables across DBMS platforms.
It is actually the view I was trying to create if you
look back at the SQL questions I asked to this mailing
list a few days ago. the subject was: "Re: [SQL] About
primary keys -- made some progress"

In the past, I ran into several problems with
different datatypes and have had to write workarounds
for those (mainly blobs and timestamps).

Concerning referential integrity:  Two options.  You
could wait to add all of your foreign keys until after
all of the data has transferred from the source to the
target.  The other option is to run the nested loops
several times (this "fills in the gaps" and allows
other tables to be filled each time you run it).  I
prefer the second.

I also have a feature to compare the rowcounts of the
source and target so I can be sure all of my data has
been transferred.

I have not yet implemented PostgreSQL into this
application, but I intend to do that over the next few
months.

Tim


__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

---(end of broadcast)---
TIP 8: explain analyze is your friend