Table is logical object, projection is physical object - column in projection is reference to table column, expression not allowed. Only PK/FK join are allowed.
PS Hm...
CREATE PROJECTION AS SELECT CASE WHEN (sysdate() - login_ts) % randomint(7) = 5 ...
How to create projection for this? Encoding? Sorting?
In projection no, but you can optimize a projection for calculation and create a VIEW. Also you can create a derived column.
For example:
daniel=> create table calendar ("date" date not null, "year" int default year("date"), "month" int default month("date"), "day" int default day("date")); CREATE TABLE daniel=> copy calendar(date) from stdin direct; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> 2000-01-01 >> 2000-01-02 >> 2000-01-03 >>\ \ . --- PARSER ((( daniel=> select * from calendar ; date | year | month | day ------------+------+-------+----- 2000-01-01 | 2000 | 1 | 1 2000-01-02 | 2000 | 1 | 2 2000-01-03 | 2000 | 1 | 3 (3 rows)
Comments
No. https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/AdministratorsGuide/ConfiguringTheDB/Phys...
Table is logical object, projection is physical object - column in projection is reference to table column, expression not allowed. Only PK/FK join are allowed.
PS
Hm... How to create projection for this? Encoding? Sorting?
The fields in projection should always be a direct set of base table fields ??
In projection no, but you can optimize a projection for calculation and create a VIEW. Also you can create a derived column.
For example:
daniel=> create table calendar ("date" date not null, "year" int default year("date"), "month" int default month("date"), "day" int default day("date")); CREATE TABLE daniel=> copy calendar(date) from stdin direct; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> 2000-01-01 >> 2000-01-02 >> 2000-01-03 >>\ \ . --- PARSER
((( daniel=> select * from calendar ; date | year | month | day ------------+------+-------+----- 2000-01-01 | 2000 | 1 | 1 2000-01-02 | 2000 | 1 | 2 2000-01-03 | 2000 | 1 | 3 (3 rows)