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
1
vote
0 answers
"GENERATED ALWAYS AS" expressions for columns has been supported in spark 3.4, When will it be support in delta catalog?
"GENERATED ALWAYS AS" expressions for columns has been supported in spark 3.4 https://github.com/apache/spark/pull/38823 .
I try to create delta table
CREATE TABLE IF NOT EXISTS delta_generated_column_test (
id INT,
name STRING,
…
业鹏王
- 11
- 1
1
vote
1 answer
ERROR: generation expression is not immutable
I'm trying to create a generated column with a hash on two columns, but I get error.
What's wrong with my hash?
create table dwh_stage.account_data_src(
id int4 not null,
status_nm text null,
create_dttm timestamp null,
update_dttm…
Nident
- 13
- 2
1
vote
1 answer
PostgreSQL Trigger function won't work on insert row for text search column
I have a pgsql trigger and a trigger function. Works when updating a previous record and does not work when inserting a new record. Ideally, the text_search column should be populated with the tsvector data mentioned.
The id column of the table idea…
LordDraagon
- 521
- 12
- 31
1
vote
2 answers
Performanceoptimization with generated column vs trigger column
A table I'm using has a char(19) column lets call it P.
Due to circumstances in some wheres I need to check if a 10 character variable is found in P (at the end of the string in P). Thus I'm doing:
where P like CONCAT('%',variableName).
The…
Thomas
- 2,886
- 3
- 34
- 78
1
vote
0 answers
create new columns with mean of specific other columns
I'm trying to create 5 new columns in my data frame by calculating the mean of specific already existing columns.
i so far tried the following code:
`data_new2 <- data1 %>%
data1$chronisch…
rstock13
- 11
- 2
1
vote
1 answer
Postgres query selection logic
CREATE TYPE edition AS ENUM (
'b',
'j'
);
CREATE TABLE IF NOT EXISTS versions (
id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
edition edition NOT NULL,
major integer NOT NULL,
minor integer NOT NULL,
patch integer NOT…
ravinggenius
- 816
- 1
- 6
- 14
1
vote
2 answers
Generated column from another table's column
Transaction table:
ID
Name
Price
Amount Paid
1
Bruce Wayne
10.0
5.0
2
Lois Lane
33.33
22.22
3
Clark Kent
44.4
44.4
4
Bruce Wayne
15.0
20.0
Person table:
ID
Name
Total Price
Total Paid
Payment Due
1
Bruce…
The.N00B.One
- 45
- 8
1
vote
2 answers
SQLite: Unique per title and date (only date without time)
I have a table in which i store some titles as a TEXT and a date also as a TEXT.
I am inserting the date value as a string in ISO format in the database.
Now i want to have a unique combination of title and date but only considering the date…
user3320813
- 13
- 2
1
vote
1 answer
postgres GENERATED ALWAYS jsonb with refers to other columns inside a string
Trying to generate a jsonb column that will look like this:
["c:cluster-x", "ns:cluster-x/namespace-1"]
The cluster and namespace will be taken from other fields. I'm struggling with finding a way to concat it successfully
Also an array literal will…
AlonG
- 79
- 2
- 6
1
vote
1 answer
Update column based on other columns
I'm building a simplel API with ExpressJS and SQLite.
I'm adding a router.patch method that will update data entries in the API database using the id as the primary key. In my database, there is a column called score that is populated by the sum of…
Stephen
- 85
- 5
1
vote
1 answer
Postgis: create column with srid in procedure
Hello,
I wonder how I could create a spatialized column with an srid retrieved from the db.
-- OK --
ALTER TABLE POI ADD COORDS GEOMETRY(POINT, 26916);
-- KO (invalid input syntax for type integer: "sridval") --
DO $$
DECLARE
sridval…
Kiruahxh
- 1,276
- 13
- 30
1
vote
1 answer
Set constraint based on another's cell value
I am making a text based RPG and I want to add a class to the player's stats, let's say for example Warrior or Hunter, I decided to use sqlite3 to store the data and I want it to assign a default value to the Power stat based on Class stat.
This is…
Hakuo
- 13
- 5
1
vote
1 answer
How to get the ID of new row in SQL?
I want to fill a column with a format using its ID.
My table:
CREATE TABLE "TEST"
(
"ID" INTEGER,
"Formatted_Column" TEXT,
PRIMARY KEY("ID" AUTOINCREMENT)
);
I want to do:
INSERT INTO TEST (Formatted_Column) VALUES ('U' +…
Baghdadi
- 35
- 3
1
vote
2 answers
How to create a Postgresql generated column of type JSONB from columns of types JSONB, VARCHAR and DATE
I have a table test_data with 4 columns as shown below. I want to create a JSONB generated column with data from these columns. Below is what I have tried but keep on getting this error:
ERROR: generation expression is not immutable
SQL state:…
Chav
- 31
- 3
1
vote
2 answers
Postgres: Can I bypass the error "cannot insert into generated column" using a PostgreSQL INSTEAD OF INSERT rule?
I know this isn't pretty but it would be helpful to bypass the error for insert into a generated column in Postgres. Let's say, we have a table like so:
create table testing (
id int primary key,
fullname_enc bytea,
fullname text…
ProgFan666
- 93
- 1
- 7