postgresql

postgresql connects to PostgreSQL, inspects tables and rows, makes focused changes, exports data, runs native SQL and creates logical backups.

DatabaseVersion 1.1.4Stable
lil-terminal

Overview

Use postgresql for a consistent PostgreSQL workflow: hidden-password connections, schema and row inspection, focused changes, exports, native SQL and logical backups without shell tools.

PostgreSQL without flattening its model

PostgreSQL is a client-server relational database with strong typing, transactions and a rich native SQL language. A database can contain schemas, and schemas contain tables and other objects, so “schema” here is also a database namespace—not only a description of columns. Use the short surface for inspection and routine changes, then native SQL after : when PostgreSQL-specific features matter.

Syntax

lil-terminal
postgresql [options] · postgresql : <sql>

Reference

postgresql

show the current PostgreSQL connection

postgresql -c <host> <user> [database] [-p <port>] [-n] [-m [name]]

connect; password is requested separately unless -n is used

-use <database>

select another database by reconnecting

-l

list databases

-t

list public tables and views

-s <table>

show columns and data types

-n <table> [rows]

preview table rows

-f <table> <text> [rows]

find text across table columns

-o <table> [-j|-csv] [file] [-f]

export table rows

-a <table> : <json>

insert one row from a JSON object

-w <table> <field> <value> : <json>

update matching rows from a JSON object

-d <table> <field> <value>

delete matching rows

-m [name]

save the current connection in .lil.key; default name is the database

-M <name>

remove a saved connection

-k [name]

list saved connections or inspect one without showing its password

-K <name>

connect using a saved connection

-e

disconnect and clear connection data

postgresql : <sql>

execute native SQL

postgresql load <file.sql|file.json>

execute SQL or restore a lil logical JSON dump

postgresql dump [file.json] [-f]

create a logical JSON dump of public tables

Start with a safe inspection

Start with postgresql -c localhost demo shop and enter the password in the separate secret prompt. Use -t to discover tables, -s products to inspect columns, -n products 2 to preview two rows, and -f products shoe 2 to search. A SELECT after : is useful when the compact options cannot express your query. -o products -csv tmp/products.csv writes a portable table export.

Before changing data

For changes, -a inserts a row, -w updates all rows matching the chosen field and -d deletes all matches. Check the selection and back up first. dump is a lil JSON copy of data from public tables, **not** a full pg_dump: it does not recreate the schema, indexes, roles or extensions. load accepts either this JSON format (into existing tables) or SQL. These examples are illustrative; PostgreSQL and the PHP pgsql extension are required for real work.

Inspection

-l lists databases, -t lists public tables/views, -s shows column definitions, -n previews rows and -f searches text across one table. -o exports table rows as JSON or CSV.

Editing

-a inserts one JSON object, -w updates rows selected by one field/value pair and -d deletes matching rows. Use native SQL for joins, transactions and advanced PostgreSQL features.

Dump and restore

Because lil does not invoke system pg_dump, dump creates a logical JSON snapshot of public table rows. load restores that lil JSON format or executes a supplied SQL file. Use the server's native backup tooling when a complete PostgreSQL cluster/schema backup is required.

Connection

-c requests the password outside command history. -use reconnects to another database with the same session credentials.

Safety

Commands run with the privileges of the connected PostgreSQL user.

The lil JSON dump is data-only: schema objects and indexes are not recreated. JSON restore is transactional, and export or dump refuses a table above 100000 rows instead of writing partial data.

Saved connections

Without -m, connection data remains scoped to the current PHP session. -m [name] explicitly stores the current profile in the protected .lil.key file (0600), -K <name> reconnects from it, -k lists or inspects profiles without exposing a stored password, and -M <name> removes one. kill removes .lil.key together with the other core state. Treat the file as sensitive because an opted-in profile can contain database credentials.

Interactive sandbox preview

Run the prepared examples directly on the page. Their results are prebuilt and shown in the browser; no terminal command is executed on the server.

Inspect a PostgreSQL table

Install and manage

Install the current compatible version. Choose Base for the complete feature set or Minimum for the reduced package; use upgrade to update a module that is already installed.

Base and Minimum are two packages of the same current module release, not separate command versions.

Base package

Install the complete package with local command reference and every published mode.

install postgresql
Minimum package

Install the reduced package. Local reference is omitted; help <cmd> -i opens the current server reference.

install postgresql -m
Exact public version

Install exactly this published version.

install postgresql -v 1.1.4
Check or update

Ask upgrade to check compatibility and replace the installed block only when needed.

upgrade postgresql
Remove module

Remove the extension block while leaving the core and unrelated modules intact.

install postgresql -u

Examples

Examples are copyable command lines, not actions executed by this website. Review paths, permissions and destructive flags before running them on a real project.

