I'm having trouble figuring out how to use the currval() function for
sequences in an INSERT statement. I did some searching online, but
couldn't find what I was looking for.
I have two Schemas in my Database:
metadata
geometry
I have a table named "metadata_geometries" in the metadata schema.
I have a table named "geometry_polaris_numbers" in the geometry schema.
Both tables have bigserial columns set up as primary keys.
There is a one-to-one relationship between records in the
metadata_geometries table and the geometry.polaris_numbers table.
Here is what I'd like to do in my query:
Insert a value into the "metadata.metadata_geometries" table.
Select the value just inserted in to the primary key column,
"pk_dbuid" of that table, and insert it into the
"geometry.polaris_numbers" table in the "metadata" column.
I had the query set up like this:
INSERT INTO metadata.metadata_geometries (value)
VALUES ('This is a test.');
INSERT INTO geometry.polaris_numbers (pnuid, number_of_digits, error,
value, metadata)
VALUES (2305, 7, 1000000, 1000, currval(metadata.metadata_geometries.pk_dbuid);
However, when I try and execute the query I get the following error message:
ERROR: Missing FROM-clause for table "metadata_geometries"
I know it is the second INSERT statement in this query that is giving
me problems, because the first statement executes by itself without a
hitch.
I think I have the syntax for the currval() call incorrect.
Can anyone help me out with the correct syntax?
Thanks,
Scott Huey
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
