Can I use to_char() and make_date() in postgreSQL table definition?-CodePudding

I'm working on a poc to migrate an on-prem SQL Server database to Amazon Aurora for PostgreSQL. Amazon's Schema Conversion Tool struggled to translate the SQL Server code for the creation of a table on this column:

[DOB] AS (CONVERT([varchar],datefromparts([DOB_year],[DOB_month],[DOB_day]),(120))) PERSISTED,

as the CONVERT function is unsupported in Postgres.

The best translation I can come up with is:

dob varchar(30) GENERATED ALWAYS AS (to_char((make_date(dob_year, dob_month, dob_day))::timestamp, 'YYYY-MM-DD HH24:MI:SS')) STORED,

but neither the SCT nor pgAdmin4 are recognising to_char() and make_date() as functions. 'dob_day', 'dob_month' and 'dob_year' are all column names with datatype of integer. I'm new to all this but another column definition is using other functions, e.g. replace() and right(), successfully, so I'm confused why this isn't working.

When I tried to run the code in pgAdmin I got this error:

ERROR:  generation expression is not immutable
SQL state: 42P17

Thanks

CodePudding user response：

to_char() is is not marked as immutable even though in your case it would be. But there are format masks that are not immutable if e.g. time zones or different locales are involved.

If you really want to (or are forced to) convert day,month, year in a formatted string (rather than a proper date which would be the correct thing to do), then you can only achieve this with a custom function:

create function create_string_date(p_year int, p_month int, p_day int) 
  returns text
as
$$
   select to_char(make_date(p_year, p_month, p_day), 'yyyy-mm-dd hh24:mi:ss');
$$
language sql
immutable;

Marking the function as immutable isn't cheating, because we know that with the given input and format string this is indeed immutable.

dob text generated always as (create_string_date(dob_year, dob_month, dob_day)) stored