Stack Overflow
Stack Overflow

Questions tagged [generated-columns]

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/

63 questions
158
votes
8 answers

Computed / calculated / virtual / derived columns in PostgreSQL

Does PostgreSQL support computed / calculated columns, like MS SQL Server? I can't find anything in the docs, but as this feature is included in many other DBMSs I thought I might be missing something. Eg:…
Mike Chamberlain
  • 39,692
  • 27
  • 110
  • 158
4
votes
1 answer

PostgreSQL Generated Column from a JSONB column with nested values

I have following table with a JSONB column: CREATE TABLE movies ( contributors JSONB ); The data in the column looks like this: INSERT INTO movies (contributors) VALUES('[ {"last_name": "Robbins", "first_name": "Tim", "age": 61}, …
ruslansteiger
  • 462
  • 1
  • 8
  • 21
4
votes
1 answer

Creation of MySQL's generated column utilizing column that is used for foreign key constraint causes error

I try to add a generated stored column to existing table running the following SQL command: ALTER TABLE routes ADD routes_hash char(32) AS (MD5(CONCAT(`traveler_id`, `track`))) stored; MySQL returns the error message: ERROR 1025 (HY000):…
andrew
  • 81
  • 6
3
votes
2 answers

Postgresql - error returned more than one row from function

My first post in Stackoverflow. I am trying to learn Postgresql (12) "on the job" and seem to be stuck on a rather simple issue. I have a simple database with 12 rows. In one column (int) there is a 4-digit number [representing a year]. I am…
James
  • 43
  • 4
3
votes
2 answers

Postgres generated column is not immutable

I am adding some columns to a table, and want a generated column that combines them together that I'll use for a unique index. When I try to add the column, I get the error ERROR: generation expression is not immutable. I followed the solution from…
Kevin
  • 1,080
  • 3
  • 15
  • 41
3
votes
0 answers

Restoring PG database from dump fails due to generated columns

We use our Postgres database dumps as a way to backup/reset our staging DB. As part of that, we frequently remove all rows in the DB and insert the rows from the PG dump. However, the generated columns are included as part of the PG dump, but with…
pir
  • 5,513
  • 12
  • 63
  • 101
3
votes
0 answers

Indexing JSON arrays of integers using Mariadb?

I'm storing arrays of integers as json in a mariadb (10.3.23) table : SELECT tag_list FROM dw.final_document LIMIT 1 ; Result : [903, 1258, 1261, 393] To retrieve entries matching a specific id, this works : SELECT SQL_NO_CACHE …
Bruno
  • 63
  • 6
3
votes
1 answer

Postgresql create generated column syntax error, why?

I have a postgres table with two columns (an identificator and a date) that are a composite primary key. I would like to hash the concatenation in another column, generating this value everytime a new record is inserted. For that I'm trying to alter…
Carlos
  • 889
  • 3
  • 12
  • 34
3
votes
2 answers

Does PostgreSQL only support STORED generated columns?

Forewords I've given some examples from PHP just to point my development environment. The question is not about PHP, it's purely about PostgreSQL. PostgreSQL documentation about generated column states that: There are two kinds of generated…
Eray Erdin
  • 2,633
  • 1
  • 32
  • 66
2
votes
1 answer

Adding autogenerated column that computes frequencies

I would like to create a generated column in my SQL table products who's value is automatically calculated by COUNTING the frequency of values in another column. Please see example below, prod_cat_id_count is the result I am after…
BoggyB
  • 23
  • 5
2
votes
2 answers

How to create a GENERATED column containing the MD5 of multiple columns?

I tried to add the following table in PostgreSQL 14.3: CREATE TABLE client_cache ( id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, request VARCHAR COMPRESSION lz4 NOT NULL CHECK (LENGTH (request) <= 10240), …
Gili
  • 86,244
  • 97
  • 390
  • 689
2
votes
1 answer

Handle pg_error on generated columns

I have a table that consists of some PostGIS related data. These data are generated automatically on INSERT or UPDATE. ALTER TABLE "Room" ADD COLUMN geolocation geography(Polygon, 4326) GENERATED ALWAYS AS (ST_MakePolygon("polygon"::geometry))…
Brook MG
  • 601
  • 10
  • 20
2
votes
1 answer

Postgres: How can I include weights in a generated column?

I have following schema: CREATE TABLE books ( title VARCHAR(255), subtitle TEXT ); Adding a generated column without weights is working fine: ALTER TABLE books ADD COLUMN full_text_search TSVECTOR GENERATED ALWAYS AS (to_tsvector('english', …
ruslansteiger
  • 462
  • 1
  • 8
  • 21
2
votes
1 answer

SQLAlchemy dynamic construction of GENERATED column

I want to dynamically derive columns with the server_default set to Computed(...). The statement going into Computed(...) has to be derived from other columns, and therefore needs to have access to the class after the relationships have been…
flying sheep
  • 8,475
  • 5
  • 56
  • 73
1
vote
1 answer

SQLite Trigger back to back invocation

I have these back to back triggers that I invoking in a SQLite database. I have a table called weight that I am using to calculate BMI, and NIH Health standards classification for the weight. This is how my code looks like: CREATE TABLE weight( …
EnlightenedFunky
  • 303
  • 1
  • 13
1
2 3 4 5