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.
Advertisement
Answer
Fix error
To use the data type ltree
and associated functions you need the additional module ltree
installed.
By default, additional modules are installed to the schema public
. If your current setting for search_path
does not include the schema public
, ltree operators are not found. Your error messages hints in this direction:
ERROR: operator does not exist: myschema.ltree @ unknown at character 63
To verify, schema-qualify the ltree operator @
:
SELECT path FROM tbl WHERE path operator(public.@) '101';
Does it work now?
If that’s the root of the problem, you can also set your search_path
properly. Check with:
SHOW search_path;
Set with:
SET search_path = public; -- add more schemas as needed
UPDATE path
If I read your question right and you want to remove a certain item from the path of all rows:
UPDATE tbl t SET path = nu.path FROM ( WITH x AS ( SELECT sno, path, index(path, '101') AS i FROM tbl WHERE path ~ '*.101.*' ) SELECT sno , subpath(path, 0, i) || CASE WHEN nlevel(path) > i+1 THEN subpath(path, i+1) ELSE '' END AS path FROM x ) nu WHERE nu.sno = t.sno;
(Note that since path
is type ltree
, the operator ~
in the expression path ~ '*.101.*'
is not a regular expression match operator, but a special ltree match operator, and the right term is taken to be of type lquery
. It matches any label path containing the label 101
.)
This would leave you with an empty path where you had just 101
. You could put this into a trigger AFTER DELETE
to purge a certain item from all rows.