Browsing tables

Click a table in the sidebar to open it. The tab has two views at the bottom: Data and Structure.

There are two kinds of grid. Tables with a primary key open in the editable grid, which sorts and filters on the server. Tables without one, views, Trino tables and query results open in a read-only grid, which works on the rows already loaded and adds per-column filters and frozen columns.

Paging

Lily loads one page at a time and shows Rows 1–500 with previous and next buttons. Change the page size in Settings › Data & queries › Rows to load per table (50 to 10,000).

Sorting

Click a column header to sort ascending, again for descending, and once more to clear. In the editable grid this runs a new query with ORDER BY from the first page, so it sorts the whole table, not just the page.

Filtering

The WHERE bar

Type a condition and press Return. It runs on the server:

status = 'active' AND created_at > now() - interval '7 days'

Curly quotes pasted from a document are straightened before it runs.

The filter builder

The funnel icon opens a builder: pick a column, an operator and a value, and add more conditions. They are joined with AND, and the builder writes the result into the WHERE bar, so you can edit it from there.

Operators: =, ≠, >, <, ≥, ≤, LIKE, contains, IS NULL, IS NOT NULL.

Column filters

In the read-only grid, hover a header and click its funnel to filter that column: contains, equals, not equal, starts with, ends with, greater than, less than, is empty, is not empty. These filters work on the loaded rows, and the footer shows how many were filtered out.

Find in table

⌘ F filters the loaded page to rows where any cell contains your text and highlights the matches. ↑ and ↓ move between them.

Reading values

Lily colors values that mean something: negative numbers and false in red, true in green, links and emails in the accent color, past dates in grey. ⌀ is NULL.

• View value… (right-click a cell) shows the full value, formats JSON, previews images stored as data URIs, bytea or base64, and counts characters.
• View row… shows one row as a list of columns and values. ↑ and ↓ move to the next row.
• Show row inspector opens a side panel that follows the selected row.

Aggregates

• In the editable grid, the Σ button in the bottom bar picks a column.
• In the read-only grid, ⌥-click a header.

The footer then shows count, sum, average, min and max for numbers, count and distinct values for text, and how many are NULL. The numbers cover the loaded rows.

Freezing columns

In the read-only grid, right-click a header and choose Freeze up to this column to keep the first columns in place while the rest scroll.

Following foreign keys

Right-click a value in a foreign-key column and choose Follow → table. Lily opens the referenced table filtered to that row. Foreign keys come from the database's declared constraints.

Copying

• ⌘ C copies the selected rows as tab-separated values, ready for a spreadsheet.
• Copy row as gives TSV, CSV, JSON, Markdown or an INSERT.
• With several rows selected, Copy N rows as INSERT writes one statement per row.

To save rows to a file, see Results, charts and export.