{"id":1244,"date":"2013-09-27T01:00:00","date_gmt":"2013-09-26T23:00:00","guid":{"rendered":"https:\/\/www.fussylogic.co.uk\/blog\/?p=1244"},"modified":"2013-09-27T17:35:15","modified_gmt":"2013-09-27T16:35:15","slug":"postgresql-and-row-ids","status":"publish","type":"post","link":"https:\/\/www.fussylogic.co.uk\/blog\/?p=1244","title":{"rendered":"PostgreSQL and Row IDs"},"content":{"rendered":"

From a blog post<\/a><\/p>\n

\n

[PostgreSQL] allows for treating INSERT and UPDATE statements as table references, when used with the RETURNING clause. This is quite fancy, even if not very useful in everyday SQL.<\/p>\n<\/blockquote>\n

\u00e2\u20ac\u0153not useful\u00e2\u20ac\u009d? Madness I tell you<\/del> I respectfully disagree.<\/p>\n

I only discovered RETURNING<\/code> in the last year; and it\u00e2\u20ac\u2122s incredibly useful in everyday SQL. I\u00e2\u20ac\u2122ll come back to what it does later.<\/p>\n

Let\u00e2\u20ac\u2122s say you write a web app that edits a single table. One page let\u00e2\u20ac\u2122s you add a new row. After the new row is added, you want to redirect the browser to the edit page for that row. Your URIs would probably go like this in a REST<\/code>ful style:<\/p>\n

- <http:\/\/yourapp.com\/rows\/new> shows the editor and submits to...\n- <http:\/\/yourapp.com\/rows\/insert> reads the `POST`ed form writes\n  the record to the database, and redirects to...\n- <http:\/\/yourapp.com\/rows\/edit\/2029> shows the editor and submits\n  to...\n- <http:\/\/yourapp.com\/rows\/update\/2029> reads the `POST`ed form writes\n  the record to the database, and redirects to...\n- <http:\/\/yourapp.com\/rows\/edit\/2029> shows the editor and submits\n  to...\n- and so on<\/code><\/pre>\n
Or similar.<\/p>\n
I\u00e2\u20ac\u2122m interested in the \/table\/insert<\/code> step. We\u00e2\u20ac\u2122re going to run a query like this in that step (with appropriate escaping to prevent SQL injection):<\/p>\n
INSERT INTO Rows\n    (Field1, Field2, Field3)\n    VALUES ('data#1', 'data#2', 'data#3');<\/code><\/pre>\n
Now we\u00e2\u20ac\u2122ll redirect to \/table\/edit\/2029<\/code>\u00e2\u20ac\u00a6 except\u00e2\u20ac\u00a6 oops, where do we get the 2029 from? This is the problem I\u00e2\u20ac\u2122ll discuss here.<\/p>\n
Let me first show the schema for the table (PostgreSQL style):<\/p>\n
CREATE TABLE Rows (\n    ID serial PRIMARY KEY,\n    Field1 text,\n    Field2 text,\n    Field3 text\n);<\/code><\/pre>\n
Importantly, PostgreSQL automatically creates a sequence for the ID<\/code> field and uses it as the default value for that field (sequences are atomic auto-incrementers, assuring that each time it\u00e2\u20ac\u2122s next value is fetched, that next value is returned only once \u00e2\u20ac\u201c multiple simultaneous INSERT<\/code>s are therefore guaranteed to get unique IDs); PRIMARY KEY<\/code> is, really, just a short way of saying \u00e2\u20ac\u0153UNIQUE NOT NULL\u00e2\u20ac\u009d, but it\u00e2\u20ac\u2122s also a way of indicating to the reader that this field is the\u00e2\u20ac\u00a6 primary key. That has the effect of automatically creating an index for that field.<\/p>\n
We can see what\u00e2\u20ac\u2122s happened in reality if we do a \\d rows<\/code> from the psql<\/code> prompt.<\/p>\n
database=> \\d rows\n                                   Table "public.database"\n  Column   |           Type           |                     Modifiers                      \n-----------+--------------------------+----------------------------------------------------\n id        | integer                  | not null default nextval('rows_id_seq'::regclass)\n field1    | text                     |\n field2    | text                     | \n field3    | text                     | \nIndexes:\n    "rows_pkey" PRIMARY KEY, btree (id)<\/code><\/pre>\n
The id<\/code> column defaults to nextval('rows_id_seq')<\/code>, being the next atomic value from the named sequence. The sequence might look like this:<\/p>\n
database=> \\d rows_id_seq\n        Sequence "public.rows_id_seq"\n    Column     |  Type   |        Value        \n---------------+---------+---------------------\n sequence_name | name    | rows_id_seq\n last_value    | bigint  | 9\n start_value   | bigint  | 1\n increment_by  | bigint  | 1\n max_value     | bigint  | 9223372036854775807\n min_value     | bigint  | 1\n cache_value   | bigint  | 1\n log_cnt       | bigint  | 24\n is_cycled     | boolean | f\n is_called     | boolean | t<\/code><\/pre>\n
Before I knew about RETURNING<\/code> I solved the problem by directly querying the sequence; noting the value and using that in the INSERT<\/code> and the redirect. That meant that I would completely bypass the default<\/code> modifier for the id<\/code> column when I needed the ID; but when I didn\u00e2\u20ac\u2122t a normal insert would work fine.<\/p>\n
import psycopg2\n\n# You will need a particularly configured pg_hba.conf for this to\n# work; along with, of course, the database and user already created.\ndbh = psycopg2.connect("dbname='database' user='yourdbuser'")\n\n# get next sequence value\ncur = dbh.cursor()\ncur.execute("SELECT nextval('rows_id_seq')")\nnextid = cur.fetchone()[0]\n\ncur = dbh.cursor()\ncur.execute("""INSERT INTO rows (id, field1, field2, field3)\n    VALUES (%s,%s,%s,%s)""", (nextid, 'data#1', 'data#2', 'data#3'))\n\nredirectToEditPage(nextid)<\/code><\/pre>\n
This is horrible. Imagine we want to make a cascade of records in various intra-referenced tables and have to go through this every time. That\u00e2\u20ac\u2122s what I used to do; and I looked enviously at the one feature of MySQL that I wished I had\u00e2\u20ac\u00a6 mysql_insert_id()<\/code>. I needn\u00e2\u20ac\u2122t have been envious, PostgreSQL has had a better solution for years (since 8.2), I just didn\u00e2\u20ac\u2122t know about it \u00e2\u20ac\u201d RETURNING<\/code>. It\u00e2\u20ac\u2122s use is simple:<\/p>\n
import psycopg2\n\n# You will need a particularly configured pg_hba.conf for this to\n# work; along with, of course, the database and user already created.\ndbh = psycopg2.connect("dbname='database' user='yourdbuser'")\n\ncur = dbh.cursor()\ncur.execute("""INSERT INTO rows (field1, field2, field3)\n    VALUES (%s,%s,%s) RETURNING ID""", ('data#1', 'data#2', 'data#3'))\nnextid = cur.fetchone()[0]\n\nredirectToEditPage(nextid)<\/code><\/pre>\n
RETURNING<\/code> makes INSERT<\/code>, which normally returns nothing, behave a little like a SELECT<\/code> on the table being altered. Here, RETURNING ID<\/code> runs the INSERT<\/code> then returns SELECT ID FROM rows<\/code>.<\/p>\n
(My defence is that I learned PostgreSQL when it was at version 7.1, so didn\u00e2\u20ac\u2122t support RETURNING<\/code>.)<\/p>\n
For me, that\u00e2\u20ac\u2122s about as useful as it gets in \u00e2\u20ac\u0153everyday SQL\u00e2\u20ac\u009d.<\/p>\n","protected":false},"excerpt":{"rendered":"
From a blog post [PostgreSQL] allows for treating INSERT and UPDATE statements as table references, when used with the RETURNING clause. This is quite fancy, even if not very useful in everyday SQL. \u00e2\u20ac\u0153not useful\u00e2\u20ac\u009d? Madness I tell you I respectfully disagree. I only discovered RETURNING in the last year; and it\u00e2\u20ac\u2122s incredibly useful in\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[89,88,6],"_links":{"self":[{"href":"https:\/\/www.fussylogic.co.uk\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1244"}],"collection":[{"href":"https:\/\/www.fussylogic.co.uk\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.fussylogic.co.uk\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.fussylogic.co.uk\/blog\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.fussylogic.co.uk\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1244"}],"version-history":[{"count":1,"href":"https:\/\/www.fussylogic.co.uk\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1244\/revisions"}],"predecessor-version":[{"id":1245,"href":"https:\/\/www.fussylogic.co.uk\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1244\/revisions\/1245"}],"wp:attachment":[{"href":"https:\/\/www.fussylogic.co.uk\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1244"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.fussylogic.co.uk\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1244"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.fussylogic.co.uk\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1244"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}