Menu
I'm working with Postgres 9.1 and I have added a new boolean column to my table:
ALTER TABLE frontend_item ADD COLUMN is_generic BOOLEAN;
The value of this column will be based on the value of another column, code. If characters 10-11 of code are AA then the value of is_generic should be TRUE. Otherwise it should be false (or null if it hasn't been set yet).
My question, is how can I do this in Postgres? I've been able to work out some individual components using the docs:
UPDATE frontend_item SET is_generic...
And then I know I can get the substring of code as follows:
substring(code from 10 for 2)
But how do I turn substring into a boolean, and then glue it together with the UPDATE statement?
558
UPDATE table SET col = new_value WHERE col = old_value AND other_col = some_other_value; UPDATE table SET col = new_value WHERE col = old_value OR other_col = some_other_value; As you can see, you can expand the WHERE clause as much as you'd like in order to filter down the rows for updating to what you need.
In Microsoft SQL Server, we can change the order of the columns and can add a new column by using ALTER command. ALTER TABLE is used to add, delete/drop or modify columns in the existing table. It is also used to add and drop various constraints on the existing table.
Syntax. UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN WHERE [condition];
UPDATE frontend_item
SET is_generic = (substring(code from 10 for 2) = 'AA');
But do you really need the redundant column? You can just keep using the expression substring(code from 10 for 2), which is more reliable in the face of possible updates to the table. The cost for the function is low and keeping the table small is a benefit for overall performance.
Redundant storage is rarely a good idea. Only for special optimizations.
BTW, there is a less verbose Postgres variant doing the same:
substr(code, 10, 2)
See string functions in the manual.
76
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
