Обсуждение: SELECT * FROM foo OFFSET -1 LIMIT 1
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>
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
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
---------------------------------------------------------
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>
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
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/