DEV Community

Franck Pachot for YugabyteDB

Posted on

set parameter if exists

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-%'
;

Enter fullscreen mode Exit fullscreen mode

On PostgreSQL, only the application name will be set:

 set_config
------------
 test
(1 row)

UPDATE 0

Enter fullscreen mode Exit fullscreen mode

On YugabyteDB, the same script sets both parameters:

 set_config
------------
 test
 on
(2 rows)

UPDATE 0
Enter fullscreen mode Exit fullscreen mode

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.

πŸ‘‹ While you are here

Reinvent your career. Join DEV.

It takes one minute and is worth it for your career.

Get started

Top comments (0)

πŸ‘‹ Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay