Re: Perl::DBI and TYPE of column

2020-03-03 Thread Christoph Moench-Tegeder 
## Matthias Apitz (g...@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

Re: Perl::DBI and TYPE of column

2020-03-03 Thread Adrian Klaver 

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.kla...@aklaver.com

Re: Perl::DBI and TYPE of column

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

> Matthias Apitz  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, ✉ g...@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

2020-03-03 Thread Tom Lane 
Matthias Apitz  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

Perl::DBI and TYPE of column

2020-03-03 Thread Matthias Apitz 
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, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub