postgres sql

Language: SQL

Posted by: Maria Dan

Added: Feb 5, 2021 4:35 PM

Modified: Mar 22, 2021 4:43 PM

Tags: comment index recursive

--select from object
SELECT client,
data->'title' AS title
FROM books;
--select from array
SELECT data->0
FROM products WHERE data->>0 = 'red';
--comment
comment ON COLUMN TABLE_NAME.column_name IS 'comment';
--deleted index
CREATE UNIQUE INDEX not_deleted_brands_idx ON brands (name) WHERE NOT is_deleted
--recursive
WITH RECURSIVE temp (id) AS
(
SELECT menu.id, menu.item FROM menu WHERE id=1
UNION ALL
SELECT menu.id, menu.item FROM temp, menu WHERE menu.parent_id = temp.id
)
SELECT * FROM temp WHERE id!=1;