Re: Importing data from CSV into a table with array and composite types
2018-05-20 7:36 GMT+02:00 a <372660...@qq.com>: > Thank you so much. BTW, may I ask one more question that, how should I > select every first element of the array?? > > I know that "select p[:] from f" will print all element of the p array, > but I probably would want to present result as "select p[:].a from f", but > the statement does not work. > > May I as the correct statement of displaying all first element of the > composite type in an array?? > it is not easy - for example, that I sent you can write a query postgres=# select array_agg(a) from f, unnest(pv) where f.c = 1000; ┌───┐ │ array_agg │ ╞═══╡ │ {10,30} │ └───┘ (1 row) > Thank you! > > Shore > > > -- Original message -- > *From:* "Pavel Stehule"; > *Sendtime:* Friday, May 18, 2018 5:46 PM > *To:* "a"<372660...@qq.com>; > *Cc:* "pgsql-general"; > *Subject:* Re: Importing data from CSV into a table with array and > composite types > > Hi > > 2018-05-18 10:37 GMT+02:00 a <372660...@qq.com>: > >> Hi: >> >> I would like to import data from a csv table. But the table that is going >> to be inserted is constructed with arrays and composite types, also with >> array of composite. >> >> I have tried many ways of inserting but fail. Can anyone help? Thank you >> so much. >> > > create type p as (a int, b int); > create table f(pv p[], c int); > insert into f values(array[(10,20),(30,40)]::p[], 1000); > insert into f values(array[(1,20),(3,40)]::p[], -1000); > > postgres=# copy f to stdout csv; > "{""(10,20)"",""(30,40)""}",1000 > "{""(1,20)"",""(3,40)""}",-1000 > Time: 0,391 ms > > So you have to respect this format. CSV doesn't know a arrays, doesn't > know composite - so these values are passed as string > > Regards > > Pavel >
Re: Importing data from CSV into a table with array and composite types
Thank you so much. BTW, may I ask one more question that, how should I select every first element of the array?? I know that "select p[:] from f" will print all element of the p array, but I probably would want to present result as "select p[:].a from f", but the statement does not work. May I as the correct statement of displaying all first element of the composite type in an array?? Thank you! Shore -- Original message -- From: "Pavel Stehule"; Sendtime: Friday, May 18, 2018 5:46 PM To: "a"<372660...@qq.com>; Cc: "pgsql-general"; Subject: Re: Importing data from CSV into a table with array and composite types Hi 2018-05-18 10:37 GMT+02:00 a <372660...@qq.com>: Hi: I would like to import data from a csv table. But the table that is going to be inserted is constructed with arrays and composite types, also with array of composite. I have tried many ways of inserting but fail. Can anyone help? Thank you so much. create type p as (a int, b int); create table f(pv p[], c int); insert into f values(array[(10,20),(30,40)]::p[], 1000); insert into f values(array[(1,20),(3,40)]::p[], -1000); postgres=# copy f to stdout csv; "{""(10,20)"",""(30,40)""}",1000 "{""(1,20)"",""(3,40)""}",-1000 Time: 0,391 ms So you have to respect this format. CSV doesn't know a arrays, doesn't know composite - so these values are passed as string Regards Pavel
Importing data from CSV into a table with array and composite types
Hi: I would like to import data from a csv table. But the table that is going to be inserted is constructed with arrays and composite types, also with array of composite. I have tried many ways of inserting but fail. Can anyone help? Thank you so much.