6

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?

Improve this question
1
  • Yes, this is guaranteed to work (and required by the SQL standard) Commented Jul 9, 2017 at 5:46

1 Answer 1

Reset to default
8

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks, this should allay my fears of non-deterministic behavior. Regarding the column renaming, I can't do that because it's not a full table rewrite. When I said "WHERE clause left out for brevity" I mean that there's a WHERE clause in the UPDATE statement that I omitted (and hence not all rows will be affected). Maybe I wasn't clear about that.
I agree with @Jacobm001's now-deleted post's comment that what you're doing is a bit weird in terms of moving data from column to column. I'd normalise to a side-table instead and update the referencing FK to the new photo.
I didn't see the comment before the post was deleted and I don't have enough reputation to see deleted comments. Could you please elaborate on why using a side-table would be better, and whether I should be doing this for just large data (like BYTEA) or for small text fields as well?
@Bernard Because of the automatic out-of-line storage done by TOAST it's not very significant for efficiency; the data won't get copied when you do this. So it's mostly a matter of "clean feeling" database design. Your design isn't wrong, it's just not how I think I'd prefer to do it, and yours may have performance advantages. Don't worry.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.