I have a table which has 2 columns ( JobDate , RecordTime) -------------------------------------- JobDate | RecordTime | SumCol -------------------------------------- 2019-07-20 | 2019-07-21 | 2019-07-19 | 2019-07-20 | I need SumCol to have a default value as UNIX_TIMESTAMP(JobDate) + UNIX_TIMESTAMP(RecordTime) I've tried creating a virtual column but it gives me this error : **Expression of generated column 'Test2' contains a disallowed function.** that's what I tried: ALTER TABLE jobsTemp ADD SumCol TIMESTAMP AS (UNIx_timestamp(JobDate) + UNIx_timestamp(RecordTime));

I'm not sure what technology I need because I'm not a DBA, but I'm sure there's a good way to do this. I have a postgres database with this schema (top two tables exist): **So I'm trying to add the 3rd table to the database. It's a subset of observation: all latest observations by stream_id and target_id with the target_key value already joined into it.** I know upfront that 99% of the queries against the target table are asking for the latest row of a certain stream_id and target_id. so I thought instead of manually building another table with duplicate data, I'll try to leverage the power of the database to make a temporal table, or a foreign data wrapper or something, that is essentially a hard coded query on the database which looks like a table we can query, (and since it's querying a subset of the data, its much faster). Ok, so that's what I'm looking for but I don't know which technology to use, as I mentioned I've been searching and I found temporal tables, virtual tables and FDW. But I'm a programmer not a DBA so I'm having a hard time telling the difference or understanding which one matches my need. What technology can I use for this?

What is the intended use case for virtual columns with a function-based index? -------- The reason I ask (novice): I've been exploring options for pre-computing columns in a table. For example, in a roads table, populate a field using the spatially-intersecting zone number from a zone table. More information here: Options for computing fields. My experience is that spatial queries are often slow — whether using Oracle's SDO_GEOMETRY datatype/functions or using a user-defined object type like Esri's ST_GEOMETRY datatype/functions. So I definitely *do* want to pre-compute that calculation — to avoid constantly making costly calculations every time the query is used. At first, I was tempted to create a virtual column with a function-based index. But now (with input from others), I'm starting to wonder if precomputing a column using a function-based index might not be the right use for FBIs. Instead, I'm wondering if simply calculating a field in the table using a trigger might be a better option. (I'm aware that materialized views are also an option. But I don't have CREATE MATERIALIZED VIEW privileges.) ----- So, long story short, I suspect a virtual column and an FBI isn't appropriate for my use case. With that said, what ***is*** the right use case for a virtual column and an FBI?

In our environment, we usually add and drop generated columns every now and then. There is a very large table in the database with more than 3 million rows and adding/removing column was extremely slow. We opted to change the generated column from STORED to VIRTUAL, so that it is instantaneous. However, we hit another road block: When we try to add the VIRTUAL column after a certain column (I believe this is due to application logic) e.g. AFTER ColumnX, the speed is extremely slow again. It takes more than 4-5 hours to just add the generated VIRTUAL column now. Why would adding the virtual column without AFTER clause be instantaneous while adding AFTER clause slows it to a crawl? Database: MySQL version 5.7 Table rows: 32,636,254 ALTER TABLE mySchema.myTable ADD COLUMN MyColumn VARCHAR(50) GENERATED ALWAYS AS json_unquote(coalesce(json_extract(int_data,'$.Item.IRefNum'), json_extract(m_dt,'$.refId'))) Virtual AFTER ColumnX;

