ERROR: relation . . . does not exist

On 28/08/2008 22:29, Albretch Mueller wrote:

jpk=# COPY j2sdk_1_4_2_18_binfls_md5sum FROM
'/media/hda3/prjx/JPack/REF/LANDMARKS/PROFILES/20080828104627_j2sdk-1_4_2_18-linux-i586.binfls.md5sum.txt'
CSV HEADER;
ERROR: relation "j2sdk_1_4_2_18_binfls_md5sum" does not exist
~
What is going on here?

Just what it says - the relation doesn't exist 'cos you haven't created
it yet. You have to create the table first before you can COPY into it.

How can I fix that error, effectively creating and populating the
tables from csv files?

Just include a CREATE TABLE statement in the same script before the COPY.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

On Thu, Aug 28, 2008 at 5:40 PM, Raymond O'Donnell <rod@iol.ie> wrote:

You have to create the table first before you can COPY into it.

~
Well, based on how the statement reads I made the wrong assumption of
thinking that PG would be smart enough to make ends-meat with the
data that it gets once you indicate:
~
1) the header names (to be mapped to column names)
~
2) the data from which PG could easily figure out the types for each
column, since it is CSV data
~
That may/should become a RFE
~
Thanks
lbrtchx
~

On Thursday 28 August 2008 3:14:01 pm Albretch Mueller wrote:

On Thu, Aug 28, 2008 at 5:40 PM, Raymond O'Donnell <rod@iol.ie> wrote:

You have to create the table first before you can COPY into it.

~
Well, based on how the statement reads I made the wrong assumption of
thinking that PG would be smart enough to make ends-meat with the
data that it gets once you indicate:
~
1) the header names (to be mapped to column names)
~
2) the data from which PG could easily figure out the types for each
column, since it is CSV data

Define easily. A CSV column has fields with 1's and 0's should that be an
integer field or a boolean field? A CSV column has a series of text entries
none of which are greater then 25 characters long. Does that mean a datatype
of varchar(25) or is it just an artifact of the data to date? You have a CSV
column with text strings of format 'YYYY/MM/DD' . Do you want them stored as
strings or dates? I could go on, but the point is that table data types
require some thought on the part of the DBA.

~
That may/should become a RFE
~
Thanks
lbrtchx
~

--
Adrian Klaver
aklaver@comcast.net

On Thu, Aug 28, 2008 at 7:50 PM, Adrian Klaver <aklaver@comcast.net> wrote:

Define easily.

~
OK, let me try to outline the approach I would go for:
~
I think "COPY FROM CSV" should have three options, namely:
~
1) the way we have used it in which you create the table first
~
2) another way in which defaults are declared, generally as:
~
2.1) aggressive: data type, value and formatting analysis is done; if
only 1 or 0 are found declare then a BOOLEAN, if repeated data is
found (say state codes) and the stratification nodes cover the rest of
the data, stratify the data out to other extra table (they have a name
I can't recall now), index it ..., if data is kind of numeric with
front slashes and/or hyphen could they possibly be dates? if they are
definitelly dates convert them to bigint (and do the formatting in the
presentation code (also this a win-win situation with i18n code)) ...
~
2.2) conservative: data type and value, but no formatting analysis is
done and the greater encompassing data type is selected, say for 1 or
0 data use bytes [0, 255], for bytes use int, if something could be
encoded as char(2), use varchar instead, . . .
~
2.3) dumn: just use the coarsest data type possible; bigint for
anything that looks like a number and varchar for the rest
~
the "dumn" option should suggest to the DBA the option they are
using, quantified consequences for their desicions (larger DBs for no
reason, approx. reduction in speed, . .) and how not to be "dumn"
~
3) or you could define "import templates" declaring which specific
data types to use for data in a certain way, which could be declared
per column using regexps
~

I could go on, but the point is that table data types require some thought on the part of the DBA.

~
Well, it still requires their minds and input, but they will have
jobs even if they get some help, don't you think so ;-)
~
lbrtchx

On Sat, Aug 30, 2008 at 08:23:25AM -0400, Albretch Mueller wrote:

OK, let me try to outline the approach I would go for:
~
I think "COPY FROM CSV" should have three options, namely:

I think you're confusing postgresql with a spreadsheet program. A
database is designed to take care of your data and ensure its
integrity. As such it requires a little more thought.

There are client programs which will do this for you, perhaps you wan
one of those?

What's so hard about:

create table foo (a text, b text);

After which your COPY will complete fine.

