Manual page for POCKETSQL(TDH)

pocketSQL - compact SQL database


pocketSQL is a compact multiuser database system that can be embedded in applications. It uses a subset of the SQL command language. pocketSQL has a simple C language API and also a command line interface called psql(1) . Web-based access is available via cgipal.

The goal of pocketSQL has been to develop a tool with reasonable SQL functionality that can connect easily with the shell, external programs, and files, and is small enough to link directly into programs rather than using a client-server approach. Ease of project administration has been another objective.

pocketSQL is not a full SQL implementation and has a number of limitations , including lack of indexing and constraints on compound logic in where-clauses. PocketSQL must be invoked on the same file system that the data resides on rather than across a network, and updating processes must have the same UID as the owner of the data files. There is no concept of database login name or password.


An example database is included in the distribution. To try it, set environment variable PSQL_CONFIG to the full path name of your example.cnf file, then type:

psql "select * from people"


Here is a summary of the SQL constructs supported by pocketSQL. Some familiarity with standard SQL is assumed.

SQL command keywords and NULL are case insensitive. Table and field names are case sensitive, should begin with a letter, and should contain only letters, digits, and underscores. SQL key words should be avoided when choosing table and field names. In commands, all "words" or "tokens" must be separated by white space, including where clause expressions (e.g. use where A = 1, not where A=1), and comma delimited lists (use select a, b, c, not select a,b,c or select a ,b ,c). The only place that field names may use the table.fieldname construct is in SELECT where a JOIN is being performed. The database.table.fieldname construct is not supported.

Literal text strings are case sensitive and may be enclosed in double quotes (") or single quotes('). A literal quote may be represented by supplying a preceding backslash.

Parentheses may be used anywhere for readability but are ignored by the command interpreter, except in function constructs.


pocketSQL News


SQL commands

      GROUP BY
      ORDER BY
    WHERE clause

pocketSQL extensions


Temporary tables

Accessing ordinary files

Result fields from SELECT

Data file format
Data file update details
Data file locking details
Transaction logging

Setting up a new project

Config file


Maintenance tips



SELECT retrieves fields from one or more tables. The SELECT syntax is:

table|filename [APPEND]]
table [ AS talias1 ] [jointype] JOIN table2 AS talias2 ON on-clause]
groupbylist ]
orderlist ]
firstrow], lastrow ]

DISTINCT eliminates duplicate result rows.

