As of PostgreSQL 9.4, you can use the ? operator: select info->>’name’ from rabbits where (info->’food’)::jsonb ? ‘carrots’; You can even index the ? query on the “food” key if you switch to the jsonb type instead: alter table rabbits alter info type jsonb using info::jsonb; create index on rabbits using gin ((info->’food’)); select info->>’name’ … Read more
Found: CREATE TABLE publishers(id INT, info JSON); CREATE INDEX ON publishers((info->>’name’)); As stated in the comments, the subtle difference here is ->> instead of ->. The former one returns the value as text, the latter as a JSON object.
Clean setup: CREATE TABLE tbl ( given_date date , set_name varchar ); Use a singular term as column name for a single value. The data type is obviously date and not a timestamp. To transform your text parameters into a useful table: SELECT unnest(string_to_array(‘2001-01-01to2001-01-05,2001-01-10to2001-01-15’, ‘,’)) AS date_range , unnest(string_to_array(‘s1,s2’, ‘,’)) AS set_name; “Parallel unnest” is … Read more
Instead of playing with symlinks in system library dirs, set the $DYLD_FALLBACK_LIBRARY_PATH to include the anaconda libraries. eg: export DYLD_FALLBACK_LIBRARY_PATH=$HOME/anaconda/lib/:$DYLD_FALLBACK_LIBRARY_PATH
For Postgres 9.4+ see adamc’s later answer. Or: Query for array elements inside JSON type Original answer for Postgres 9.3 Yes, that’s possible: SELECT * FROM tbl t, json_array_elements(t.json_col->’emails’) AS elem WHERE elem->>’id’ = 123; tbl being your table name, json_col the name of the JSON column. See also: How do I query using fields … Read more
You should refresh the view in triggers after insert/update/delete/truncate for each statement on table1 and table2. create or replace function refresh_mat_view() returns trigger language plpgsql as $$ begin refresh materialized view mat_view; return null; end $$; create trigger refresh_mat_view after insert or update or delete or truncate on table1 for each statement execute procedure refresh_mat_view(); … Read more
Erwin sent a correct reply. My next reply is related to possibility to disable overloading. It is not possible to disable overloading – this is a base feature of PostgreSQL function API system – and cannot be disabled. We know so there are some side effects like strong function signature rigidity – but it is … Read more
SELECT * FROM crosstab ( ‘SELECT ProductNumber, ProductName, Salescountry, SalesQuantity FROM product ORDER BY 1’ , $$SELECT unnest(‘{US,UK,UAE1}’::varchar[])$$ ) AS ct ( “ProductNumber” varchar , “ProductName” varchar , “US” int , “UK” int , “UAE1” int); Detailed explanation: PostgreSQL Crosstab Query Pivot on Multiple Columns using Tablefunc Completely dynamic query for varying number of distinct … Read more
TL;DR Use 172.17.0.0/16 as IP address range, not 172.17.0.0/32. Don’t use localhost to connect to the PostgreSQL database on your host, but the host’s IP instead. To keep the container portable, start the container with the –add-host=database:<host-ip> flag and use database as hostname for connecting to PostgreSQL. Make sure PostgreSQL is configured to listen for … Read more
serial is a pseudo data type, not an actual data type. It’s an integer underneath with some additional DDL commands executed automatically: Create a sequence (with matching name by default). Set the column NOT NULL and the default to draw from that sequence. Make the column “own” the sequence. Details: Safely and cleanly rename tables … Read more