PostgreSQL – I have a Syntax error in my SUBSTRING query

December 6, 2022

I’m trying to make use of the SUBSTRING() function to extract a substring from vm.location_path, starting at the second character and ending at the position of the ‘]’ character, minus two.

I want to extract the text between the square brackets ([]) in vm.location_path but I’m hitting a syntax error.

SELECT
    'UPDATE vm SET dstore_moref = ''' || datastore_inv.moref || ''' WHERE id = ''' || vm.id || ''';'
FROM
    vm
INNER JOIN vapp_vm ON vapp_vm.svm_id = vm.id
INNER JOIN vm_inv ON vm_inv.moref = vm.moref
INNER JOIN datastore_inv ON datastore_inv.vc_display_name =(
        SUBSTRING(
            vm.location_path,
            2,
            POSITION(']',
            vm.location_path) - 2
        )
    )
WHERE
    vm.dstore_moref IS NULL AND vm_inv.is_deleted IS FALSE
GROUP BY datastore_inv.moref, vm.id;

SQL Error [42601]: ERROR: syntax error at or near ","
  Position: 370


Error position: line: 11 pos: 369

It’s between the comma at the end of

POSITION(']',

and

vm.location_path) - 2

What am I not seeing?

This is for vCloud Director. I am trying to get a print out of all VMs that are NULL.

>Solution :

The syntax is POSITION(search_string in main_string) with IN Keyowrd instead of ,

SELECT
    'UPDATE vm SET dstore_moref = ''' || datastore_inv.moref || ''' WHERE id = ''' || vm.id || ''';'
FROM
    vm
INNER JOIN vapp_vm ON vapp_vm.svm_id = vm.id
INNER JOIN vm_inv ON vm_inv.moref = vm.moref
INNER JOIN datastore_inv ON datastore_inv.vc_display_name =(
        SUBSTRING(
            vm.location_path,
            2,
            POSITION(']' IN vm.location_path) - 2
        )
    )
WHERE
    vm.dstore_moref IS NULL AND vm_inv.is_deleted IS FALSE
GROUP BY datastore_inv.moref, vm.id;