From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | a <372660931(at)qq(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Importing data from CSV into a table with array and composite types |
Date: | 2018-05-20 05:44:41 |
Message-ID: | CAFj8pRCtBpvrYeOMNf7Tn1iKtgyw5OD=Lbp5uCzFfBLZrs_tow@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
2018-05-20 7:36 GMT+02:00 a <372660931(at)qq(dot)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"<372660931(at)qq(dot)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(at)qq(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2018-05-20 13:19:58 | Re: Fwd: postgresql is down in whm status page |
Previous Message | a | 2018-05-20 05:36:09 | Re: Importing data from CSV into a table with array and composite types |