Mega Code Archive

NATURAL JOIN works with LIMIT and OFFSET

postgres=# CREATE TABLE "editions" ( postgres(# "isbn" text NOT NULL, postgres(# "book_id" integer, postgres(# "edition" integer, postgres(# "publisher_id" integer, postgres(# "publication" date, postgres(# "type" character(1), postgres(# CONSTRAINT "integrity" CHECK (((book_id NOTNULL) AND (edition NOTNULL))), postgres(# Constraint "pkey" Primary Key ("isbn") postgres(# ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pkey" for table "editions" CREATE TABLE postgres=# postgres=# insert into editions values('039480001X', 1608, 1, 59, '1957-03-01', 'h'); INSERT 0 1 postgres=# insert into editions values('0451160916', 7808, 1, 75, '1981-08-01', 'p'); INSERT 0 1 postgres=# insert into editions values('0394800753', 1590, 1, 59, '1949-03-01', 'p'); INSERT 0 1 postgres=# insert into editions values('0451198492', 4267, 3, 101, '1999-10-01', 'h'); INSERT 0 1 postgres=# insert into editions values('0823015505', 2038, 1, 62, '1958-01-01', 'p'); INSERT 0 1 postgres=# insert into editions values('0596000855', 41473, 2, 113, '2001-03-01', 'p'); INSERT 0 1 postgres=# postgres=# select * from editions; isbn | book_id | edition | publisher_id | publication | type ------------+---------+---------+--------------+-------------+------ 039480001X | 1608 | 1 | 59 | 1957-03-01 | h 0451160916 | 7808 | 1 | 75 | 1981-08-01 | p 0394800753 | 1590 | 1 | 59 | 1949-03-01 | p 0451198492 | 4267 | 3 | 101 | 1999-10-01 | h 0823015505 | 2038 | 1 | 62 | 1958-01-01 | p 0596000855 | 41473 | 2 | 113 | 2001-03-01 | p (6 rows) postgres=# postgres=# postgres=# CREATE TABLE "books" ( postgres(# "id" integer NOT NULL, postgres(# "title" text NOT NULL, postgres(# "author_id" integer, postgres(# "subject_id" integer, postgres(# Constraint "books_id_pkey" Primary Key ("id") postgres(# ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "books_id_pkey" for table "books" CREATE TABLE postgres=# postgres=# postgres=# insert into books values (1590, 'Java', 4156, 9); INSERT 0 1 postgres=# insert into books values (7808, 'Javascript', 1866, 15); INSERT 0 1 postgres=# insert into books values (1608, 'Perl Cookbook', 7806, 4); INSERT 0 1 postgres=# insert into books values (41473, 'Practical PostgreSQL', 1212, 4); INSERT 0 1 postgres=# postgres=# select * from books; id | title | author_id | subject_id -------+----------------------+-----------+------------ 1590 | Java | 4156 | 9 7808 | Javascript | 1866 | 15 1608 | Perl Cookbook | 7806 | 4 41473 | Practical PostgreSQL | 1212 | 4 (4 rows) postgres=# postgres=# SELECT isbn, title, publication postgres-# FROM editions NATURAL JOIN books AS b (book_id) postgres-# ORDER BY publication DESC postgres-# LIMIT 5 postgres-# OFFSET 2; isbn | title | publication ------------+---------------+------------- 039480001X | Perl Cookbook | 1957-03-01 0394800753 | Java | 1949-03-01 (2 rows) postgres=# postgres=# postgres=# postgres=# drop table books cascade; DROP TABLE postgres=# drop table editions cascade; DROP TABLE postgres=#