itemlist is a comma-delimited list of fieldnames, aggregate functions, or quoted strings. An asterisk (*) may appear alone to select all fields, or may be used to match a subset of fields, e.g. t1.*. AS may be used to specify fieldname aliases (however, natural field names, not aliases, must be used other parts of the command (WHERE, ORDER BY, etc.). Aggregate functions such as count() may be given in itemlist; these may be used alone to operate on all requested rows, or with GROUP BY, to operate on groups of records. Supported aggregate functions are: count(), sum(), avg(), min(), and max(). count( fieldname ) counts the number of non-null instances of fieldname; count(*) counts the number of rows. The other functions must contain one field name within the parentheses.

INTO causes the results to be written to a temporary table or ordinary file ; it can also append content to a database table (non-temporary). Temp table names must begin with dollar sign ($). File names must begin with slash (/) or dot (.). For temp tables and ordinary files, APPEND may be used to append the results to existing content, but no consistency checking is performed. For non-temporary database tables, APPEND must be used, and the field counts of result rows and of existing table rows must match. Datatypes are not checked.

FROM names the table(s) from which data will be retrieved. Temporary table names should contain a leading dollar sign ($). Ordinary file may be used too; file names should begin with slash (/) or dot (.).

JOIN causes a relational join to be performed using two tables. The JOIN keyword must be present in order to perform a join. The on-clause specifies field(s) that will be key fields when performing the join (see examples). Matching is case-insensitive. When using JOIN, fieldnames must be specified using full table.fieldname notation everywhere in the SQL command. Table name aliases may be defined using AS; this helps to keep fieldnames short. If large table(s) are involved, adding selection conditions to the WHERE clause may increase speed. Available jointypes are described below. If jointype is not specified an INNER JOIN will be performed.

(LS = left side content; RS = right side content)

INNER (content must be present on both sides to produce an output record);
LEFT (all LS kept; RS joined when available, NULL otherwise);
RIGHT (all RS kept; LS joined when available, NULL otherwise);
OUTER (all LS and RS kept; companion content joined when available, NULL otherwise).

For all types of joins except LEFT, multiple rows having identical key field(s) are allowed in the right side table only. The matching row from the left side will be replicated for every instance. For LEFT JOIN this treatment is reversed. Multiple rows having identical key field(s) are never allowed in both tables. Nested joins are not supported in the FROM clause but may be implemented using temporary tables. Note: pocketSQL performs joins by executing the psqlj program; in order for this to work the config file joinprog attribute must be set to the full path name of the psqlj executable.

GROUP BY specifies field(s) that will control grouping of records for aggregate computation e.g. count(), sum(), etc. These fields should be given in groupbylist, and must also be present in the SELECT itemlist. GROUP BY implicitly orders result rows using groupbylist. ORDER BY cannot be used with GROUP BY, but any of the ORDER BY keywords may be used in groupbylist to influence the order of result rows. HAVING is not supported. A temp table may be used to perform the same function as HAVING, and/or to present the results in another order. DISTINCT cannot be used with GROUP BY.

WHERE specifies a conditional expression to select records of interest, in the form of a WHERE clause . If not specified, all rows in the table will be presented.

ORDER BY controls the ordering of the result rows. orderlist is a list of field names to use in the sort. Each field name in orderlist may optionally be followed by ASCENDING (normal order), DESCENDING (reverse order). Also available are the keywords NUMERIC (numeric sort), and DICT (dictionary order-- punctuation characters are ignored). By default, the sort method is ASCENDING, and alpha method is used unless data field is defined as numeric . PocketSQL also allows * (natural sort) to be given as a field name in orderlist, either alone or last in the list, with no trailing modifiers. This causes an ascending alpha sort to be done by comparing all fields not explicitly named in the orderlist, from left to right based on field location within row.

LIMIT may be used to present only a certain range of the rows that have been selected, useful for displaying "pages" of results, e.g. LIMIT 10 would present the first 10 rows, while LIMIT 101, 150 would present result rows 101 through 150 (and the row count would be 50).


select * from people

select lastname, firstname, email from people
where people_id = 578

select trans_id (as id), tag (as tname), measurement (as meas)
into tmp1
from valuelist
where tag isnot null
order by measurement descending num, tag ascending dict
limit 51, 100

select distinct, t.description
from customers (as c) join transactions (as t) 
on c.cust_id = t.cust_id

select p.*, ps.currentstatus
from patients (as p) left join patientstatus (as ps) 
on p.patientid = ps.patientid
where ps.class = "B"
order by p.patientid


INSERT adds one new row to a table. If fieldname1..N is omitted then table fields will be used in the order defined. Any fields not mentioned will be initialized to null. pocketSQL also allows the UPDATE NEWROW command to be used to add new rows (see below).

INSERT INTO table [ ( fieldname1 .., fieldnameN ) ]
value1 ..,valueN )


insert into people ( people_id )
values ( 257 )

Another example:

insert into songs 
  ( id, artist, title, writer, length, album )
  ( 1, "Pink Floyd", "Speak to me", 
        null, null, "Dark side of the moon" )


UPDATE modifies one or more existing rows in a table, or may be used to add a new row if the NEWROW keyword is used. If MULTIROW is specified, update will be to all matching rows. Otherwise, update will be to a maximum of one row. For safety, a WHERE clause is always required (except with NEWROW) even when you want to modify all rows (use like '*' or something similar). It is not considered an error if no rows are found that meet the WHERE clause (the affected row count may be checked to determine this). (NEWROW and MULTIROW are extensions to standard SQL.)

fieldname1 = value1 ,
. .
fieldnameN = valueN


update people
lastname = "Sherman",
firstname = "Bobby",
email = ""
where people_id = 245

A multirow example:

update peoplegroups multirow
group_id = 277
where group_id = 287


DELETE deletes one or more existing rows. If MULTIROW is specified, all matching rows will be deleted. Otherwise, a maximum of one row will be deleted. (MULTIROW is an extension to standard SQL.)



delete from people
where people_id = 279

A multirow example:

delete from chemlinks multirow
where user_id = 554 || user_id = null


The SELECT, UPDATE, and DELETE commands allow the use of a WHERE clause to select rows. A WHERE clause is a conditional expression made up of comparison operators and operands (field names and literals). No functions are supported. Examples:

WHERE lastname = "Jones"

WHERE lastname LIKE "J%"

WHERE balance < 0.0 AND status = "A"

WHERE status != null

WHERE zscore > 1.0 OR zscore < -1.0

pocketSQL supports the following comparison operators in WHERE clauses:

=         Equal to.  Case sensitive for strings. (alt: == or is)
!=        Not equal to (alt: <> or isnot)
>         Greater than.
>=        Greater than or equal to.
<         Less than.
<=        Less than or equal to.

Note: if at least one operand is non-numeric, comparison operators such as > will do an ascii value comparison.

Wild card matching: Wild card matching may be done using like. Wild card characters are % which matches any number of any character, and ? which matches a single instance of any character. This matching is case-insensitive. (e.g. * may be used rather than % by setting sqlwildchar in your project config file .)

like      Wild card match.  Example: hello like h% (true)
!like     Not a wild card match (alt: notlike)

Commalist operators: these take a commalist on the right side and perform set or list operations. These are extensions to standard SQL.

in        Member of list.  Example: z in "x,y,z" (true)
!in       Not a member of list. (can also use notin)
inlike    Same as in but wild cards may be used in list members,
		e.g. z inlike "x%,y%,z%"
!inlike   Same as !in but wild card matching is used. 
                (can also use notinlike)

Logical connectors: Conditionals may be connected together using these logical connectors:

and       logical AND (can also use &&)
or        logical OR (can also use ||)

Caution! Parentheses may not be used to establish presidence; thus mixing AND and OR in the same expression requires care, or should be avoided altogether. When the expression is parsed, it is first split into OR terms, then each of the OR terms is split into AND terms. For example: A = B and C = D or E = E of F = G would evaluate to true, because it is interpreted as if it were written (a = B and C = D) or (e = e) or (f = g). It may be best to avoid mixing AND and OR in the same expression and use multiple expressions instead. Or, in some cases commalist operations such as in may be used to avoid using OR.


The following are extensions to standard SQL implemented by pocketSQL.


LOCK and UNLOCKALL are provided to manage table locking explicitly. They may be useful when a table is being modified by a repetitive series of updates or inserts.

LOCK explicitly locks (reserves) one or more tables for exclusive write access. Other processes may still read tables. Useful when a series of updates will be done to table(s), since otherwise the table will be locked and then unlocked for each individual operation. If a table cannot be reserved (because in use by another user) a fatal error results. Only one LOCK command may be in effect at any one time.

lock table1 .. tableN

Example: lock people peoplegroups

UNLOCKALL explicitly unlocks all tables reserved with the most recent LOCK.



Allows a temporary table to be created from the results of a shell command or external program. The syntax is:

SHELLINTO temptable [APPEND] FORMAT formatname COMMAND shellcommand

temptable names the temporary table that will be created. If APPEND is specified, new content will be appended to any existing content of the temp table. formatname is the name of an existing data record format or the special symbol #USEHEADER, which indicates that the temp table will have a conforming field name header (see temporary tables . shellcommand is the shell command to be executed. It will be executed with the project data directory as its working directory.

Example: shellinto tmp1 format gema2 command cat exp*/gema*

Warning: if you use user-entered values to build the shell command, you run the risk of hack attempts. User input should be processed to remove all characters that have special meaning to the shell.


Allows a virtual temporary table to be created by specifying a shell command. When this "temp table" is referenced in a SELECT command, the shell command is executed and the results captured. The syntax is:

