Thanks for the insight.
Pai-Hung
On 4/18/15 1:30 AM, Pai-Hung Chen wrote:
Thanks for the help. So in this case, the performance of ORDER BY will
not be affected at all by whether an index is created on the jsonb
"setting" field?
No. The optimizer is going to first try and satisfy the WHERE clause
with that relevant index. Bec
"pgsql-general@postgresql.org"
Subject: Re: [GENERAL] ORDER BY for jsonb
On 4/17/15 9:53 PM, Pai-Hung Chen wrote:
> Hi,
>
> I am new to PostgreSQL and have a question about the new jsonb type in
> 9.4. Suppose I have a table called "user" that has two columns: (1)
&g
On 4/17/15 9:53 PM, Pai-Hung Chen wrote:
Hi,
I am new to PostgreSQL and have a question about the new jsonb type in
9.4. Suppose I have a table called "user" that has two columns: (1)
"user_id" of type text, also the primary key, (2) "setting" of type
jsonb. With the following query pattern:
SE
Hi,
I am new to PostgreSQL and have a question about the new jsonb type in 9.4.
Suppose I have a table called "user" that has two columns: (1) "user_id" of
type text, also the primary key, (2) "setting" of type jsonb. With the
following query pattern:
SELECT *
FROM user
WHERE user_id IN [...]
ORD
>
>
> On Wed, Jan 21, 2015 at 4:09 PM, Bryn Jeffries <
> bryn.jeffr...@sydney.edu.au> wrote:
>
>>
>> Maybe what we need in ODBC libs and the like is a "protected
>> statement" that follows the same construction as a prepared statement but
>> additionally checks catalogs to validate identifiers.
>>
On 01/21/2015 03:09 PM, Bryn Jeffries wrote:
Paul Jungwirth wrote
I'm not sure how to make a prepared statement that lets you name a
column when you execute it. Maybe someone else can chime in if that's
possible.
David J. responded
You cannot. By definition parameters, in this context, are v
On Wed, Jan 21, 2015 at 4:09 PM, Bryn Jeffries
wrote:
> Paul Jungwirth wrote
> > I'm not sure how to make a prepared statement that lets you name a
> > column when you execute it. Maybe someone else can chime in if that's
> > possible.
>
> David J. responded
> > You cannot. By definition paramet
Paul Jungwirth wrote
> I'm not sure how to make a prepared statement that lets you name a
> column when you execute it. Maybe someone else can chime in if that's
> possible.
David J. responded
> You cannot. By definition parameters, in this context, are values - not
> identifiers.
> [...]
> In
@postgresql.org"
Subject: [GENERAL] ORDER BY in prepared statements
Date: Thu, Jan 22, 2015 08:18
On 01/21/2015 12:51 PM, Bryn Jeffries wrote:
> In a number of places on the web I've seen it claimed that ordering can
> be set via prepared statements.
Can you give a link t
Paul Jungwirth wrote
>> In a number of places on the web I've seen it claimed that ordering can
>> be
>> set via prepared statements.
>> ...
>> sandbox=# PREPARE testplan(text) AS
>> SELECT * FROM test ORDER BY $1;
>>
>> But the output is not what one would expect:
>>
>> sandbox=# EXECUTE testplan(
On 01/21/2015 12:51 PM, Bryn Jeffries wrote:
In a number of places on the web I've seen it claimed that ordering can
be set via prepared statements.
Can you give a link to one of those examples?
Many thanks,
Bryn
--
Adrian Klaver
adrian.kla...@aklaver.com
--
Sent via pgsql-general mail
> In a number of places on the web I've seen it claimed that ordering can be
> set via prepared statements.
> ...
> sandbox=# PREPARE testplan(text) AS
> SELECT * FROM test ORDER BY $1;
>
> But the output is not what one would expect:
>
> sandbox=# EXECUTE testplan('gender');
> ...
> As opposed to:
In a number of places on the web I've seen it claimed that ordering can be set
via prepared statements. Indeed, the expected syntax is accepted on my 9.3
server without errors:
sandbox=# CREATE TABLE test (
id serial PRIMARY KEY,
gender char
);
sandbox=# INSERT INTO test(gender) VALUES('m') VA
Steve Clark wrote:
> It is like the space character and the - in -Letter is ignored.
Yes, that is how the en_US collation is defined. I think the goal
is to make it something like "phone book" ordering. If you still
have a "white pages" book around, look at how a business name with
a hyphen (o
On 08/07/2014 11:36 AM, Kevin Grittner wrote:
Steve Clark wrote:
I am confused by how postgres 8,4..13 is sorting my data.
select * from test order by data;
data
--
-
--
1
11
11F
1F
a
b
C
F
-F
Feneric
Generic
(14 rows)
The fir
Steve Clark wrote:
> I am confused by how postgres 8,4..13 is sorting my data.
> select * from test order by data;
> data
> --
>
> -
> --
> 1
> 11
> 11F
> 1F
> a
> b
> C
> F
> -F
> Feneric
> Generic
> (14 rows)
>
> The first row is a single space, the next r
Hello,
I am confused by how postgres 8,4..13 is sorting my data.
\d test
Table "public.test"
Column | Type | Modifiers
+--+---
data | text |
select * from test order by data;
data
--
-
--
1
11
11F
1F
a
b
C
F
-F
Feneric
Generic
(14 rows)
Th
On Mon, 07 Apr 2014 11:04:23 +0100
"howardn...@selestial.com" wrote:
> Hi,
>
> just as I thought I had postgres mastered :) the ordering of strings is
> causing me some confusion.
>
> Can someone explain how the database orders strings in the ORDER BY command.
>
> My example:
>
> My databas
howardn...@selestial.com wrote:
>> http://www.postgresql.org/docs/current/static/collation.html#AEN33298
>> The ordering depends on the collation. Which collations are available
>> and how they order depends on your operating system. What is your OS
>> and what do you get for SHOW lc_collate; Yours
On 07/04/2014 11:58, Albe Laurenz wrote:
http://www.postgresql.org/docs/current/static/collation.html#AEN33298
The ordering depends on the collation. Which collations are available
and how they order depends on your operating system. What is your OS
and what do you get for SHOW lc_collate; Your
howardn...@selestial.com wrote:
> just as I thought I had postgres mastered :) the ordering of strings is
> causing me some confusion.
>
> Can someone explain how the database orders strings in the ORDER BY command.
>
> My example:
>
> My database is encoding is UTF-8, and default language is e
Hi,
just as I thought I had postgres mastered :) the ordering of strings is
causing me some confusion.
Can someone explain how the database orders strings in the ORDER BY command.
My example:
My database is encoding is UTF-8, and default language is english,
If I have a text column in a ta
Nice one. Yep, that works. Cheers..
I'll submit a bug report for this, though I'm on the fence as to wether
this is actually a bug per se.. I would have reasonably expected my
original syntax to have worked (as it does without any ORDER BY)..
On Thu, Jun 20, 2013 at 3:44 PM, Adrian Klaver wrote:
On 06/20/2013 07:05 AM, Tim Kane wrote:
Hi all,
I seem to be having problems constructing a query that combines the use
of distinct, order by, and collate key words.
For instance:
# select distinct(value) from properties order by 1 collate "C";
ERROR: collations are not supported by type int
Hi all,
I seem to be having problems constructing a query that combines the use of
distinct, order by, and collate key words.
For instance:
# select distinct(value) from properties order by 1 collate "C";
ERROR: collations are not supported by type integer
LINE 1: ... distinct(value) from pro
Hi.
Anything I can do about this? Seems that the problem is that the query is a
prepared statement, even if the parameters ($1 and $6) are the same.
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select
list at character 493
STATEMENT: SELECT DISTINCT t0.package_name, ts_rank(t
writes:
> Please consider this plpgsql function:
> = = = = = = = = = =
> CREATE Or Replace FUNCTION fx_order_by ( )
> RETURNS table( last_name text, first_name )
> AS $eofx$
> DECLARE
> --
> BEGIN
> Return Query
> select
> lname, fname
> from
> my_table
> order by
> lname ASC
Please consider this plpgsql function:
= = = = = = = = = =
CREATE Or Replace FUNCTION fx_order_by ( )
RETURNS table( last_name text, first_name )
AS $eofx$
DECLARE
--
BEGIN
Return Query
select
lname, fname
from
my_table
order by
lname ASC
;
END;
$eofx$ LANGUAGE plpgsql;
= =
salah jubeh writes:
> There is something strange with the views definitions, I have "order by"
> statement amended to the views where the view is ordered by all the columns
> in
> the view. Is this a common behavior?
> Ps: I am using postgresql 8.3
If the views use SELECT DISTINCT, PG version
Hello,
There is something strange with the views definitions, I have "order by"
statement amended to the views where the view is ordered by all the columns in
the view. Is this a common behavior?
Ps: I am using postgresql 8.3
Regards
On Tue, Feb 9, 2010 at 11:21, Fredric Fredricson
wrote:
> Scott Marlowe wrote:
>
> On Tue, Feb 9, 2010 at 1:42 AM, Fredric Fredricson
> wrote:
>
>
> I use locale en_US.UTF-8 but why this should affect how leading characters
> in strings are ignored is beyond me.
>
>
> P.s. this page may shed some
Scott Marlowe wrote:
On Tue, Feb 9, 2010 at 1:42 AM, Fredric Fredricson
wrote:
I use locale en_US.UTF-8 but why this should affect how leading characters
in strings are ignored is beyond me.
P.s. this page may shed some light on the subject:
http://en.wikipedia.org/wiki/Collation
OK,
On Tue, Feb 9, 2010 at 1:42 AM, Fredric Fredricson
wrote:
> I use locale en_US.UTF-8 but why this should affect how leading characters
> in strings are ignored is beyond me.
P.s. this page may shed some light on the subject:
http://en.wikipedia.org/wiki/Collation
--
Sent via pgsql-general mail
On Tue, Feb 9, 2010 at 1:42 AM, Fredric Fredricson
wrote:
> Justin Graf wrote:
>
> On 2/8/2010 7:09 PM, Fredric Fredricson wrote:
>
> Hi!
> New to the list with a question that I cannot find the answer to in the
> manual or on the internet but I suspect is trivial. If somebody could point
> me in
Justin Graf wrote:
On 2/8/2010 7:09 PM, Fredric Fredricson wrote:
Hi!
New to the list with a question that I cannot find the answer to in
the manual or on the internet but I suspect is trivial. If somebody
could point me in the correct direction I would be greatful.
This is what I do (conden
On 2/8/2010 7:09 PM, Fredric Fredricson wrote:
> Hi!
> New to the list with a question that I cannot find the answer to in
> the manual or on the internet but I suspect is trivial. If somebody
> could point me in the correct direction I would be greatful.
>
> This is what I do (condensed, of cour
Hi!
New to the list with a question that I cannot find the answer to in the
manual or on the internet but I suspect is trivial. If somebody could
point me in the correct direction I would be greatful.
This is what I do (condensed, of course):
# create table tmp ( x text ) ;
CREATE TABLE
# inse
On Tue, Sep 08, 2009 at 06:29:28AM -0700, BlackMage wrote:
>
> I have a question about ordering by date. I have a table with two fields and
> some date
>
> Name(character varying) | Event_Date(timestamp with timezone)
> A |2009-09-10 5:30:00
> B
On Tue, Sep 08, 2009 at 06:29:28AM -0700, BlackMage wrote:
> I want to order by date and then by name, so I want the result A,B,C,D. The
> problem is when I do a 'SELECT * FROM table_name ORDER BY Event_Date, DESC',
> it includes the actual time (HH:MM:SS) so the order comes out B,A,D,C.
>
> So wh
On Tuesday 08 September 2009 6:29:28 am BlackMage wrote:
> I have a question about ordering by date. I have a table with two fields
> and some date
>
> Name(character varying) | Event_Date(timestamp with timezone)
> A |2009-09-10 5:30:00
> B
In response to BlackMage :
>
> I have a question about ordering by date. I have a table with two fields and
> some date
>
> Name(character varying) | Event_Date(timestamp with timezone)
> A |2009-09-10 5:30:00
> B |2009-0
In response to BlackMage :
>
> I have a question about ordering by date. I have a table with two fields and
> some date
>
> Name(character varying) | Event_Date(timestamp with timezone)
> A |2009-09-10 5:30:00
> B |2009-
I have a question about ordering by date. I have a table with two fields and
some date
Name(character varying) | Event_Date(timestamp with timezone)
A |2009-09-10 5:30:00
B |2009-09-10- 00:00:00
C
In response to Anton Marchenkov :
> Hi!
>
> I'm trying to use the order by parameter inside a function, but it is
> ignored. Any ideas why? And how can I sort by external parameters inside
> pgsql function?
You an use dynamic SQL with EXECUTE, for instance:
create or replace function my_order
On Tuesday 2. June 2009, Anton Marchenkov wrote:
>CREATE OR REPLACE FUNCTION "public"."test_order_by" (sort_key varchar)
>RETURNS SETOF "customers"."customers_with_mark_deleted" AS
>$body$
>DECLARE
> rec RECORD;
>BEGIN
> FOR rec IN SELECT * FROM customers.customers_with_mark_deleted c
>
On Tuesday 2. June 2009, Anton Marchenkov wrote:
>Hi!
>
>I'm trying to use the order by parameter inside a function, but it is
>ignored. Any ideas why? And how can I sort by external parameters
> inside pgsql function?
What's the problem with
SELECT * FROM foo(myvar) ORDER BY sort_key ASC
?
If
Hi!
I'm trying to use the order by parameter inside a function, but it is
ignored. Any ideas why? And how can I sort by external parameters inside
pgsql function?
CREATE OR REPLACE FUNCTION "public"."test_order_by" (sort_key varchar)
RETURNS SETOF "customers"."customers_with_mark_deleted" AS
Il 18-03-2009 21:32, Bryan Herger ha scritto:
All,
I am porting a database from MS SQL Server to Postgres. One of the
tables contains a list of names, which I would like to list
alphabetically. I noticed in the “O” names the following difference:
MSSQL:
O’Daniel
O’Neill
Oliveira
Oliver
While P
Alvaro Herrera writes:
> Bryan Herger wrote:
>> I am porting a database from MS SQL Server to Postgres. One of the
>> tables contains a list of names, which I would like to list
>> alphabetically. I noticed in the "O" names the following difference:
> Note to whoever answers: this is a frequent
Bryan Herger wrote:
> I am porting a database from MS SQL Server to Postgres. One of the
> tables contains a list of names, which I would like to list
> alphabetically. I noticed in the "O" names the following difference:
Note to whoever answers: this is a frequent question, so consider adding
All,
I am porting a database from MS SQL Server to Postgres. One of the
tables contains a list of names, which I would like to list
alphabetically. I noticed in the "O" names the following difference:
MSSQL:
O'Daniel
O'Neill
Oliveira
Oliver
While PGSQL sorts as if the apostrophe w
Kevin Kempter wrote on 26.11.2008 20:46:
Hi All;
I'm selecting 3 columns. I want to order the results ascending by col1 and
col2 and then descending by col3
Whats the syntax for this?
ORDER BY col1 ASC, col2 ASC, col3 DESC
this is documented in the manual
--
Sent via pgsql-general maili
Kevin Kempter <[EMAIL PROTECTED]> schrieb:
> Hi All;
>
> I'm selecting 3 columns. I want to order the results ascending by col1 and
> col2 and then descending by col3
>
> Whats the syntax for this?
order by col1 asc, col2 asc, col3 desc;
Andreas
--
Really, I'm not out to destroy Microsoft.
On Wed, Nov 26, 2008 at 7:46 PM, Kevin Kempter
<[EMAIL PROTECTED]>wrote:
> Hi All;
>
> I'm selecting 3 columns. I want to order the results ascending by col1 and
> col2 and then descending by col3
>
> Whats the syntax for this?
>
select * from foo order by a,b asc, c desc;
?
--
GJ
Hi All;
I'm selecting 3 columns. I want to order the results ascending by col1 and
col2 and then descending by col3
Whats the syntax for this?
Thanks in advance
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.
mike stanton napisal 24.10.2008 21:03:
Hello everyone.
We have a simple problem...that we have keys that include blanks and
various other commonly used characters like ",", ";" and "-". For some
reason, the select we have, nothing complicated, ignores these "special"
characters and happily so
Hello everyone.
We have a simple problem...that we have keys that include blanks and various
other commonly used characters like ",", ";" and "-". For some reason, the
select we have, nothing complicated, ignores these "special" characters and
happily sorts by the A-z range. How do we sort by t
"Scott Marlowe" <[EMAIL PROTECTED]> writes:
> But is there a way to force a use of varchar_pattern_ops on a column
> used in an order by?
ORDER BY x USING ~<~("asc" direction)
ORDER BY x USING ~>~("desc" direction)
(assuming those are the right names of the operators, I'm too lazy
to chec
OK, so I know about varchar_pattern_ops for things like matching with
like in a non C locale.
But is there a way to force a use of varchar_pattern_ops on a column
used in an order by?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http
Kevin Reynolds a écrit :
Does postgresql have something similar to the ORDER BY FIELD feature
found in MySQL?
Something like ORDER BY FIELD(ID, 10, 2, 56, 40);
It is listed here: http://dev.mysql.com/doc/refman/5.0/en/sorting-rows.html
I am using the Sphinx Search program, www.sphinxsearch
Does postgresql have something similar to the ORDER BY FIELD feature found in
MySQL?
Something like ORDER BY FIELD(ID, 10, 2, 56, 40);
It is listed here: http://dev.mysql.com/doc/refman/5.0/en/sorting-rows.html
I am using the Sphinx Search program, www.sphinxsearch.com, and it r
phazon <[EMAIL PROTECTED]> writes:
> I've "**" before the name in order to display them at first, but the 2
> chars "**" are ignored in the order by.
What's your lc_collate setting? In locales other than "C" this is not
at all unexpected.
regards, tom lane
--
Hi,
I've to order by q query by a column.
SELECT a.id, a.nom
FROM table a
ORDER BY a.nom;
And i've something strange. It's order like that:
5 |
8 | aaab
15 | ** aaac
6 | aaad
I've "**" before the name in order to display them at first, but the 2
chars "**" are ignored in the order b
Stefan Schwarzer wrote:
Hi there,
if I order a given year in DESCending ORDER, so that the highest
values (of a given variable) for the countries are displayed at the
top of the list, then actually the NULL values appear as first. Only
below, I find the values ordered correctly.
Is there an
Stefan Schwarzer ha scritto:
From 8.3 beta release notes:
- ORDER BY ... NULLS FIRST/LAST
I think this is what you want right?
Yes, indeed. Sounds great. unfortunately I am on 8.1. And wouldn't
really want to migrate to 8.3 and beta for the moment
order by 1 ?
---
From 8.3 beta release notes:
- ORDER BY ... NULLS FIRST/LAST
I think this is what you want right?
Yes, indeed. Sounds great. unfortunately I am on 8.1. And
wouldn't really want to migrate to 8.3 and beta for the moment
Thanks anyway!
Stef
---(end of broadca
Richard Huxton escribió:
Stefan Schwarzer wrote:
Hi there,
if I order a given year in DESCending ORDER, so that the highest
values (of a given variable) for the countries are displayed at the
top of the list, then actually the NULL values appear as first.
Only below, I find the values ordered
Stefan Schwarzer wrote:
Hi there,
if I order a given year in DESCending ORDER, so that the highest
values (of a given variable) for the countries are displayed at the
top of the list, then actually the NULL values appear as first. Only
below, I find the values ordered correctly.
Is there any
Hi there,
if I order a given year in DESCending ORDER, so that the highest
values (of a given variable) for the countries are displayed at
the top of the list, then actually the NULL values appear as
first. Only below, I find the values ordered correctly.
Is there any way to
a) make the
Stefan Schwarzer wrote:
Hi there,
if I order a given year in DESCending ORDER, so that the highest values
(of a given variable) for the countries are displayed at the top of the
list, then actually the NULL values appear as first. Only below, I find
the values ordered correctly.
Is there an
Hi there,
if I order a given year in DESCending ORDER, so that the highest
values (of a given variable) for the countries are displayed at the
top of the list, then actually the NULL values appear as first. Only
below, I find the values ordered correctly.
Is there any way to
a) make t
Does this change...
---
Allow index scans to use an intermediate in-memory bitmap (Tom)
In previous releases, only a single index could be used to do
lookups on a table. With this feature, if a query has WHERE tab.col1 =
4 and tab.col2 = 9, and there is no multicolumn index on col1 and
col2, b
On Jun 21, 2007, at 8:08 PM, brian wrote:
Michael Glaesemann wrote:
On Jun 21, 2007, at 17:35 , brian wrote:
I have a lookup table with a bunch of disciplines:
To answer your ordering question first:
SELECT id, name
FROM discipline
ORDER BY name = 'other'
, name;
id |name
+--
Michael Glaesemann wrote:
On Jun 21, 2007, at 17:35 , brian wrote:
I have a lookup table with a bunch of disciplines:
To answer your ordering question first:
SELECT id, name
FROM discipline
ORDER BY name = 'other'
, name;
id |name
+-
8 | community
4 |
Josh Tolley wrote:
It seems to me you could replace it all with one query, something like
this:
SELECT discipline, COUNT(1) FROM showcase WHERE EXISTS (SELECT * FROM
showcase_item WHERE showcase_id = showcase.id LIMIT 1) GROUP BY
discipline ORDER BY (discipline != 'other'), discipline;
disci
On Jun 21, 2007, at 17:35 , brian wrote:
I have a lookup table with a bunch of disciplines:
To answer your ordering question first:
SELECT id, name
FROM discipline
ORDER BY name = 'other'
, name;
id |name
+-
8 | community
4 | dance
5 | film and telev
On 6/21/07, brian <[EMAIL PROTECTED]> wrote:
I have a lookup table with a bunch of disciplines:
# SELECT id, name FROM discipline;
id |name
+-
1 | writing
2 | visual arts
3 | music
4 | dance
5 | film and television
6 | theatre
7 | media arts
I have a lookup table with a bunch of disciplines:
# SELECT id, name FROM discipline;
id |name
+-
1 | writing
2 | visual arts
3 | music
4 | dance
5 | film and television
6 | theatre
7 | media arts
8 | community
9 | fine craft
10 | other
(10 rows
Using Postgresql 8.2.3
The following query functions correctly:
select lastname as name from person where lastname ='Smith'
union
select firstname as name from person where firstname = 'John'
order by name;
---
The followi
On Thu, Apr 05, 2007 at 06:46:27PM -0300, Carlos H. Reimer wrote:
> Hi,
>
> I was trying to find the docs about the collating sequence standards but
> could not find.
>
> Would like to know for example which characters are ignored by the "order
> by" in some of the collating types.
There are no
standards?
Thanks in advance!
> -Mensagem original-
> De: Stephan Szabo [mailto:[EMAIL PROTECTED]
> Enviada em: quarta-feira, 28 de março de 2007 19:23
> Para: Carlos H. Reimer
> Cc: pgsql-general@postgresql.org
> Assunto: Re: [GENERAL] Order by behaviour
>
>
> On Wed
On Thu, 29 Mar 2007, Carlos H. Reimer wrote:
> Humm, ok, it is clear now.
>
> And is there a way to change something in this behaviour, like not ignore
> spaces and some type of symbols?
Well, right now it's generally determined by your OS's definition of the
locale you've chosen. You might be ab
Carlos H. Reimer wrote:
Humm, ok, it is clear now.
And is there a way to change something in this behaviour, like not ignore
spaces and some type of symbols?
A configuration file or a patch?
Pick a different locale?
--
Richard Huxton
Archonet Ltd
---(end of broad
han Szabo
> Enviada em: quarta-feira, 28 de março de 2007 19:23
> Para: Carlos H. Reimer
> Cc: pgsql-general@postgresql.org
> Assunto: Re: [GENERAL] Order by behaviour
>
>
> On Wed, 28 Mar 2007, Carlos H. Reimer wrote:
>
> > Hi,
> >
> > We have a PostgreSQL 8.0
On Wed, 28 Mar 2007, Carlos H. Reimer wrote:
> Hi,
>
> We have a PostgreSQL 8.0.6 cluster configured with lc_collate=pt_BR.UTF-8
> and when we run the following SELECT:
> SELECT substr(nomerazao,1,4),
> ascii(substr(nomerazao,1,1)),
> ascii(substr(nomerazao,2,1))
> from spunico.unico order by nome
Carlos H. Reimer wrote:
> Are not the lines out of order
No.
> or is it a normal behaviour for a
> server with lc_collate=pt_BR.UTF-8?
Yes.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
---(end of broadcast)---
TIP 2: Don't 'kill
On 28/03/2007 22:52, Carlos H. Reimer wrote:
SELECT substr(nomerazao,1,4),
ascii(substr(nomerazao,1,1)),
ascii(substr(nomerazao,2,1))
from spunico.unico order by nomerazao;
You need to add aliases to the returned column by which you'd like to
order the result: your query is ordering the rows
Hi,
We have a PostgreSQL 8.0.6 cluster configured with lc_collate=pt_BR.UTF-8
and when we run the following SELECT:
SELECT substr(nomerazao,1,4),
ascii(substr(nomerazao,1,1)),
ascii(substr(nomerazao,2,1))
from spunico.unico order by nomerazao;
is returning:
substr | ascii | ascii
+-
Alvaro Herrera wrote:
> Bruce Momjian wrote:
>
> > Here the column result is an expression, and you reference that.
> > Updated wording:
> >
> > test=> select * from test union select * from test order by x is null;
> > ERROR: A UNION/INTERSECT/EXCEPT ORDER BY cannot use expressions or
> > f
Bruce Momjian <[EMAIL PROTECTED]> writes:
> Where are the style guidelines? I looked before but can't find them.
http://developer.postgresql.org/pgdocs/postgres/error-style-guide.html
regards, tom lane
---(end of broadcast)
Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Updated wording:
>
> > test=> select * from test union select * from test order by x is null;
> > ERROR: A UNION/INTERSECT/EXCEPT ORDER BY cannot use expressions or
> > functions, only result column names
>
> This does not meet t
Bruce Momjian wrote:
> Here the column result is an expression, and you reference that.
> Updated wording:
>
> test=> select * from test union select * from test order by x is null;
> ERROR: A UNION/INTERSECT/EXCEPT ORDER BY cannot use expressions or
> functions, only result column names
Th
Bruce Momjian <[EMAIL PROTECTED]> writes:
> Updated wording:
> test=> select * from test union select * from test order by x is null;
> ERROR: A UNION/INTERSECT/EXCEPT ORDER BY cannot use expressions or
> functions, only result column names
This does not meet the style guidelines.
Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Well, it can be an expression _if_ the expression _matches_ an existing
> > UNION column.
>
> You're mistaken. It has to be *an output column name*. Not anything else.
Yea, I was thinking of this:
SELECT *, state IS NULL A
Bruce Momjian <[EMAIL PROTECTED]> writes:
> Well, it can be an expression _if_ the expression _matches_ an existing
> UNION column.
You're mistaken. It has to be *an output column name*. Not anything else.
regards, tom lane
---(end of broadcast)-
Mike Benoit wrote:
> That helps some, but I'm sure it could be even more clear.
>
> The main issue is that you can't order by an expression computed by
> unions, correct? So couldn't the error message by something like:
>
> "ORDER BY on a UNION/INTERSECT/EXCEPT result must match existing result
On Thu, 2007-01-11 at 13:44 -0500, Bruce Momjian wrote:
> Tom Lane wrote:
> > Michael Glaesemann <[EMAIL PROTECTED]> writes:
> > > On Dec 26, 2006, at 18:39 , Mike Benoit wrote:
> > >> ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of
> > >> the result columns
> >
> > > Even th
Tom Lane wrote:
> Michael Glaesemann <[EMAIL PROTECTED]> writes:
> > On Dec 26, 2006, at 18:39 , Mike Benoit wrote:
> >> ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of
> >> the result columns
>
> > Even though state is a column in both tables, the order by is using
> > an
Michael Glaesemann <[EMAIL PROTECTED]> writes:
> On Dec 26, 2006, at 18:39 , Mike Benoit wrote:
>> ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of
>> the result columns
> Even though state is a column in both tables, the order by is using
> an expression, rather than a colu
1 - 100 of 169 matches
Mail list logo
