alistairphillips.com

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


PGSQL, SQLite and *nix tips

PostgresSQL

If you're using PostgreSQL and need to retrieve the last inserted id then http://stackoverflow.com/a/2944481/89783 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;

SQLite

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
.dump

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