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.
