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.

Do your career a big favor. Join DEV. (The website you're on right now)

It takes one minute, it's free, and is worth it for your career.

Get started

Community matters

Top comments (0)

πŸ‘‹ Kindness is contagious

Dive into an ocean of knowledge with this thought-provoking post, revered deeply within the supportive DEV Community. Developers of all levels are welcome to join and enhance our collective intelligence.

Saying a simple "thank you" can brighten someone's day. Share your gratitude in the comments below!

On DEV, sharing ideas eases our path and fortifies our community connections. Found this helpful? Sending a quick thanks to the author can be profoundly valued.

Okay