I’m : a web and mobile developer based in the Manning Valley, Australia.

PGSQL, SQLite and *nix tips


If you're using PostgreSQL and need to retrieve the last inserted id then has all the details you need. The best seems to be using the RETURNING keyword to retrieve your identifier. Here's what it looks like for a table with an id field:

INSERT INTO persons (lastname,firstname) VALUES ('Smith', 'John') RETURNING id;


To dump the contents of a SQLite database out to a file containing SQL commands to CREATE tables and INSERT data use the following:

.output file.sql

You can optionally specify a table name after .dump to just export that.

Find matching lines between 2 files

If you need to find what lines occur in two files then you'll find comm to be useful for that.

comm -12 file file2