Thread: SELECT * FROM foo OFFSET -1 LIMIT 1

SELECT * FROM foo OFFSET -1 LIMIT 1

From
Markus Bertheau ☭
Date:
Hi,

is there a reason that

SELECT * FROM t1 OFFSET -1 LIMIT 1

does not return 0 rows? Accordingly

SELECT * FROM t1 OFFSET -1 LIMIT 2

should return 1 row, imo.

Markus

-- 
Markus Bertheau ☭ <twanger@bluetwanger.de>



pg_attribute.attlen -1 for varchar e pbchar fields

From
"jimmy.olsen"
Date:
Hi listers,   I'm facing a problem getting information from meta tables. The command:
SELECT attname, atttypid, attlen
FROM pg_attribute
where atttypid IN(1042, 1043)

The attlen column always returns -1 for bpchar and varchar columns. the
postgre version is 8.0.1. When I look the tables in pgAdmin the column
lengths are correct. Where can I find the correct column lengths??

Alessandro - Liga One Sistemas
alessandro@ligaone.com.br



Re: SELECT * FROM foo OFFSET -1 LIMIT 1

From
Martín Marqués
Date:
El Lun 27 Jun 2005 12:34, Markus Bertheau ☭ escribió:
> Hi,
>
> is there a reason that
>
> SELECT * FROM t1 OFFSET -1 LIMIT 1
>
> does not return 0 rows? Accordingly
>
> SELECT * FROM t1 OFFSET -1 LIMIT 2
>
> should return 1 row, imo.

From the docs:
The LIMIT clause consists of two independent sub-clauses:

LIMIT { count | ALL }
OFFSET start

count specifies the maximum number of rows to return, while start specifies
the number of rows to skip before starting to return rows. When both are
specified, start rows are skipped before starting to count the count rows to
be returned.

How do you skip -1 rows?

--
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
---------------------------------------------------------
Martín Marqués          |   Programador, DBA
Centro de Telemática    |     Administrador              Universidad Nacional                   del Litoral
---------------------------------------------------------


Re: SELECT * FROM foo OFFSET -1 LIMIT 1

From
Markus Bertheau
Date:
Dnia 27-06-2005, Mo o godzinie 14:17 -0300, Martín Marqués napisał(a):

> How do you skip -1 rows?

You redefine OFFSET to mean "where in the result set to start returning
rows" and give -1 the meaning that I described above.

Please don't ask me, where in the result set position -1 is.


I've stumbled over this because I needed a query which given an id a
result set like this:

id txt pos
23 foo 0
11 bar 1
34 baz 2
13 xop 3

returns the previous and next rows relative to the given id, as ordered
by the position). That would be 23 and 34 for 11, or NULL and 11 for 23.
I'm basically determining the position of the middle row and then
querying with offset pos-1 limit 1 and offset pos+1 limit 1.

Offset for negative numbers means 0, as it seems. I think there is a
sensible meaning for negative offset numbers and wondered, what
arguments led to negative offsets being processed as 0 offset.

Markus

--
Markus Bertheau <twanger@bluetwanger.de>

Re: SELECT * FROM foo OFFSET -1 LIMIT 1

From
Greg Stark
Date:
Markus Bertheau <twanger@bluetwanger.de> writes:

> Offset for negative numbers means 0, as it seems. I think there is a
> sensible meaning for negative offset numbers and wondered, what
> arguments led to negative offsets being processed as 0 offset.

Frankly I'm surprised it's not a syntax error.

-- 
greg



Re: pg_attribute.attlen -1 for varchar e pbchar fields

From
Michael Fuhr
Date:
On Mon, Jun 27, 2005 at 02:27:41PM -0300, jimmy.olsen wrote:
>
> SELECT attname, atttypid, attlen
> FROM pg_attribute
> where atttypid IN(1042, 1043)
> 
> The attlen column always returns -1 for bpchar and varchar columns. the
> postgre version is 8.0.1. When I look the tables in pgAdmin the column
> lengths are correct. Where can I find the correct column lengths??

See the atttypmod column or the format_type() function:

CREATE TABLE foo (   col_char_1      char(1),   col_char_5      char(5),   col_varchar_10  varchar(10),   col_text
 text
 
);

SELECT attname, attlen, atttypmod, format_type(atttypid, atttypmod)
FROM pg_attribute
WHERE attrelid = 'foo'::regclass AND attnum > 0;
   attname     | attlen | atttypmod |      format_type      
----------------+--------+-----------+-----------------------col_char_1     |     -1 |         5 |
character(1)col_char_5    |     -1 |         9 | character(5)col_varchar_10 |     -1 |        14 | character
varying(10)col_text      |     -1 |        -1 | text
 
(4 rows)

Notice that atttypmod is 4 greater than the declared length;
presumably the difference is due to the varlena header (the 32-bit
integer that stores the data length).  However, that's getting a
little close to internals, so you might want to stick with calling
format_type().

BTW, it's "PostgreSQL" or "Postgres," not "Postgre."

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/