5

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Richard
  • 62,943
  • 126
  • 334
  • 542

1 Answers1

7
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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks very much for this. I want to index on this value, which is why I was thinking of creating a redundant column. But could I do something like `CREATE INDEX code_substr ON code (substring(code from 10 for 2) = 'AA')`? And then query it using `SELECT * FROM CODE where (substring(code from 10 for 2) = 'AA');`? This feels a bit prone to typos at query time, though. – Richard Mar 17 '15 at 14:23
  • @Richard: You *could*. Postgres has expression indexes. There are many example here: http://stackoverflow.com/search?q=[postgres]+url%3Ahttp%3A%2F%2Fwww.postgresql.org%2Fdocs%2Fcurrent%2Finteractive%2Findexes-expressional.html But an index on a boolean column (or expression) is hardly ever useful: http://stackoverflow.com/questions/12025094/performance-tuning-create-index-for-boolean-column/12026593#12026593. Start a new question about that if its unclear. You can always link to this one for context. – Erwin Brandstetter Mar 17 '15 at 14:31