Asked  7 Months ago    Answers:  5   Viewed   116 times

Does PostgreSQL support computed / calculated columns, like MS SQL Server? I can't find anything in the docs, but as this feature is included in many other DBMSs I thought I might be missing something.

Eg: http://msdn.microsoft.com/en-us/library/ms191250.aspx

 Answers

38

Up to Postgres 11 generated columns are not supported - as defined in the SQL standard and implemented by some RDBMS including DB2, MySQL and Oracle. Nor the similar "computed columns" of SQL Server.

STORED generated columns are introduced with Postgres 12. Trivial example:

CREATE TABLE tbl (
  int1    int
, int2    int
, product bigint GENERATED ALWAYS AS (int1 * int2) STORED
);

db<>fiddle here

VIRTUAL generated columns may come with one of the next iterations. (Not in Postgres 14, yet).

Related:

  • Attribute notation for function call gives error

Until then, you can emulate VIRTUAL generated columns with a function using attribute notation (tbl.col) that looks and works much like a virtual generated column. That's a bit of a syntax oddity which exists in Postgres for historic reasons and happens to fit the case. This related answer has code examples:

  • Store common query as column?

The expression (looking like a column) is not included in a SELECT * FROM tbl, though. You always have to list it explicitly.

Can also be supported with a matching expression index - provided the function is IMMUTABLE. Like:

CREATE FUNCTION col(tbl) ... AS ...  -- your computed expression here
CREATE INDEX ON tbl(col(tbl));

Alternatives

Alternatively, you can implement similar functionality with a VIEW, optionally coupled with expression indexes. Then SELECT * can include the generated column.

"Persisted" (STORED) computed columns can be implemented with triggers in a functionally identical way.

Materialized views are a closely related concept, implemented since Postgres 9.3.
In earlier versions one can manage MVs manually.

Tuesday, June 1, 2021
 
medhybrid
answered 7 Months ago
89

From the documentation:

When you create a materialized view, Oracle Database creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle Database uses these objects to maintain the materialized view data.

So having the table and materialized view with the same name is normal. The MV needs to store the data somewhere, so having a table makes sense; the MV itself then defines how the table data is maintained.

You can use the ON PREBUILT TABLE clause to create a view over an existing table, which I assume is what "they had a temp table earlier ... and switched to Materialized view later" refers to.

You can also go the other way, with the DROP MATERIALIZED VIEW ... PRESERVE TABLE option, which leaves the underlying table behind.

When you SELECT * FROM TEMP_DATA; you're querying the underlying table, but the distinction isn't really important as they refer to the same combined object.

Based on the definition to added to the question later, it will refresh every day at midnight.

Thursday, August 12, 2021
 
clean_coding
answered 4 Months ago
73

Seems like with the addition of the DISTINCT, you've made your view's underlying SQL ineligible for fast refresh, and therefore not able to be used with ON COMMIT (even tho you specify refresh complete instead of refresh fast). From Oracle docs:

The two refresh execution modes are ON COMMIT and ON DEMAND. Depending on the materialized view you create, some of the options may not be available. Table 8-4 describes the refresh modes.

Table 8-4 Refresh Modes

ON COMMIT

Refresh occurs automatically when a transaction that modified one of the materialized view's detail tables commits. This can be specified as long as the materialized view is fast refreshable (in other words, not complex). The ON COMMIT privilege is necessary to use this mode.

ON DEMAND

Refresh occurs when a user manually executes one of the available refresh procedures contained in the DBMS_MVIEW package (REFRESH, REFRESH_ALL_MVIEWS, REFRESH_DEPENDENT).

The same document link has a list of restrictions for fast refresh as well.

Saturday, August 14, 2021
 
Valdas
answered 4 Months ago
86

You still have an instance of gunicorn running. foreman runs a daemon instance of gunicorn so if you close terminal down it will still run in the background. For this reason you should always Ctrl + C foreman before closing down terminal. There is however a way to kill the server.

Firstly you can find the id for unicorn to kill it via $ ps ax|grep unicorn and then using the id of the gunicorn instance $ kill <id>

Alternatively you can use $ pkill gunicorn

Sunday, November 14, 2021
 
Mr.D
answered 3 Weeks ago
89

Because there is no such member. virtual functions take place from the class where they are declared first down through all derived classes. They do not get bubbled up. When the compiler looks at arr[ i ]->f2() it looks up the definition of arr for its type. arr has (static) type A. So, the compiler looks up A's definition for a suitable definition of f2 and doesn't find any. Hence the diagnostic.

virtual functions can be safely inlined. In fact, all in-class definitions are implicitly inlined by the compiler. So, your A::f1 is already inlined.

Thursday, December 2, 2021
 
Carrot
answered 5 Days ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :  
Share