Postgresql – Replacing last 3 characters of values

postgresqlpostgresql-9.3

I presumably appended .png to all entries in the column image like this:

update genres set image=image||'.png'.

I then realised that the images I'm referencing will be jpegs; and I'm too lazy to go and convert them; so I'd rather spend 10 minutes trying to form an SQL query and then result to serverfault; so I want to change them again.

I've tried:

update genres set image = image.slice(0,-4)||'.png';

and then I remember it's SQL, everything is as confusing as its string-concatenation operator.

So then I looked up a few string functions and came up with this:

UPDATE genres SET image = right(overlay(right(image) placing right('.png') FROM 0));

But it just doesn't work. There's always someone who's been working with SQL for 20 years that can write this paragraph in 10 characters of functional code.

I'm postgresql, it's basically the same thing.

Best Answer

There are several ways to do that.

The first one that Rick already mentioned will work on Postgres as well:

update genres
   set image = replace(image, '.png', '.jpg');

But that will not work if the file names contain .png before the end. To be sure to only replace that value at the end you can use a regular expression:

update genres
   set image = regexp_replace(image, '(.*)\.png$', '\1.jpg');

or you can simply use the first "length - 3" characters and append the new extension to that.

update genres
   set image = left(image, -3)||'png';

Alternatively as you seem to not like the || operator that has been defined for SQL 30 years ago:

update genres
   set image = concat(left(image, -3), 'png');

Related Solutions

Postgresql – Get maximum from aggregated values in a query

SELECT *
FROM  (
   SELECT match_id
         ,SUM(CASE WHEN d.player_slot BETWEEN   0 AND   4
                   THEN d.kills ELSE 0 END) AS team1_kills
         ,SUM(CASE WHEN d.player_slot BETWEEN 128 AND 132
                   THEN d.kills ELSE 0 END) AS team2_kills
   FROM   details d
   WHERE (d.player_slot BETWEEN   0 AND   4
      OR  d.player_slot BETWEEN 128 AND 132) -- parens optional
   GROUP  BY d.match_id
   ) sub
ORDER  BY @(team1_kills - team2_kills) DESC
LIMIT  1;

The subquery is only for convenience.
The WHERE can might improve performance if a matching index is available. Redundant otherwise.
The CASE statements sum only kills from qualified players.
The @- operator produces the absolute value of the score difference. This is used to sort by (descending), then we only need to pick the first.
Don't use m as table alias for details, when a second table called matches is involved. That's confusing. Using d instead.
More generally, why do you join to matches at all? It is not used in the query. Are there details without associated match to exclude? (Then it would make sense.)

Based on comments

The query could probably be simpler / faster this way:

SELECT *
FROM  (
   SELECT match_id
         ,SUM(CASE WHEN p.team_id = ? THEN d.kills ELSE 0 END) AS team1_kills -- team1
         ,SUM(CASE WHEN p.team_id = ? THEN d.kills ELSE 0 END) AS team2_kills -- team2
   FROM   details d
   JOIN   players p ON p.id = d.player_slot  -- guessing from comment
   WHERE  p.team_id  IN (?, ?)               -- teams to compare go here.
   GROUP  BY d.match_id
   ) sub
ORDER  BY @(team1_kills - team2_kills) DESC
LIMIT  1;

Postgresql – Replacing a large object in PostgreSQL

The idea behind the large object API is to mimic a file-like API. The OID is like the path of the file, and the file descriptor obtained by lo_open or lo_creat is the equivalent of the POSIX open() and creat() system calls for files.

JDBC provides LargeObject.truncate() and the both libpq (in C) and the server have built-in lo_truncate() functions.

So yes it makes sense to replace LO contents (keeping the same OID) by a truncate followed by lo_write if libpq, or lowrite if server-side code, or LargeObject.write() if using the dedicated JDBC class, as you could do with a file on a filesystem.

It would work as well to create a new LO with a new OID and then unlink the old one, it's just less elegant and it consumes a new OID for no good reason.

Best Answer

Related Solutions

Postgresql – Get maximum from aggregated values in a query

Based on comments

Postgresql – Replacing a large object in PostgreSQL

Related Question