Re: COPY with composite type column

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!