[SQL] How to delete a particular label from ltree in Postgres table?

How to delete a particular label from ltree in Postgres table? I have a table?

Test table:

CREATE TABLE tbl (sno int, path ltree, userid int);

INSERT INTO tbl (sno, path, userid)
VALUES
(1, '123', 123)
, (2, '123.101', 123)
, (3, '123.101.103', 123)
, (4, '123.101.103.105', 123)
, (5, '123.101.103.107', 123)
, (6, '123.102.104.106', 123)
, (7, '123.102.104.108', 123)
, (8, '123.102.104', 123)
, (9, '123.102', 123);


I want to pass a userid to a query, to remove it from every path in the table. For example, if I pass 101, then 123.101.103 should update as 123.103.

Is it possible to do this directly? Or shall I update path using path replace functions?

I tried the following select query from PHP, but it returns the error below. The same query works properly in phpPgAdmin!?

Query:

$selectPathq=pg_query($con,"select path from myschema.test where path @ '101'")
or die('could not connect: '. pg_last_error($con));


Error:

could not connect:
ERROR: operator does not exist: myschema.ltree @ unknown at character 63
HINT: No operator matches the given name and argument type(s).
You might need to add explicit type casts.​

Continue reading...