pg-cmd - PostgreSQL interactive and batch query command-line tool »

Download pg-cmd Free Trial »

Load PostgreSQL data from flat file


delimeter: delimiter char ("|",",","#","TAB","WHITESPACE")

quote: quote char (quote=' => single quotes, quote=" => double quotes)

header: y|n, data file contains field name at first line

schema: schema name, use this parameter to load specific schema's data, if not set, use logon user's default schema

table: table name

datafile: data file name

loadtype: append|replace|update|upsert
	append: appends the new rows to the table.
	replace: all rows in the table are deleted and the new data is loaded.
	update: update the rows by the new data.
	upsert upsert the rows by the new data.

updatekey: key feild to update/upsert with, usually use primary key.

dateformat: date format (e.g. "yyyy-mm-dd hh24:mi:ss")

timestampformat: timestamp format (e.g. '')

nullif: specifies that the column should be loaded as NULL 
("BLANKS" for zero length strings being loaded into numeric columns)

badfile: write the content that can not load to this badfile

errors:  maxinum error count

quit = y|n (quit pg-cmd after run in batch mode)


In interactive mode:

pg-cmd> load table=media_messages 
      > datafile=f:\media_messages.txt 
      > delimiter=, 
      > quote=" 
      > header=y;

In batch mode:
my-cmd dsn="pan:withdata@" task=load table=media_messages datafile=f:\media_messages.txt delimiter=, quote=" header=y quit=y