Stack Overflow
Stack Overflow

Questions tagged [virtual-column]

A column that is not existing in a table of the database, but that is calculated at access time.

A column that is not existing in a table of the database, but that is calculated at access time.

50 questions
10
votes
3 answers

INSERT INTO ... SELECT if destination column has a generated column

Have some tables: CREATE TABLE `asource` ( `id` int(10) unsigned NOT NULL DEFAULT '0' ); CREATE TABLE `adestination` ( `id` int(10) unsigned NOT NULL DEFAULT '0', `generated` tinyint(1) GENERATED ALWAYS AS (id = 2) STORED NOT NULL ); I copy…
alik
  • 2,244
  • 3
  • 31
  • 44
8
votes
1 answer

Concatenating numbers in virtual column expression throws ORA-12899: value too large for column

While I gave this answer to a question yesterday, I suggested to use a VIRTUAL COLUMN for computed values instead of manually updating it. I did a test myself, and figured out an issue with the data size that the virtual column expression takes…
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
7
votes
1 answer

MySQL SELECT return wrong results

I'm working with MySQL 5.7. I created a table with a virtual column (not stored) of type DATETIME with an index on it. While I was working on it, I noticed that order by was not returning all the data (some data I was expecting at the top was…
Stefano Giacone
  • 2,016
  • 3
  • 27
  • 50
6
votes
3 answers

Identify if a column is Virtual in Snowflake

Snowflake does not document its Virtual Column capability that uses the AS clause. I am doing a migration and needing to filter out virtual columns programatically. Is there any way to identify that a column is virtual? The Information…
Rob D
  • 121
  • 1
  • 4
4
votes
2 answers

Rails PostgreSQL check if column is virtual

PostgreSQL 12 has a cool Generated Columns feature but I can't check whether column is virtual or not in Rails. For reference, MySQL Adapter in Rails allows you to do like MyModel.supports_virtual_columns? => true MyModel.columns.last.virtual? =>…
Al17
  • 431
  • 7
  • 20
4
votes
1 answer

is there any way to check if a column is virtual in java?

I want to know if there is any way to check if a column in a table is virtual in java? I was trying the ResultSetMetaData no luck there: rsmd.isWritable(column); rsmd.isReadOnly(column); rsmd.isDefinitelyWritable(column); I need to check if it is a…
Habib Gahntous
  • 41
  • 1
3
votes
1 answer

Apache Druid GroupBy Virtual columns

I am trying to do a groupby virtual column in a Druid native query which looks like this... { "queryType": "groupBy", "dataSource": "trace_info", "granularity": "none", "virtualColumns": [ { "type": "expression", "name":…
PhaKuDi
  • 141
  • 8
3
votes
2 answers

What is the alternative for generated column in MySQL 5.6

I have a MySQL alter statement ALTER TABLE `employee` ADD `employee_name_generator` CHAR(20) GENERATED ALWAYS AS  (COALESCE(concat(`employee_name`), '^')) VIRTUAL; This is needed for adding employee_name_generator in a unique constraint. This…
user9920500
  • 606
  • 7
  • 21
3
votes
1 answer

How to create virtual column with multiple value using MySQL SELECT?

I can add virtual columns as SELECT '1' as id | id | ------- | 1 | But I want add multiple values, example: SELECT ('1','2','3') as id | id | ------- | 1 | | 2 | | 3 | But this don't work
Piotr
  • 31
  • 1
  • 4
2
votes
0 answers

MYSQL: JSON with indexed virtual column VS normal column & normalization

Problem statement I've a big table users composed of 2 different kinds of users, A and B. At the moment, they share some fields, while other fields are A-only or B-only. Now, normally with a proper normalization on this table, we can end up having 1…
Joseph
  • 1,029
  • 13
  • 26
2
votes
1 answer

Oracle 11g Replace Physical Columns with Virtual Columns

After googling for a while , I am posting this question here since I was not able to find such a problem posted anywhere. Our application has a table with 274 columns(No LOB or Long Raw columns) and over a period of 8 years the table started to have…
Rammy
  • 31
  • 3
2
votes
1 answer

Using UNIX_TIMESTAMP in a virtual (calculate) column in MariaDB / MySql

I am trying to create a table with a timestamp column and a virtual (calculated) column that holds a helper key for grouping the rows with timestamps of the same hour. For that I am using the following command in MariaDB / MySql: CREATE TABLE…
Claude
  • 21
  • 2
2
votes
1 answer

Oracle virtual column generates -1.0002... instead of -1 on one instance

In one environment on 11G, I have created a table with one virtual column as follows create table TEST_VIRTUAL_COL( col1 number(5), col2 varchar2(10), col3 number generated always as((-1)) virtual visible ); Then insert a row into this insert…
Rohit G
  • 39
  • 1
  • 5
2
votes
2 answers

Save entities with virtual columns in gorm grails

So , in my application i have a entity with a virtual column. The column takes values depending on what i save in another column. On save i have the following error: java.sql.BatchUpdateException: ORA-54013: INSERT operation disallowed on virtual…
ciker001
  • 57
  • 2
  • 5
1
vote
4 answers

MySQL: Optimize left join on formatted date

I'm trying to optimize the speed of this query: SELECT t.t_date td, v.visit_date vd FROM temp_dates t LEFT JOIN visits v ON DATE_FORMAT(v.visit_date, '%Y-%m-%d') = t.t_date ORDER BY t.t_date v.visit_date is of type DATETIME…
Sempervivum
  • 928
  • 1
  • 9
  • 21
1
2 3 4