2.1) aggressive: data type, value and formatting analysis is done; if
only 1 or 0 are found declare then a BOOLEAN, if repeated data is
found (say state codes) and the stratification nodes cover the rest of
the data, stratify the data out to other extra table (they have a name
I can't recall now), index it ..., if data is kind of numeric with
front slashes and/or hyphen could they possibly be dates? if they are
definitelly dates convert them to bigint (and do the formatting in the
presentation code (also this a win-win situation with i18n code)) ...

PostgreSQL has 60+ types and many look like eachother. How do you
propose to differentiate?

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

I think you're confusing postgresql with a spreadsheet program.

~
I wonder what makes you think so
~

There are client programs which will do this for you, perhaps you wan one of those?

~
Well, then obviously there is the need for it and you were not
successful enough at convincing these developers that they were
"confusing postgresql with a spreadsheet program"
~

PostgreSQL has 60+ types and many look like eachother. How do you propose to differentiate?

~
Data Types are basically about value ranges (how many bits do you
need to hold the value) and formatting. IMHO, finding an optimal
[im|ex]port reasoning among 60+ types should not be that much of a big
deal. In fact as a data analyst I have exported and imported CSV data
a whole lot and in many occasions it required some extra custom
coding. I may as well consolidate my code as a whole jcsvport library
in java and start an OS project when I find the time to so
~
lbrtchx

On Aug 30, 2008, at 6:26 AM, Albretch Mueller wrote:

Well, then obviously there is the need for it and you were not
successful enough at convincing these developers that they were
"confusing postgresql with a spreadsheet program"

The behavior you are looking for is typical of a spreadsheet, because
spreadsheet programs (generally; I'm sure there are exceptions) don't
have the notion of a schema; each cell can hold its own particular
type. That being said, the automatic type-guessing that Excel, say,
provides is far from foolproof; I've probably spent more time
cleaning up Excel's bad guesses than would have been saved by my just
specifying a type for each column.

As has been noted, text representation of values are extremely
ambiguous as of which Postgres type they mean... and, of course, you
could have user-defined domains and types as well. It's true that it
could take a wild guess, but that's not a traditional part of a
database engine.

That being said, it would not be too hard to write a client that
accepted a CSV or tab-delimited file, parsed the header into column
names, and then scanned the values of the columns to take a
reasonable guess as to the column type from a highly limited set of
possibilities. This is probably one of those classic "twenty lines
of Perl" problems.

It doesn't seem as though COPY INTO is the right place for that,
since the particular guesses and set of types that one would make
strike me as very closely tied to your particular application domain.

On Saturday 30 August 2008 5:23:25 am Albretch Mueller wrote:

On Thu, Aug 28, 2008 at 7:50 PM, Adrian Klaver <aklaver@comcast.net> wrote:

Define easily.

~
OK, let me try to outline the approach I would go for:
~
I think "COPY FROM CSV" should have three options, namely:
~
1) the way we have used it in which you create the table first
~
2) another way in which defaults are declared, generally as:
~
2.1) aggressive: data type, value and formatting analysis is done; if
only 1 or 0 are found declare then a BOOLEAN, if repeated data is
found (say state codes) and the stratification nodes cover the rest of
the data, stratify the data out to other extra table (they have a name
I can't recall now), index it ..., if data is kind of numeric with
front slashes and/or hyphen could they possibly be dates? if they are
definitelly dates convert them to bigint (and do the formatting in the
presentation code (also this a win-win situation with i18n code)) ...
~
2.2) conservative: data type and value, but no formatting analysis is
done and the greater encompassing data type is selected, say for 1 or
0 data use bytes [0, 255], for bytes use int, if something could be
encoded as char(2), use varchar instead, . . .
~
2.3) dumn: just use the coarsest data type possible; bigint for
anything that looks like a number and varchar for the rest
~
the "dumn" option should suggest to the DBA the option they are
using, quantified consequences for their desicions (larger DBs for no
reason, approx. reduction in speed, . .) and how not to be "dumn"
~
3) or you could define "import templates" declaring which specific
data types to use for data in a certain way, which could be declared
per column using regexps
~

I could go on, but the point is that table data types require some
thought on the part of the DBA.

~
Well, it still requires their minds and input, but they will have
jobs even if they get some help, don't you think so ;-)
~
lbrtchx

This is a combination of more work then necessary and putting the cart after
the horse. All I can see happening is delaying the point of decision to a
later time and or dumping the decision process on someone else. There is
already a "dumb" solution that has been brought many times on this list. It
involve creating a holding table that has text only fields and copying the
data into and then moving the data from there to a final table. As far as
import templates I suggest looking at:
http://pgloader.projects.postgresql.org/
It also addresses some of your other suggestions. It does not automatically
create a table though.

