Setting column DEFAULT value as a concatenation of other columns within CREATE TABLE statement

Community Manager
Community Manager

Is it possible to set a DEFAULT value for a column as a concatenation of other columns?

 

Consider a scenario, there exists a table of a kind,

CREATE TABLE "EXAMPLE"(
"COL1" VARCHAR(10),
"COL2" VARCHAR(10),
"COL_GENERATED" VARCHAR(20));

where upon insertion of values into COL1 and COL2, COL_GENERATED would be automatically filled as COL1 || COL2

I am looking for something similar to GENERATED ALWAYS AS in MySQL.

Connecting Customers, Partners, Prospects and Exasolians is my passion. Apart from that I cycle, listen to music, and try to understand what all those technical discussions really mean...
1 ACCEPTED SOLUTION

Community Manager
Community Manager

Hi Christian,

Unfortunately, this isn't really possible right now. We have an idea for this already created (IDEA-199). If you are a customer, you can vote on it to help increase the priority!  

Sports Enthusiast. Database Guy. Member of Team Exasol.
Having trouble? Just let me know!

Please Give Kudos if you find this post useful and also mark this as Accepted Solution if this is the required solution

View solution in original post

3 REPLIES 3

Padawan

Hi Christian,

coming back to this question, asked by the past me in the previous version of the Exasol community, I now understand it's possible to construct this kind of functionality using a pre-processor scripts, granted, painstaking to write, yet surely possible. Now, what I can see as options is generating pre-write, that is altering an insert statement, adding a relevant column before writing, or pre-read, that is altering a select query.

From what I understand, pre-read option would be more in line with the GENERATED ALWAYS AS, since in that case the data in the specific column shouldn't exist until queried, yet it has a major drawback coming from the tendency of the SELECT statements to get quite complex, so the pre-processor script would have a giant overhead on different logic and checks.

The second, pre-write option, would actually create very real entries into the column, but since insert statements are rarely complex, it shouldn't have as big of an overhead.

I also have a vague notion, that something like this should be possible with a simple SCALAR RETURNS script in a post-write manner but a trigger would be necessary for that.

Besides the above mentioned, are there any other stumbling-blocks I should consider when writing this type of functionality?
Can a trigger functionality be achieved in some sensible way?

Thanks

Regards
Peter

Community Manager
Community Manager

Hi @LordBertson - while the preprocessor is certainly possible (though it would require some heavy testing), depending on how complex you make it, it will certainly have an impact on performance (especially since it runs before every statement). Would it be possible to make your ETL process 2-fold, one that loads all of the other columns, and another which loads the "generated" column using a select statement? (you could say this is actually post-write, since it happens after the initial load)

You can still commit only at the end, so that the data remains consistent.

 

Sports Enthusiast. Database Guy. Member of Team Exasol.
Having trouble? Just let me know!

Please Give Kudos if you find this post useful and also mark this as Accepted Solution if this is the required solution

Community Manager
Community Manager

Hi Christian,

Unfortunately, this isn't really possible right now. We have an idea for this already created (IDEA-199). If you are a customer, you can vote on it to help increase the priority!  

Sports Enthusiast. Database Guy. Member of Team Exasol.
Having trouble? Just let me know!

Please Give Kudos if you find this post useful and also mark this as Accepted Solution if this is the required solution

View solution in original post