Questions tagged [computed-column]

Computed columns are columns that represent a calculation or operation performed on other columns contained in the given table. Known as generated or virtual columns in some products.

Filter by
Sorted by
Tagged with
0 votes
1 answer
27 views

Computed Columns using Foreign Key

I was wondering if it was possible to populate a computed/generated column using the foreign key to get information from the linked cell/table I have a relationship set up between TableA and TableB. ...
user avatar
  • 111
0 votes
1 answer
40 views

Intended use case for virtual columns with function-based index?

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 ...
user avatar
  • 1,387
-2 votes
1 answer
45 views

split a row value according to a specific column

I have total value in table A and need to get row value in table B until reached value in table A. Table A ID Available 1 99 2 0 Table B ID Rack RequiredQty 1 A 60 1 B 102 1 C 8 2 A 10 ...
user avatar
  • 1
4 votes
1 answer
60 views

Foreign key from one column to two (fixed computed column)?

I am trying to set up some foreign keys for some tables and I have an inheritance scenario with a "supertable" and some "subtables". The basic structure is that the supertable ...
user avatar
6 votes
1 answer
272 views

SQL Server Scalar UDF Parallelism Mystery!

Referencing this blog post by Erik Darling on the website of my favorite SQL Server guru - Brent Ozar: When you select from that table alone, it shows "CouldNotGenerateValidParallelPlan". ...
user avatar
0 votes
0 answers
217 views

Expression of generated column 'birthday_filter' contains a disallowed function

I want to add a generate column using this sql command: ALTER TABLE spark_user_demo ADD COLUMN birthday_filter bigint GENERATED ALWAYS AS (1000 * UNIX_TIMESTAMP(str_to_date(birthday, '%Y年%m月%d日'))) ...
user avatar
  • 477
0 votes
0 answers
55 views

Letting MariaDB infer the data type of the generated column

How to let MariaDB infer the data type of the generated column? For example: alter table Customers add NormalizedName varchar(100) as (lower(`Name`)) virtual And alter table Keywords add `Length` ...
user avatar
1 vote
1 answer
22 views

Is there a generator to create a table in MongoDB just like in Laravel?

I'm a newbie in mongoDB, and I just would like to ask a simple question. In creating tables in MongoDB, do you go ahead in MongoDB compass and create the fields in a collection, or is there a command ...
user avatar
0 votes
2 answers
256 views

What is the difference between tsvector_update_trigger and ts_vector generated column?

Newbie to full text search, I have a feed_items table as follows feed_items (...title varchar, summary varchar, fullarticle varchar,...) I want to create a ts_vector column to search query from ...
user avatar
  • 246
1 vote
2 answers
515 views

Index on VARCHAR(MAX) column

Table structure: ID textvalue longtextvalue textvalue has an index and a length of 900 longtextvalue has no index and a length of MAX Goal is to put together both columns with a length of MAX and an ...
user avatar
  • 13
0 votes
1 answer
161 views

Occasional incorrect values for generated columns in PostgreSQL 13

I have a PostgreSQL 13 server running in Google Cloud and one of our tables has a few generated columns and occasionally I find rows where the value saved in the generated column isn't up-to-date. For ...
user avatar
  • 173
-3 votes
1 answer
92 views

Get datetime based on change in another column using UDF and computed column in TSQL [closed]

Given: Given a Microsoft SQL database table Log with multiple columns including these important ones: id (primary key), code (an integer that can take multiple values representing status changes), ...
user avatar
0 votes
1 answer
76 views

Adding computed column extremely slow

