Postgres Pro
Downloads
Home   >   mailing lists

Thread: Perl::DBI and TYPE of column

Perl::DBI and TYPE of column

From
Matthias Apitz
Date:
Hello,

We unload Sybase and Oracle data to migrate the database to PostgreSQL.
The loading is done very fast with PostgreSQL's COPY command.

During unload trailing blanks in all columns are discarded, because they
would cause problems during loading for INT and DATE columns. The
discarding is done like this after fetching the row into the array
@row_ary:

    ...
    # SRP-25024: support for PostgreSQL: we remove on export trailing blanks
    foreach my $i (0..$#row_ary) {
        $row_ary[$i] =~ s/\s+$//;
        # but for CHAR columns we keep one
        # print $dba->{'sth'}->{NAME}->[$i] . " " . $dba->{'sth'}->{TYPE}->[$i] . "\n"; 
        # it seems that VARCHAR in Sybase is TYPE=1 and in Oracle TYPE=12
        # see also
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36273.1570/html/sprocs/CIHHGDBC.htm
        # and ftp://sqlstandards.org/SC32/SQL_Registry/
        #
        if ($dba->{'sth'}->{TYPE}->[$i] == 1 || $dba->{'sth'}->{TYPE}->[$i] == 12)  {
            $row_ary[$i] =~ s/^$/ /;
        }
    }

My question here is: How I could get a copy of the document 
ftp://sqlstandards.org/SC32/SQL_Registry/

Any copy available here in this list? Thanks

    matthias
-- 
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

Re: Perl::DBI and TYPE of column

From
Tom Lane
Date:
Matthias Apitz <guru@unixarea.de> writes:
> During unload trailing blanks in all columns are discarded, because they
> would cause problems during loading for INT and DATE columns.

Really?

regression=# select '123  '::int;
 int4 
------
  123
(1 row)

regression=# select '12-02-2019  '::date;
    date    
------------
 2019-12-02
(1 row)

            regards, tom lane

Re: Perl::DBI and TYPE of column

From
Matthias Apitz
Date:
El día Dienstag, März 03, 2020 a las 09:36:32 -0500, Tom Lane escribió:

> Matthias Apitz <guru@unixarea.de> writes:
> > During unload trailing blanks in all columns are discarded, because they
> > would cause problems during loading for INT and DATE columns.
> 
> Really?
> 
> regression=# select '123  '::int;
>  int4 
> ------
>   123
> (1 row)
> 
> regression=# select '12-02-2019  '::date;
>     date    
> ------------
>  2019-12-02
> (1 row)

The problem occurs when loading CSV data like ...|    |... into an INT
column with COPY. I could make you an exact example.

But this wasn't my question, my question is where the document is.

    matthias

-- 
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

Re: Perl::DBI and TYPE of column

From
Adrian Klaver
Date:
On 3/3/20 6:02 AM, Matthias Apitz wrote:
> Hello,
> 
> We unload Sybase and Oracle data to migrate the database to PostgreSQL.
> The loading is done very fast with PostgreSQL's COPY command.
> 
> During unload trailing blanks in all columns are discarded, because they
> would cause problems during loading for INT and DATE columns. The
> discarding is done like this after fetching the row into the array
> @row_ary:
> 
>      ...
>      # SRP-25024: support for PostgreSQL: we remove on export trailing blanks
>      foreach my $i (0..$#row_ary) {
>          $row_ary[$i] =~ s/\s+$//;
>          # but for CHAR columns we keep one
>          # print $dba->{'sth'}->{NAME}->[$i] . " " . $dba->{'sth'}->{TYPE}->[$i] . "\n";
>          # it seems that VARCHAR in Sybase is TYPE=1 and in Oracle TYPE=12
>          # see also
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36273.1570/html/sprocs/CIHHGDBC.htm
>          # and ftp://sqlstandards.org/SC32/SQL_Registry/
>          #
>          if ($dba->{'sth'}->{TYPE}->[$i] == 1 || $dba->{'sth'}->{TYPE}->[$i] == 12)  {
>              $row_ary[$i] =~ s/^$/ /;
>          }
>      }
> 
> My question here is: How I could get a copy of the document
> ftp://sqlstandards.org/SC32/SQL_Registry/
> 
> Any copy available here in this list? Thanks

All I could find:

https://grokbase.com/t/perl/dbi-users/074q99ddsn/registry-of-values-for-ansi-x3-135-and-iso-iec-9075-sql-standards


> 
>     matthias
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Perl::DBI and TYPE of column

From
Christoph Moench-Tegeder
Date:
## Matthias Apitz (guru@unixarea.de):

> My question here is: How I could get a copy of the document 
> ftp://sqlstandards.org/SC32/SQL_Registry/

Methinks that the most interesting constants of that are already in
DBI (export tag sql_types) - man DBI, /sql_types. Is that the data
you're looking for? Also look at DBD::Pg, pg_types.

Regards,
Christoph

-- 
Spare Space