I'm just running a command: optimize table some_table_name_here; But it results with: Table | Op | Msg_type | Msg_text db.some_table_name_here | optimize | note | Table does not support optimize, doing recreate + analyze instead db.some_table_name_here | optimize | error | Unknown column 'db.t.total_area' in 'GENERATED ALWAYS' db.some_table_name_here | optimize | status | Operation failed 3 rows in set, 1 warning (0.001 sec) *Server version: 10.5.10-MariaDB-1:10.5.10+maria~buster-log mariadb.org binary distribution.* This problem appeared after upgrade from mariadb 10.3 to 10.5 (via 10.4). // EDIT I've recently found that the problem is related to generated (virtual) persistent/stored columns that I have in this table. Here are both columns that may have problems: calcOne float GENERATED ALWAYS AS (if(total_area = 0,0,if(square_meter_price = 0,0,round(((price_actual - auction) / total_area / square_meter_price - 1) * 100,2)))) STORED calcTwo float GENERATED ALWAYS AS (if(total_area = 0,0,if(square_meter_price_analog = 0,0,round(((price_actual - auction) / total_area / square_meter_price_analog - 1) * 100,2)))) STORED I've already tried `ALTER TABLE some_table_name_here DROP COLUMN calcOne; to drop column and add it again, but error happens: ERROR 1054 (42S22): Unknown column 'db.t.total_area' in 'GENERATED ALWAYS'` // EDIT As requested (result of SHOW CREATE TABLE some_table_name_here\G): CREATE TABLE some_table_name_here ( id bigint(11) NOT NULL, title varchar(1000) CHARACTER SET utf8mb4 DEFAULT NULL, description text CHARACTER SET utf8mb4 NOT NULL, price_actual int(15) NOT NULL, auction int(11) DEFAULT 0, square_meter_price int(11) DEFAULT 0, square_meter_price_analog int(11) DEFAULT 0, accuracy int(11) DEFAULT 0, accuracy_analog int(11) DEFAULT 0, total_area float DEFAULT 0, calcOne float GENERATED ALWAYS AS (if(total_area = 0,0,if(square_meter_price = 0,0,round(((price_actual - auction) / total_area / square_meter_price - 1) * 100,2)))) STORED, calcTwo float GENERATED ALWAYS AS (if(total_area = 0,0,if(square_meter_price_analog = 0,0,round(((price_actual - auction) / total_area / square_meter_price_analog - 1) * 100,2)))) STORED, PRIMARY KEY (id) USING BTREE, KEY idx_price_actual (price_actual), KEY idx_auction (auction), KEY idx_square_meter_price (square_meter_price), KEY idx_square_meter_price_analog (square_meter_price_analog), KEY idx_accuracy (accuracy), KEY idx_accuracy_analog (accuracy_analog), KEY idx_total_area (total_area) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 // EDIT When I added those virtual (generated) columns (on original mariadb server 10.3.27) I used this command/query: ALTER TABLE some_table_name_here ADD COLUMN sootn float AS (IF(total_area=0, 0, IF(square_meter_price=0, 0, ROUND(((((price_actual-auction)/total_area/square_meter_price)-1)*100), 2)))) PERSISTENT; ALTER TABLE some_table_name_here ADD COLUMN sootn_analog float AS (IF(total_area=0, 0, IF(square_meter_price_analog=0, 0, ROUND(((((price_actual-auction)/total_area/square_meter_price_analog)-1)*100), 2)))) PERSISTENT; There were no such problems before. I used optimize table command in order to free space from unexisting/dropped indexes. I've upgraded mariadb 100% using this instructions: https://mariadb.com/kb/en/upgrading-from-mariadb-103-to-mariadb-104/ https://mariadb.com/kb/en/upgrading-from-mariadb-104-to-mariadb-105/ https://mariadb.com/docs/deploy/upgrade-community-server/ Is there any way how can I find where and why this problem occurs? For now I only see this way in fixing this problem: 1) dump/export table 2) drop it (if it's possible) 3) create table structure 4) fill it with data // FINAL I've tried workaround provided by @RolandoMySQLDBA and it helped after slightly modification. Command INSERT INTO some_new_table_name_here SELECT * FROM some_table_name_here; produced error: ERROR 1906 (HY000): The value specified for generated column 'calcOne' in table 'some_table_name_here' has been ignored Since there are generated columns there, it's not possible to INSERT value into that column, so I had to change INSERT query to this: INSERT INTO some_new_table_name_here (id, title, description, price_actual, auction, square_meter_price, square_meter_price_analog, accuracy, accuracy_analog, total_area) SELECT id, title, description, price_actual, auction, square_meter_price, square_meter_price_analog, accuracy, accuracy_analog, total_area FROM some_table_name_here; And afterwards command was fine too: ANALYZE TABLE some_new_table_name_here; +-----------------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------------------------+---------+----------+----------+ | db.some_new_table_name_here | analyze | status | OK | +-----------------------------+---------+----------+----------+ 1 row in set (2.137 sec) I've also discovered one nice thing - after successfully duplicating table, renaming both of old and new of them and doing analyze, I tried to analyze and also optimize some_old_table_name_here table and it worked without errors! IDK what was the problem. Maybe since the table previously was in use, it was not possible to alter it, while there are generated columns.

I have two tables and I would like to generate a query relating the tables. Until now I have this query: SELECT Table1.*, table2.* FROM Table1 INNER JOIN table2 ON table2.FK_ID = Table1.ID but I return the 3 rows repeating the data in table 1. I would like my query to return the data as follows (see image) Any suggestions to see if it is possible to make my requirement? Thank you! =============================== UPDATE =============================== I Tried with this query SELECT Table1.*, (SELECT Table2.Columna2 WHERE Table2.Columna4='ALTA') AS Name1, (SELECT Table2.Columna2 WHERE Table2.Columna4='ATENCION') AS Name2 FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.FK_ID GROUP BY Table1.ID but in my **Name2** always is **NULL**

