Thread: 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.
Hi
2018-05-18 10:37 GMT+02:00 a <372660931@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);
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
"{""(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
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"<372660931@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 <372660931@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);
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
"{""(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
2018-05-20 7:36 GMT+02:00 a <372660931@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)
┌───────────┐
│ array_agg │
╞═══════════╡
│ {10,30} │
└───────────┘
(1 row)
Thank you!Shore------------------ Original message ------------------From: "Pavel Stehule";Sendtime: Friday, May 18, 2018 5:46 PMTo: "a"<372660931@qq.com>;Cc: "pgsql-general";Subject: Re: Importing data from CSV into a table with array and composite typesHi2018-05-18 10:37 GMT+02:00 a <372660931@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 msSo you have to respect this format. CSV doesn't know a arrays, doesn't know composite - so these values are passed as stringRegardsPavel