This is a multi-part message in MIME format.
------_=_NextPart_001_01C8C48A.2ABE01F3
Content-Type: text/plain;
charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi,
I'm trying to im****t some relatively huge (~300GiB) set of data from
Microsoft SQL Server database to PostgreSQL. For this purpose, I use
bcp.exe (bulk copy utility) comes with MSSQL. Despite there are command
line arguments which you can specify batch size, escape characters, null
fields, etc.; %90 of these arguments are unsuprisingly ignored by
bcp.exe. The problem is, bcp.exe
- Uses nothing to specify null fields, which is equivalent to
COPY ... WITH NULL AS ''
in PostgreSQL.
- Uses \x00 character to specify empty strings.
(Sorry not any single part of this mess is configurable.) To
successfully accept such an input during "COPY ... FROM ..." in
PostgreSQL, I decided to write middleware triggers to tables including
columns of type character varying. Below is the procedure I come up with
CREATE OR REPLACE FUNCTION create_bcp_fix_triggers (_table_schema =
text)
RETURNS void AS $$
DECLARE
_table record;
_column record;
_create_stmt text;
BEGIN
SET standard_conforming_strings TO off;
SET escape_string_warning TO off;
-- Find every table having a column of type 'character varying'.
FOR _table
IN SELECT table_name
FROM information_schema.columns
WHERE table_schema =3D _table_schema AND
data_type =3D 'character varying'
GROUP BY table_name
ORDER BY table_name
LOOP _create_stmt =3D 'BEGIN;\n' ||
'CREATE OR REPLACE\n' ||
'FUNCTION ' || _table.table_name || '_bcp_fix =
()\n' ||
'RETURNS "trigger" AS $bcp-fix$\n' ||
'BEGIN\n';
-- Create appropriate bcp.exe fix clauses for every found =
column.
FOR _column
IN SELECT column_name
FROM information_schema.columns
WHERE table_schema =3D _table_schema AND
table_name =3D _table.table_name
LOOP _create_stmt =3D _create_stmt ||
' IF NEW.' || _column.column_name || ' =
=3D E''\0'' THEN\n' ||
' NEW.' || _column.column_name || ' =
=3D ''''\n' ||
' END IF;\n';
END LOOP;
_create_stmt =3D _create_stmt ||
' RETURN NEW;\n' ||
'END;' ||
'$bcp-fix$ LANGUAGE plpgsql;\n' ||
'CREATE TRIGGER ' || _table.table_name || =
'_bcp_fix\n' ||
' BEFORE INSERT ON ' || _table.table_name || =
'\n' ||
' FOR EACH ROW EXECUTE ' ||
' PROCEDURE ' || _table.table_name || =
'_bcp_fix();\n' ||
'COMMIT;';
EXECUTE _create_stmt;
END LOOP;
END;
$$ LANGUAGE plpgsql;
But executing this procedure fails for some reason I couldn't
understand.
> SELECT public.create_bcp_fix_triggers('commsrv');
ERROR: syntax error at or near "AS $"
LINE 4: RETURNS "trigger" AS $bcp-fix$
^
QUERY: BEGIN;
CREATE OR REPLACE
FUNCTION xyz_bcp_fix ()
RETURNS "trigger" AS $bcp-fix$
BEGIN
IF NEW.foo =3D E' NEW.foo =3D ''
END IF;
IF NEW.bar =3D E' NEW.bar =3D ''
END IF;
RETURN NEW;
END;$bcp-fix$ LANGUAGE plpgsql;
CREATE TRIGGER xyz_bcp_fix
BEFORE INSERT ON xyz
FOR EACH ROW EXECUTE PROCEDURE xyz_bcp_fix();
COMMIT;
CONTEXT: PL/pgSQL function "create_bcp_fix_triggers" line 41 at
EXECUTE statement
Can anybody help me to spot the problem?
Regards.
P.S. For data transfer during migratition, I first considered using "EMS
Data Ex****t for SQL Server" tool, but it lacks of performance.
(~1000rows/sec) Namely, I'm open to any bcp.exe alternatives.
------_=_NextPart_001_01C8C48A.2ABE01F3
Content-Type: text/html;
charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
6.5.7652.24">
<TITLE>bcp.exe Fix Triggers</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=3D2>Hi,<BR>
<BR>
I'm trying to im****t some relatively huge (~300GiB) set of data from<BR>
Microsoft SQL Server database to PostgreSQL. For this purpose, I use<BR>
bcp.exe (bulk copy utility) comes with MSSQL. Despite there are =
command<BR>
line arguments which you can specify batch size, escape characters, =
null<BR>
fields, etc.; %90 of these arguments are unsuprisingly ignored by<BR>
bcp.exe. The problem is, bcp.exe<BR>
<BR>
- Uses nothing to specify null fields, which is equivalent to<BR>
<BR>
COPY ... WITH NULL AS ''<BR>
<BR>
in PostgreSQL.<BR>
<BR>
- Uses \x00 character to specify empty strings.<BR>
<BR>
(Sorry not any single part of this mess is configurable.) To<BR>
successfully accept such an input during "COPY ... FROM ..." =
in<BR>
PostgreSQL, I decided to write middleware triggers to tables =
including<BR>
columns of type character varying. Below is the procedure I come up =
with<BR>
<BR>
CREATE OR REPLACE FUNCTION create_bcp_fix_triggers (_table_schema =
text)<BR>
RETURNS void AS $$<BR>
DECLARE<BR>
=
_table record;<BR>
_column =
record;<BR>
_create_stmt text;<BR>
BEGIN<BR>
SET standard_conforming_strings TO =
off;<BR>
SET escape_string_warning TO off;<BR>
-- Find every table having a column of =
type 'character varying'.<BR>
FOR _table<BR>
IN SELECT table_name<BR>
FROM =
information_schema.columns<BR>
WHERE =
table_schema =3D _table_schema AND<BR>
&=
nbsp; data_type =3D 'character varying'<BR>
GROUP BY table_name<BR>
ORDER BY table_name<BR>
LOOP _create_stmt =3D 'BEGIN;\n' ||<BR>
&=
nbsp; =
'CREATE OR REPLACE\n' ||<BR>
&=
nbsp; =
'FUNCTION ' || _table.table_name || '_bcp_fix ()\n' ||<BR>
&=
nbsp; =
'RETURNS "trigger" AS $bcp-fix$\n' ||<BR>
&=
nbsp; =
'BEGIN\n';<BR>
-- Create =
appropriate bcp.exe fix clauses for every found column.<BR>
FOR _column<BR>
IN SELECT =
column_name<BR>
&=
nbsp; FROM information_schema.columns<BR>
&=
nbsp; WHERE table_schema =3D _table_schema AND<BR>
&=
nbsp; table_name =
=3D _table.table_name<BR>
LOOP _create_stmt =3D =
_create_stmt ||<BR>
&=
nbsp; &n=
bsp; ' IF NEW.' || =
_column.column_name || ' =3D E''\0'' THEN\n' ||<BR>
&=
nbsp; &n=
bsp; ' NEW.' =
|| _column.column_name || ' =3D ''''\n' ||<BR>
&=
nbsp; &n=
bsp; ' END IF;\n';<BR>
END LOOP;<BR>
_create_stmt =3D =
_create_stmt ||<BR>
&=
nbsp; =
' RETURN NEW;\n' ||<BR>
&=
nbsp; =
'END;' ||<BR>
&=
nbsp; =
'$bcp-fix$ LANGUAGE plpgsql;\n' ||<BR>
&=
nbsp; =
'CREATE TRIGGER ' || _table.table_name || '_bcp_fix\n' ||<BR>
&=
nbsp; =
' BEFORE INSERT ON ' || _table.table_name || '\n' =
||<BR>
&=
nbsp; =
' FOR EACH ROW EXECUTE ' ||<BR>
&=
nbsp; =
' PROCEDURE ' || _table.table_name || '_bcp_fix();\n' =
||<BR>
&=
nbsp; =
'COMMIT;';<BR>
EXECUTE =
_create_stmt;<BR>
END LOOP;<BR>
END;<BR>
$$ LANGUAGE plpgsql;<BR>
<BR>
But executing this procedure fails for some reason I couldn't<BR>
understand.<BR>
<BR>
> SELECT public.create_bcp_fix_triggers('commsrv');<BR>
ERROR: syntax error at or near "AS $"<BR>
LINE 4: RETURNS "trigger" AS $bcp-fix$<BR>
&=
nbsp; &n=
bsp; ^<BR>
QUERY: BEGIN;<BR>
CREATE OR REPLACE<BR>
FUNCTION xyz_bcp_fix ()<BR>
RETURNS "trigger" AS $bcp-fix$<BR>
BEGIN<BR>
IF NEW.foo =3D =
E' NEW.foo =3D ''<BR>
END IF;<BR>
IF NEW.bar =3D =
E' NEW.bar =3D ''<BR>
END IF;<BR>
RETURN NEW;<BR>
END;$bcp-fix$ LANGUAGE plpgsql;<BR>
CREATE TRIGGER xyz_bcp_fix<BR>
BEFORE INSERT ON xyz<BR>
FOR EACH ROW =
EXECUTE PROCEDURE xyz_bcp_fix();<BR>
COMMIT;<BR>
CONTEXT: PL/pgSQL function =
"create_bcp_fix_triggers" line 41 at<BR>
EXECUTE statement<BR>
<BR>
Can anybody help me to spot the problem?<BR>
<BR>
<BR>
Regards.<BR>
<BR>
P.S. For data transfer during migratition, I first considered using =
"EMS<BR>
Data Ex****t for SQL Server" tool, but it =
lacks of performance.<BR>
(~1000rows/sec) Namely, I'm open to any bcp.exe =
alternatives.</FONT>
</P>
</BODY>
</HTML>
------_=_NextPart_001_01C8C48A.2ABE01F3--
|
