Re: [GENERAL] Problems importing my 6.3 database into 6.5.1

On Wed, Jul 28, 1999, Herouth Maoz boldly stated
> No way to get around it. You have to change the name of the field in the
> create statement (and all the queries that use it), or put it in double
> quotes (and do the same in all the queries).
>
> While you are at it, do a thorough search for all the SQL92 reserved words,
> and make sure you don't have any other such problems. This will save you
> going through this process again (the main problem is going through all
> your applications, and possibly through your view definitions) in the
> future, when more and more SQL92 words will be put into use in PostgreSQL.

Thanks for the info, and for the quick reply.  For others who find themselves
in a similar situation, here is a quick and dirty perl script that might help
them clean up.  It's not generic, or well written, or suited for the job
without some looking at the errors, but it cleans up my output file by doing
these things:

For every column name that does not already have an _ in it, the script
prefixes tablename_ to the column name.  That should guarantee no keywords are
there.  It skips the pga stuff, and skips columns with _ in their name just
because we had started using a cleaner namespace, and table_name_tn_id is
lousy when tn_id does what we wanted it to do in the first place.

We had char2, char4, char16 datatypes peppered through our tables.  These
appeared to be fine in 6.3, but trash 6.5.1.  the script replaces them with
char(x) instead.

To find out what the problems were, I did the import and tee'd stdout & stderr
to a file which I could then search for ERROR.  This could be done better,
too, but this command got the job done under bash:

psql -e template1 < db.out 2>&1 | tee error.out

You got a good database when grep ERROR error.out gives you no output.

Without further ado, here is the script.  I hope this isn't needed by anyone
out there, and hope it helps anyone who does need it.  It doesn't fix the
apps, but at least now I have a database to test the apps against as I fix
them.

--
Randy Dees                                                             -o)
SCA: Talorgen nei Wrguist                                              /\\
Unix Systems Administrator              Comptek Amherst Systems, Inc. _\_v


#! /usr/bin/perl


$database = shift;
open DATABASE,$database;

while ($line=<DATABASE>) {
  unless ($line =~ /CREATE TABLE/) {
    print $line;
    next;
    }
  my ($create,$tble,$table_name,$table) = split / /, $line, 4;
  if ($table_name =~ /pga_/) {
    print $line;
    next;
    }
  $table =~ s/[\(\)\;]//g;
  chomp $table;
  @table = split /,/, $table;
  @newtable = undef;
  foreach $pair (@table) {
    $pair =~ s/^ //;
    $pair =~ s/char([1-9]+)/char($1)/g;
    if ($pair =~ /_/) {
        push @newtable,$pair;
    }
    else {
        $pair = "${table_name}_${pair}";
        push @newtable,$pair;
        }
    }
  $newtable = undef;
  foreach $pair (@newtable) {
    if ($newtable) {
      $newtable = $newtable .", $pair";
      }
    else {$newtable = $pair;}
    }
  print "$create $tble $table_name ($newtable);\n";

  }