Postgres question: Can I easily update two columns in the same table with the same value, by setting one and then referring to the first column's new value to set the second?
In MariaDB I can do this:
update tablename set column1='new value', column2=column1
Is there a Postgres equivalent?
🇳🇱
@noelle Is there a reason you wouldn't want to just set the new value on both columns in the same command rather than self referencing?
I dont know if the self referencing would work, never tried but id imagine just setting the same value on both columns would be just as good and perhaps even a tad faster?
Ultimately id have to try it to see and dont have PSQL next to me right now.
@freemo @craigmaloney (Also, full disclosure, I'm asking on behalf of a friend who has already rejected any solution that involves repeating the input. :)
Fair, wish i had a PSQL instance nearby to test on.. it might just work as is I dont know, if not there are some solutions of increasing complexity I can think of that should work
@freemo @craigmaloney Yeah, the solution I keep coming back to is stored procedures, which aren't ideal but might be the only real option. Ah well. Thank you!
Oh no i am pretty sure there is easier than that.. I jsut cant test it.. This is what I'd try off hand (but may need tweaking as i cant test it and doing this off the top of my head):
WITH foo AS (
SELECT 'desired value'
)
update tablename set column1=foo, column2=foo;
@freemo @craigmaloney Oh, interesting. It hadn't occurred to me to put WITH first.
Yea its a bit of a weird syntax but its basically how you would create a variable in a query in the only way I know how without getting more complex.
See examples here for this general format: https://www.postgresql.org/docs/9.1/queries-with.html
Thats fair, particularly if your just doing this manually. I had assumed this was done programatically where the variables were set in a local language and thus that wouldnt be an issue when translating it over to SQL... but yea if this is a by-hand operation on a DB i can see the need/advantage. Sorry I dont have a good answer for you.
@freemo The issue is scaling - in the actual case I need to update more than two columns with long values, and referencing previous updates gives less chance for error than typing/pasting multiple times. cc @craigmaloney