Postgres Pro
Downloads
Home   >   mailing lists

Thread: error tryiing to insert with collate

error tryiing to insert with collate

From
"Jean-Yves F. Barbier"
Date:
Hi list,

Original collation srv+clis = fr_FR.utf8

I'm trying to insert as:
INSERT INTO tst1m VALUES ( default,
    'źŠÿǚŦÍÏÔJŽććżûŘäpèăîÍŢŃDµŴŻĊĜÿG' COLLATE "hu_HU.utf8",
    'úĽčŏṻŗĿÁťħĵŇğŚáÛĨæħŽīhĴÝUĶďeźİīŇč' COLLATE "hu_HU.utf8" );

and get: ERROR:  unsupported node type: 325

hu_HU.utf8 is installed - so is this operation impossible
and collate only reserved to select statements?

JY
--
Everything can be filed under "miscellaneous".

Re: error tryiing to insert with collate

From
Thom Brown
Date:
On 2 January 2012 15:06, Jean-Yves F. Barbier <12ukwn@gmail.com> wrote:
> Hi list,
>
> Original collation srv+clis = fr_FR.utf8
>
> I'm trying to insert as:
> INSERT INTO tst1m VALUES ( default,
>        'źŠÿǚŦÍÏÔJŽććżûŘäpèăîÍŢŃDµŴŻĊĜÿG' COLLATE "hu_HU.utf8",
>        'úĽčŏṻŗĿÁťħĵŇğŚáÛĨæħŽīhĴÝUĶďeźİīŇč' COLLATE "hu_HU.utf8" );
>
> and get: ERROR:  unsupported node type: 325
>
> hu_HU.utf8 is installed - so is this operation impossible
> and collate only reserved to select statements?

I suspect you have char or varchar fields you're inserting those text
values into that are smaller than the values you're attempting to
insert into them.  In any case, it's not a helpful error you've been
given back so that should be improved.

What do you get if you perform the exact same INSERT without the COLLATE parts?

--
Thom

Re: error tryiing to insert with collate

From
"Jean-Yves F. Barbier"
Date:
On Mon, 2 Jan 2012 15:22:19 +0000
Thom Brown <thom@linux.com> wrote:

Oops, strange: often my answer goes to ML goes only to sender;
back in the loop.

>
> I suspect you have char or varchar fields you're inserting those text
> values into that are smaller than the values you're attempting to
> insert into them.

Wrong hunch:
INSERT INTO tst1m VALUES (default,
    'źŠÿǚŦÏÔćÍĊĜÿG' COLLATE "hu_HU.utf8",
    'úĽčŏUĶďeźİīŇč' COLLATE "hu_HU.utf8" );
ERROR:  unsupported node type: 325

Anyway the columns are large enough to cope with these strings (32 & 64)
as they are copy from an existing row.

>  In any case, it's not a helpful error you've been
> given back so that should be improved.

Yep, I seeked gogol for that and only get a very few answers, most of
them linking to PG source or unrelated.

> What do you get if you perform the exact same INSERT without the COLLATE parts?

Works (of course, see above) perfectly:
INSERT INTO tst1m VALUES ( default,
    'źŠÿǚŦÍÏÔJŽććżûŘäpèăîÍŢŃDµŴŻĊĜÿG' ,
    'úĽčŏṻŗĿÁťħĵŇğŚáÛĨæħŽīhĴÝUĶďeźİīŇč'  );
INSERT 0 1

JY
--
Love is the only game that is not called on account of darkness.
        -- M. Hirschfield

Re: error tryiing to insert with collate

From
Tom Lane
Date:
"Jean-Yves F. Barbier" <12ukwn@gmail.com> writes:
> I'm trying to insert as:
> INSERT INTO tst1m VALUES ( default,
>     'źŠÿǚŦÍÏÔJŽććżûŘäpèăîÍŢŃDµŴŻĊĜÿG' COLLATE "hu_HU.utf8",
>     'úĽčŏṻŗĿÁťħĵŇğŚáÛĨæħŽīhĴÝUĶďeźİīŇč' COLLATE "hu_HU.utf8" );

> and get: ERROR:  unsupported node type: 325

Hmm, that's a bug, which I will go fix --- thanks for the report!
However, the reason that nobody noticed the bug before now is that it's
entirely useless to attach a COLLATE clause to an INSERT value.
COLLATE is only interesting when attached to an argument of an operator
or function that pays attention to collation, such as the "<" operator.
Here, your values are going to go directly into the table without any
comparisons or anything else happening to them, and once they're in
the table it'll be the table column's declared collation that controls
how they're compared.

            regards, tom lane

Re: error tryiing to insert with collate

From
Thom Brown
Date:
On 2 January 2012 18:07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Jean-Yves F. Barbier" <12ukwn@gmail.com> writes:
>> I'm trying to insert as:
>> INSERT INTO tst1m VALUES ( default,
>>       'źŠÿǚŦÍÏÔJŽććżûŘäpèăîÍŢŃDµŴŻĊĜÿG' COLLATE "hu_HU.utf8",
>>       'úĽčŏṻŗĿÁťħĵŇğŚáÛĨæħŽīhĴÝUĶďeźİīŇč' COLLATE "hu_HU.utf8" );
>
>> and get: ERROR:  unsupported node type: 325
>
> Hmm, that's a bug, which I will go fix --- thanks for the report!
> However, the reason that nobody noticed the bug before now is that it's
> entirely useless to attach a COLLATE clause to an INSERT value.
> COLLATE is only interesting when attached to an argument of an operator
> or function that pays attention to collation, such as the "<" operator.
> Here, your values are going to go directly into the table without any
> comparisons or anything else happening to them, and once they're in
> the table it'll be the table column's declared collation that controls
> how they're compared.

For reference, Tom committed the fix for this in both master and 9.1
branch: http://archives.postgresql.org/pgsql-committers/2012-01/msg00019.php

--
Thom