Postgres 12 - Generated Columns - Backup/Restore

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Postgres 12 - Generated Columns - Backup/Restore

Santosh Udupi
My Postgres 12 database tables have generated columns. I use pg_dump to backup the database but when I restore using pg_restore, the generated columns are empty. How do I trigger creating the data in the generated columns?
Reply | Threaded
Open this post in threaded view
|

Re: Postgres 12 - Generated Columns - Backup/Restore

Tom Lane-2
Santosh Udupi <[hidden email]> writes:
> My Postgres 12 database tables have generated columns. I use pg_dump to
> backup the database but when I restore using pg_restore, the generated
> columns are empty. How do I trigger creating the data in the generated
> columns?

Can you give a fleshed-out example of what you're doing?

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: Postgres 12 - Generated Columns - Backup/Restore

Laurenz Albe
In reply to this post by Santosh Udupi
On Wed, 2021-01-20 at 17:32 -0800, Santosh Udupi wrote:
> My Postgres 12 database tables have generated columns. I use pg_dump to backup
>  the database but when I restore using pg_restore, the generated columns are empty.
>  How do I trigger creating the data in the generated columns?

Does the expression that generates the computed column uses an
expression containing a function that is marked IMMUTABLE, but
isn't really?

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com



Reply | Threaded
Open this post in threaded view
|

Re: Postgres 12 - Generated Columns - Backup/Restore

Santosh Udupi
Hi Laurenz - Based on Tom's email earlier, I realized it was my function that was causing the issue. I am trying to figure out where the problem is

Here is my function:

column definition is as below:

completed_date_time timestamptz GENERATED ALWAYS AS  
(task_completed_date(info->>'dd', info->>'qq', info->>'dp', info->>'ej', info->>'dq', info->>'ek') ) stored

-- 'info' is a jsonb column in the same table
------------------------------------------
-- task_completed_date() is defined as 'immutable', but calls another function:

;create or replace FUNCTION task_completed_date(completed_date_string text, completed_time_string text default null,
check_in_date_string text default null, check_in_time_string text default null,
check_out_date_string text default null, check_out_time_string text default null)

RETURNS timestamptz as
$$
begin

if check_out_date_string is not null and length(check_out_date_string)> 0 then
return
date_convert_date_time_string_to_timestamptz(check_out_date_string, check_out_time_string);

else

return date_convert_date_time_string_to_timestamptz(completed_date_string, completed_time_string);
 
end if;

exception when others then
return null;
 END ;
$$
LANGUAGE plpgsql immutable;
-----------------------------------------------------------

-- function date_convert_date_time_string_to_timestamptz is also defined as 'immutable':

create or replace FUNCTION date_convert_date_time_string_to_timestamptz(VARIADIC params text[])

RETURNS timestamptz as
$$
begin

 return array_to_string($1 , ' ')::timestamptz ;
exception when others then
return null;
 END ;
$$
LANGUAGE plpgsql immutable;
-----------------------------------------------------------------

On Thu, Jan 21, 2021 at 4:36 AM Laurenz Albe <[hidden email]> wrote:
On Wed, 2021-01-20 at 17:32 -0800, Santosh Udupi wrote:
> My Postgres 12 database tables have generated columns. I use pg_dump to backup
>  the database but when I restore using pg_restore, the generated columns are empty.
>  How do I trigger creating the data in the generated columns?

Does the expression that generates the computed column uses an
expression containing a function that is marked IMMUTABLE, but
isn't really?

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com