waiting for pg 8.4

i will try to follow development of 8.4, and write examples of what's possible with it, based on current HEAD code.

of course there is no guarantee that it will work in final, released 8.4 (it is a bit too early to talk about it, but given the fact that the patch got committed, there is pretty good chance we will see it whenever 8.4 will be released.

today 2 new features: limit (select ) and optional as in select.

first feature: limit (select )

this is actually not new feature, but rather removed limitation from code.

in pg (up to 8.3) this will fail:

CREATE TABLE x (id int4 PRIMARY KEY, FIELD int4);
INSERT INTO x (id, FIELD) SELECT i, random() * 10000 FROM generate_series(1,100) i;
SELECT * FROM x ORDER BY FIELD LIMIT (SELECT COUNT(*) / 10 FROM x);

error will be:

# SELECT * FROM x ORDER BY FIELD LIMIT (SELECT COUNT(*) / 10 FROM x);
ERROR:  argument OF LIMIT must NOT contain subqueries

but in 8.4, you can do it:

# SELECT * FROM x ORDER BY FIELD LIMIT (SELECT COUNT(*) / 10 FROM x);
 id | FIELD
----+-------
  4 |    40
 86 |   159
 69 |   267
 46 |   478
 55 |   543
 61 |   698
 32 |   875
 35 |   916
 73 |  1072
 15 |  1090
(10 ROWS)

it is not terribly useful, but there are cases that you'll need it.

next feature brings pgsql a bit closer to sql standard. apparently standard says that “as" is optional.

i.e., it should be possible to write:

SELECT FIELD some_name, other_field some_other_name FROM TABLE ...

instead of:

SELECT FIELD AS some_name, other_field AS some_other_name FROM TABLE ...

but, in postgresql it was not available:

# SELECT id x_id, FIELD random_value FROM x LIMIT 1;
ERROR:  syntax error at OR near "x_id"
LINE 1: SELECT id x_id, FIELD random_value FROM x LIMIT 1;
                  ^

in 8.4 though, you can:

# SELECT id x_id, FIELD random_value FROM x LIMIT 1;
 x_id | random_value
------+--------------
    1 |         6449
(1 ROW)

both commits by tom lane.

2 thoughts on “waiting for pg 8.4”

  1. I suppose that also the OFFSET limitations will go away.
    There’s no need and “paged” selects are quite common!

  2. The second feature could hide some buggy SQL without “,” between field names…

    Don’t like it…

Comments are closed.