select issue with order v8.1

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