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
1
vote
0 answers

How can I alter a column so that it has a generated constraint in postgres

I have a table in Postgres that has the following columns: Table Definition: FirstName varchar(255)| LastName varchar(255)| FullName varchar(255) what I want to do is add the following generated constraint to the FullName column like this (FullName…
TejusN
  • 77
  • 9
1
vote
1 answer

Recompute values in Postgres generated column after underlying function changed

Image a table with a generated column like this: CREATE OR REPLACE FUNCTION extract_first_name(p_name text) RETURNS text LANGUAGE SQL IMMUTABLE AS $$ SELECT split_part(p_name, ' ', 1); $$; CREATE TABLE customers ( id serial, name text, …
pir
  • 5,513
  • 12
  • 63
  • 101
1
vote
1 answer

Auto updated column and generated always values

Usually if I need to have some auto updated column as updated_at I used function and trigger. For example as it is described here. In Postgres 12 we got generated columns. It does not give ability to use now() function directly, however I could…
Anton Feoktistov
  • 321
  • 1
  • 9
1
vote
1 answer

Generated columns in Postgres using a given pattern

I need to generate a SQL query which can generate a column that follows a certain pattern The pattern is stored like this Hello [Name] [lastname] where name and lastname are both columns on the table. How do i add a constant string at end, start…
kafka
  • 573
  • 1
  • 11
  • 28
1
vote
1 answer

Create a generated column in MySql with count

I am not a database guy, despite this I have created a statement which counts the ids function by the unique group ids from the table as this: USE farm; SELECT reg.grpId, COUNT(reg.id) as TOTAL FROM farm.reg group by reg.grpId; Because I do't want…
A B
  • 177
  • 1
  • 3
  • 13
1
vote
1 answer

Update SQLite version used by RSQLite

I'm building a database in SQLite using R and Rmarkdown. Both the CRAN and development RSQLite packages are using SQLite version 3.30.1. Recently (1/2020) SQLite released v 3.31.1 which supports the creation of generated columns within tables. …
Christopher Michaud
  • 105
  • 1
  • 8
0
votes
1 answer

Postgres Generated Column not Updating

I've found a strange example where a generated column doesn't seem to update when I would expect it to. create table example ( "id" bigserial primary key, "value" text ); insert into example ("value") values ('A'), ('B'), ('C'); alter…
user2659205
  • 157
  • 1
  • 1
  • 7
0
votes
0 answers

to_timestamp in generated column

I have a table in my DB (PstgreSQL 14) that is auto-generated by another software. This table contains a t_stamp column stored as a BIGINT. I need to use this t_stampsomewhere else as a TIMESTAMP data type. I thought about creating a generated…
Sim
  • 45
  • 1
  • 7
0
votes
0 answers

Postgres generated columns produces a syntax error

I want to create column with Generated Columns Postgres version: PostgreSQL 15.1, compiled by Visual C++ build 1914, 64-bit Dbeaver version: 23.0.1.202303250535 query (yes, i now it violates 1NF): CREATE TABLE employees ( first_name VARCHAR(50), …
Candra wijayanto
  • 25
  • 4
0
votes
1 answer

How to handle generated column in database table with Laravel Backpack?

I have a table in a MySQL database that contains a virtual generated column. It is defined in the appropriate migration using ->virtualAs(). I've tweaked the Create and Update operations of the default CRUD controller that Backpack generated for me…
Andover Beacon
  • 49
  • 6
0
votes
3 answers

"ERROR: generation expression is not immutable" why is the alter table for new generated column is immutable?

Why is the below code not valid. I have only used immutable columns. Below code is supposed to add a new column to the users table. There are few conditions that needs to meet for the is_valid column to be true else it will be false. The email and…
LordDraagon
  • 521
  • 12
  • 31
0
votes
1 answer

Generated sha256 column in Postgres from varchar/text column?

I'm trying to create the following table in PostgreSQL 13: CREATE TABLE cached ( text VARCHAR NOT NULL, text_hash BYTEA GENERATED ALWAYS AS (sha256(convert_to(text, 'LATIN1'))) STORED PRIMARY KEY ); However, I'm getting the following…
Attila Kun
  • 2,215
  • 2
  • 23
  • 33
0
votes
1 answer

Is there a way to mark a property as unchangeable on updates?

I really want to avoid accidental overwrites on a specific property. ValueGenerated.OnAdd is not useful in this regard because it still tries to set a value in the database (1), unfortunately. I want to essentially make this field read-only so that…
user15716642
  • 171
  • 1
  • 12
0
votes
1 answer

GENERATED ALWAYS AS ( generation_expr ) STORED using gen_random_uuid

Looking to create a table like the following: CREATE TABLE newtable ( id UUID GENERATED ALWAYS AS gen_random_uuid() STORED, org uuid NOT NULL, name text, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP UNIQUE(name,…
matic
  • 301
  • 2
  • 3
  • 10
0
votes
1 answer

PostgreSQL Computed Column with Format ('TR-'+format([Id],'0000000000'))

I am new to Postgres and I need to create computed column to format request number as below TR-000000001 TR-000000011 TR-000000111 in SQL server i handled it as follow ('TR-'+format([Id],'0000000000')) and it works fine How can I do this using…
Ahmed Mostafa
  • 65
  • 1
  • 5
1 2
3
4 5