--
Adrian Klaver
aklaver@comcast.net

On Aug 30, 2008, at 9:19 AM, Christophe wrote:

On Aug 30, 2008, at 6:26 AM, Albretch Mueller wrote:

Well, then obviously there is the need for it and you were not
successful enough at convincing these developers that they were
"confusing postgresql with a spreadsheet program"

The behavior you are looking for is typical of a spreadsheet,
because spreadsheet programs (generally; I'm sure there are
exceptions) don't have the notion of a schema; each cell can hold
its own particular type. That being said, the automatic type-
guessing that Excel, say, provides is far from foolproof; I've
probably spent more time cleaning up Excel's bad guesses than would
have been saved by my just specifying a type for each column.

As has been noted, text representation of values are extremely
ambiguous as of which Postgres type they mean... and, of course, you
could have user-defined domains and types as well. It's true that
it could take a wild guess, but that's not a traditional part of a
database engine.

That being said, it would not be too hard to write a client that
accepted a CSV or tab-delimited file, parsed the header into column
names, and then scanned the values of the columns to take a
reasonable guess as to the column type from a highly limited set of
possibilities. This is probably one of those classic "twenty lines
of Perl" problems.

About 150 line of perl[1]A validator (regex) for each data type, then for each column track which data types it may be, as you scan through the file. Use the relative priorities of different data types to assign something appropriate for each column, then do a second pass translating the format into something Postgresql is comfortable with and feed it into pg_putcopydata.. It can actually work quite well, but is
entirely a client-side problem. None of that sort of heuristics should
go anywhere near COPY in.

It doesn't seem as though COPY INTO is the right place for that,
since the particular guesses and set of types that one would make
strike me as very closely tied to your particular application domain.

Cheers,
Steve

[1]: A validator (regex) for each data type, then for each column track which data types it may be, as you scan through the file. Use the relative priorities of different data types to assign something appropriate for each column, then do a second pass translating the format into something Postgresql is comfortable with and feed it into pg_putcopydata.
which data types it may be, as you scan through the file. Use the
relative priorities of different data types to assign something
appropriate for each column, then do a second pass translating the
format into something Postgresql is comfortable with and feed it into
pg_putcopydata.

spreadsheet programs (generally; I'm sure there are exceptions) don't have the notion of a schema; each cell can hold its own particular type.

~
Oh, now I see what Martin meant!
~

that's not a traditional part of a database engine.

~
well, yeah! I would totally agree with you, but since I doubt very
much "COPY FROM CSV" is part of the SQL standard to beging with, why
not spice it up a little more?
~

This is probably one of those classic "twenty lines of Perl" problems.

~
java since 1.5 comes with a full blown, PERL-like regexp engine
~

I suggest looking at: http://pgloader.projects.postgresql.org/
[1] A validator (regex) for each data type . . .

~
that sort of things was what i was talking about, but I would go
quite a bit farther
~
Thanks
lbrtchx

On Saturday 30 August 2008 9:42:19 am Adrian Klaver wrote:

On Saturday 30 August 2008 5:23:25 am Albretch Mueller wrote:

On Thu, Aug 28, 2008 at 7:50 PM, Adrian Klaver <aklaver@comcast.net>

wrote:

Define easily.

~
OK, let me try to outline the approach I would go for:
~
I think "COPY FROM CSV" should have three options, namely:
~
1) the way we have used it in which you create the table first
~
2) another way in which defaults are declared, generally as:
~
2.1) aggressive: data type, value and formatting analysis is done; if
only 1 or 0 are found declare then a BOOLEAN, if repeated data is
found (say state codes) and the stratification nodes cover the rest of
the data, stratify the data out to other extra table (they have a name
I can't recall now), index it ..., if data is kind of numeric with
front slashes and/or hyphen could they possibly be dates? if they are
definitelly dates convert them to bigint (and do the formatting in the
presentation code (also this a win-win situation with i18n code)) ...
~
2.2) conservative: data type and value, but no formatting analysis is
done and the greater encompassing data type is selected, say for 1 or
0 data use bytes [0, 255], for bytes use int, if something could be
encoded as char(2), use varchar instead, . . .
~
2.3) dumn: just use the coarsest data type possible; bigint for
anything that looks like a number and varchar for the rest
~
the "dumn" option should suggest to the DBA the option they are
using, quantified consequences for their desicions (larger DBs for no
reason, approx. reduction in speed, . .) and how not to be "dumn"
~
3) or you could define "import templates" declaring which specific
data types to use for data in a certain way, which could be declared
per column using regexps
~

