When you code a SQL script that must run on PostgreSQL or YugabyteDB, you may want to set additional parameters in YugabyteDB that are not recognized by PostgreSQL. Using SET
or set_config
in this case would raise an error on PostgreSQL.
One approach is to check if the version is compatible using version() LIKE '%-YB-%'
, but a simpler solution is to set all parameters and ignore any that do not exist.
For example, if you want to set the application name and, for YugabyteDB, default to range sharding, you can use the following statement:
with new_settings (name, setting) as ( values
('application_name', 'test'),
('yb_use_hash_splitting_by_default', 'on')
) update pg_settings as o set setting = n.setting
from new_settings n
where o.name = n.name and version() like '%-YB-%'
;
On PostgreSQL, only the application name will be set:
set_config
------------
test
(1 row)
UPDATE 0
On YugabyteDB, the same script sets both parameters:
set_config
------------
test
on
(2 rows)
UPDATE 0
Note that a better option is to start the YugabyteDB cluster with "PostgreSQL Parity" enabled, which will set many parameters by default, and you should not have to set more YugabyteDB specific ones.
Top comments (0)