Archives par mot-clé : postgresql

Cloning a row (even if the structure gets modified in the future)

Publié le par
Répondre

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 = :oldidcontrat

Notice 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.

PostgreSQL : changer le propriétaire d’une base de données

Publié le par
Répondre

Une méthode un peu bourrine mais qui marche.

  • Se connecter à la base de données voulue
  • copier/coller les lignes suivantes en changeant newuser et éventuellement public.
\t\a
\o /tmp/hack.sql
select 'ALTER '
      ||CASE WHEN relkind='r' THEN 'TABLE' WHEN relkind='S' THEN 'SEQUENCE' WHEN relkind='v' THEN 'VIEW' END ||' '||relname||' OWNER TO newuser;'
from pg_class c 
join pg_namespace ns ON ns.oid=c.relnamespace 
where relkind in ('r','S','v') 
and ns.nspname='public' 
ORDER BY CASE WHEN relkind='r' THEN 1 ELSE 2 END;
\o
\i /tmp/hack.sql
  • Le ORDER BY est nécessaire pour que les tables soient traitées avant les séquences

Pour remplacer postgres par newuser comme propriétaire de toutes les fonctions « possédées » par postgres :

\t\a
\o /tmp/hack.sql
SELECT 'ALTER FUNCTION '
       || format('%I.%I(%s)', n.nspname, p.proname, oidvectortypes(p.proargtypes))
       ||' OWNER TO newuser;'
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
AND n.nspname NOT IN ('pg_catalog','information_schema')
AND pg_catalog.pg_get_userbyid(p.proowner)='postgres';
\o
\i /tmp/hack.sql

Enjoy !