We often work with large databases and only want to return a small number of rows to view our data. Postgresql provides the LIMIT keyword to help with this. In this article, we will learn how to use LIMIT in Postgresql.
For our setup, we will use docker compose to create a Postgres database and to connect phpmyadmin. Start by copying the following into a docker compose file called
version: '3' services: db: image: 'postgres:latest' ports: - 5432:5432 environment: POSTGRES_USER: username POSTGRES_PASSWORD: password POSTGRES_DB: default_database volumes: - psqldata:/var/lib/postgresql phpmyadmin: image: phpmyadmin/phpmyadmin links: - db environment: PMA_HOST: db PMA_PORT: 3306 PMA_ARBITRARY: 1 restart: always ports: - 8081:80 volumes: psqldata:
We can run this file, we can use
docker-compose up. One this is done, open up phpmyadmin by going to http://localhost:8081.
You can then login by leaving the host empty and using the following credentials.
POSTGRES_USER: username POSTGRES_PASSWORD: password
In this article, we will need some data to work with. If you don't understand these commands, don't worry, we will cover them in later articles.
We will be using the sample db provided here: https://dev.mysql.com/doc/sakila/en/. However, we will only enter what we need rather than import the whole db.
Next, let's create an
CREATE TABLE actor ( actor_id smallint, first_name VARCHAR(45) NOT NULL, last_name VARCHAR(45) NOT NULL, last_update TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (actor_id) ) ;
And finally, let's enter a few rows.
INSERT INTO actor VALUES (1,'PENELOPE','GUINESS','2006-02-15 04:34:33'), (2,'NICK','WAHLBERG','2006-02-15 04:34:33'), (3,'ED','CHASE','2006-02-15 04:34:33'), (4,'JENNIFER','DAVIS','2006-02-15 04:34:33'), (5,'JOHNNY','LOLLOBRIGIDA','2006-02-15 04:34:33'), (6,'BETTE','NICHOLSON','2006-02-15 04:34:33'), (7,'GRACE','MOSTEL','2006-02-15 04:34:33'), (8,'MATTHEW','JOHANSSON','2006-02-15 04:34:33')
The basic sql structure as the following pattern.
SELECT * FROM actor LIMIT 2;
Running this command will ensure we only return the first 2 rows matching our query. Since we did not use and WHERE clause, this should be the first 2 rows in the DB.
The LIMIT keyword allows us to specify two parameters: one for limiting and one for offsetting. This allows us to load our database in sections, also called paginating.
For example, the following query will return the 2 rows after the first row. In this case, it will be rows 2 and 3. The
SELECT * FROM actor LIMIT 1, 2;
The syntax here can be hard to read and easy to mixup which param means offset. Postgresql provides the OFFSET keyword to solve this.
SELECT * FROM actor LIMIT 2 OFFSET 1;` `` |actor_id|first_name|last_name|last_update| |--------|----------|---------|-----------| |2|NICK|WAHLBERG|2006-02-15 04:34:33| |3|ED|CHASE|2006-02-15 04:34:33|