×

Welcome to TagMyCode

Please login or create account to add a snippet.
0
0
 
0
Language: SQL
Posted by: Maria Dan
Added: Feb 5, 2021 4:35 PM
Modified: Mar 22, 2021 4:43 PM
Views: 4744
  1. --select from object
  2. SELECT client,
  3.     data->'title' AS title
  4.     FROM books;
  5.    
  6. --select from array
  7. SELECT data->0
  8. FROM products WHERE data->>0 = 'red';
  9.  
  10. --comment
  11. comment ON COLUMN TABLE_NAME.column_name IS 'comment';
  12.  
  13. --deleted index
  14. CREATE UNIQUE INDEX not_deleted_brands_idx ON brands (name) WHERE NOT is_deleted
  15.  
  16. --recursive
  17. WITH RECURSIVE temp (id) AS
  18.                    (
  19.                        SELECT menu.id, menu.item FROM menu WHERE id=1
  20.                        UNION ALL
  21.                        SELECT menu.id, menu.item FROM temp, menu WHERE menu.parent_id = temp.id
  22.                    )
  23. SELECT * FROM temp WHERE id!=1;
  24.