Re: URGENT: temporary table not recognized?

On Friday, January 06, 2012 4:16:09 pm Phoenix Kiula wrote:
> On Fri, Jan 6, 2012 at 6:54 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
> > Try:
> > copy vl from 'data.txt' WITH CSV DELIMITER '|';
>
> Doesn't work. Can't see what the different in CSV is from a text file.
> Same errors are thrown.
>
> > If that doesn't work take a look at pgloader:
> > http://pgfoundry.org/projects/pgloader/
>
> Wow, another geeky tool. Hard to find documentation. Archaic
> presentation, no simple steps to install and get using. Anyway doesn't
> seem to provide the options I need
> (http://pgloader.projects.postgresql.org/) --

Install:
Download
tar -xzvf pgloader-2.3.2.tar.gz
cd pgloader-2.3.2/
sudo make

Very difficult.

http://pgloader.projects.postgresql.org/
>
> a) Ability to assign more than one NULL value
null
You can configure here how null value is represented into your flat data file.

This parameter is optional and defaults to '' (that is empty string). If defined
on a table level, this local value will overwrite the global one.

empty_string
You can configure here how empty values are represented into your flat data file.

This parameter is optional and defaults to \ (that is backslash followed by
space). If defined on a table level, this local value will overwrite the global
one.

reformat
Use this option when you need to preprocess some column data with pgloader
reformatting modules, or your own. The value of this option is a comma separated
list of columns to rewrite, which are a colon separated list of column name,
reformat module name, reformat function name. Here's an example to reformat
column dt_cx with the mysql.timestamp() reformatting function:

reformat = dt_cx:mysql:timestamp
See global setting option reformat_path for configuring where pgloader will look
for reformat packages and modules.

If you want to write a new formating function, provide a python package called
reformat (a directory of this name containing an empty __init__.py file will do)
and place in there arbitrary named modules (foo.py files) containing functions
with the following signature:

def bar(reject, input)
The reject object has a log(self, messages, data = None) method for you to log
errors into section.rej.log and section.rej files.

> b) Ability to ignore lines altogether that have any problem

reject_log
In case of errors processing input data, a human readable log per rejected input
data line is produced into the reject_log file.
reject_data
In case of errors processing input data, the rejected input line is appended to
the reject_data file.

>
> Really, other databases have mechanisms to ignore "problem lines"
> while copying. Does Postgresql allow me to *ignore* lines while
> COPYING?

No.

I await with bated breath your most excellent patch to COPY.


--
Adrian Klaver
adrian.klaver@gmail.com