The array subscript numbers are written within square brackets. By default PostgreSQL uses a one-based numbering convention for arrays, that is, an array of n elements starts with array[1] and ends with array[n].

create_table "project" do |t| t.bigint "employee_ids", array: true t.string "title" end

add_column :videos, :tag_ids, :bigint, array: true Tag.has_many :videos, array: true

ARRAY( SELECT DISTINCT ... FROM unnest(arr) )

unnest ( anyarray ) → setof anyelement Expands an array into a set of rows. The array's elements are read out in storage order.

In PostrgreSQL 8.4 and up you can use: select array_agg(x) from (select unnest(ARRAY[1,5,3,7,2]) AS x order by x) as _; But it will not be very fast.

The best way to sort an array of integers is without a doubt to use the intarray extension, which will do it much, much, much faster than any SQL formulation: CREATE EXTENSION intarray; SELECT sort( ARRAY[4,3,2,1] );

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 parenthesized (not bracketed) subquery.

An array constructor is an expression that builds an array value using values for its member elements. A simple array constructor consists of the key word ARRAY, a left square bracket [, a list of expressions (separated by commas) for the array element values, and finally a right square bracket ].

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;