Hot answers tagged

108

Postgres 9.4 or newer Obviously inspired by this post, Postgres 9.4 added the missing function(s): Thanks to Laurence Rowe for the patch and Andrew Dunstan for committing! json_array_elements_text(json) jsonb_array_elements_text(jsonb) To unnest the JSON array. Then use array_agg() or an ARRAY constructor to build a Postgres array from it. Or string_agg() ...


25

WITH ORDINALITY in Postgres 9.4 or later The new feature simplifies this class of problems. The above query can now simply be: SELECT * FROM regexp_split_to_table('I think Postgres is nifty', ' ') WITH ORDINALITY x(word, rn); Or, applied to a table: SELECT * FROM tbl t, regexp_split_to_table(t.my_column, ' ') WITH ORDINALITY x(word, rn); Details: ...


20

Support for array foreign keys was worked on with the goal of getting it into PostgreSQL 9.3, but it didn't make the cut for the release due to performance and reliability problems. It doesn't seem to be being worked on for 9.4. At this time, you need to stick to the usual relational approach of using a "join table" to model an m:n relationship. CREATE ...


20

It would be much more efficient to store your values in a normalized schema. That said, you can also make it work with your current setup. Assumptions Assuming this table definition: CREATE TABLE tbl (tbl_id int, usr jsonb); "user" is a reserved word and would require double quoting to be used as column name. Don't do that. I use usr instead. Query The ...


18

PG 9.4+ The accepted answer is definitely what you need, but for the sake of simplicity here is a helper I use for this: CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(p_input jsonb) RETURNS text[] LANGUAGE sql IMMUTABLE AS $function$ SELECT array_agg(ary)::text[] FROM jsonb_array_elements_text(p_input) AS ary; $function$; Then just do: SELECT ...


18

There is a way: combine the containment operator @> with the ANY construct: SELECT d FROM grp WHERE d->'customers' @> ANY (ARRAY ['[{"id":"1"}]', '[{"id":"5"}]']::jsonb[]); Or: ... WHERE d->'customers' @> ANY ('{"[{\"id\": \"1\"}]","[{\"id\": \"5\"}]"}'::jsonb[]); It's essential to cast the array to jsonb[] explicitly. And note that ...


18

There is nothing "old school" or "outdated" about an ARRAY constructor (That's what ARRAY(SELECT x FROM foobar) is). It's modern as ever. Use it for simple array aggregation. The manual: It is also possible to construct an array from the results of a subquery. In this form, the array constructor is written with the key word ARRAY followed by a ...


15

Use an array to represent the series of values: pg_prepare($con, "prep", "select * from test where tid=ANY($1::int[])"); $strpar = "{3,4,6,8,10}"; pg_execute($con, "prep", array($strpars)); The cast to int[] in the query might even be superfluous if the planner is able to infer the type by itself.


13

It's highly likely that the best approach will be a side-table of sometable(main_id, value) where you have a composite index on (main_id, value). This allows very fast lookups to see "for this mainid, does this value exist". This will let you enforce foreign key relationships. Unless you have a good reason, use this conventional relational approach. Failing ...


13

Postgres arrays are 1-based by default. And in typical applications it's best to stick with the default. But the syntax allows to start with any integer number. The documentation: Subscripted assignment allows creation of arrays that do not use one-based subscripts. For example one might assign to myarray[-2:7] to create an array with subscript values ...


13

You can use UNNEST. select unnest(ports) as port, count(*) from foo group by port; Using more than one UNNEST in the same query (or the same select list, anyway) is confusing and is probably best avoided.


13

If containers can be empty, the currently accepted solution does not work for you. It has to be an outer join to preserve rows without match - to get equivalent results to the correlated subqueries you are using in your fiddle: select *, array(select thing_id from container_thing where container_id = container.id) as "thingIds" from container 1. SELECT ...


12

This would do what you desire: WITH p AS ( INSERT INTO parent_table (column_1) SELECT $1 RETURNING id) INSERT INTO child_table (parent_table_id, column_a, column_b) SELECT p.id, t.a, t.b FROM p, (SELECT unnest($2::text[]) AS a, unnest($3::bigint[]) AS b) t The subtle difference here is that unnest() calls in the same SELECT list are ...


12

Sure, with json_object_keys(). This returns a set - unlike the JavaScript function Object.keys(obj) you are referring to, which returns an array. Feed the set to an ARRAY constructor to transform it: SELECT id, ARRAY(SELECT json_object_keys(obj)) AS keys FROM tbl_items; Or use jsonb_object_keys() for jsonb. This returns an array of keys per row (not for ...


12

Pass the array as is using the VARIADIC key word in the call: CREATE OR REPLACE FUNCTION json_extract_path_text(string citext, VARIADIC params text[]) RETURNS text LANGUAGE sql IMMUTABLE AS 'SELECT json_extract_path_text(string::json, VARIADIC params)'; Call: SELECT json_extract_path_text('{"f1":{"f2":1,"f3":"foo"}}', VARIADIC '{f1, f3}'); ...


11

jsonb in Postgres 9.4 or later Consider the jsonb data type in Postgres 9.4 or later. The 'b' at the end stands for 'binary'. Among other things, there is an equality operator (=) for jsonb. Most people will want to switch. Depesz blog about jsonb. json There is no = operator defined for the data type json, because there is no well defined method to ...


11

I would suggest not using a JSON array, and instead using the native SQL array syntax which is likely much faster and more efficiently stored. It's also stronger typed. The JSON array is "possibly-heterogeneously-typed" per the docs. I also wouldn't do this routinely. I would alter the table's schema to have an ARRAY (preferably SQL) on the table itself to ...


10

Sample data You have a table like, CREATE TABLE research(colors) AS VALUES ('Blue'), ('Orange'), ('Yellow'); ENUM type You have an enumerated list of colors. So the easy thing here would be to use an ENUM type CREATE TYPE colors AS ENUM ('Red','Orange','Yellow','Green','Blue'); Then ALTER TABLE research ALTER COLUMN colors -- myColorsColumn ...


9

While passing integer numbers, you can either cast the whole array: TG_ARGV::int[] Or you can cast an element, then it must be the element type: TG_ARGV[0]::int I used it that way in my answer to your previous question: SELECT in trigger function in two tables However, you are not passing integer numbers, but the text representation of integer arrays: an ...


9

Correct results? First off: correctness. You want to produce an array of unique elements? Your current query does not do that. The function uniq() from the intarray module only promises to: remove adjacent duplicates Like instructed in the manual, you would need: SELECT l.d + r.d, uniq(sort(array_agg_mult(r.arr))) FROM ... Also gives you sorted arrays - ...


9

I was told to implement as demonstrated below as this is how its been done before. ... this smells funny to me. You know what smells funnier than this approach? That line of "reasoning". The old "this is how it's always been done" is simply a means of avoiding thinking about it and discussing it. And that alone should raise a red flag, even if the code is ...


8

This question was asked on the PostgreSQL mailing lists and I came up with this hackish way of converting JSON text to PostgreSQL text type via the JSON field extraction operator: CREATE FUNCTION json_text(json) RETURNS text IMMUTABLE LANGUAGE sql AS $$ SELECT ('['||$1||']')::json->>0 $$; db=# select json_text(json_array_elements('["hello",1.3,"\...


8

There is no built-in function that I would know of. In Postgres 9.3+ you have the similar function array_remove(anyarray, anyelement) to (per documentation): remove all elements equal to the given value from the array (array must be one-dimensional) To delete the 3rd element from a 1-dimenstional array with unique elements: SELECT array_remove(arr, ...


8

should return 0 rather than nothing (NULL), right? It's up for debate what this should return. But the way Postgres defines it, the result for any dimension that does not exist is NULL. COALESCE is the tool to fix your query, but the better solution is not to break it to begin with. Alternative in Postgres 9.4 Postgres 9.4 or later provides a separate ...


8

It looks like PostgreSQL returns NULL for empty arrays. Try: SELECT array_length(ARRAY[]::bigint[], 1), array_length(ARRAY[]::bigint[], 1) IS NULL, array_lower(ARRAY[]::bigint[], 1), array_upper(ARRAY[]::bigint[], 1) You get: null|true|null|null Looks weird, but that's just the way it is. The workaround is to use COALESCE: SELECT ...


8

First off, your question as well as your column name "key" are misleading. The column key does not contain any JSON keys, only values. Else we could use the function jsonb_object_keys(jsonb) to extract keys, but that's not so. Assuming all your JSON arrays are either empty or hold integer numbers as demonstrated. And the scalar values (non-arrays) are also ...


8

TL;DR: Do not use an array. Use individual boolean columns. Your coworker may not be aware of actual storage requirements. Null storage is very cheap and efficient in Postgres. Do nullable columns occupy additional space in PostgreSQL? A couple of boolean columns are also very cheap and efficient - nullable or not. Actually, just 5 boolean columns hardly ...


8

While I think Postgresql has had arrays since before SQL-99, the SQL-99 standard specifies 1-indexed arrays, and it's consistent with other areas in SQL where the first element is 1 instead of 0. PostgreSQL actually has support for custom array start indexes, but I strongly advise you not to use it.


8

Without knowing your data, I can only guess that the selectivity of your index is low (which happen if the length of the array does not vary much). One trick to overcome this might be changing the query slightly and creating a covering index. For this, choose a NOT NULL column (for example, the primary key of the table) to count, and then include this ...


8

The data type of the column people is json, as is the result of json_array_elements(people). And there is no equality operator (=) for the data type json. So you also cannot run GROUP BY on it. More: How to remove known elements from a JSON[] array in PostgreSQL? jsonb has an equality operator, so the "workaround" in your answer is to cast to jsonb and use ...


Only top voted, non community-wiki answers of a minimum length are eligible