Thread: Implicit casts to array types
I'm trying to create an implicit cast from an unknown type into a text array
type by creating a simple array of size 1. e.g.
create function textarray(unknown)
returns text[]
as 'select ARRAY[$1::text];'
language sql
immutable;
create cast (unknown as text[]) with function textarray(unknown) as
implicit;
However, when I try to use this, the planner doesn't use the implicit cast.
Instead it still tries to cast 'a' directly to a text[] and complains that
it's not formatted as '{a}' (ERROR: array value must start with "{" or
dimension information)
I added an additional parallel cast from text to text[]:
create function textarray(text)
returns text[]
as 'select ARRAY[$1];'
language sql
immutable;
create cast (text as text[]) with function textarray(text) as implicit;
Now, if I explicitly cast 'a'::text the implicit cast to text[] fires.
However, this doesn't help because I need all the implicit casts to fire
since this is intended to be used by COPY FROM.
I tried adding an implicit cast from unknown to text to try to get
unknown->text->text[], but that didn't work either (same error as first
attempt).
Is there something special about the unknown data type that I'm unaware of?
I don't understand why it worked for text but not for unknown.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Implicit-casts-to-array-types-tp5736582.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Fri, Dec 14, 2012 at 9:16 AM, joshua <jzuellig@arbormetrix.com> wrote:
> I'm trying to create an implicit cast from an unknown type into a text array
> type by creating a simple array of size 1. e.g.
>
> create function textarray(unknown)
> returns text[]
> as 'select ARRAY[$1::text];'
> language sql
> immutable;
>
> create cast (unknown as text[]) with function textarray(unknown) as
> implicit;
>
> However, when I try to use this, the planner doesn't use the implicit cast.
> Instead it still tries to cast 'a' directly to a text[] and complains that
> it's not formatted as '{a}' (ERROR: array value must start with "{" or
> dimension information)
> I added an additional parallel cast from text to text[]:
create function textarray(anyelement)
returns text[]
as 'select ARRAY[$1::text];'
language sql
immutable;
(emphasis on 'anyelement')...get rid of the cast. use 'any' type
arguments for polymorphic functions, that is when you want them to
operate over wide range of input types. hacking casts is almost never
a good idea.
merlin
Thanks, that function does help, but it still cannot cast directly to text[];
The point of the functions was only ever to allow for an implicit cast to
text[];
My goal is to be able to copy from a simple csv with nonarray entries
("1,2,3,...") and extract text arrays when the target table calls for it by
creating an array of size 1. I believe this will require a custom implicit
cast from unknown to text[], but if there's a better way to do it, I'd love
to know.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Implicit-casts-to-array-types-tp5736582p5736588.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
joshua <jzuellig@arbormetrix.com> writes:
> I'm trying to create an implicit cast from an unknown type into a text array
> type by creating a simple array of size 1. e.g.
> create function textarray(unknown)
> returns text[]
> as 'select ARRAY[$1::text];'
> language sql
> immutable;
> create cast (unknown as text[]) with function textarray(unknown) as
> implicit;
No, that isn't gonna work. "unknown" isn't a real type and the parser
doesn't use normal casting rules for it. It's just a placeholder until
the parser can figure out what type an undecorated literal ought to be.
What's your actual problem? (I don't see how this connects to COPY
at all --- COPY never does any type inference, nor any implicit
casting.)
regards, tom lane
On Fri, Dec 14, 2012 at 9:32 AM, joshua <jzuellig@arbormetrix.com> wrote:
> Thanks, that function does help, but it still cannot cast directly to text[];
> The point of the functions was only ever to allow for an implicit cast to
> text[];
I'm not parsing that. Why do you need an explicit cast?
> My goal is to be able to copy from a simple csv with nonarray entries
> ("1,2,3,...") and extract text arrays when the target table calls for it by
> creating an array of size 1. I believe this will require a custom implicit
> cast from unknown to text[], but if there's a better way to do it, I'd love
> to know.
If I want to handle csv data in an array, I usually do it like this:
*) create a one column table for staging the load
*) COPY the data to that table, intentionally faking the delimiter
*) process via conversion to array with 'string_to_array'.
the above only works if there's no commas inside your strings, so a
little extra massaging might be required in that case.
merlin
Tom-
My apologies, I'm still somewhat new to this. Specifically, I'm dealing with
COPY FROM CSV. I had assumed that since a csv is essentially a pile of text
and COPY FROM is smart enough to interpret all sorts of csv entries into
postgresql data types that if I wanted to allow a nonstandard conversion,
I'd have to define some sort of cast to allow COPY FROM to interpret, say
...,green,... as {'green}.
Merlin-
I could set this up to use a staging table, but honestly, given our systems,
it'd be easier for me to change all of our source csv's to simply read
...,{abc},... instead of ...,abc,... than to change our code base to use a
series of staging tables (we will be using brackets in the future; this is
more of a backwards compatibility issue). Especially since it currently
doesn't have to inspect the target data type of columns we load up, it
simply allows the COPY FROM command to do all of the interpreting which
brings me back to my original point. :)
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Implicit-casts-to-array-types-tp5736582p5736596.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Fri, Dec 14, 2012 at 9:52 AM, joshua <jzuellig@arbormetrix.com> wrote:
> Tom-
> My apologies, I'm still somewhat new to this. Specifically, I'm dealing with
> COPY FROM CSV. I had assumed that since a csv is essentially a pile of text
> and COPY FROM is smart enough to interpret all sorts of csv entries into
> postgresql data types that if I wanted to allow a nonstandard conversion,
> I'd have to define some sort of cast to allow COPY FROM to interpret, say
> ...,green,... as {'green}.
>
> Merlin-
> I could set this up to use a staging table, but honestly, given our systems,
> it'd be easier for me to change all of our source csv's to simply read
> ...,{abc},... instead of ...,abc,... than to change our code base to use a
> series of staging tables (we will be using brackets in the future; this is
> more of a backwards compatibility issue). Especially since it currently
> doesn't have to inspect the target data type of columns we load up, it
> simply allows the COPY FROM command to do all of the interpreting which
> brings me back to my original point. :)
If input csv doesn't match your destination structure, then staging
the input to a temporary work table and processing the transformation
with a query is really the way to go. Hacking casts is about as ugly
as it gets.
merlin
Merlin Moncure-2 wrote > If input csv doesn't match your destination structure, then staging > the input to a temporary work table and processing the transformation > with a query is really the way to go. Hacking casts is about as ugly > as it gets. > > merlin Thanks. I thought that might be the case. I just wanted to see if there was a way to slightly alter the COPY FROM protocol's functionality. But it'll probably be safest to just update all our csv's. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Implicit-casts-to-array-types-tp5736582p5736602.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
joshua <jzuellig@arbormetrix.com> writes:
> My apologies, I'm still somewhat new to this. Specifically, I'm dealing with
> COPY FROM CSV. I had assumed that since a csv is essentially a pile of text
> and COPY FROM is smart enough to interpret all sorts of csv entries into
> postgresql data types that if I wanted to allow a nonstandard conversion,
> I'd have to define some sort of cast to allow COPY FROM to interpret, say
> ...,green,... as {'green}.
COPY is not smart at all. It just looks at the column types of the
target table and assumes that the incoming data is of those types.
(More precisely, it applies the input conversion function of each
column's data type, after having separated and de-escaped the text
according to datatype-independent format rules.)
> I could set this up to use a staging table, but honestly, given our systems,
> it'd be easier for me to change all of our source csv's to simply read
> ...,{abc},... instead of ...,abc,... than to change our code base to use a
> series of staging tables
In that case, adjusting the source data is the way to go. Or you could
look at using an external ETL tool to do that for you. We've resisted
putting much transformational smarts into COPY because the main goal
for it is to be as fast and reliable as possible.
regards, tom lane
Tom Lane-2 wrote > COPY is not smart at all. It just looks at the column types of the > target table and assumes that the incoming data is of those types. > (More precisely, it applies the input conversion function of each > column's data type, after having separated and de-escaped the text > according to datatype-independent format rules.) > > In that case, adjusting the source data is the way to go. Or you could > look at using an external ETL tool to do that for you. We've resisted > putting much transformational smarts into COPY because the main goal > for it is to be as fast and reliable as possible. > > regards, tom lane I see, it's that input conversion function that I would have needed to change. I understand and agree, we depend on COPY FROM in many contexts to upload huge batches of data orders of magnitude faster than INSERT. We've also used it recently as a generic table interface for smaller configuration tables, but you're right: speed and reliability should be the primary focus of COPY FROM. Thanks for all the quick responses, you guys. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Implicit-casts-to-array-types-tp5736582p5736610.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.