PIPEDEF temptable FORMAT formatname COMMAND shellcommand

Operates similarly to SHELLINTO (see above), but may be more efficient since it avoids temp table file i/o. Temporary tables produced by PIPEDEF cannot be processed with INSERT, UPDATE, or DELETE.

Example: pipedef tmp1 format #useheader command compstats 65 gbvol M

Warning: if you use user-entered values to build the shell command, you run the risk of hack attempts. User input should be processed to remove all characters that have special meaning to the shell.


Remove temporary table(s). The syntax is:

DROP *|temptable1 .. temptableN

* causes all temporary tables generated by the current process to be removed. Otherwise, temp tables may be named explicitly, with or without leading dollar sign ($). Only temp tables belonging to the current process may be removed.

Example: drop $tmp1 $tmp2


Database tables are tabular bodies of data items having rows and columns. In this documentation we refer to columns as "fields".

pocketSQL database tables are stored as plain ascii tablular files . and are normally located together in a directory which is defined in the project config file . Temporary tables are generally stored in a different directory. Field formats are defined in fdf files ; they too are usually located together in a directory which may be specified in the project config file .

Tables are referenced in pocketSQL by name. For example, suppose that you put the following into your project config file :

datapath: /home/steve/mydata
fdfpath: /home/steve/fdf
In your datapath directory you create a file named pricelist, and in your fdfpath directory you create an fdf file called pricelist.fdf. You should then be able to do: select * from pricelist.

The following are pocketSQL extensions:

Hierarchical collections of tables: Hierarchies of tables may be set up in the data directory by creating subdirectories there (any number of levels). These tables may be referenced in SQL commands by path name relative from the data directory. Either the first segment or the last segment of the path name should be a defined fdf format . Be careful not to use legal format names in both the first and last segment. For example, suppose you create a subdirectory called cases in your data directory, and put data files in it called A, B, and C. You also set up an fdf file called cases.fdf. You could then do: select * from cases/b

Format name embedded in table name: Normally the table name and the format name are the same, or if hierarchies are used, the format name is the first or last segment of the path name. However it is also possible for a table to use a format having a different name, using this notation:



Temporary tables may be created using the SELECT INTO, SHELLINTO, or PIPEDEF commands in pocketSQL. They may then be accessed just like any other table using SELECT. Temporary tables are unique to a process; your process cannot access or affect temp tables created by another process. They are stored as files in a directory defined in the project config file . Temporary tables provide a way of "chaining" or combining commands. These are particularly useful with pocketSQL since the more elaborate forms of some commands aren't supported. For instance, you can put the results of a two-table join into a temp table, then join that temp table against another table.

In pocketSQL temporary table names begin with a single dollar sign ($). Currently, temp tables may not be manipulated using INSERT, UPDATE, or DELETE. The DROP command may be used to clean up temp tables when they are no longer needed. It is also a good idea to clean up old files in the temp table directory regularly (use crontab to invoke the find(1) command).

Field name headers: Temporary tables often use a "field name header" to define their field names. This simply means that field names, and perhaps data types, are listed in the first line or two of the file. If you are generating temp tables using SHELLINTO or PIPEDEF with #USEHEADER, your external program will need to know how to build a field name header.

The first non-comment line of the file should contain field names, each followed by a single space. You can optionally supply a second line containing datatype information in the form of one data type symbol per field. Data type symbols are:

---   alpha (any embedded underscores assumed to represent blanks)
--n   numeric
--u   alpha with embedded underscores 

If the data type line is not given, all fields will be assumed to be ---. Here is an example:

id lastname firstname email nvisits
--- --- --- --u --n
0001 Adams John 2
0002 De_Graft Scott 4


Any available file may be accessed and processed by the SELECT command if it has one record per line and fields delimited by a single TAB or space. Within a field embedded spaces or TABS are not allowed; embedded spaces and TABS must be represented as underscores.

Instead of a table name the file path name should be used; the first character in the path name must be slash (/) or dot (.). Ordinary files must contain a field name header (see above). SELECT INTO may be used to write data to an ordinary file. Ordinary files may not be manipulated using INSERT, UPDATE, or DELETE.

