public inbox for [email protected]
help / color / mirror / Atom feedPostgres 12 - Generated Columns - Backup/Restore
4+ messages / 3 participants
[nested] [flat]
* Postgres 12 - Generated Columns - Backup/Restore
@ 2021-01-21 01:32 Santosh Udupi <[email protected]>
2021-01-21 01:35 ` Re: Postgres 12 - Generated Columns - Backup/Restore Tom Lane <[email protected]>
2021-01-21 12:36 ` Re: Postgres 12 - Generated Columns - Backup/Restore Laurenz Albe <[email protected]>
0 siblings, 2 replies; 4+ messages in thread
From: Santosh Udupi @ 2021-01-21 01:32 UTC (permalink / raw)
To: [email protected]
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?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Postgres 12 - Generated Columns - Backup/Restore
2021-01-21 01:32 Postgres 12 - Generated Columns - Backup/Restore Santosh Udupi <[email protected]>
@ 2021-01-21 01:35 ` Tom Lane <[email protected]>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2021-01-21 01:35 UTC (permalink / raw)
To: Santosh Udupi <[email protected]>; +Cc: [email protected]
Santosh Udupi <[email protected]> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Postgres 12 - Generated Columns - Backup/Restore
2021-01-21 01:32 Postgres 12 - Generated Columns - Backup/Restore Santosh Udupi <[email protected]>
@ 2021-01-21 12:36 ` Laurenz Albe <[email protected]>
2021-01-21 13:51 ` Re: Postgres 12 - Generated Columns - Backup/Restore Santosh Udupi <[email protected]>
1 sibling, 1 reply; 4+ messages in thread
From: Laurenz Albe @ 2021-01-21 12:36 UTC (permalink / raw)
To: Santosh Udupi <[email protected]>; [email protected]
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Postgres 12 - Generated Columns - Backup/Restore
2021-01-21 01:32 Postgres 12 - Generated Columns - Backup/Restore Santosh Udupi <[email protected]>
2021-01-21 12:36 ` Re: Postgres 12 - Generated Columns - Backup/Restore Laurenz Albe <[email protected]>
@ 2021-01-21 13:51 ` Santosh Udupi <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Santosh Udupi @ 2021-01-21 13:51 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: [email protected]
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 <[email protected]>
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
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2021-01-21 13:51 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-01-21 01:32 Postgres 12 - Generated Columns - Backup/Restore Santosh Udupi <[email protected]>
2021-01-21 01:35 ` Tom Lane <[email protected]>
2021-01-21 12:36 ` Laurenz Albe <[email protected]>
2021-01-21 13:51 ` Santosh Udupi <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox