Hot answers tagged

86

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() ...


21

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: ...


19

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 ...


17

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 ...


16

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[] AS $BODY$ DECLARE v_output text[]; BEGIN SELECT array_agg(ary)::text[] INTO v_output FROM jsonb_array_elements_text(p_input) AS ary; ...


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.


15

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 ...


12

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 ...


11

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 ...


11

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 ...


11

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}'); ...


10

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 ...


10

jsonb in Postgres 9.4 or later You will be interested in the jsonb data type in Postgres 9.4. 'b' is for 'binary'. Among other things, there is an equality operator = for jsonb. Most people will want switch to jsonb. Depesz blog about jsonb. json There is currently no = operator defined for the data type json, because there is no well defined method to ...


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 ...


9

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.


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

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

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 ...


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

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 ...


7

This sort of thing gets complicated. I am working on some related projects right now. The basic tweak is that PostgreSQL uses a format which uses double quotes internally in tuple representation to represent literal values, so: SELECT save_book('(179,the art of war,fiction,"{190,220}")'::book); should work. In essence a neat trick is creating a csv and ...


7

Is there some operator or function that can give me the indices of the array No, you have to loop through the associative array: declare type a_arr_t is table of PLS_INTEGER index by PLS_INTEGER; type keys_t is table of PLS_INTEGER; a_arr a_arr_t; keys_ keys_t := keys_t(); l_index integer; begin a_arr(1) := 2; ...


7

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, ...


7

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: ...


7

'some test', so let's say you want to add 10 to 96 : db.student.update({name:'student_1','courses.name':'Algebra'},{$inc:{"courses.$.grades.1":10}}) In the find part, you point to the courses with name 'Algebra'. The .$ points you to that found element. Of that found element, it will change the second (.1) value in the grades array. Using $, you don't need ...


7

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 ...


7

You put the cast outside the array constructor - use ::varchar[] instead. The current query will return you the array literal (which is of type varchar) instead of an actual array - see the example SQL Fiddle. But overall, if you only need the output as an array downstream, it would make your life easier if you returned proper columns upstream - easier to ...


7

I answered a basically identical question on SO some time ago: Array of composite type as stored procedure input Suggesting the same solution with unnest() like @a_horse did. Assuming that books_table has the same row type as your composite type books, you do not need to create the additional type at all, just use the row type of the table: CREATE TABLE ...


7

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 ...


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