Thread: ? bug pg 9.6

? bug pg 9.6

From

"alain bourgeois"

Date:

25 August 2017, 18:00:39

Table t1 (id int).

Using pg admin:

select id from t1 order by t1

=> I expected an error, but it works???????

Alain Bourgeois
Software Engineer
Tel: +32 (0)2 333 49 20
Mobile: +32 (0)496 51 85 75
skype: abozetes
ZETES PASS, division of ZETES SA/NV
Disclaimer : This e-mail may contain material that is confidential and privileged for the sole use of the intended recipient. Any review, reliance or distribution by others or forwarding without express permission is strictly prohibited and may be unlawful. If you receive this message in error, please contact the sender and delete the material from any computer.

Re: ? bug pg 9.6

From

Dave Page

Date:

25 August 2017, 18:10:07

On Fri, Aug 25, 2017 at 1:00 PM, alain bourgeois <a.bourgeois@zetescards.be> wrote:

Table t1 (id int).

Using pg admin:

select id from t1 order by t1

=> I expected an error, but it works???????

As it does in psql:

postgres=# create table t1(id int);

CREATE TABLE

postgres=# select id from t1 order by t1;

----

(0 rows)

I suspect it's because you can order by anything in the select list, and as t1 is a type as well as a table (each table has a corresponding row type), it's seen as a row constructor.

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: ? bug pg 9.6

From

"alain bourgeois"

Date:

25 August 2017, 18:19:42

But t1 is not in the select list... (and this doesn't work in oracle nor mariadb)... It is "strange" but not blocking.

Alain Bourgeois
Software Engineer
Tel: +32 (0)2 333 49 20
Mobile: +32 (0)496 51 85 75
skype: abozetes
ZETES PASS, division of ZETES SA/NV
Disclaimer : This e-mail may contain material that is confidential and privileged for the sole use of the intended recipient. Any review, reliance or distribution by others or forwarding without express permission is strictly prohibited and may be unlawful. If you receive this message in error, please contact the sender and delete the material from any computer.

From: Dave Page <dpage@pgadmin.org>
To: alain bourgeois <a.bourgeois@zetescards.be>
Cc: pgadmin-support@lists.postgresql.org
Date: 25-08-17 14:10
Subject: Re: ? bug pg 9.6

On Fri, Aug 25, 2017 at 1:00 PM, alain bourgeois <a.bourgeois@zetescards.be> wrote:
Table t1 (id int).

Using pg admin:

select id from t1 order by t1

=> I expected an error, but it works???????

As it does in psql:

postgres=# create table t1(id int);
CREATE TABLE
postgres=# select id from t1 order by t1;
id
----
(0 rows)

I suspect it's because you can order by anything in the select list, and as t1 is a type as well as a table (each table has a corresponding row type), it's seen as a row constructor.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: ? bug pg 9.6

From

Dave Page

Date:

25 August 2017, 18:46:26

On Fri, Aug 25, 2017 at 1:19 PM, alain bourgeois <a.bourgeois@zetescards.be> wrote:

But t1 is not in the select list... (and this doesn't work in oracle nor mariadb)... It is "strange" but not blocking.

Poor phrasing on my part; the docs phrase it this way: The sort expression(s) can be any expression that would be valid in the query's select list.

In other words, *could* be in the select list, not *is* in the select list.

Alain Bourgeois
Software Engineer
Tel: +32 (0)2 333 49 20
Mobile: +32 (0)496 51 85 75
skype: abozetes
ZETES PASS, division of ZETES SA/NV
Disclaimer : This e-mail may contain material that is confidential and privileged for the sole use of the intended recipient. Any review, reliance or distribution by others or forwarding without express permission is strictly prohibited and may be unlawful. If you receive this message in error, please contact the sender and delete the material from any computer.

From: Dave Page <dpage@pgadmin.org>
To: alain bourgeois <a.bourgeois@zetescards.be>
Cc: pgadmin-support@lists.postgresql.org
Date: 25-08-17 14:10
Subject: Re: ? bug pg 9.6

On Fri, Aug 25, 2017 at 1:00 PM, alain bourgeois <a.bourgeois@zetescards.be> wrote:
Table t1 (id int).

Using pg admin:

select id from t1 order by t1

=> I expected an error, but it works???????

As it does in psql:

postgres=# create table t1(id int);
CREATE TABLE
postgres=# select id from t1 order by t1;
id
----
(0 rows)

I suspect it's because you can order by anything in the select list, and as t1 is a type as well as a table (each table has a corresponding row type), it's seen as a row constructor.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: ? bug pg 9.6

From

Thom Brown

Date:

25 August 2017, 19:23:56

On 25 August 2017 at 13:19, alain bourgeois <a.bourgeois@zetescards.be> wrote:
> But t1 is not in the select list... (and this doesn't work in oracle nor
> mariadb)... It is "strange" but not blocking.

You can refer to the whole table like you would with columns in a
query, so it's valid.

For example:

SELECT tablename
FROM tablename;

This will return the table's data as a single column, the type of
which is the table itself.

SELECT DISTINCT (tablename) tablename, count(*)
FROM tablename
GROUP BY tablename
HAVING count(*) > 1
ORDER BY tablename;

This will effectively show you which rows are duplicated, and how many
times they are duplicated.

And being able to pass the table to a function can be really useful.
For example:

SELECT to_jsonb(tablename) FROM tablename;

This will output the table data as JSON, and use the column names as the keys.

Thom