Обсуждение: Perl::DBI and TYPE of column
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
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
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
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
## 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