Re: COPY with composite type column

Hi all.

I have this composite type:

create type ct as (
ct1 text,
ct2 int
);

Then I have this table

create table atable (
somedata numeric,
otherdata text,
compo ct
);

when I try to COPY data to that table and use the following "query"
I get a syntax error message:

COPY atable( somedata,(ct).ct1 ) from stdin;
ERROR: syntax error at or near "("
LINE 1: COPY atable( somedata,(ct).ct1 ) from stdin;
^

The "caret" points to the open parenthesis in "(ct)".
Same error is reported on the first open parenthesis if I write "((ct.).ct1)".

Any hint on how to write this COPY?

--
Reg me, please!

Il Wednesday 26 December 2007 12:58:34 Reg Me Please ha scritto:
> Hi all.
>
> I have this composite type:
>
> create type ct as (
> ct1 text,
> ct2 int
> );
>
> Then I have this table
>
> create table atable (
> somedata numeric,
> otherdata text,
> compo ct
> );
>
> when I try to COPY data to that table and use the following "query"
> I get a syntax error message:
>
> COPY atable( somedata,(ct).ct1 ) from stdin;
> ERROR: syntax error at or near "("
> LINE 1: COPY atable( somedata,(ct).ct1 ) from stdin;
> ^
>
> The "caret" points to the open parenthesis in "(ct)".
> Same error is reported on the first open parenthesis if I write
> "((ct.).ct1)".
>
> Any hint on how to write this COPY?

OK.
I've managed to walk the first step.
The correct grammar is

COPY atable( somedata,ct ) FROM STDIN;

That is you have to consider the full composed type column.
As stated into the COPY manual.
The data to be entered for the composed type column is to be enclosed within
paretheses. The single sub-columns are to be comma separated (!).

Is there a way to just enter some of the composed types composing columns?

--
Reg me, please!

Il Wednesday 26 December 2007 14:31:04 Reg Me Please ha scritto:
> Il Wednesday 26 December 2007 12:58:34 Reg Me Please ha scritto:
> > Hi all.
> >
> > I have this composite type:
> >
> > create type ct as (
> > ct1 text,
> > ct2 int
> > );
> >
> > Then I have this table
> >
> > create table atable (
> > somedata numeric,
> > otherdata text,
> > compo ct
> > );
> >
> > when I try to COPY data to that table and use the following "query"
> > I get a syntax error message:
> >
> > COPY atable( somedata,(ct).ct1 ) from stdin;
> > ERROR: syntax error at or near "("
> > LINE 1: COPY atable( somedata,(ct).ct1 ) from stdin;
> > ^
> >
> > The "caret" points to the open parenthesis in "(ct)".
> > Same error is reported on the first open parenthesis if I write
> > "((ct.).ct1)".
> >
> > Any hint on how to write this COPY?
>
> OK.
> I've managed to walk the first step.
> The correct grammar is
>
> COPY atable( somedata,ct ) FROM STDIN;
>
> That is you have to consider the full composed type column.
> As stated into the COPY manual.
> The data to be entered for the composed type column is to be enclosed
> within paretheses. The single sub-columns are to be comma separated (!).
>
> Is there a way to just enter some of the composed types composing columns?

The answer is YES, by leaving empty values between the commas.
I found it by test-and-try: I've been too lazy to dig very deeply into
the 8.2 manual.

What instead it seems to be not possible is to define default values
for every single sub-column of a composite type.
Right?

Sorry for this "autoanswering", though!

--
Reg me, please!