ALTER TABLE TAB_NAME ADD TEST_COL VARCHAR2(255);
ALTER TABLE TAB_NAME MODIFY TEST_COL GENERATED ALWAYS as ('some_exp') VIRTUAL;

Second script gives an error:

Error report -
ORA-54026: Real column cannot have an expression
54026. 0000 -  "Real column cannot have an expression"
*Cause:    Attempted to alter a real column to have an expression.
*Action:   This is not valid, change the DDL.

My query is SELECT (house_rent+conveyance+medical+dearness+others_allowances) AS earnings ,(income_tax+pro_tax+emp_state_insu+absence_fine+others_deductions) AS deductions ,(earnings - deductions) AS net_salary FROM salary and im getting an error that unknown column earnings and deductions because these are column alias not column name .. Any solution? Thanks in advance

We want to update the condition of a virtual column and to minimize error posibilites we want to extract the exact function working today to add new parameters to take into account. I've firstly test with desc tablename but doesn't work, only shows column types.

We use statement based bin log master-slave replication with MySQL 5.7. We use the slave for analytics and so add additional indexes and things not required on the master. I recently tried to add a virtual column and indexed it. Unfortunately, it seems that none of the new data being replicated ends up in the index. When I query the new rows, the generated column values are present and correct but when I filter the table on the generated column values, none of the rows created since the index was created are returned. Am I doing something wrong here or is this not supported with bin log replication?

I have the following function: CREATE FUNCTION UUID_AS_HEX(bytes IN RAW) RETURN CHAR DETERMINISTIC IS uuid CHAR(36); BEGIN RETURN REGEXP_REPLACE(bytes, '([0-9A-F]{8})?([0-9A-F]{4})?([0-9A-F]{4})?([0-9A-F]{4})?([0-9A-F]{12})', '\1-\2-\3-\4-\5'); END; and I want to create a virtual column: CREATE TABLE example_table ( uuid RAW(16) NOT NULL PRIMARY KEY, v_uuid CHAR(36) GENERATED ALWAYS AS (UUID_AS_HEX(uuid)) VIRTUAL ); throws the following exception: ORA-12899: value too large for column "V_UUID" (actual: 36, maximum: 4000) I tried creating the function with RETURN CHAR(36) DETERMINISTIC but it complains about that syntax with 2:14:PLS-00103: Encountered the symbol "(" when expecting one of the following: ; is authid as cluster order using external varying character ###How do I create this function where it match the CHAR(36) in the table?

Here's a table create table tq84_virtual_test_without ( col_1 number, col_2 number, col_3 number, col_4 number, col_5 number ); with the rule that col_5's value is the sum of the other four columns. So the table is filled accordingly: insert into tq84_virtual_test_without values( 1, 2, 3, 4, 10); insert into tq84_virtual_test_without values( 3, 8, 7, 5, 23); commit; Now, say, that there is a need to copy one row and change *just one* column's value. This can of course be done quite elegantly (imho, that is) with a *rowtype-variable*, like so declare r tq84_virtual_test_without%rowtype; begin select * into r from tq84_virtual_test_without where col_2 = 8; r.col_4 := r.col_4 - 2; r.col_5 := r.col_5 - 2; insert into tq84_virtual_test_without values r; end; / This is elegant because it doesn't clutter the source code with insert into ... (col_1, col_2...) values (.., ..) statements and I'd like to keep this feature, if possible. On the other hand, col_5 is a perfact candidate for a *virtual column*. So, here's almost the same thing, but with col_5 being a virtual column: create table tq84_virtual_test_with ( col_1 number, col_2 number, col_3 number, col_4 number, col_5 as (col_1 + col_2 + col_3 + col_4) virtual ); insert into tq84_virtual_test_with values( 1, 2, 3, 4, default); insert into tq84_virtual_test_with values( 3, 8, 7, 5, default); commit; Now, and this is unfortunate, the following construct doesn't work anymore: declare r tq84_virtual_test_with%rowtype; begin select * into r from tq84_virtual_test_with where col_2 = 8; r.col_4 := r.col_4 - 2; -- -- ORA-54013: INSERT operation disallowed on virtual columns -- insert into tq84_virtual_test_with values r; end; / So, is this still somehow possible (and if so, how) to use this *rowtype-variable* along with *virtual columns*?

