How do I copy an element of composite type array into csv file?

On Wed, May 23, 2018 at 2:05 PM, a <372660931@qq.com> wrote:

Hi suppose I have composite type and table

create type A as(
x float8,
y float8
);

create table B(
Ay A[]
);

insert into B
values(array[
(1,2)::A,
(3,4)::B]
);

Now I would like to export the first element of table B into an csv file:

COPY B(Ay[1])
to 'E:/products_199.csv' DELIMITER ',' CSV HEADER;

The code above reported an syntax error.

How should I do it??

Try COPY (SELECT Ay[1] FROM B) to 'E:/products_199.csv' DELIMITER ','
CSV HEADER;

Regards,
Amul

Thank you very much.

BTW, may I ask if I would like to do the opposite that copy csv file content into the first element, how should I do it??

COPY B(Ay[1])
from 'E:/products_199.csv' DELIMITER ',' CSV HEADER;

------------------ Original ------------------
From: "amul sul";<sulamul@gmail.com>;
Send time: Wednesday, May 23, 2018 5:11 PM
To: "a"<372660931@qq.com>;
Cc: "pgsql-general"<pgsql-general@postgresql.org>;
Subject: Re: How do I copy an element of composite type array into csv file?

On Wed, May 23, 2018 at 2:05 PM, a <372660931@qq.com> wrote:

Hi suppose I have composite type and table

create type A as(
x float8,
y float8
);

create table B(
Ay A[]
);

insert into B
values(array[
(1,2)::A,
(3,4)::B]
);

Now I would like to export the first element of table B into an csv file:

COPY B(Ay[1])
to 'E:/products_199.csv' DELIMITER ',' CSV HEADER;

The code above reported an syntax error.

How should I do it??

Try COPY (SELECT Ay[1] FROM B) to 'E:/products_199.csv' DELIMITER ','
CSV HEADER;

Regards,
Amul

On Wednesday, May 23, 2018, a <372660931@qq.com> wrote:

Thank you very much.

BTW, may I ask if I would like to do the opposite that copy csv file
content into the first element, how should I do it??

COPY B(Ay[1])
from 'E:/products_199.csv' DELIMITER ',' CSV HEADER;

you cannot put "[1]" there. The csv file value has to be an array input
literal with only one element. i.e., something like: {(1,2)}

There is no first element as such when you are creating a new record.
There will be however many elelements to supply to the array input.

David J.

Thank you so much, did you mean the section 8.15.6??

------------------ Original message ------------------
From: "David G. Johnston";
Sendtime: Wednesday, May 23, 2018 9:18 PM
To: "a"<372660931@qq.com>;
Cc: "amul sul"; "pgsql-general";
Subject: How do I copy an element of composite type array into csv file?

On Wednesday, May 23, 2018, a <372660931@qq.com> wrote:
Thank you very much.

BTW, may I ask if I would like to do the opposite that copy csv file content into the first element, how should I do it??

COPY B(Ay[1])
from 'E:/products_199.csv' DELIMITER ',' CSV HEADER;

you cannot put "[1]" there. The csv file value has to be an array input literal with only one element. i.e., something like: {(1,2)}

There is no first element as such when you are creating a new record. There will be however many elelements to supply to the array input.

David J.

On Wed, May 23, 2018 at 7:03 AM, a <372660931@qq.com> wrote:

Thank you so much, did you mean the section 8.15.6??

​Yes.
​