Tagged: sql

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

Postgresql: Window functions intro

The group by SQL statement winds up all rows based on a column. Then you use aggregate functions to output something based on the wound up rows.

Sometimes you don’t want to wind up all the rows. You may want them to display as normal. But calculate a value on a load of rows (say, a ‘window’ of rows…).

This is where Window functions come into play. They allow you to specify a load of (or window of) rows on which to use a aggregate function.

Let’s say we have this table.

 name  | department | salary 
 chris | IT         |  30000
 jason | IT         |  35000
 kate  | IT         |  22000
 david | sales      |  40000
 matt  | sales      |  45000
 james | sales      |  50000

We could use this group by SQL statement to output salary averages of the departments.

select department, avg(salary) from employees group by department;
 department |        avg
 IT         | 29000.000000000000
 sales      | 45000.000000000000

But we may not want to wind all the columns up, just diplay an average column after each row. In that case:

   select name, department, salary, avg(salary) over (partition by department) from employees;
    name  | department | salary |        avg         
    chris | IT         |  30000 | 29000.000000000000
    jason | IT         |  35000 | 29000.000000000000
    kate  | IT         |  22000 | 29000.000000000000
    david | sales      |  40000 | 45000.000000000000
    matt  | sales      |  45000 | 45000.000000000000
    james | sales      |  50000 | 45000.000000000000

We use the ‘over’ keyword to specify the column we want to partition our window by.

And, voila.

postgresql postgresql-window

Page 1 of 3