Postgresql – PG_bulkload utility postgresql need help

exportimportpostgresql

i am using COPY command to export the data to the out file with changes made to the data before export using option ST_asBinary() using command

COPY (
    SELECT st_asBinary(geom) FROM n_america
) TO '/install/shp_data/n_america_asbin_1_col.txt' 
WITH DELIMITER ',';

now i am facing problem with Restoring back to the other table with geometry column , which is now converted to binary while exporting

i have tried with COPY FROM as well as PG_BULKLOAD utility , but i did't got any solution to do this ,

any one please help

Regards
Deepak M

Best Answer

Your simple solution is to import into a separate table and then use another query to copy the data back, converting to proper format.

Related Solutions

Eliminate database fragmentation (export, drop and re-import tables)

Instead of export/import, you should use

alter table t shrink space;

which is much less dangerous & keeps the table even accessable during the reorganization. After the shrink, you may use below script (originally from Tom Kyte) to find out to what size you can resize (making them smaller) the datafile(s):

set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings  format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report

column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/

select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
/

column cmd format a75 word_wrapped

select 'alter database datafile '''||file_name||''' resize ' ||
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' cmd
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
  and ceil( blocks*&&blksize/1024/1024) -
      ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/

Ms-access – Microsoft Access Export to Excel

The first and easiest option is to select the "External Data" option on the top and select Excel from the Export Data options. This lets you customize the export location and if you want to keep the Access formatting. If you wanted a different format you would have to manually change the column order before exporting to achieve this. If your referring to it not looking the same as before in Excel because it was a table there is a quick fix. Use the keyboard shortcuts ctrl + Home > ctrl + A > ctrl + L to create a table object which formats it nicely.

You can save this manual export process and re run it manually or in a macro if you choose to in the future. This saves time with manually recreating the export each time.

A more advanced option would be creating a VBA module to do the exporting and formatting keyboard shortcut for you. I have the code you can copy and paste from Access to Excel Export Code. You could additionally customize this code to format the columns in the correct order. I would recommend looking into the fld.OrdinalPostion property to do this here Microsoft Field.OrdinalPostion

Best Answer

Related Solutions

Eliminate database fragmentation (export, drop and re-import tables)

Ms-access – Microsoft Access Export to Excel

Related Question