In SQL database that stores Images in a parcelImages table.Since each parcel can take part in multiple Images and each images can involve multiple parcels. I want to get each parcel having Image value with comma separated values as shown above I tried with this SQL Query SELECT ParcelId,id,concat(ParcelId, ' ', id) FROM ParcelImages WHERE Id IN (43418,43464,43465,43466,43467,43470,43471,43482,43483) but expected result not get. How can this be done? I have also tried an INNER JOIN How can I achieve this, I'm kinda stuck at the early beginning. If there is a table called STUDENTS ID StudentName ---------- ------------- 1 Mary 1 John 1 Sam 2 Alaina 2 Edward Result I expected was: ID StudentName ---------- ------------- 1 Mary, John, Sam 2 Alaina, Edward

I'm trying to create a table in OracleDB and assign random number to one of the columns as a default. I have this setup working in postgresql create table table_name ( column integer NOT NULL DEFAULT (random()) ) how can I do something similar in oracle (preferably without triggers)? I've tried this create table table_name ( column integer generated always as (dbms_random.random) virtual ) but oracle doesn't like it since the function is non deterministic.

Starting reading point: https://www.percona.com/blog/2015/04/29/generated-virtual-columns-in-mysql-5-7-labs/ Running some tests with the most recent percona 5.7 they seem to work well. Explain plans and wall time look pretty good but I'm not seeing any difference in show table status index_length or data_length. How do you find the true footprint or overhead for using these? Unlike views they do seem to be faster than bookmarked selects.

Does anyone know of any Oracle dictionary views that hold references of 'real' columns to virtual columns? For example lets say I have a table like this: create table t ( c1 varchar2(5) ,c2 as (c1 || '*') ) I'm after a way of determining that c1 has a dependency of c2 (without trying to parse user_tab_cols.data_default). I'm writing a schema synchronising tool and I'm trying to work out what I need to do to synchronise a table with a 'model' performing the minimal amount of work. If a model showed that c1 from the above table changed from varchar2(5) to varchar2(6), I'm hoping to be able to determine that I need to do something like: alter table t modify( c2 as ( null ) ); alter table t modify( c1 varchar2(6) ); alter table t modify( c2 as ( c1 || '*' ) ); And avoid an ORA-54031. [EDIT] It seems I wasn't clear enough in explaining my problem so here goes. When our developers start working on a bug they create a git branch of the application along with a database schema that is populated with the contents of the master git branch. While working on the bug the developer may make DML changes or may occasionally make DDL changes (as well as code changes of course). Once their bug has been completed and reviewed, their git branch is merged with master. The sync tool allows a complete 'diff' to be done on the database schema - both from a content and a structure perspective. This forms part of the review. The developer uses the sync tool to generate an export of their schema and this export is part of the branch. After the bug branch is merged to master, the master database schema is sync'd from the export done by the developer. Yes, scripts would be one solution to the problem but it requires a level of discipline that is not present in all developers. The sync tool currently handles the scenario I outlined but imo somewhat clumsily. The reason I asked my original question was that if I was able to determine via a dictionary view the relationship between real and virtual columns, the sync sql my tool generates would be more 'surgical'. Thanks.

I'm wondering whether exists any database engine that can share cells or rows amongst many tables. For example: TABLE Customer ( CID: int Fullname: nvarchar(50) Age: int ) TABLE Group ( GID: int Name: nvarchar(50) ) TABLE Customer_Group ( CusName: nvarchar(50) GrpName: nvarchar(50) ) In which, Customer_Group is designated for a certain query, like a column family in Cassandra. Now I want to insert to Customer_Group table a row whose CusName cell references to Fullname cell of a row in table Customer. Similar to GrpName. As a result, when I update Fullname in Customer table, I don't have to update Customer_Group table, because it references to same cell address. And Customer_Group table itself doesn't have to store actual data for CusName but in fact is a pointer. Does anyone know what database engine supports this idea? And is it a good idea though?

I have a SQL Server 2000 database used by our ERP system. There are many columns that always have the same value. A table row is really huge, potentially exceeding the maximum row size in SQL Server 2000 (8kB). I'm thinking about replacing these columns with constant calculated columns. This should decrease the size of the table. I expect better performance, because there is less data to read from disk, when the server needs to load the table in memory. Is this a good idea ? Will I obtain the expected performance gain ?

I have a table id name 1 Alpha 2 Alpha 3 Beta 4 Charlie 5 Charlie I want to assign a temporary id at the run time in the SQL statement, So the desired result should be like myid name 1 Alpha 2 Beta 3 Charlie So basically the logic should be like reading the value of name field and see if it is changed then increment the value for custom field. How we can achieve this?