Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is it possible in SQL ellegantly achieve the following:

insert into my_table (the_geom) VALUES 

which involves invoking an external function geomfromwkb with two parameters?

So far I have a un-elegant solution. The first solution is of this type:

insert into mytable (the_geom) select 
    geomfromwkb('\x010100000058b4b610a73a27408144b7147f194840',4326)) union
    geomfromwkb('\x010100000058b4b610a73a27408144b7147f194840',4326)) union

The second one uses the prepare statement:

PREPARE my_insert AS INSERT INTO geom_example (the_geom) 
    VALUES (geomfromwkb($1,4326));
EXECUTE my_insert ('\x010100000058b4b610a73a27408144b7147f194840');
EXECUTE my_insert ('\x010100000058b4b610a73a27408144b7147f194840');
statement: DEALLOCATE my_insert;

Question 1: Is there anything more ellegant than this?

Question 2: What does the DB do when it sees a bunch of unions?

Question 3: does the prepare really speed up things compared to one fat insert?

Question 4: the byte sequence is of the geometry type. It is possible to skip the consistency checks (to insure that this is indeed the geometry byte sequence) to speed up inserts?

share|improve this question
Re2: Finding out what the DB does with UNIONs is the easiest by running the query with EXPLAIN ANALYZE. You will see some Appends there, which means that each part of the UNION gets handled basically separated from the others. Re3: as always, it depends... Re4: and what to do if you skip the checks which later turn out to be violated? – dezso Dec 9 '13 at 21:41
up vote 2 down vote accepted

You can build a 'table' using the VALUES clause and build the function call on top of it:

INSERT INTO wgs84 (wkb_geometry, wkb_geography)
SELECT st_transform(st_setsrid(st_makepoint(col1, col2), col3), col4)
  FROM (
        VALUES (694274.6998, 5341745.2959, 25832, 4326),
               ( 69425.6998, 5341754.23,   25834, 4327)
       ) t (col1, col2, col3, col4);

This is easier to read, to write, and to modify than the repeated function calls. You can even align your data which sometimes makes finding errors easier.

A version of this can be putting the VALUES into a WITH query. In some cases it is better to put (preferably using the COPY command) the data into a temporary table and selecting from there in the INSERT statement - this comes handy when the input size gets bigger.

share|improve this answer
this is clearly a very ellegant solution – arthur Dec 10 '13 at 10:28

Aparently I was able to invoke such a command without any problems:

insert into wgs84 (wkb_geometry,wkb_geography) values
(st_transform(st_setsrid(st_makepoint( 694274.6998, 5341745.2959), 25832),4326),
st_transform(st_setsrid(st_makepoint( 694274.6998, 5341745.2959), 25832),4326)::geography)

which shows that a plain insert is possible.. I wonder why postgres then was once confused about too many commas..

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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