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
0answers
36 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` ...
1
vote
1answer
14 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 ...
0
votes
2answers
29 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 ...
1
vote
2answers
198 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 ...
0
votes
1answer
121 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 ...
-3
votes
1answer
44 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
52 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
483 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
30 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
297 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
435 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
34 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
62 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
21 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
347 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
296 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
167 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
436 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
376 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
193 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
152 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
77 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
19 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
195 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
151 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 ...
2
votes
2answers
69 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
292 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
37 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
450 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
91 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
286 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
541 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
546 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
91 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
4k 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
731 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
153 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
36 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
95 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
61 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
254 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
5k 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
108 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
101 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
3k 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
287 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 ...