Inspect a PostgreSQL table
postgresql
Illustrative sandbox preview — no live connection
A connection is required; the status view names the server and selected database.
Postgresql · localhost
postgresql -c localhost demo shop -n
Illustrative sandbox preview — no live connection
Connection example: server and database will be shown after a successful connection.
Postgresql · localhost
postgresql -c localhost demo shop
Illustrative sandbox preview — no live connection
Password is requested separately; selected database: shop.
Postgresql · localhost
postgresql -c localhost demo shop -p 5433 -n
Illustrative sandbox preview — no live connection
Connection example: server and database will be shown after a successful connection.
Select another database by reconnecting
postgresql -use shop
Illustrative sandbox preview — no live connection
Selected database: shop (requires an active connection)
List databases
postgresql -l
Illustrative sandbox preview — no live connection
postgres
shop
List public tables and views
postgresql -t
Illustrative sandbox preview — no live connection
products  BASE TABLE
Show columns and data types
postgresql -s products
Illustrative sandbox preview — no live connection
column_name  data_type
id  bigint
sku  text
name  text
category  text
price  numeric
active  boolean
Preview table rows
postgresql -n products 2
Illustrative sandbox preview — no live connection
sku=SHOE-1  name=City shoe  category=shoes  price=79.9  active=True
sku=BAG-1  name=Canvas bag  category=bags  price=44.5  active=True
Find text across table columns
postgresql -f products shoe 2
Illustrative sandbox preview — no live connection
sku=SHOE-1  name=City shoe  category=shoes  price=79.9  active=True
Export table rows
postgresql -o products -j
Illustrative sandbox preview — no live connection
[
  {
    "sku": "SHOE-1",
    "name": "City shoe",
    "category": "shoes",
    "price": 79.9,
    "active": true
  },
  {
    "sku": "BAG-1",
    "name": "Canvas bag",
    "category": "bags",
    "price": 44.5,
    "active": true
  }
]
Postgresql · products
postgresql -o products -csv tmp/products.csv
Illustrative sandbox preview — no live connection
Saved  sandbox/tmp/products.csv (if the destination does not exist)
Postgresql · products
postgresql -o products -j tmp/products.json -f
Illustrative sandbox preview — no live connection
Saved  sandbox/tmp/products.json (replace existing file only with -f)
Handle a missing table
postgresql -a products : {"sku":"NEW-1","name":"Sample","category":"test","price":5,"active":true}
Illustrative sandbox preview — no live connection
Inserted  1 (example with a fresh, isolated copy)
Update matching rows from a JSON object
postgresql -w products sku SHOE-1 : {"price":75}
Illustrative sandbox preview — no live connection
Updated  1 (illustrative single matching row)
Delete matching rows
postgresql -d products sku SHOE-1
Illustrative sandbox preview — no live connection
Deleted  1 (example only; all matches would be affected)
Execute native SQL
postgresql : SELECT COUNT(*) AS total FROM products
Illustrative sandbox preview — no live connection
total
2
Create a logical JSON dump of public tables
postgresql dump tmp/shop-pg.json
Illustrative sandbox preview — no live connection
Saved  sandbox/tmp/shop-pg.json
Format  lil-postgresql-json (logical data export, not server backup)
Postgresql · dump
postgresql dump tmp/shop-pg.json -f
Illustrative sandbox preview — no live connection
Saved  sandbox/tmp/shop-pg.json
Format  lil-postgresql-json (logical data export, not server backup)
Execute SQL or restore a lil logical JSON dump
postgresql load db/postgresql.sql
Illustrative sandbox preview — no live connection
SQL/JSON import requires an existing schema; running the supplied CREATE TABLE twice causes a conflict.
Execute SQL or restore a lil logical JSON dump
postgresql load tmp/shop-pg.json
Illustrative sandbox preview — no live connection
SQL/JSON import requires an existing schema; running the supplied CREATE TABLE twice causes a conflict.
Save the current connection in .lil.key; default name is the database
postgresql -m shop-demo
Illustrative sandbox preview — no live connection
Saved  shop-demo (stores a sensitive profile in .lil.key)
List saved connections or inspect one without showing its password
postgresql -k
Illustrative sandbox preview — no live connection
No saved connections (unless a profile has already been saved)
List saved connections or inspect one without showing its password
postgresql -k shop-demo
Illustrative sandbox preview — no live connection
Saved connection not found: shop-demo (unless saved earlier)
Connect using a saved connection
postgresql -K unknown
Illustrative sandbox preview — no live connection
Saved connection not found: unknown
Remove a saved connection
postgresql -M unknown
Illustrative sandbox preview — no live connection
Saved connection not found: unknown
Disconnect and clear connection data
postgresql -e
Illustrative sandbox preview — no live connection
+
Postgresql · localhost
postgresql -c localhost demo shop -p 99999 -n
Illustrative sandbox preview — no live connection
Invalid database port
Show columns and data types
postgresql -s missing_table
Illustrative sandbox preview — no live connection
Table not found: missing_table

File used in this example: sandbox/db/postgresql.sql

The example uses a file or directory from the protected /sandbox/ documentation workspace.

CREATE TABLE products (id BIGSERIAL PRIMARY KEY, sku text UNIQUE NOT NULL, name text NOT NULL, category text NOT NULL, price numeric(10,2) NOT NULL, active boolean NOT NULL DEFAULT true);
INSERT INTO products(sku,name,category,price,active) VALUES ('SHOE-1','City shoe','shoes',79.90,true),('BAG-1','Canvas bag','bags',44.50,true);

Related documentation

Which databases can I work with from the terminal? →Is lil-terminal safe to leave on a production site? →

Current public release

v1.1.42026-11-01

Production refinement

Current stable release of postgresql. postgresql connects to PostgreSQL, inspects tables and rows, makes focused changes, exports data, runs native SQL and creates logical backups.

# Version history