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.
125
questions
0
votes
0
answers
39
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 ...
- 27
0
votes
1
answer
22
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
...
- 279
1
vote
1
answer
37
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 `...
- 169
1
vote
1
answer
67
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
...
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 ...
- 33
1
vote
0
answers
42
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; ...
- 11
0
votes
1
answer
778
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 ...
- 1
1
vote
2
answers
600
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 ...
- 11
0
votes
0
answers
71
views
Computed column marked as PERSISTED still calculates every time [duplicate]
I have the following table:
CREATE TABLE dbo.Cases (
CaseCaption nvarchar(255),
NickNameComputed AS (dbo.fn_ParseNickName(CaseCaption)) NOT NULL,
NickNamePersisted AS (dbo.fn_ParseNickName(...
- 1,929
2
votes
1
answer
115
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 ...
- 123
0
votes
0
answers
942
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 ...
- 101
4
votes
1
answer
883
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 ...
- 195
2
votes
1
answer
768
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.
...
- 167
0
votes
1
answer
284
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 ...
- 1,437
-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
...
- 1
4
votes
1
answer
365
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 ...
- 141
6
votes
1
answer
387
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".
...
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日'))) ...
- 715
0
votes
0
answers
146
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,407
1
vote
1
answer
226
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 ...
- 13
2
votes
2
answers
990
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 ...
- 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 ...
- 13
0
votes
1
answer
378
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 ...
- 173
-3
votes
1
answer
340
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), ...
- 95
0
votes
1
answer
190
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`,'$....
- 195
4
votes
2
answers
3k
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 ...
- 715
1
vote
1
answer
819
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 (...
- 179
0
votes
2
answers
53
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 ...
- 153
2
votes
1
answer
166
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 ...
- 3,741
0
votes
1
answer
35
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>
...
- 135
1
vote
0
answers
1k
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 /...
- 294
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 ...
- 13
-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 ...
- 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 ...
- 203
0
votes
2
answers
665
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 "...
- 279
0
votes
1
answer
151
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
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 ...
- 223
2
votes
2
answers
370
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 ...
- 111
2
votes
2
answers
463
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 ...
- 33
5
votes
2
answers
353
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].[...
- 241
0
votes
1
answer
468
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 ...
- 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 ...
- 21
1
vote
1
answer
522
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 ...
- 111
4
votes
2
answers
338
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 ...
- 1,407
0
votes
1
answer
989
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:
...
3
votes
1
answer
2k
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 ...
- 252
0
votes
1
answer
494
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 ...
- 177
2
votes
2
answers
10k
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'...
- 1,575
0
votes
1
answer
1k
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 ...
- 167
-1
votes
1
answer
291
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 ...
- 34.2k