Here is an example of a SELECT command that is accessing an ordinary file.

select * from ./trial3/55x25
where intensity > 0.7


When a SELECT command is issued and results are captured, a set of result field names is provided. These field names are often used in the calling environment as variable names for accessing and manipulating result fields. Or, if SELECT INTO is being used to build a temp table, these field names will become the operational field names for the temp table.

The list of field names is built this way: Field name aliases are used if specified, otherwise natural names are used. For join results, the table or table alias prefix is kept, except with SELECT INTO, when the table prefix is removed from the beginning of fieldnames. For count( * ), the natural result field name will be __rowcount. For other aggregating functions, the natural result field name will be the subject field name with an appropriate suffix attached, e.g. the result of avg( score ) will be called score__avg.


pocketSQL tables are managed as ordinary tabular ascii data files. These files may be created or modified using a text editor, by external programs, and so on. Data files have one "record" per line, and each record has one or more "fields". Fields are separated by a single space. The last field on each record must also be followed by at least one space. Spaces that are embedded in data fields are represented using underscores. Missing or null data fields should be represented using some visible symbol. By default this is null but this may be defined in the project config file .


When pocketSQL adds a new record, it appends it to the end of the data file. When pocketSQL updates a record, it overwrites the old with the new, and overwrites any remaining old record content with blanks. If the new record is too big to fit where the old one was, the new record is appended to the end of the data file and the old record is completely overwritten with blanks. Single-record deletes are also implemented by blanking out. This is why, over time, you may notice some blank lines in pocketSQL data files. They may be removed if you like.


pocketSQL locks data files during update to prevent simultaneous update by multiple users. The locking is automatic but may be overridden using LOCK and UNLOCKALL.

The pocketSQL locking mechanism uses the file's permissions mode to indicate lock status (see your config file for the modes that are used). Since update is generally by batch script, data files are normally locked for only short periods of time. When attempting to gain access to a file access is automatically retried several times over a period of several seconds.

There is no facility for record locking.


Transaction logging and error logging will be written to the files indicated in your config file . You can set the script variable _TRANSTAG to any string (e.g. user id) that would be useful to have in the transaction log and error log. The format of the transaction log is:

filename op tag yy/mm/dd hh:mm:ss field1 .. fieldn

filename is the file that was updated.

op describes what was done and may be one of:

add = new record appended to file

delete = single record deletion.. tracked in subsequent pair of replace records

replace_was = single record update.. previous contents

replace_new = single record update.. new contents

multid = multiple record deletion.. one log entry per record deleted

multiu_was = multiple record update.. previous contents.. one per record updated

multiu = multiple record update.. new contents.. one per record updated

tag is an arbitrary transaction tag from variable _TRANSTAG.

Then come the date, time, and all data fields.

There is no locking on the transaction log so collisions are possible especially with multirecord ops since these take longer.

The format of the error log is:

date time error:N tag:[T] msg:[MSG1][MSG2]

Where N is an error code, tag is _TRANSTAG if any, and MSG1 and MSG2 describe the error.


Here is the procedure for setting up a new project that will use pocketSQL.

* If not yet done, download and compile pocketSQL. If you are using the C language API link your code against libpsql.a

* Choose a project directory. For illustration purposes suppose its name is volunteers

* Within volunteers create subdirectories for data, table definitions, logs, and one for temp files e.g. cd volunteers; mkdir data fdf logs tmp

      |             |            |           |           |
    config         data         fdf         logs        tmp
                  | | |        | | |        | |        | | | 

* Create a project config file in volunteers. Suppose we name it config
It might look like this:

	varvalue:      		PROJDIR=/home/scg/volunteers
	datapath:       	@PROJDIR/data 
	fdfpath:        	@PROJDIR/fdf
	tmpdir:         	@PROJDIR/tmp
	dbtranslog:     	@PROJDIR/logs/dbtranslog
	dberrorlog:     	@PROJDIR/logs/dberrorlog
	dbnull:         	null
	dbfileavailmode:        400
	dbfilelockedmode:       600

