Обсуждение: 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.