Tagged: sql

Postgresql: Remove punctuation from field with a regex

Sometimes it’s useful to remove all punctuation from a field.

Take the sentence It's done - and dusted, finally. & that's that.. We’ll use regexp_replace on this.

select regexp_replace(lower($$It's done - and dusted, finally. & that's that. $$),$$\sand\s|[&\.,\s'-]$$,$$$$,$$g$$)

That results in itsdonedustedfinallythatsthat. We’ve also removed the word and.

If we use It's done--and dusted, finally. & that's that. then the \sand\s matcher would fail. It’s an exercise to the reader to work a way around that.

We’ve also not dealt with $ and the like, but that’s also facile to remove using the above.


Postgresql: Use of psql, .pgpass file and PGPASSFILE to store passwords

If you don’t want to enter your password in everytime you use psql, you can use a .pgpass file.

The file itself–.pgpass–is in the format YOUR_HOST:YOUR_PORT:DB_NAME:USER_NAME:PASSWORD. I.e.


Importantly, you need to give the password file the 600 permission, or psql will just ignore it, so:

chmod 600 .pgpass

Then psql should, normally, just pick that up. If not do something like

PGPASSFILE=/home/dave/.pgpass psql -h localhost -U dave -d my_db 

Getting started with SQLite and Android

Here’s a code sample to quickly add and query an sqlite database. There are better ways. This is the basic.

Here’s the helper that creates your database, and optionally upgrades it:

private static class DatabaseHelper extends SQLiteOpenHelper {
  private static final String SQL_CREATE_ENTRIES =
          "CREATE TABLE whoopwhoop (" +
                  "_id INTEGER PRIMARY KEY NOT NULL," +
                  "t text NOT NULL)";
  public DatabaseHelper(Context context) {
    super(context, "yourdatabasename", null, 1);
  @Override public void onCreate(SQLiteDatabase sqLiteDatabase) {
  @Override public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {}

Next let’s use that to create the database, pass content values into it to insert and then query it.

SQLiteDatabase db = new DatabaseHelper(YOUR_APPLICATION_CONTEXT).getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put("t", "woooooo");
long insert = db.insert("whoopwhoop", "null", cv);
Log.d("HIYA", ""+insert);

Here’s the code to query it. You should be using placeholder values in rawQuery to make it sql injection safe.

Cursor query = db.rawQuery("SELECT * FROM whoopwhoop", null);
Log.d("HIYA", ""+query.getInt(query.getColumnIndex("_id")));
Log.d("HIYA", query.getString(query.getColumnIndex("t")));
android android-sqlite android-database

Postgresql dump all functions to a file

If you want to dump all the functions in your schema, public in the case, do this:

SELECT pg_get_functiondef(f.oid)
FROM pg_catalog.pg_proc f
INNER JOIN pg_catalog.pg_namespace n ON (f.pronamespace = n.oid)
WHERE n.nspname = 'public';

If you issue \o somefile.sql and then issue the above you’ll save the functions to a file.

postgresql postgresql-functions

Alter a Postgresql constraint on a table

If you have a constraint you wish to alter, you need to drop it and re-add it.

First let’s look at the constraint with \d+ yourtable

\d+ youtable
Foreign-key constraints:
"constraint_name" FOREIGN KEY (some_id) REFERENCES yourtable(some_id)

We can we see the, in this case, foreign key constraint we want to alter. We’ll use text above later.

Now let’s drop the constraint.

alter table yourtable drop constraint constraint_name;

Now let’s copy the text we saw above, and paste it after the text alter table yourtable add constraint.

alter table yourtable add constraint "constraint_name" FOREIGN KEY (some_id) REFERENCES yourtable(some_id) on delete cascade;

In our case, we altered the constraint by adding on delete cascade.

postgresql postgresql-alter-table postgresql-constraints

Page 1 of 3