* Set up record definition files for some of your tables. For example, for a people table, the file people.fdf would be placed in the project fdf subdirectory and might look like this:

	#title	People
	people_id  num
	email  und

* Populate your database. You can use psql(1) and add data using INSERT statements, or use other means to get your data into ascii fields. Fields should be delimited by a single space, and any embedded spaces converted to underscores.

* Try some queries using psql(1), or your own software.


A project config file should be set up to tell pocketSQL where to find different types of files, and how to operate. The shell environment variable PSQL_CONFIG should be set to hold the path name of your config file. See config file man page and setting up a new project for more details.


pocketSQL is a simplified compact SQL implementation, and consequently has a number of limitations, many of which are enumerated below.
  • No indexed data access (all file access is sequential).
  • Limitations on compound WHERE clause expressions. Parentheses are ignored so there is no way to specify presidence if both AND and OR are used in a WHERE clause.
  • No support for submitting requests over a network. Data must be accessible via file system.
  • Updates can only be done by processes having the same UID as the owner of the data files. There is no concept of database login name or password.
  • GROUP BY cannot be used in the same query as ORDER BY. Maximum 20 field names in an ORDER BY or GROUP BY specification.
  • Supported SQL commands and syntax are limited to the specific constructs and syntax described herein.
  • WHERE clause cannot contain arithmetic expressions or functions.
  • SELECT item list cannot contain arithmetic expressions or functions, except for certain aggregation functions such as count() and avg(). SELECT item list must contain at least one data field reference.
  • Maximum field length is 250 characters. Maximum number of fields in a table, retrieval result, or join result is 80. Maximum length of any single data file record is 3000 characters.
  • Max length of a field name, including any table name prefix with joins, is 48.
  • A path name maximum of 125 characters.
  • Embedded spaces within fields are converted to underscores in the tabular ascii data files. This allows the data files to be processed with standard unix utilities, and is usually not a problem. However, this will be problematic for data fields containing a mixture of spaces and underscores.
  • No CREATE. Tables are created by making an FDF file . DROP only applies to temporary tables.
  • There is no record locking; locking occurs only at the table level to prevent simultaneous updates of the data file.
  • No HAVING.
  • INSERT operates on one row at a time only.
  • SQL commands must be submitted one at a time; they may not be batched.
  • No transaction/rollback control, triggers, or stored procedures.
  • No direct support for unlimited-length text (memo) fields or large binary objects, but this can often be implemented by storing such data in files and then storing file name references in the database.


Data files: over time data files may contain a significant number of blank records. These are the result of normal data file updates and deletes . You may want to set up an automated process to filter out these blank lines. For example, this shell script could do it:

# change AVAILMODE to suit..
cd data
for i in *
  chmod 600 $i
  cat $i | sed "/^ *$/d" > tmp
  mv tmp $i
  chmod $AVAILMODE $i

Data files that stay locked: data files are normally locked only for a split second while updates occur. A program crash or other problem could conceivably cause an unlock to never be done, resulting in a data file that remains locked. Doing an ls -l in your data directory will reveal any data file that has an incorrect protection mode.

Temp files: pocketSQL may create temp files in tmpdir as defined in your config file . These files are not always removed, so a regular clean up of old temp files is recommended. A good strategy is to invoke the find(1) command regularly via cron to remove temp files that are over a certain age. Here is a crontab entry that does this:

# Every morning 4:18, remove tmp files that are > 1 days old
18 04 * * * /usr/bin/find /home/proj1/tmp  ! -type d  -mtime +1 -exec rm -f {} \;


Steve Grubb ( 2001
Freely downloadable from

Bug reports, questions, doc errors, and general correspondence may be sent to me at the address above. Please indicate your platform and operating system, and the version of pocketSQL that you are using (see ./src/Version). It is very helpful if a small self-contained example that reproduces the problem can be attched.

Copyright Steve Grubb

Markup created by unroff 1.0,    August 07, 2001.