Hyperstage > Importing and Exporting Data in Hyperstage > Hyperstage COPY TO Syntax

Hyperstage COPY TO Syntax

Topics:

COPY TO can be used for export Hyperstage table data to a file. It allows for fast exporting of data from a select statement. This is equivalent to SELECT INTO OUTFILE in MySQL.

The COPY TO syntax works in a manner similar to PostgreSQL (COPY TO), but supports a different set of options. The examples and table below outline these differences.

Usage Examples

copy (select ...) to '/tmp/data' with (format csv, delimiter ';')
copy (select ...) to '/tmp/data' with (format txt_variable, lines_terminated_by e'\n', delimiter ';')
copy (select ...) to '/tmp/data' with (format ib_binary)
 
COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' | PROGRAM 'command' | STDOUT }
    [ [ WITH ] ( option [, ...] ) ]

where:

option

Can be one of the following:

  • format: {'txt_variable' | 'infobright' | 'ib_binary'}
  • fields-terminated-by
  • fields-enclosed-by
  • escaped-by
  • data-chartset 'encoding_name'
  • lines_terminated_by
  • reject-file-path
  • abort-on-count
  • abort-on-threshold
  • input type {'client' | 'server'}
  • timeout

For Hyperstage (ENGINE=INFOBRIGHT) tables, the following formats are supported:

  • Hyperstage formats: txt_variable, ib_binary
  • PostgreSQL formats: text, csv, binary

OPTIONS FOR DIFFERENT FORMATS

OPTION

TEXT, CSV, BINARY

TXT_VARIABLE

IB_BINARY

oids

As in PostgreSQL

Not supported

Not supported

null

As in PostgreSQL

Supported

Not supported

header

As in PostgreSQL

Not supported

Not supported

force_quote

As in PostgreSQL

Not supported

Not supported

force_not_null

As in PostgreSQL

Not supported

Not supported

delimiter

As in PostgreSQL

Supported

Not supported

quote

As in PostgreSQL

Supported

Not supported

escape

As in PostgreSQL

Supported

Not supported

encoding

As in PostgreSQL

Supported

Not supported

lines_terminated_by

Not supported

Supported

Not supported

reject_file_path

Not supported

Not supported

Not supported

abort_on_count

Not supported

Not supported

Not supported

abort_on_threshold

Not supported

Not supported

Not supported

pipe_mode

Not supported

Supported

Supported

timeout

Not supported

Not supported

Not supported

WebFOCUS

Feedback