Postgresql – Is the order of records in a VALUES list well-defined (postgres)

postgresqlwindow functions

Postgres 9.4 introduced the WITH ORDINALITY clause which adds an ordinality column to a table-like expression, somewhat similar to ROW_NUMBER() OVER (). Apparently, this is used to provide ranks for set-returning functions like unnest(<array>). My question is, is the order of rows defined for VALUES lists? I would like very much for this to be the case:

 SELECT *, ROW_NUMBER() OVER () FROM (VALUES ('a'), ('b'), ('c')) xs(a);
 a | row_number 
---+------------
 a |          1
 b |          2
 c |          3

EDIT: Fixed to use ROW_NUMBER instead of WITH ORDINALITY (which is syntactically incompatible with a values list).

Best Answer

VALUES is treated like SELECT. The SELECT documentation says:

If the ORDER BY clause is specified, the returned rows are sorted in the specified order. If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce.

Your version of Postgres happens to return the three rows in the order you have written them. While unlikely, this could change in the future.

Related Solutions

Postgresql – Checking for multiple identical values in a Posgresql array

Note: this will only work if you make data.id a PRIMARY KEY

SELECT
    data.id
FROM 
    data, 
    LATERAL (SELECT DISTINCT unnest(values)) no_duplicates
GROUP BY 
    data.id 
HAVING
    array_length(values, 1) > COUNT(no_duplicates)

Here's an SQL Fiddle.

This works by converting your array into a recordset/table (which I've called "no_duplicates") using unnest(), and removing duplicates using DISTINCT:

LATERAL (SELECT DISTINCT unnest(values)) no_duplicates

Then I GROUP BY the original data table's ID, and compare the length of the new, filtered recordset with the old, unfiltered table. If the original, unfiltered array is bigger, then we removed duplicates so we should select that row:

array_length(values, 1) > COUNT(no_duplicates)

Postgresql – find all JSON scalars in PostgreSQL 9.3

Horrible as it is, you could write a PL/PgSQL function that attempts to extract an element from the object, traps the exception if it fails, and returns true on success or false on exception.

Untested:

create or replace function json_isobject(obj json)
returns boolean immutable language plpgsql as $$
begin
    begin
        perform obj->'';
    exception
      when invalid_parameter_value then
        return false;
      when others then
        raise;
    end;
    return true;
end;
$$;

This'll be pretty inefficient, since it's creating a sub-transaction for each call.

Or you could just update to 9.4.

On 9.4 I can't reproduce your reported error. Trying to look up a key in a json scalar returns null.

test=> select ('{"k":"v"}'::json) -> 'k' -> 'blah';
 ?column? 
----------

(1 row)

... and the same with using ->> to dereference a scalar.

So I'm not sure if this function will work on 9.3, and it won't on 9.4. To be 9.4-compatible you'd have to test if current_setting('pg_version_num') >= 90400 and return a result based on json_typeof instead.

You might need to adapt it to use the same expression you use to trigger your reported error.

Best Answer

Related Solutions

Postgresql – Checking for multiple identical values in a Posgresql array

Postgresql – find all JSON scalars in PostgreSQL 9.3

Related Question