Edouardb Edouardb - 7 months ago 9
SQL Question

SQL Column as a "View Column"

I have the following table

security
of securities:

id | bbg_ticker | ticker | currency | exchange | type
-------+------------------+----------+----------+----------+--------
1762 | 653 HK Equity | 653 | | HK | Equity
5734 | LO US Equity | LO | | US | Equity
6175 | H US Equity | H | | US | Equity
4563 | BDTY Index | BDTY | | | Index
6253 | MOA Comdty | MOA | | | Comdty
7414 | 1333 JP Equity | 1333 | | JP | Equity
7538 | 2377 TT Equity | 2377 | | TT | Equity


As you can guess, the Bloomberg ticker (column
bbg_ticker
) is actually the concatenation of:


  • ticker + exchange + type if exchange is not null

  • ticker + type else



This is obviously an irrelevant duplicating of data. For instance, one could one day change the
exchange
of a security without changing its
bbg_ticker
, which would lead to a failure in the data integrity.

As I am currently refactoring my database, I was wondering if I could do something like a "view" but with a column (not a whole table). This view would replace the
bbg_ticker
column (would be named the same), and would be defined for instance with (I know the following request is totally wrong):

CREATE COLUMN VIEW bbg_ticker
ON security AS (
ticker
|| (IF exchange IS NOT NULL THEN (' ' || exchange) ELSE '')
|| ' '
|| type
)
;


It seems than VIEWS cannot be defined this way, but would you see another solution which would lead to the same result?
I know I could use check constraints and regex, which would solved the integrity question, but not the duplicating one. My question is more about having a better solution.

I am on PostgreSQL 9.5.

Thank you

Answer

You can just create a view from the whole table:

CREATE VIEW improved_security AS
    SELECT id, concat_ws(' ', ticker, exchange, type) AS  bbg_ticker,
           ticker, currency, exchange, type
    FROM security;

The concat_ws() function does all the nasty string concatenation for you, with the appropriate separator (a space in this case) and ignoring NULLs.

If you need to maintain the name security in order not to break any applications, then you should rename the table (which will require a table lock for a brief period of time) and then create the view with the name security and grant appropriate permissions to the view and revoke the same from the table. Wrap this all inside of a transaction so you will not get any errors on the client side, at best a brief delay in putting or pulling data.