I could go on, but the point is that table data types require some
thought on the part of the DBA.

~
Well, it still requires their minds and input, but they will have
jobs even if they get some help, don't you think so ;-)
~
lbrtchx

This is a combination of more work then necessary and putting the cart
after the horse. All I can see happening is delaying the point of decision

Lets try this again. The cart before the horse.
Memo to self:
1) Drink sufficient coffee.
2) Answer email.

to a later time and or dumping the decision process on someone else. There
is already a "dumb" solution that has been brought many times on this list.
It involve creating a holding table that has text only fields and copying
the data into and then moving the data from there to a final table. As far
as import templates I suggest looking at:
http://pgloader.projects.postgresql.org/
It also addresses some of your other suggestions. It does not automatically
create a table though.

--
Adrian Klaver
aklaver@comcast.net

--
Adrian Klaver
aklaver@comcast.net

On Aug 30, 2008, at 10:33 AM, Albretch Mueller wrote:

well, yeah! I would totally agree with you, but since I doubt very
much "COPY FROM CSV" is part of the SQL standard to beging with, why
not spice it up a little more?

I'd guess that coming up with a general algorithm to guess the type
from a column of CSV text would satisfy no one, since we'd always
miss a particular case that is important to someone (are times local
or UTC? Should we assume integer or float? Varchar or text?), and the
option is a forest of switches that would be extremely complex and
error prone.

This sounds very much like an application-domain problem, best solved
in the application domain.

... are times local or UTC

~
this is a rather semantic, not a syntactic issue that some code could
NOT decide based on the data it reads
~

Should we assume integer or float?

~
is a dot anywhere in the data you read in for that particular column? ...
~

Varchar or text?

~
Is the length of the data read in always less than 255 bytes ( or
characters?)? ...
~
You have made clear to me why my attempt for a RFE for COPY FROM CVS
has found some technical resistance/disagreement, but I still think my
idea even if not so popular for concrete and cultural reasons makes at
least sense to some people
~
DBAs ussualy have a mental map of the data they have on each table,
etc; whereas as a data analyst you find yourself constantly reading
in, cleasing and marshaling data from which you have no prior
knowledge
~
lbrtchx

You have made clear to me why my attempt for a RFE for COPY FROM CVS
has found some technical resistance/disagreement, but I still think my
idea even if not so popular for concrete and cultural reasons makes at
least sense to some people

It's a perfectly reasonable problem to want to solve; the question is
whether COPY is the right place to solve it. I would think that a tool
that reads the CSV data and produces a proposed schema definition for
the table would be a more generally-useful approach.

Albretch Mueller wrote:

Varchar or text?

~
Is the length of the data read in always less than 255 bytes ( or
characters?)? ...

It may be more limited than that by application-domain-specific constraints -
e.g., a license plate might be statutorily limited to eight characters.

It might be coincidence that the input happens to fit within 255 characters
when future inputs might not. One cannot make certain statements about
whether "data read in always [being] less than 255 ... characters" based on a
limited sample set, only probabilistic ones, absent reasoning about the
application domain.

Additionally, one could use TEXT for shorter columns if one wanted:

There are no performance differences between these three types,

[character varying(n), character(n) and text]

apart from increased storage size when using the blank-padded
type, and a few extra cycles to check the length when storing
into a length-constrained column. While character(n) has
performance advantages in some other database systems, it has
no such advantages in PostgreSQL. In most situations text or
character varying should be used instead.

<http://www.postgresql.org/docs/8.3/static/datatype-character.html&gt;

DBMSes are about schemata and planning the data structures, not loosey-goosey
typing. (This reminds me of the debate between the loosely-typed language
(e.g., PHP) camp versus the strongly-typed language (C#, Java) camp.) Schemas
are based on analysis of and reasoning about the application domain, not lucky
guesses about a limited sample of inputs.

--
Lew

Albretch Mueller wrote:

PostgreSQL has 60+ types and many look like eachother. How do you propose to differentiate?

~
Data Types are basically about value ranges (how many bits do you
need to hold the value) and formatting.

That is exactly wrong, at least in the PostgreSQL approach to the type
system. Data types are about functions and operators associated with
them. So it is not about what the data looks like, but what kind of
processing you want to do with it. Guessing that information from a
dump of data seems pretty much impossible.