Hyperstage COPY TO Syntax

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=BRIGHTHOUSE) 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