[postgis-users] Getting GID value from an Insert query

[Michael Fuhr]

On Thu, May 25, 2006 at 11:13:48AM -0600, Bruce Rindahl wrote:
> This is probably a straight SQL issue but hopefully someone can help.
> I am running an insert query into a PostGIS/PostgeSql database.  The query
> runs fine into a table with a unique GID value.  I then need to get that row
> id back to display in a web display.  How can I get the GID from the insert
> query I just ran?
> 
> The query is:
> 
> insert into dwr_gis (the_geom) values (GeomFromText('POLYGON((-2.29
> 1510.00,-1586.66 1851.53,.....,-2.29 1510.00))',26913))

If gid is a serial type (i.e., an integer that takes its default
value from a sequence) then you can use currval() to get the last
value obtained from the sequence (currval() returns the last value
obtained in the current session so you don't have to worry about
race conditions, except perhaps when using a connection pool).

You can call currval() with a hard-coded sequence name or, in 8.0 and
later, you can query for the sequence name with pg_get_serial_sequence().

SELECT currval('dwr_gis_gid_seq');
SELECT currval(pg_get_serial_sequence('dwr_gis', 'gid'));

http://www.postgresql.org/docs/8.1/interactive/functions-sequence.html
http://www.postgresql.org/docs/8.1/interactive/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE

-- 
Michael Fuhr