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
0 answers
25 views

Interest Accrual and insert monthly records since start date

I need to calculate the (1) "To" date after the initial contribution amount to accrue for accounting interest. The "TO" is calculated as End of month date if no other are events ...
Myran thiru's user avatar
0 votes
1 answer
38 views

Add Flag for Missing Related Record

PersonID Name IncidentID Role MissingOffender 1 Tom 20 Victim 2 Steve 20 Offender 3 Susan 20 Offender 4 John 21 Offender 5 Bill 21 Victim 6 Larry 22 Victim Y 7 Bert 23 Offender I have a table ...
theonlynewts's user avatar
4 votes
1 answer
212 views

Unique index on computed column when contributing columns are already unique

I have a table like this: CREATE TABLE dbo.DiscountBarcodeList ( ID int NOT NULL IDENTITY CONSTRAINT PK_DiscountBarcodeList PRIMARY KEY CLUSTERED , Discount int NOT NULL CONSTRAINT ...
Andriy M's user avatar
  • 23k
0 votes
1 answer
41 views

JSON_Value problem when adding persisted Computed column

i want to add a persisted column and i get an error: Msg 8116, Level 16, State 8, Line 3 Argument data type NULL is invalid for argument 2 of JSON_QUERY function. here is my script: ALTER TABLE [...
dexon's user avatar
  • 47
0 votes
1 answer
34 views

Help inconsistent index result on generated column after server restart

I have a table that has a date column and I need a data that will be group by week. And I found this Youtube video that used an index generated column Faster database indexes (straight from the docs) ...
zer09's user avatar
  • 453
0 votes
1 answer
43 views

counting related records

I have 2 related models, model_A and model_B There's a 1:n relationship among them Meaning one record in model_A can have several related records in model_B I'd like model_A to have a generated (and ...
user1632812's user avatar
0 votes
0 answers
42 views

How to Count calculated work hours in one month for each labors

Good Day. I have some data like bellow id name in out 1 lala 2023-09-29 07:00:00 2023-09-29 17:00:00 2 lili 2023-09-29 07:30:00 2023-09-29 16:00:00 1 lala ...
epm.007's user avatar
  • 29
0 votes
1 answer
64 views

Why cannot I create a generated column with a CASE expression? [duplicate]

I'm trying to add a generated column to a table in V7R3 of IBM DB2: ALTER TABLE MYLIB.MYTABLE ADD COLUMN NEW_COL VARCHAR(255) GENERATED ALWAYS AS ( COALESCE( CASE ...
Sarov's user avatar
  • 279
1 vote
1 answer
204 views

Cannot restore MariaDB dump after upgrade from 10.6.10 to 10.6.15

I have just updated MariaDB server from 10.6.10 to 10.6.15 on my development machine, and trying to restore a previously working SQL dump now fails. Here is an example of failing query: CREATE TABLE `...
Matteo Tassinari's user avatar
1 vote
1 answer
567 views

Postgres GENERATED ALWAYS performance when adding new column

I have a fairly big PostgreSQL 13 table that I need to add a new TEXT column and with this new column I would like to add a GENERATED ALWAYS column with the following definition: ALTER TABLE my_table ...
Rodrigo Ce Moretto's user avatar
0 votes
0 answers
18 views

Combine INT, TIME fields and compute Elapsed Percentage

Need calculation and conversion help... Columns: [Days] INT [hh:mm:ss] Time [AllowedRuntimeMinutes] INT Need to combine [Days]+[hh:mm:ss] as the duration the query has been running, and then calculate ...
CrushingIT's user avatar
1 vote
0 answers
63 views

How to add a calculated field to a Microsoft Access query that equals the minimum value of the previous column field

I have a simple query on a very complex query that reads: SELECT Query1.Rdate, Query1.Rank, Query1.Score FROM Query1; ...
HBHAYWOOD's user avatar
0 votes
1 answer
1k views

Dynamic data used as column name in Amazon Redshift

I am trying to use a year-month dynamic function to use it as a column name in Amazon Redshift. I don't want to write a hardcode for that. The database tool I'm using is DBeaver Ultimate and the code ...
vinifts's user avatar
2 votes
2 answers
1k views

Is it possible to add a GENERATED COLUMN to a large table without locking/downtime on postgres?

I am trying to add a new column as a generated column to a large table (over millions of rows). This generated column takes in the id column and performs a custom PostgreSQL function and returns it. I ...
Hyperion's user avatar
2 votes
1 answer
195 views

Is it possible to get MySQL to retry PK generation n times?

I'd like to use random bytes as PK in my table and to ease the application implementation I thought about leaving the generation to the database server so collisions can be handled transparent to the ...
Noel Nemeth's user avatar
0 votes
0 answers
1k views

Generate dynamic number of columns from CTE

A hospital database (SQL Server 2016) has a table of visits to the hospital. If patients transfer to a different specialty, a new visit is made (but from a research perspective it's considered one ...
Edge's user avatar
  • 101
4 votes
1 answer
1k views

How to pass the current row to Generated Column function in Postgres?

Postgres 12+ supports generated columns: https://www.postgresql.org/docs/current/ddl-generated-columns.html From the docs, the syntax seems limited - it forces one to explicitly name the columns on ...
Pragy Agarwal's user avatar
2 votes
1 answer
1k 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. ...
PaulMcF87's user avatar
  • 167
0 votes
1 answer
467 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 ...
User1974's user avatar
  • 1,457
-2 votes
1 answer
1k 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 ...
Alice's user avatar
  • 1
4 votes
1 answer
616 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 ...
InterLinked's user avatar
6 votes
1 answer
421 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". ...
Anil Raghavan's user avatar
0 votes
0 answers
1k 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日'))) ...
Dolphin's user avatar
  • 775
0 votes
0 answers
161 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` ...
Saeed Neamati's user avatar
1 vote
1 answer
259 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 ...
Jumar Juaton's user avatar
2 votes
2 answers
1k 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 ...
PirateApp's user avatar
  • 296
1 vote
2 answers
2k 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 ...
Nostra's user avatar
  • 13
0 votes
1 answer
433 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 ...
Joshmaker's user avatar
  • 173
-3 votes
1 answer
420 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), ...
Cogicero's user avatar
0 votes
1 answer
213 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`,'$....
Ali's user avatar
  • 305
6 votes
2 answers
4k 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 ...
Dolphin's user avatar
  • 775
1 vote
1 answer
1k 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 (...
Joe's user avatar
  • 179
0 votes
2 answers
75 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 ...
Sami.C's user avatar
  • 153
3 votes
1 answer
235 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 ...
gotqn's user avatar
  • 4,016
0 votes
1 answer
37 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> ...
Mister Equis's user avatar
1 vote
0 answers
2k 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 /...
Appleoddity's user avatar
1 vote
1 answer
1k 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 ...
Hermes_88's user avatar
-1 votes
2 answers
2k 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 ...
Pxtl's user avatar
  • 151
0 votes
1 answer
1k 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 ...
hud's user avatar
  • 203
0 votes
2 answers
801 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 "...
Sarov's user avatar
  • 279
0 votes
1 answer
165 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(...
user218174's user avatar
0 votes
1 answer
27 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 ...
William Jockusch's user avatar
2 votes
2 answers
515 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 ...
axdna's user avatar
  • 111
2 votes
2 answers
637 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 ...
Micael's user avatar
  • 33
5 votes
2 answers
360 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].[...
Chris Nelson's user avatar
0 votes
1 answer
634 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 ...
sibert's user avatar
  • 166
2 votes
3 answers
2k 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 ...
Rahul's user avatar
  • 21
1 vote
1 answer
609 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 ...
MannikJ's user avatar
  • 111
4 votes
2 answers
352 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 ...
Saeed Neamati's user avatar
0 votes
1 answer
1k 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: ...
Julius Rupšys's user avatar