Re: [SQL] can someone explain confusing array indexing nomenclature
Στις Τετάρτη 14 Φεβρουάριος 2007 21:31, ο/η chrisj έγραψε:
> given the following table:
>
> protocal2=> select * from sal_emp ;
> name | pay_by_quarter | schedule
> ---+---+---
> Bill | {1,1,1,1} |
> {{meeting,lunch},{training,presentation}}
> Carol | {2,25000,25000,25000} |
> {{breakfast,consulting},{meeting,lunch}}
> (2 rows)
>
> why do the following two queries yield different results??
>
> protocal2=> SELECT schedule[1][2] FROM sal_emp WHERE name = 'Bill';
> schedule
> --
> lunch
> (1 row)
>
> protocal2=> SELECT schedule[1:1][2] FROM sal_emp WHERE name = 'Bill';
> schedule
> ---
> {{meeting,lunch}}
> (1 row)
The [n:m] notation denotes a slice of the array (not element).
So schedule[1][2] is the Array element on 2nd col of 1st row,
while schedule[1:1][2] could mean
the second row of the subarray schedule[1:1][1:2].
So these two are foundamentally different things.
In my 7.4 even if you gave
SELECT schedule[1:1][888] FROM sal_emp WHERE name = 'Bill';
you would still get {{meeting,lunch}} as a result.
(Right or wrong is another story).
Anyway the first time you query for a "text",
the second time you query for a "text[]", so you should expect
different results.
--
Achilleas Mantzios
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[SQL] "for SELECT DISTINCT, ORDER BY expressions must appear in select list" - is that the standart or a limitation of postgresql?
CREATE TABLE test
(
id int4 NOT NULL DEFAULT nextval('teste_id_seq'::regclass),
name varchar,
number int4
)
WITHOUT OIDS;
The data:
1;"walter";1
2;"walter";1
3;"walter";1
4;"walter";1
5;"walter";2
6;"walter";3
7;"rodrigo";1
8;"rodrigo";2
9;"rodrigo";3
The query:
SELECT distinct name from test order by number
(well, I think that que query doesn't make any sense, but raises the error :) )
The error: ERROR: for SELECT DISTINCT, ORDER BY expressions must
appear in select list is due to a standart implementarion or a design
decision of postgres?
The comentary on parse_clause.c looks like the second option. I'm right?
[]'s
- Walter
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] "for SELECT DISTINCT, ORDER BY expressions must appear in select list" - is that the standart or a limitation of postgresql?
Walter Cruz wrote: SELECT distinct name from test order by number (well, I think that que query doesn't make any sense, but raises the error :) ) The error: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list is due to a standart implementarion or a design decision of postgres? I think ORDER BY is defined to take place after DISTINCT, which means there is no meaningful "number" for it to order by. You could arbitrarily choose the first number encountered, but I can't see what sense it would make to order by them. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] "for SELECT DISTINCT, ORDER BY expressions must appear in select list" - is that the standart or a limitation of postgresql?
On Feb 15, 2007, at 22:35 , Richard Huxton wrote: Walter Cruz wrote: SELECT distinct name from test order by number (well, I think that que query doesn't make any sense, but raises the error :) ) The error: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list is due to a standart implementarion or a design decision of postgres? I think ORDER BY is defined to take place after DISTINCT, which means there is no meaningful "number" for it to order by. You could arbitrarily choose the first number encountered, but I can't see what sense it would make to order by them. I believe the reason is that DISTINCT depends on a sort to determine uniqueness (distinctness), so it's a implementation detail that if you're going to include an ORDER BY, you also need to include the same columns in the ORDER BY in the DISTINCT clause. Though I suspect Richard is right that ORDER BY takes place after DISTINCT. (My cursory attempt at parsing the SQL 2003 draft failed me.) On further thought, I bet SELECT DISTINCT name FROM test ORDER BY name, number fails with a different error, one directly supporting Richard's conclusion. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Retrieving 'Credit' when 'C'
Hello, Does anyone know how to make a Select that even having a Char(1) with the letter C the statement makes the rows appearing 'CREDIT' ? Like: On table: When Type 2007-01-01C 2007-01-02C 2007-01-03C On SQL result When Type 2007-01-01CREDIT 2007-01-02CREDIT 2007-01-03CREDIT Thanks in advande, -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/
Re: [SQL] Retrieving 'Credit' when 'C'
SELECT CASE WHEN TYPE = 'C' THEN 'CREDIT' END AS TYPE FROM mytable ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Retrieving 'Credit' when 'C'
Ezequias Rodrigues da Rocha escribió: > Hello, > > Does anyone know how to make a Select that even having a Char(1) with the > letter C the statement makes the rows appearing 'CREDIT' ? case when column = 'C' then 'CREDIT' end -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Retrieving 'Credit' when 'C'
Just a question, where to put it ? I didn't notice yet. Ezequias 2007/2/15, Alvaro Herrera <[EMAIL PROTECTED]>: Ezequias Rodrigues da Rocha escribió: > Hello, > > Does anyone know how to make a Select that even having a Char(1) with the > letter C the statement makes the rows appearing 'CREDIT' ? case when column = 'C' then 'CREDIT' end -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/
Re: [SQL] can someone explain confusing array indexing nomenclature
Thanks Achilleas,
I see what you are saying, but if we consider just the index "[2]" for a
moment,
it means something different depending upon the context (in one case it
means "2" and in the other case it means "1:2") and the context is
determined by the format of indexes on other dimensions.
I believe I understandbut incredibly confusing.
- chris
Achilleas Mantzios wrote:
>
> Στις Τετάρτη 14 Φεβρουάριος 2007 21:31, ο/η chrisj έγραψε:
>> given the following table:
>>
>> protocal2=> select * from sal_emp ;
>> name | pay_by_quarter | schedule
>> ---+---+---
>> Bill | {1,1,1,1} |
>> {{meeting,lunch},{training,presentation}}
>> Carol | {2,25000,25000,25000} |
>> {{breakfast,consulting},{meeting,lunch}}
>> (2 rows)
>>
>> why do the following two queries yield different results??
>>
>> protocal2=> SELECT schedule[1][2] FROM sal_emp WHERE name = 'Bill';
>> schedule
>> --
>> lunch
>> (1 row)
>>
>> protocal2=> SELECT schedule[1:1][2] FROM sal_emp WHERE name = 'Bill';
>> schedule
>> ---
>> {{meeting,lunch}}
>> (1 row)
>
> The [n:m] notation denotes a slice of the array (not element).
> So schedule[1][2] is the Array element on 2nd col of 1st row,
> while schedule[1:1][2] could mean
> the second row of the subarray schedule[1:1][1:2].
> So these two are foundamentally different things.
> In my 7.4 even if you gave
> SELECT schedule[1:1][888] FROM sal_emp WHERE name = 'Bill';
> you would still get {{meeting,lunch}} as a result.
> (Right or wrong is another story).
> Anyway the first time you query for a "text",
> the second time you query for a "text[]", so you should expect
> different results.
> --
> Achilleas Mantzios
>
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faq
>
>
--
View this message in context:
http://www.nabble.com/can-someone-explain-confusing-array-indexing-nomenclature-tf3229165.html#a8989242
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] "for SELECT DISTINCT, ORDER BY expressions must appear in select list" - is that the standart or a limitation of postgresql?
Michael Glaesemann <[EMAIL PROTECTED]> writes: > On Feb 15, 2007, at 22:35 , Richard Huxton wrote: >> Walter Cruz wrote: >>> The error: ERROR: for SELECT DISTINCT, ORDER BY expressions must >>> appear in select list is due to a standart implementarion or a design >>> decision of postgres? >> >> I think ORDER BY is defined to take place after DISTINCT, which >> means there is no meaningful "number" for it to order by. You could >> arbitrarily choose the first number encountered, but I can't see >> what sense it would make to order by them. > I believe the reason is that DISTINCT depends on a sort to determine > uniqueness (distinctness), so it's a implementation detail that if > you're going to include an ORDER BY, you also need to include the > same columns in the ORDER BY in the DISTINCT clause. No, there's actually a definitional reason for it. Consider SELECT DISTINCT x FROM tab ORDER BY y; For any particular x-value in the table there might be many different y values. Which one will you use to sort that x-value in the output? Back in SQL92 they avoided this problem by specifying that ORDER BY entries had to reference output columns. SQL99 has some messy verbiage that I think comes out at the same place as our restriction: A) If K(i) is not equivalent to a immediately contained in any in the SL of QS contained in QE, then: I) T shall not be a grouped table. II) QS shall not specify the DISTINCT or directly contain one or more s. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Retrieving 'Credit' when 'C'
SELECT when, CASE WHEN type = 'C' THEN 'Credit' END AS type FROMmytable; Assuming your column names are actually "when" and "type" you should just have to change "mytable" to the correct table name and run in psql or the SQL Window of pgAdmin or wherever you usually run your SQL queries to get what you want. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ezequias Rodrigues da Rocha Sent: Friday, 16 February 2007 03:45 To: Ezequias Rodrigues da Rocha; [email protected] Subject: Re: [SQL] Retrieving 'Credit' when 'C' Just a question, where to put it ? I didn't notice yet. ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments
Re: [SQL] "for SELECT DISTINCT, ORDER BY expressions must appear in select list" - is that the standart or a limitation of postgresql?
Thanks Tom, Thank all :) Maybe the commentary on parse_clase.c (beggining with "Now, DISTINCT list consists of all non-resjunk") needs to be updated - In the comment, looks likes this is a postgresql limitation. []'s - Walter On 2/15/07, Tom Lane <[EMAIL PROTECTED]> wrote: Michael Glaesemann <[EMAIL PROTECTED]> writes: > On Feb 15, 2007, at 22:35 , Richard Huxton wrote: >> Walter Cruz wrote: >>> The error: ERROR: for SELECT DISTINCT, ORDER BY expressions must >>> appear in select list is due to a standart implementarion or a design >>> decision of postgres? >> >> I think ORDER BY is defined to take place after DISTINCT, which >> means there is no meaningful "number" for it to order by. You could >> arbitrarily choose the first number encountered, but I can't see >> what sense it would make to order by them. > I believe the reason is that DISTINCT depends on a sort to determine > uniqueness (distinctness), so it's a implementation detail that if > you're going to include an ORDER BY, you also need to include the > same columns in the ORDER BY in the DISTINCT clause. No, there's actually a definitional reason for it. Consider SELECT DISTINCT x FROM tab ORDER BY y; For any particular x-value in the table there might be many different y values. Which one will you use to sort that x-value in the output? Back in SQL92 they avoided this problem by specifying that ORDER BY entries had to reference output columns. SQL99 has some messy verbiage that I think comes out at the same place as our restriction: A) If K(i) is not equivalent to a immediately contained in any in the SL of QS contained in QE, then: I) T shall not be a grouped table. II) QS shall not specify the DISTINCT or directly contain one or more s. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
