A few years ago, I asked this question on StackExchange and got a brilliant answer (by Erwin Brandstetter). I just finally understood how it works. So here is my explanation (to myself mainly). Enjoy!
The goal was to INSERT a row in a table, cloning an existing one while replacing some value (like the primary key) in the new row. I didn’t want to list each and every column so that even if I add new fields to the table, the cloning mechanism would still work.
Here is the response I got:
INSERT INTO contrats
SELECT (sub.subalias).*
FROM (
SELECT t #= hstore('idcontrat', nextval(pg_get_serial_sequence('contrats','idcontrat'))::TEXT)
#= hstore('date_saisie', CURRENT_TIMESTAMP::TEXT)
#= hstore('numero', numero || '-DUP')
#= hstore('notes', NULL)
AS subalias
FROM contrats t
WHERE idcontrat = :oldidcontrat
) sub
RETURNING idcontrat
First thing to know: when you create a table, PostgreSQL automatically creates a composite type with the same name and all fields in that table (see [here]). As you will discover in this documentation, you reference composite types with (parens).
So how does the query work? There’s an outer query:
INSERT INTO contrats
SELECT (sub.subalias).*
FROM (
<inner_query>
) sub
RETURNING idcontrat.This outer query is pretty obvious except the (sub.subalias) part.
The inner query is also simpler than it seems. If we didn’t want to change any field value, it would be:
SELECT t AS subalias
FROM contrats t
WHERE idcontrat = :oldidcontratNotice that we didn’t write SELECT t.* but just SELECT t. Here, t is the composite type that represents the whole contrats record/row.
Then we use the hstore PostgreSQL extension to do the transformations that we want. This is the syntax we use:
t #= hstore('fieldname',value)This syntax only works on composite types and replaces the value of the fieldname field with the provided value. That value must be of type TEXT. As you can see, you can apply several transformations at once.
What was bugging me for so long was that in the initial answer I got, the second line was written:
SELECT (subalias).*And I didn’t understand why that subalias was visible outside the inner query (I thought it was out of the scope).
subalias is just one field (of type contrats) and since it is of a composite type, you have to use the parens around the whole left part of the SELECT. You can’t write sub.(subalias).* but instead (sub.subalias).*
I must confess I asked the Perplexity AI to help me understand this.
