Format generation_expression
От | Nils Bergmann |
---|---|
Тема | Format generation_expression |
Дата | |
Msg-id | 03e76a96-bf36-4773-8272-cc29bd786386@Spark обсуждение исходный текст |
Ответы |
Re: Format generation_expression
|
Список | pgsql-general |
Hi,
Currently, I have a problem. I am working on a project with generated columns. I try to implement the generated column feature in the typeorm project. The thing is, I don't want to drop and add a generated column every time the application starts. Therefore I need a way to check whether a generated column expression has changed.
With select generation_expression from information_schema."columns" c where c.generation_expression is not null; I can retrieve the saved generation expression. However, It seems like Postgres optimizes or changes it. Therefore, it is different.
Example:
I add a column like this:
alter table company add tsv tsvector generated always as (setweight(to_tsvector('german', coalesce(name, '')), 'A') || setweight(jsonb_to_tsvector('german', keywords::jsonb, '["string"]'),'A')) stored;
Now if I try to retrieve the expression with select c.generation_expression from information_schema."columns" c where c.column_name = 'tsv' and c.table_name = 'company'; it is different.
Input expression:
setweight(to_tsvector('german', coalesce(title, '')), 'A') || setweight(to_tsvector('german', coalesce(description, '')), 'B');
What I got from postgres:
(setweight(to_tsvector('german'::regconfig, (COALESCE(name, ''::character varying))::text), 'A'::"char") || setweight(jsonb_to_tsvector('german'::regconfig, keywords, '["string"]'::jsonb), 'A'::"char"))
Postgres formats the query in some way. Is there any way to do this manually?
Like:
magic_function_to_format_sql(`setweight(to_tsvector('german', coalesce(title, '')), 'A') || setweight(to_tsvector('german', coalesce(description, '')), 'B')`) Returns: (setweight(to_tsvector('german'::regconfig, (COALESCE(name, ''::character varying))::text), 'A'::"char") || setweight(jsonb_to_tsvector('german'::regconfig, keywords, '["string"]'::jsonb), 'A'::"char"))
Then I could check if the saved expression is equal to the formatted one. I tried to find information in the documentation. But I had no luck.
Currently, I have a problem. I am working on a project with generated columns. I try to implement the generated column feature in the typeorm project. The thing is, I don't want to drop and add a generated column every time the application starts. Therefore I need a way to check whether a generated column expression has changed.
With select generation_expression from information_schema."columns" c where c.generation_expression is not null; I can retrieve the saved generation expression. However, It seems like Postgres optimizes or changes it. Therefore, it is different.
Example:
I add a column like this:
alter table company add tsv tsvector generated always as (setweight(to_tsvector('german', coalesce(name, '')), 'A') || setweight(jsonb_to_tsvector('german', keywords::jsonb, '["string"]'),'A')) stored;
Now if I try to retrieve the expression with select c.generation_expression from information_schema."columns" c where c.column_name = 'tsv' and c.table_name = 'company'; it is different.
Input expression:
setweight(to_tsvector('german', coalesce(title, '')), 'A') || setweight(to_tsvector('german', coalesce(description, '')), 'B');
What I got from postgres:
(setweight(to_tsvector('german'::regconfig, (COALESCE(name, ''::character varying))::text), 'A'::"char") || setweight(jsonb_to_tsvector('german'::regconfig, keywords, '["string"]'::jsonb), 'A'::"char"))
Postgres formats the query in some way. Is there any way to do this manually?
Like:
magic_function_to_format_sql(`setweight(to_tsvector('german', coalesce(title, '')), 'A') || setweight(to_tsvector('german', coalesce(description, '')), 'B')`) Returns: (setweight(to_tsvector('german'::regconfig, (COALESCE(name, ''::character varying))::text), 'A'::"char") || setweight(jsonb_to_tsvector('german'::regconfig, keywords, '["string"]'::jsonb), 'A'::"char"))
Then I could check if the saved expression is equal to the formatted one. I tried to find information in the documentation. But I had no luck.
Best regards,
Nils Bergmann
В списке pgsql-general по дате отправления: