My Favorite PostgreSQL Queries and Why They Matter

Re:My Favorite PostgreSQL Queries and Why They Matter by wiredfool on Sunday April 15, 2018 @03:11PM (#16844324)

I'd probably use a temp table for that, rather than a case.

  begin;
  select id, new_rating into temp_ratings values (1,25),(2,94),(3,16);
  update table place set rating=new_rating
    from temp_ratings
    where place.id=temp_ratings.id;
  drop table temp_ratings;
  commit;

Scales well, and almost all db engines know how to do that join. You can index the id, or use a temp table, or whatever depending on the shape and quantity of your data.

(edit -- actually finished the post)

[ Parent | on HN ]

Re:My Favorite PostgreSQL Queries and Why They Matter by trinitry3 on Monday April 16, 2018 @12:48PM (#16850459)

This is really bad advice:

  1) This is not really a temporary table in the traditional
     sense; you're creating a real table and then dropping it.
     This has problems with concurrency (you can't have two
     "real" tables with the same name), among other issues.
  2) Temporary tables in Postgres suck because they bloat
     the catalogs.  Creating temporary tables regularly is
     better avoided.
  3) There's no real need to create a table of any kind.  A
     regular VALUES would do.  (Though you might have to encode
     some type information into the query.)

[ Parent | on HN ]