We are trying to add computed column to a table and this operation is taking more than 20 hours to complete: json_unquote(coalesce(json_extract(`int_data`,'$.Item.IRefNum'), json_extract(`m_dt`,'$....
user avatar
  • 195
4 votes
2 answers
989 views

How to add a generated column with an expression subtracting days?

I have this table in PostgreSQL 13: CREATE TABLE public."domain" ( id int8 NOT NULL GENERATED ALWAYS AS IDENTITY, domain_name varchar NOT NULL, -- more columns expire_date ...
user avatar
  • 477
1 vote
1 answer
137 views

Alter table generated text column to normal text column

If I have a table with a generated text column, how do I alter the column to remove the generation expression to get a normal, not-generated text type? Here's the schema: CREATE TABLE product_history (...
user avatar
  • 149
2 votes
1 answer
683 views

Convert a postgres GENERATED column to a regular non-generated column (in place without DROP)

I'm using postgres 13.3, and I already have an existing table with a GENERATED column such as: CREATE TABLE test_table ( id uuid NOT NULL DEFAULT uuid_generate_v4(), the_column_to_alter bool ...
user avatar
  • 223
2 votes
1 answer
627 views

Can not drop virtual column | ERROR 1054 (42S22): Unknown column in 'GENERATED ALWAYS'

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, ...
user avatar
  • 31
0 votes
2 answers
39 views

Conditionally use computed column

IS it possible that a not nullable column can be setup to either a) add value freely with INSERT/UPDATE query b) if value not provided on insert, to avoid null entry added default the value to that of ...
user avatar
  • 143
2 votes
1 answer
67 views

"Persisted" column is not used

I have a table with the following structure: ItemID int ItemType char(1) Language char(2) Localization char(2) Literal nvarchar(4000) which is used to translate ...
user avatar
  • 3,619
0 votes
1 answer
22 views

query for showing columns from various tables, grouping and showing calculated field in each row of the result

I am doing a stock management system. In one table I store items: ITEM<br> id_item<br> item_name<br> ORDER<br> id_order<br> date<br> ORDER_ITEM<br> ...
user avatar
1 vote
0 answers
591 views

Does RedShift support computed columns?

In PostgreSQL I can do this: CREATE TABLE Test( Effective_Date timestamp NULL, Max_Age_Date timestamp generated always as dateadd(month, 5, Effective_Date) stored ); How do I create a computed /...
user avatar
1 vote
1 answer
494 views

SQLite: Using count in triggers to update another column

UPDATE: Fixed. Not 100% sure why sqlfiddle.com didn't like my syntax, but using https://dbfiddle.uk/?rdbms=sqlite_3.27 worked. I did change the last WHERE clause to be based on the id column, so that ...
user avatar
4 votes
3 answers
278 views

Large hexadecimal PRIMARY KEY - how to index substrings of the PK field?

I have a requirement for a very large (in terms of the number of rows, not data) database, and need to use a very long, unique, hexadecimal PRIMARY KEY instead of the usual AUTO_INCREMENT integer. As ...
user avatar
  • 143
3 votes
2 answers
547 views

How to handle divide by zero in GENERATED columns in MySQL

I am using GENERATED AS columns in MySQL 5.7 as follows: ALTER TABLE report_user MODIFY avg_trans_out_amount bigint GENERATED ALWAYS AS (round(trans_out_amount/(trans_out_count))) STORED; but ...
user avatar
  • 477
-1 votes
2 answers
538 views

What is the correct way to do a computed column with complex logic with performant sorting and filtering at scale in MS SQL Server? [closed]

I'll provide a toy example here: I have 2 tables, a Thing table and an AdditionGroup table. Things may be members of an AdditionGroup, or they may not. Both Thing and AdditionGroup have a Quantity ...
user avatar
  • 132
0 votes
1 answer
306 views

Displaying number before decimal points in oracle

I have a logic where I want to calculate and display numbers based upon some operations. The operations are below V_CALPERCENT nvarhcar(100), v_yearMSA1 nvarchar(100), So I want to calculate ...
user avatar
  • 203
0 votes
2 answers
250 views

How to add generated column to db2 table?

I've got a table with the following: Columns: --- URL [nullable, e.g. "https://dba.stackexchange.com/"] APPFK [nullable, e.g. 654654] LINKTYPE ["Page", "Link", or "...
user avatar
  • 255
0 votes
1 answer
98 views

Learning COMPUTED BY - what right parenthesis does it need? [closed]

I am reading this document on Automatic columns. The first snippet in it creates a COMPUTED BY column: create table PERSON ( (employee_id integer, employee_id_disp computed by SUBSTRING (CAST(...
user avatar
0 votes
0 answers
22 views

What are best practices in designing tables for dynamic mathematical inputs?

I am taking a first stab in designing a system that gives ratings to our assets based on various criteria. So to simplify what I am doing - it will be this times 10... I have an asset tables (docs/...
user avatar
  • 515
0 votes
1 answer
26 views

Data that are sometimes entered and sometimes computed

Suppose I have a database of StorageComplex objects. StorageComplex has a guid Id, plus two data columns: Capacity (a number), and Location (a string). My data also contain a table of StorageTank ...
user avatar
0 votes
0 answers
277 views

PostgreSQL 10 - create partitions on column that needs to be calculated after importing a csv

I am in the situation where I need to create a table which also has a time column. This table is filled with data taken from a CSV, but the csv has no time column. Only after importing the data inside ...
user avatar
  • 111
2 votes
2 answers
175 views

What is the best choose for PK in Partitioned Table?

I have one large table that is partitioned (table name: Trans). At the moment this table is to be created on 32 partitions. This table will contain approximately 300 million records and data older ...
user avatar
  • 91
2 votes
2 answers
121 views

Using NEW on tables with generated columns

I have a PostgreSQL 12 database, and in a table I created a column with a generated column, then I have a before update trigger that fires only if the NEW value is DISTINCT from OLD value, essentially ...
user avatar
  • 33
5 votes
2 answers
310 views

Why can't I persist binary(4) computed column?

I'm playing with storing and indexing IP addresses. I'm starting with a simple, stupid table: CREATE TABLE [dbo].[IP_addresses]( [IP_as_text] [char](16) NOT NULL, [IP] AS ([dbo].[...
user avatar
0 votes
1 answer
50 views

Is it bad practice to calculate within the insert query?

I am trying to do a web application for time sheets. I have three options to calculate the value of hours (AFAIK): In the form In the backend In the query Is it smart or bad practice to do this ...
user avatar
  • 156
2 votes
3 answers
624 views

How to calculate end_date from start_date and duration in SQL Server Query

I have a table with 4 fields .Start-date, Duration, End_date, Actual_date. I am providing the start_date and duration as the input to calculate end-date. While calculating the end_date the ...
user avatar
  • 21
1 vote
1 answer
149 views

MySQL: Unable To Create Generated Column With Expression That Uses DATE_ADD() Function

There might be a really obvious solution to my question, but I am stuck and did not manage find out what I am doing wrong, so you guys might tell me. I am not able to create a nullable stored column ...
user avatar
  • 111
4 votes
2 answers
292 views

Why is the result of a totally deterministic and all-encompassing "case" in select query in view nullable?

I have a table, with Id as the primary key. create table Anything ( Id bigint not null primary key identity(1, 1) ) When I look at this table in Object Explorer, I see this image of course: As ...
user avatar
0 votes
1 answer
656 views

Foreign key referencing table with composite primary key when I only need 1 column association

So lets say we have 2 tables on SQL Server database: CREATE TABLE VersionedData ( Id INT NOT NULL, Version INT NOT NULL CONSTRAINT [PK_VerData] PRIMARY KEY (Id, Version) ) And: ...
user avatar
2 votes
1 answer
863 views

Refreshing a stored generated column in Postgres

How in PostgreSQL can I refresh the values of a stored generated column. Example CREATE TABLE people ( first_name TEXT, last_name TEXT ); CREATE OR REPLACE FUNCTION name(people) RETURNS ...
user avatar
0 votes
1 answer
136 views

Calculating the value of a column based on consecutive values of rows in another column

It is straight forward to compute column value based on values of other columns, e.g. speed/time AS acceleration where speed and time are columns. Is there a way to compute column's value based on ...
user avatar
  • 105
2 votes
2 answers
6k views

UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER' in Agent job

In brief, I added a computed column to a table and then Agent jobs began failing and reporting the error: UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'...
user avatar
0 votes
1 answer
775 views

AutoIncrement non-primary key or Make unique composite key?

I have a db with a table that has a rollover value such as OrderNumber and another column is year. This is the primary key - a composite key - but there are other tables with which I need to use a ...
user avatar
  • 155
-1 votes
1 answer
176 views

Can I use built-in (deterministic) SQL functions in a Computed Column or do I need to create my own function as a passthrough to leverage them? [closed]

I'm trying to create a (persisted) computed column on a table that is essentially:YEAR(DateColumn). When I try to directly call the YEAR() function inside the Formula of the Computed Column ...
user avatar
  • 24.6k
-2 votes
1 answer
58 views

Subtracting the previous qty to the current quantities

I have a table as: Here I want to subtract the required Qty for a part from On Hand and then the result from the required for next sequence Hoping to get this as a final result:
user avatar
  • 1
31 votes
4 answers
4k views

Does SQL Server Only Perform Calculations In A SELECT List Once?

Take the following example: SELECT <CalculationA> As ColA, <CalculationB> As ColB, <CalculationA> + <CalculationB> As ColC FROM TableA Would CalculationA and ...
user avatar
  • 421
0 votes
1 answer
118 views

Computed Columns - SQL Server Function

I'm looking to improve overall SQL performance of a customers application. On each table of the application, there is a computed column, that uses GETUTCDATE and a combination of three columns on that ...
user avatar
  • 1
2 votes
1 answer
67 views

Automatic seek on an index on a calculated column

I have a table with a varchar field than I need to be unique, but it is too long for the nonclustered index that the uniqueness constraint requires, so I have added a computed column (either using ...
user avatar
  • 123
0 votes
1 answer
378 views

Computed Column Operand type clash on Date Column When Checking For Null

This error is generated when trying to create a table with a computed column from a Date field. Operand type clash: int is incompatible with date If I comment out the computed column it works. [...
user avatar
  • 377