Обсуждение: select issue with order v8.1
Hello, I have an application that is doing something stupid in that it is tacking on its own order clause at the end of the statement I am providing. For example, I am putting this statement in: select ev_id,type,ev_time,category,error,ev_text,userid,ex_long,client_ex_long,ex_text from clients_event_log limit 100 It is tacking on ORDER BY ev_id. The problem is that isn't per the syntax. Can anyone think of anything clever to get around this stupid application doing what it is doing? For example, anything I can do beside limit? I appreciate the thoughts!
Terry <td3201@gmail.com> writes:
> I have an application that is doing something stupid in that it is
> tacking on its own order clause at the end of the statement I am
> providing.
> For example, I am putting this statement in:
> select ev_id,type,ev_time,category,error,ev_text,userid,ex_long,client_ex_long,ex_text
> from clients_event_log limit 100
> It is tacking on ORDER BY ev_id. The problem is that isn't per the
> syntax. Can anyone think of anything clever to get around this stupid
> application doing what it is doing? For example, anything I can do
> beside limit?
Hrm, fix the application?
You might be able to make a go out of something along the lines of
select ev_id,... from (select * from clients_event_log limit 100) as ss;
which would admit an ORDER BY on the end.
BTW, in most cases it doesn't make any sense to have a LIMIT without an
ORDER BY inside the subselect --- unless you really don't care which 100
rows you get.
regards, tom lane
On Wed, Feb 24, 2010 at 8:50 PM, Terry <td3201@gmail.com> wrote: > Hello, > > I have an application that is doing something stupid in that it is > tacking on its own order clause at the end of the statement I am > providing. > > For example, I am putting this statement in: > select ev_id,type,ev_time,category,error,ev_text,userid,ex_long,client_ex_long,ex_text > from clients_event_log limit 100 > > It is tacking on ORDER BY ev_id. The problem is that isn't per the > syntax. Can anyone think of anything clever to get around this stupid > application doing what it is doing? For example, anything I can do > beside limit? > > I appreciate the thoughts! You could either wrap it in a subselect or make a view. select * from (select ev_id,type,ev_time,category,error,ev_text,userid,ex_long,client_ex_long,ex_text from clients_event_log limit 100) as a and an order by tacked on the end of that is ok.
On Wed, Feb 24, 2010 at 10:30 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > On Wed, Feb 24, 2010 at 8:50 PM, Terry <td3201@gmail.com> wrote: >> Hello, >> >> I have an application that is doing something stupid in that it is >> tacking on its own order clause at the end of the statement I am >> providing. >> >> For example, I am putting this statement in: >> select ev_id,type,ev_time,category,error,ev_text,userid,ex_long,client_ex_long,ex_text >> from clients_event_log limit 100 >> >> It is tacking on ORDER BY ev_id. The problem is that isn't per the >> syntax. Can anyone think of anything clever to get around this stupid >> application doing what it is doing? For example, anything I can do >> beside limit? >> >> I appreciate the thoughts! > > You could either wrap it in a subselect or make a view. > > select * from (select > ev_id,type,ev_time,category,error,ev_text,userid,ex_long,client_ex_long,ex_text > from clients_event_log limit 100) as a > > and an order by tacked on the end of that is ok. > This and the previous poster's advice both worked. Thank you. However, I am having another issue where the application is not viewing a 'serial' data type as a number. Clearly none of this is a postgres issue. Stupid programming.
On Thu, Feb 25, 2010 at 9:42 AM, Terry <td3201@gmail.com> wrote: > On Wed, Feb 24, 2010 at 10:30 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: >> On Wed, Feb 24, 2010 at 8:50 PM, Terry <td3201@gmail.com> wrote: >>> Hello, >>> >>> I have an application that is doing something stupid in that it is >>> tacking on its own order clause at the end of the statement I am >>> providing. >>> >>> For example, I am putting this statement in: >>> select ev_id,type,ev_time,category,error,ev_text,userid,ex_long,client_ex_long,ex_text >>> from clients_event_log limit 100 >>> >>> It is tacking on ORDER BY ev_id. The problem is that isn't per the >>> syntax. Can anyone think of anything clever to get around this stupid >>> application doing what it is doing? For example, anything I can do >>> beside limit? >>> >>> I appreciate the thoughts! >> >> You could either wrap it in a subselect or make a view. >> >> select * from (select >> ev_id,type,ev_time,category,error,ev_text,userid,ex_long,client_ex_long,ex_text >> from clients_event_log limit 100) as a >> >> and an order by tacked on the end of that is ok. >> > > This and the previous poster's advice both worked. Thank you. > However, I am having another issue where the application is not > viewing a 'serial' data type as a number. Clearly none of this is a > postgres issue. Stupid programming. > Based on my above comment. Is there a way to create a view or something that presents the serial column as an integer? In the end, that's what it is but on the insert side it is incrementing the number for the underlying app. I'm not a SQL guy but that's my understanding anyways. I could even perhaps do a table copy process and simply make the destination type an integer rather than a serial? Just thinking out loud. Anyone have an idea here?
On Fri, Feb 26, 2010 at 1:46 PM, Terry <td3201@gmail.com> wrote: > On Thu, Feb 25, 2010 at 9:42 AM, Terry <td3201@gmail.com> wrote: >> On Wed, Feb 24, 2010 at 10:30 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: >>> On Wed, Feb 24, 2010 at 8:50 PM, Terry <td3201@gmail.com> wrote: >>>> Hello, >>>> >>>> I have an application that is doing something stupid in that it is >>>> tacking on its own order clause at the end of the statement I am >>>> providing. >>>> >>>> For example, I am putting this statement in: >>>> select ev_id,type,ev_time,category,error,ev_text,userid,ex_long,client_ex_long,ex_text >>>> from clients_event_log limit 100 >>>> >>>> It is tacking on ORDER BY ev_id. The problem is that isn't per the >>>> syntax. Can anyone think of anything clever to get around this stupid >>>> application doing what it is doing? For example, anything I can do >>>> beside limit? >>>> >>>> I appreciate the thoughts! >>> >>> You could either wrap it in a subselect or make a view. >>> >>> select * from (select >>> ev_id,type,ev_time,category,error,ev_text,userid,ex_long,client_ex_long,ex_text >>> from clients_event_log limit 100) as a >>> >>> and an order by tacked on the end of that is ok. >>> >> >> This and the previous poster's advice both worked. Thank you. >> However, I am having another issue where the application is not >> viewing a 'serial' data type as a number. Clearly none of this is a >> postgres issue. Stupid programming. >> > > Based on my above comment. Is there a way to create a view or > something that presents the serial column as an integer? In the end, > that's what it is but on the insert side it is incrementing the number > for the underlying app. I'm not a SQL guy but that's my understanding > anyways. I could even perhaps do a table copy process and simply make > the destination type an integer rather than a serial? Just thinking > out loud. Anyone have an idea here? You could alter it to an int, then create a sequence with the same start as the old sequence and assign it as default for the int.
On Fri, Feb 26, 2010 at 3:16 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Fri, Feb 26, 2010 at 1:46 PM, Terry <td3201@gmail.com> wrote:
>> On Thu, Feb 25, 2010 at 9:42 AM, Terry <td3201@gmail.com> wrote:
>>> On Wed, Feb 24, 2010 at 10:30 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>>>> On Wed, Feb 24, 2010 at 8:50 PM, Terry <td3201@gmail.com> wrote:
>>>>> Hello,
>>>>>
>>>>> I have an application that is doing something stupid in that it is
>>>>> tacking on its own order clause at the end of the statement I am
>>>>> providing.
>>>>>
>>>>> For example, I am putting this statement in:
>>>>> select ev_id,type,ev_time,category,error,ev_text,userid,ex_long,client_ex_long,ex_text
>>>>> from clients_event_log limit 100
>>>>>
>>>>> It is tacking on ORDER BY ev_id. The problem is that isn't per the
>>>>> syntax. Can anyone think of anything clever to get around this stupid
>>>>> application doing what it is doing? For example, anything I can do
>>>>> beside limit?
>>>>>
>>>>> I appreciate the thoughts!
>>>>
>>>> You could either wrap it in a subselect or make a view.
>>>>
>>>> select * from (select
>>>> ev_id,type,ev_time,category,error,ev_text,userid,ex_long,client_ex_long,ex_text
>>>> from clients_event_log limit 100) as a
>>>>
>>>> and an order by tacked on the end of that is ok.
>>>>
>>>
>>> This and the previous poster's advice both worked. Thank you.
>>> However, I am having another issue where the application is not
>>> viewing a 'serial' data type as a number. Clearly none of this is a
>>> postgres issue. Stupid programming.
>>>
>>
>> Based on my above comment. Is there a way to create a view or
>> something that presents the serial column as an integer? In the end,
>> that's what it is but on the insert side it is incrementing the number
>> for the underlying app. I'm not a SQL guy but that's my understanding
>> anyways. I could even perhaps do a table copy process and simply make
>> the destination type an integer rather than a serial? Just thinking
>> out loud. Anyone have an idea here?
>
> You could alter it to an int, then create a sequence with the same
> start as the old sequence and assign it as default for the int.
>
I am somewhat confused. My app is detecting it as a serial data type
but describing the table shows that its an integer. What am I
missing?
dssystem=# \d clients_event_log
Table "public.clients_event_log"
Column | Type |
Modifiers
----------------+-------------------------+-------------------------------------------------------------------
ev_id | integer | not null default
nextval('clients_event_log_ev_id_seq'::regclass)
Terry <td3201@gmail.com> writes:
> I am somewhat confused. My app is detecting it as a serial data type
> but describing the table shows that its an integer. What am I
> missing?
> dssystem=# \d clients_event_log
> Table "public.clients_event_log"
> Column | Type |
> Modifiers
> ----------------+-------------------------+-------------------------------------------------------------------
> ev_id | integer | not null default
> nextval('clients_event_log_ev_id_seq'::regclass)
Nothing. "Serial" is nothing but a shorthand for an integer column
with a default like that.
regards, tom lane
On Fri, Feb 26, 2010 at 3:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Terry <td3201@gmail.com> writes:
>> I am somewhat confused. My app is detecting it as a serial data type
>> but describing the table shows that its an integer. What am I
>> missing?
>
>> dssystem=# \d clients_event_log
>> Table "public.clients_event_log"
>> Column | Type |
>> Modifiers
>> ----------------+-------------------------+-------------------------------------------------------------------
>> ev_id | integer | not null default
>> nextval('clients_event_log_ev_id_seq'::regclass)
>
> Nothing. "Serial" is nothing but a shorthand for an integer column
> with a default like that.
And a dependency for the sequence on the column. I wonder if Terry's
application is sorting through dep info to make a bad decision...
On Fri, Feb 26, 2010 at 4:52 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Fri, Feb 26, 2010 at 3:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Terry <td3201@gmail.com> writes:
>>> I am somewhat confused. My app is detecting it as a serial data type
>>> but describing the table shows that its an integer. What am I
>>> missing?
>>
>>> dssystem=# \d clients_event_log
>>> Table "public.clients_event_log"
>>> Column | Type |
>>> Modifiers
>>> ----------------+-------------------------+-------------------------------------------------------------------
>>> ev_id | integer | not null default
>>> nextval('clients_event_log_ev_id_seq'::regclass)
>>
>> Nothing. "Serial" is nothing but a shorthand for an integer column
>> with a default like that.
>
> And a dependency for the sequence on the column. I wonder if Terry's
> application is sorting through dep info to make a bad decision...
>
I am back to this particular problem. I found a workaround, that is
very poor, which is to create a new table to present the column as an
integer type rather than serial. What do you mean by a dependency?
What is dependent on what? Are there any functions that would create
a new column in the output as an integer type based on the value in
another column?
On Thursday 04 March 2010 2:59:45 pm Terry wrote:
> On Fri, Feb 26, 2010 at 4:52 PM, Scott Marlowe <scott.marlowe@gmail.com>
wrote:
> > On Fri, Feb 26, 2010 at 3:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> Terry <td3201@gmail.com> writes:
> >>> I am somewhat confused. My app is detecting it as a serial data type
> >>> but describing the table shows that its an integer. What am I
> >>> missing?
> >>>
> >>> dssystem=# \d clients_event_log
> >>> Table "public.clients_event_log"
> >>> Column | Type |
> >>> Modifiers
> >>> ----------------+-------------------------+----------------------------
> >>>--------------------------------------- ev_id | integer
> >>> | not null default
> >>> nextval('clients_event_log_ev_id_seq'::regclass)
> >>
> >> Nothing. "Serial" is nothing but a shorthand for an integer column
> >> with a default like that.
> >
> > And a dependency for the sequence on the column. I wonder if Terry's
> > application is sorting through dep info to make a bad decision...
>
> I am back to this particular problem. I found a workaround, that is
> very poor, which is to create a new table to present the column as an
> integer type rather than serial. What do you mean by a dependency?
> What is dependent on what? Are there any functions that would create
> a new column in the output as an integer type based on the value in
> another column?
The dependency is the sequence "clients_event_log_ev_id_seq". When you use the
serial type it creates an integer column with a dependency on the sequence.
--
Adrian Klaver
adrian.klaver@gmail.com