How do I insert this custom data type with libpq?

Question

I'm having some difficulty with inserting some data using libpq. I have two custom data types:

create type size as (width real, height real);
create type rotated_rect as (angle real, center point, bounding_box box, size size)

and I would like to insert a record into a table which has a rotated_rect field, so for the field using libpq I'm putting together the string value:

paramv[3] = "(10.5,10.1,10.2,20,20,20,40,(5,5))";

However, it's giving me the error: invalid input syntax for type point: "10.1"

I've also tried:

paramv[3] = "(10.5,(10.1,10.2),20,20,20,40,(5,5))"; -> invalid input syntax for "(10.1"
paramv[3] = "(10.5,(10.1,10.2),(20,20,20,40),(5,5))"; -> as above

and the sql command I'm using is:

res = PQexecParams(conn, "insert into test (r,b,s,rr) values ($1::real,$2::box,$3::size,$4::rotated_rect)", 4, NULL, paramv, NULL, NULL,0);

How do I fix this?

You error message is incomplete and misleading. The actual error message should be `ERROR: invalid input syntax for type point: "(10.1"`. Please, *always* provide the verbatim error message. — Erwin Brandstetter, Aug 07 '14 at 22:18
@ErwinBrandstetter where is it misleading? That's exactly what I've put! — user3791372, Aug 07 '14 at 22:30
It is not. Read again, and please fix your question. Also, your added `INSERT` statement has more values than parameters. — Erwin Brandstetter, Aug 07 '14 at 22:42
Yeah, but how to do it in **binary** mode, instead of **ascii** mode? — ddevienne, Jun 05 '19 at 14:40

Erwin Brandstetter · Answer 1 · 2014-08-07T22:47:28.557

2

This works (tested in Postgres 9.3):

SELECT '(10.5,"(10.1,10.2)","(20,20,20,40)","(5,5)")'::rotated_rect

Returns:

'(10.5,"(10.1,10.2)","(20,40),(20,20)","(5,5)")'

Note the different syntax for box. Try this form.

edited Aug 07 '14 at 22:47

answered Aug 07 '14 at 22:18

Erwin Brandstetter

605,456
145
1,078
1,228

I'm inserting, not selecting, and no, `paramv[3] = "(10.5,\"(10.1,10.2)\",\"(20,20,20,40)\",\"(5,5)\")"` doesn't work, giving the error: malformed record literal "(20,20,20,40)" detail: too many columns – user3791372 Aug 07 '14 at 22:35
I works, I tested in Postgrs 9.3 before posting. But try the syntax Postgres returns. – Erwin Brandstetter Aug 07 '14 at 22:59
doesn't work with libpq: `paramv[3] = "(10.5,\"(10.1,10.2),\"(20,21),(22,40)\",\"(5,5)\")";` gives the error: "malformed record literal: "(20,21),(22,40)" detail: junk after right parenthesis – user3791372 Aug 08 '14 at 00:44

user3791372 · Accepted Answer · 2014-08-08T03:57:27.063

What got me were that escaped double quotes and parenthesis need to be used around the values representing a field of the custom compound data type which requires more than one value to create, so:

paramv[0] = "(10.5,\"(10.1,10.2)\",\"(20,20,20,40)\",\"(5,5)\")";

As this string is used as a parameter, the single quotes that would usually wrap the outer parenthesis are not needed.

In a non-parameterised query, it would be implemented like so with the single quotes:

res = PQexec(conn, "insert into test (rr) values ('(10.5,\"(10.1,10.2)\",\"(20,20,20,40)\",\"(5,5)\")')");

How do I insert this custom data type with libpq?

2 Answers2