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
-3
votes
1answer
30 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), ...
0
votes
1answer
47 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`,'$....
3
votes
2answers
309 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 ...
0
votes
1answer
19 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 (...
1
vote
1answer
123 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 ...
2
votes
1answer
276 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, ...
0
votes
2answers
31 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 ...
2
votes
1answer
61 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 ...
0
votes
1answer
18 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> ...
1
vote
0answers
213 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 /...
1
vote
1answer
186 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 ...
4
votes
3answers
130 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 ...
3
votes
2answers
319 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 ...
-1
votes
2answers
229 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 ...
0
votes
1answer
123 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 ...
0
votes
2answers
98 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 "...
0
votes
1answer
68 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(...
0
votes
0answers
18 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/...
0
votes
1answer
25 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 ...
0
votes
0answers
150 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 ...
2
votes
2answers
142 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 ...
1
vote
2answers
51 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 ...
5
votes
2answers
274 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].[...
0
votes
1answer
34 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 ...
2
votes
3answers
316 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 ...
1
vote
1answer
66 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 ...
4
votes
2answers
285 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 ...
0
votes
1answer
404 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: ...
2
votes
1answer
375 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 ...
0
votes
1answer
84 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 ...
2
votes
2answers
3k 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'...
0
votes
1answer
681 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 ...
-1
votes
1answer
140 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 ...
-2
votes
1answer
27 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:
30
votes
4answers
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 ...
0
votes
1answer
91 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 ...
2
votes
1answer
54 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 ...
0
votes
1answer
195 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. [...
7
votes
3answers
4k views

How to change definition of a Postgres 12 GENERATED column

How to change definition of a Postgres 12 GENERATED column? CREATE TABLE test ( id serial PRIMARY KEY, val_a int4 NOT NULL DEFAULT 0, val_b int4 NOT NULL DEFAULT 0, val_sum int4 ...
-4
votes
2answers
97 views

Joining two tables together based on common identifier [closed]

I have two tables. Table 1 : 'DQ' is the golden source of information, Table 2 : 'PS' is whats currently in the DB. What I would like to do is compare the DQ table to whats inthe PS Table and ...
0
votes
2answers
84 views

Create integer id columns from existing string columns (integer coding?)

I have an PostgreSQL server with an existing table which has two fixed-width-non-unique-string (variable size) columns such as this: | ID_STRING_A | ID_STRING_B | | 'AAAA' | 'BBBB' | | ...
1
vote
1answer
2k views

Insert into table with column same values as auto increment column ( computed column )

Say I have a table with "column1" (Primary Key, Auto Increment) and "column2". How can insert into the table with column2 will have the same values as column1 which is auto increment column?
1
vote
2answers
2k views

Create an unique integer id column for an existing string column

I have an PostgreSQL server with an existing table which has an fixed-width-non-unique-string column such as this: | ID_STRING | | 'ABCDEFG' | | 'HIJKLMN' | Now I want to compute integer ids for ...
0
votes
1answer
262 views

HashBytes Computed Column: Exclude Column

We want to create Hashbytes computed column Non-Persisted. Table has twenty columns, and would like to exclude IdentityId column. How could I do this in computed column? Is there a SQL library ...
0
votes
0answers
25 views

Why sum with previous and current column do't working

I want to update my specific row with that row previous value, after searching i found some post about this but those solution not working with me. So what is wrong with my query or how can i ...
0
votes
3answers
7k views

Sql Max value from multiple columns in Computed Column

In this article solution 1, it talks about finding the maximum value from many columns. I would like to conduct this in a computed/persisted column. How would I do this? https://www.mssqltips.com/...
1
vote
1answer
292 views

Is any caching mechamism used for non-persisted computed columns?

Does SQL Server cache the result of a non-persisted computed column so it can be reused without incurring cost of recalculation? Additional Context I've always given the advice that if using computed ...
3
votes
1answer
7k views

How to create a Hash Computed Column for Many Columns?

Does anyone know how to create a Hash Computed Column Persisted? I keep receiving an error below. Otherwise, I will have to utilize an update or trigger statement everytime. I know how to conduct for ...
3
votes
2answers
707 views

Datatype for a Persisted Computed Column? Should I Convert/Cast When Creating It?

What is the Datatype for a Persisted Computed Column? (a) Does SQL Server automatically infer it? (b) Also is better to cast it as a datatype, so SQL does not spend time to find what data type it ...