PostgreSQL and DB2 use different styles of conventions to represent NULL. In PostgreSQL, the convention is to have “N” in the data to be loaded with the COPY command. In DB2, the LOAD command can be set to consider and empty field as NULL. Thus, to convert from DB2 to POstgreSQL style data dump, assuming TAB as the delimiter, we would use:
sed -e 's/tt/t\Nt/g' -e 's/t$/t\N/g' -e 's/tt/t\Nt/g' db2_file > pg_file
Conversely, to convert from PostgreSQL to DB2 style, with TAB delimited data:
sed -e 's/t\Nt/tt/g' -e 's/t\N$/t/g' -e 's/t\Nt/tt/g' db2_file > pg_file
The reason we need to go with 3 steps is the following:
- step 1: convert TAB NULL TAB
- step2: convert TAB NULL at the end of line
- step3: convert TAB NULL TAB from sequences where originally there was TAB NULL TAB NULL
All this has been tested on AIX and Linux.
