Moving data from one column to another in PostgreSQL

Question

Sometimes, one might want to move some data from one column to another. By moving (in constrast to copying), I mean that the new column was originally null before doing the operation, and the old column should be set to null after doing the operation.

I have a table defined as such:

CREATE TABLE photos(id BIGSERIAL PRIMARY KEY, photo1 BYTEA, photo2 BYTEA);

Suppose there is an entry in the table where photo1 contains some data, and photo2 is NULL. I would like to make an UPDATE query wuch that photo1 becomes NULL and photo2 contains the data that was originally in photo1.

I issue the following SQL command (WHERE clause left out for brevity):

UPDATE photos SET photo2 = photo1, photo1 = NULL;

It seems to work.

I also tried it this way:

UPDATE photos SET photo1 = NULL, photo2 = photo1;

It also seems to work.

But is it guaranteed to work? Specifically, could photo1 be set to NULL before photo2 is set to photo1, thereby causing me to end up with NULL in both columns?

As an aside, this standard UPDATE syntax seems inefficient when my BYTEAs are large, as photo2 has to be copied byte-by-byte from photo1, when a simple swapping of pointers might have sufficed. Maybe there is a more efficient way that I don't know about?

Answer 1

This is definitely safe.

Column-references in the UPDATE refer to the old columns, not the new values. There is in fact no way to reference a computed new value from another column.

See, e.g.

CREATE TABLE x (a integer, b integer);
INSERT INTO x (a,b) VALUES (1,1), (2,2);

UPDATE x SET a = a + 1, b = a + b;

results in

test=> SELECT * FROM x;
 a | b 
---+---
 2 | 2
 3 | 4

... and the ordering of assignments is not significant. If you try to multiply-assign a value, you'll get

test=> UPDATE x SET a = a + 1, a = a + 1;
ERROR:  multiple assignments to same column "a"

because it makes no sense to assign to the same column multiple times, given that both expressions reference the old tuple values, and order is not significant.

However, to avoid a full table rewrite in this case, I would just ALTER TABLE ... ALTER COLUMN ... RENAME ... then CREATE the new column with the old name.