You can do this with crosstab() from the additional module tablefunc: SELECT b , COALESCE(a1, 0) AS “A1” , COALESCE(a2, 0) AS “A2” , COALESCE(a3, 0) AS “A3” , … — all the way up to “A30” FROM crosstab( ‘SELECT colb, cola, 1 AS val FROM matrix ORDER BY 1,2’ , $$SELECT ‘A’::text || g … Read more
Yes, that is a feature of Postgres and parallel unnesting is guaranteed to be in sync (as long as all arrays have the same number of elements). Postgres 9.4 adds a clean solution for parallel unnest: Unnest multiple arrays in parallel The order of resulting rows is not guaranteed, though. Actually, with a statement as … Read more
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
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
Because the PRIMARY KEY makes the included column(s) NOT NULL automatically. I quote the manual here: The primary key constraint specifies that a column or columns of a table can contain only unique (non-duplicate), nonnull values. Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL. Bold emphasis mine. I ran a test … 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.
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