jkatz

       # Maps PostgreSQL-specific data types to logical Rails types.
        def simplified_type(field_type)
          case field_type
            # Numeric and monetary types
            when /^(?:real|double precision)$/
              :float
            # Monetary types
            when /^money$/
              :decimal
            # Character types

jkatz

SELECT a.*, my_list.list
FROM (
  SELECT a.id, array_agg(b.id) AS list
  FROM a
  JOIN b ON a.id = b.a_id
  GROUP BY a.id
) my_list
JOIN a ON a.id = my_list.id;

jkatz

When you run pg_upgrade, you must provide the following information:
  the data directory for the old cluster  (-d OLDDATADIR)
  the data directory for the new cluster  (-D NEWDATADIR)
  the "bin" directory for the old version (-b OLDBINDIR)
  the "bin" directory for the new version (-B NEWBINDIR)

For example:
  pg_upgrade -d oldCluster/data -D newCluster/data -b oldCluster/bin -B newCluster/bin
or
  $ export OLDDATADIR=oldCluster/data

jkatz

CREATE TABLE a (id serial, x int);

INSERT INTO a (x) VALUES (5);
INSERT INTO a (x) VALUES (7);
INSERT INTO a (x) VALUES (11);
INSERT INTO a (x) VALUES (13);

--- first data
SELECT * FROM a;

jkatz

// source: http://stackoverflow.com/questions/149055/how-can-i-format-numbers-as-money-in-javascript
Number.prototype.formatMoney = function(c, d, t){
  var n = this, c = isNaN(c = Math.abs(c)) ? 2 : c, d = d == undefined ? "," : d, t = t == undefined ? "." : t, s = n < 0 ? "-" : "", i = parseInt(n = Math.abs(+n || 0).toFixed(c)) + "", j = (j = i.length) > 3 ? j % 3 : 0;
  return s + (j ? i.substr(0, j) + t : "") + i.substr(j).replace(/(\d{3})(?=\d)/g, "$1" + t) + (c ? d + Math.abs(n - i).toFixed(c).slice(2) : "");
};

jkatz

Notes:
    - R headers are required. Download and install R prior to building
      PL/R.

    - R must have been built with the --enable-R-shlib option when it was
      configured, in order for the libR shared object library to be available.

    - R_HOME must be defined in the environment of the user under which
      PostgreSQL is started, before the postmaster is started. Otherwise
      PL/R will refuse to load.

jkatz

WITH first AS (
  INSERT INTO y
  SELECT * FROM
  generate_series(1, 100)
  RETURNING a
)
SELECT *
FROM first f1
JOIN first f2 ON f1.a = f2.a;

jkatz

$ createdb test2
$ psql test2
psql (9.2.0)
Type "help" for help.

test2=# DROP DATABASE test2;
ERROR:  cannot drop the currently open database
test2=# 

jkatz

    # only supply or default logfile path when none is given explicitly in
    # postgresql.conf
    @options = ($pg_ctl, 'start', '-D', $info{'pgdata'});
    $logsize = 0;
    if ($info{'logfile'}) {
        push @options, ('-l', $info{'logfile'});
        # remember current size of the log
        $logsize = (stat $info{'logfile'})[7];
    }

jkatz

WITH old_rows AS (
  SELECT a, b, c
  FROM update_me
  WHERE blah = true
), new_rows AS (
  UPDATE update_me
  SET a = 4, b = 5, c = 6
  FROM old_rows
  WHERE blah = true
)