0

I'm creating vector tiles using ST_AsMVT and I would like to store them immediately after they are created in addition to returning the object.

The below code works fine at creating a bytea response.

WITH mvtgeom AS
(
SELECT ST_AsMVTGeom(
ST_GeomFromText('POLYGON ((0 0, 10 0, 10 5, 0 -5, 0 0))'),
ST_MakeBox2D(ST_Point(0, 0), ST_Point(4096, 4096)),
4096, 0, false)
)

SELECT ST_AsMVT(mvtgeom.*)
FROM mvtgeom

But when I change the last section to include an INSERT statement:

INSERT INTO test SELECT ST_AsMVT(mvtgeom.*)
FROM mvtgeom

I get the response:

This result object does not return rows

Which is of course true, but how to insert data which isn't a row?

The table test only has an ID and a BYTEA column.

1 Answer 1

0

Spell out the target column:

...
INSERT INTO test(bytea_column)  -- use undisclosed column name
SELECT ST_AsMVT(mvtgeom.*)
FROM   mvtgeom
RETURNING bytea_column;

Else, the INSERT inserts into the (leading) ID column you mentioned.

The added RETURNING clause covers:

... in addition to returning the object.

Or provide a value for the ID column additionally, and (optionally) return the whole row:

...
INSERT INTO test(id, bytea_col)  -- use undisclosed column names
SELECT 1, ST_AsMVT(mvtgeom.*)
FROM   mvtgeom
RETURNING *;
1
  • This worked perfectly thankyou. Commented Nov 3, 2022 at 21:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.