PostgreSQL 12 Generated columns. Calculated columns. Virtual columns. - https://www.postgresql.org/docs/devel/ddl-generated-columns.html - https://www.2ndquadrant.com/en/blog/generated-columns-in-postgresql-12/
Questions tagged [generated-columns]
63 questions
0
votes
1 answer
Postgres index on GENERATED column slow
I've a datetime column in my postgres 13 database that's stored without a timezone (my mistake).
In a view, it's converted but that makes queries slow as Postgres does a full table scan, ignoring the index on the original column.
I created a new…
AndrewB
- 49
- 6
0
votes
0 answers
Idiomatic way to add generated column with stored function
I have a table baseTable with columns id and dateCol. I want to create several tables where I group by a function of dateCol.
My first attempt at this was
CREATE derivedTable ...
INSERT INTO derivedTable
SELECT id, myFunc(dateCol) AS datePrime,…
crockeea
- 21,651
- 10
- 48
- 101
0
votes
2 answers
Postgres: Use JSONB column keys as generated stored column
I have a table which has a jsonB column named as emailAddress. An example row in that column is like this:
{
"john.doe@best.com": {
"tags": {
"work": true
}
},
"nate.calman@best.com": {
"tags": {
"work": true
}
}
}
I…
Baqir Khan
- 694
- 10
- 25
0
votes
1 answer
Calculated time of day field in Postgres from timestamp
I have a timestamp with time zone field named statdate and the entry looks like this 2021-11-17 12:47:54-08. I want to create a field with just the time of day expressed locally, so it would look like 12:47:54. (This data was recorded on an iPhone…
Greg
- 2,359
- 5
- 22
- 35
0
votes
1 answer
Postgresql: Generated column as output from a function
I was trying to create a generated column. It should be output of some function with parameter as one of its column, e.g. custom_function(column1).
where custom_function is a user defined function of postgresql
and column1 is any column of the…
siddhartha attri
- 93
- 8
0
votes
1 answer
Adding Generated Columns Crashes MariaDB
I am experiencing a strange bug with generated columns and MariaDB running in a Docker container.
The image I'm using is mariadb:10.
I have been trying to add generated columns. The first column I add works fine; the second I add crashes the…
qotsa42
- 147
- 1
- 1
- 11
0
votes
1 answer
how to embed a function to column in PostgreSQL
I'm new to SQL and PostgreSQL and I'm trying to find a way to create a new table in database where a column will automatically calculate a pre-defined function with data from other columns.
Specifically I want a LOG table with
TIME_IN | TIME_OUT |…
danb2000
- 19
- 3
0
votes
1 answer
Stored/Virtual Generated Column- Pros/Cons/Best Practices?
I've read the MySQL Documentation on them, but still not clear on the benefits of Stored/Virtual Generated Columns? What are the pros/cons over storing the same data in an actual column and indexing that into memory? What are the pros/cons, and the…
user3718843
- 93
- 8
0
votes
1 answer
Generated columns with condition/if else/case to populate the new column mysql
I have a column-City, i want to create a new column - Metro, i want it to check
if city in('x','y','z') then Metro='Metro' else 'Non metro'
ALTER TABLE sales ADD COLUMN Metro_city VARCHAR(45) GENERATED ALWAYS AS (IF (CITY in('Mumbai ','Delhi' ,…
Chai
- 69
- 10
0
votes
1 answer
sqlite generated column dependent expression
I have encountered this situation where i plan to insert a generated column which the expression (the expression to design quoted in (???..I need this suggestion expression too..???) schema table below. the output is a calculation depend on the…
hfsoon
- 25
- 4
0
votes
1 answer
Creating Temporary Columns in PostgreSQL
I have a table with the following:
| Customer | Order Count|
-----------------------
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
and I want to create an additional column so I end up with the following:
|…
ilikemath3.14
- 39
- 6
0
votes
1 answer
Putting a default value as a difference of two columns in Mysql while creating a table
Can we create a table in Mysql server with a column as the difference between two other columns. So when those two columns are populated the third column will be computed from them.
CREATE TABLE LOAD(
START_TIME DATETIME,
END_TIME…
Sahil Vohra
- 1
- 3
0
votes
1 answer
Spatial index on generated (virtual/stored) column?
I have a generated (storedAs) column in my Laravel database that takes two decimal columns lat and lng and returns a point geospatial type. I'm using a generated column so as to avoid a scenario where the decimal lat/lng columns somehow get…
Erich
- 2,408
- 18
- 40
-1
votes
1 answer
MySQL Virtual Column Wrong Values
i really loves mysql virtual generated columns but am having a small issue with it am trying to make a virtual column from a varchar column where i extract the number as the following.
DB::statement('ALTER TABLE reservations ADD number_vc BIGINT AS…
Emad Rashad Muhammed
- 75
- 1
- 2
- 11
-1
votes
1 answer
Db2: Create a Column that stores the timestamp a value in the same row is set
I have a Boolean column in my database called: IS_SUBMITTED that is by default set to False.
What I would like to do is to have another column in the database called TIME_SUBMITTED that is set to Null by default, but when IS_SUBMITTED is set to…
A. Lewis
- 67
- 8