Is it possible to CREATE TABLE with a column that is a combination of other columns in the same table?

Question

I know that the question is very long and I understand if someone doesn't have the time to read it all, but I really wish there is a way to do this.

I am writing a program that will read the database schema from the database catalog tables and automatically build a basic application with the information extracted from the system catalogs.

Many tables in the database can be just a list of items of the form

CREATE TABLE tablename (id INTEGER PRIMARY KEY, description VARCHAR NOT NULL);

so when a table has a column that references the id of tablename I just resolve the descriptions by querying it from the tablename table, and I display a list in a combo box with the available options.

There are some tables however that cannot directly have a description column, because their description would be a combination of other columns, lets take as an example the most important of those tables in my first application

CREATE TABLE bankaccount (
    bankid INTEGER NOT NULL REFERENCES bank,
    officeid INTEGER NOT NULL REFERENCES bankoffice,
    crc INTEGER NOT NULL,
    number BIGINT NOT NULL
);

this as many would know, would be the full account number for a bank account, in my country it's composed as follows

[XXXX][XXXX][XX][XXXXXXXXXX]
  ^      ^    ^    ^
bank id  |   crc   account number 
         |
         |_ bank office id

so that's the reason of the way my bankaccount table is structured as is.

Now, I would like to have the complete bank account number in a description column so I can display it in the application without giving a special treatment to this situation, since there are some other tables with similar situation, something like

CREATE TABLE bankaccount (
    bankid INTEGER NOT NULL REFERENCES bank,
    officeid INTEGER NOT NULL REFERENCES bankoffice,
    crc INTEGER NOT NULL,
    number BIGINT NOT NULL, 
    description VARCHAR DEFAULT bankid || '-' || officeid || '-' || crc || '-' || number
);

Which of course doesn't work since the following error is raised¹

ERROR:  cannot use column references in default expression

If there is any different approach that someone can suggest, please feel free to suggest it as an answer.

¹ _{This is the error message given by PostgreSQL.}

i am not familiar to postgres, in mssql you can add a so called "computed column", which is computed by the values of other columns in the same row — swe, Mar 09 '15 at 16:06
Have a look at this question: http://stackoverflow.com/questions/8250389/computed-calculated-columns-in-postgresql — swe, Mar 09 '15 at 16:08
@swe this link is great, I just learned something new and really useful about postgresql. — Iharob Al Asimi, Mar 09 '15 at 16:47

score 3 · Answer 1 · answered Mar 09 '15 at 16:02

3

What you want is to create a view on your table. I'm more familiar with MySQL and SQLite, so excuse the differences. But basically, if you have table 'AccountInfo' you can have a view 'AccountInfoView' which is sort of like a 'stored query' but can be used like a table. You would create it with something like

CREATE VIEW AccountInfoView AS 
SELECT *, CONCATENATE(bankid,officeid,crc,number) AS FullAccountNumber 
FROM AccountInfo

Another approach is to have an actual FullAccountNumber column in your original table, and create a trigger that sets it any time an insert or update is performed on your table. This is usually less efficient though, as it duplicates storage and takes the performance hit when data are written instead of retrieved. Sometimes that approach can make sense, though.

answered Mar 09 '15 at 16:02

RobP

9,144
3
20
33

The trigger idea isn't bad, I will wait to see if someone else answers, if not then I will use this approach. – Iharob Al Asimi Mar 09 '15 at 16:05
Triggers would definitely be the way to go if you can't use a view! – RobP Mar 09 '15 at 16:06
The only good reason to store the full account number would be if you wanted to index it. – Dan Bracuk Mar 09 '15 at 16:27

score 2 · Accepted Answer · answered Jun 24 '15 at 02:19

What actually works, and I believe it's a very elegant solution is to use a function like this one

CREATE FUNCTION description(bankaccount) RETURNS VARCHAR AS $$   
    SELECT
        CONCAT(bankid, '-', officeid, '-', crc, '-', number)
    FROM
        bankaccount this
    WHERE
        $1.bankid = this.bankid AND
        $1.officeid = this.officeid AND
        $1.crc = this.crc AND
        $1.number = this.number
$$ LANGUAGE SQL STABLE;

which would then be used like this

SELECT bankaccount.description FROM bankaccount;

and hence, my goal is achieved.

_{Note: this solution works with PostgreSQL only AFAIK.}

Is it possible to CREATE TABLE with a column that is a combination of other columns in the same table?

2 Answers2