Based on documentation of postgres 9.1 at
http://www.postgresql.org/docs/9.3/static/functions-matching.html
975715
@Override
	public String sqlRegex(String columnname, String pattern, boolean caseinSensitive, boolean multiLine, boolean dotAllMode) {

        if(quotes.matcher(pattern).matches() ) {
            pattern = pattern.substring(1, pattern.length() - 1); // remove the
            // enclosing
            // quotes
        }
		//An ARE can begin with embedded options: a sequence (?n)  specifies options affecting the rest of the RE. 
		//n is newline-sensitive matching
		String flags = "";
		if (multiLine)
			flags = "(?w)"; //inverse partial newline-sensitive matching
		else
		if(dotAllMode)
			flags = "(?p)"; //partial newline-sensitive matching
		
		return columnname + " ~" + ((caseinSensitive)? "* " : " ") + "'"+ ((multiLine && dotAllMode)? "(?n)" : flags) + pattern + "'";
	}
156676
function baseMatches(source) {
  var matchData = _getMatchData(source);
  if (matchData.length == 1 && matchData[0][2]) {
    return _matchesStrictComparable(matchData[0][0], matchData[0][1]);
  }
  return function(object) {
    return object === source || _baseIsMatch(object, source, matchData);
  };
}
334392
function Match(path, route, match, query, valueDecoders) {
  this.path = path;
  this.route = route;
  this.match = match;
  this.query = query;

  if (valueDecoders) {
    Object.keys(match || {}).forEach(function(key) {
      if (typeof valueDecoders === 'function') {
        match[key] = valueDecoders(key, match[key]);
        return;
      }
      var fn = valueDecoders.hasOwnProperty(key) && valueDecoders[key];
      if (typeof fn === 'function') {
        match[key] = fn(match[key]);
      }
    });
  }

  this.unmatchedPath = this.match && this.match._ ?
    this.match._[0] :
    null;

  this.matchedPath = this.unmatchedPath ?
    this.path.substring(0, this.path.length - this.unmatchedPath.length) :
    this.path;
}
1223532
def p_joinx(self,t):
    # todo: support join types http://www.postgresql.org/docs/9.4/static/queries-table-expressions.html#QUERIES-JOIN
    
    if len(t)==4: t[0] = JoinX(t[1],t[3],None,t[2])
    elif len(t)==6: t[0] = JoinX(t[1],t[3],t[5],t[2])
    else: raise NotImplementedError('todo: join .. using')
541431
def is_pg_at_least_nine_two(self):
        

        if self._is_pg_at_least_nine_two is None:
            results = self.version()
            regex = re.compile("PostgreSQL (\d+\.\d+\.\d+) on")
            matches = regex.match(results[0].version)
            version = matches.groups()[0]

            if version > '9.2.0':
                self._is_pg_at_least_nine_two = True
            else:
                self._is_pg_at_least_nine_two = False

        return self._is_pg_at_least_nine_two
648033
def build_db_query(fields_names, field_values):
    
    if isinstance(field_values, string_types):
        field_values = [field_values]

    if len(fields_names) != len(field_values):
        raise ValueError('Error: unable to build a primary key query due '
                         'to mismatch in number of fields {0} vs {1}'
                         .format(len(fields_names), len(field_values)))

    query = dict()
    for k, v in zip(fields_names, field_values):
        query[k] = v
    return query
525780
def compile(self):
        
        params = self.column_params + self.join_params + self.params
        if self.limit and self.limit >= 0:
            self.sql_limit = 'LIMIT ?'
            params += [self.limit]
        return (self.sql, params, self.keys)
1306747
function (e, table) {
            var h, i, args;
            if (isString(e)) {
                //this should not be hit but here just for completeness
                return this.stringToIdentifier(e);
            } else if (isArray(e)) {
                return e.map(function (exp) {
                    return this._qualifiedExpression(exp, table);
                }, this);
            } else if (isInstanceOf(e, Identifier)) {
                return new QualifiedIdentifier(table, e);
            } else if (isInstanceOf(e, OrderedExpression)) {
                return new OrderedExpression(this._qualifiedExpression(e.expression, table), e.descending,
                    {nulls: e.nulls});
            } else if (isInstanceOf(e, AliasedExpression)) {
                return new AliasedExpression(this._qualifiedExpression(e.expression, table), e.alias);
            } else if (isInstanceOf(e, CaseExpression)) {
                args = [this._qualifiedExpression(e.conditions, table), this._qualifiedExpression(e.def, table)];
                if (e.hasExpression) {
                    args.push(this._qualifiedExpression(e.expression, table));
                }
                return CaseExpression.fromArgs(args);
            } else if (isInstanceOf(e, Cast)) {
                return new Cast(this._qualifiedExpression(e.expr, table), e.type);
            } else if (isInstanceOf(e, SQLFunction)) {
                return SQLFunction.fromArgs([e.f].concat(this._qualifiedExpression(e.args, table)));
            } else if (isInstanceOf(e, ComplexExpression)) {
                return ComplexExpression.fromArgs([e.op].concat(this._qualifiedExpression(e.args, table)));
            } else if (isInstanceOf(e, SubScript)) {
                return new SubScript(this._qualifiedExpression(e.f, table), this._qualifiedExpression(e.sub, table));
            } else if (isInstanceOf(e, PlaceHolderLiteralString)) {
                args = [];
                var eArgs = e.args;
                if (isHash(eArgs)) {
                    h = {};
                    for (i in eArgs) {
                        h[i] = this._qualifiedExpression(eArgs[i], table);
                    }
                    args = h;
                } else {
                    args = this._qualifiedExpression(eArgs, table);
                }
                return new PlaceHolderLiteralString(e.str, args, e.parens);

            } else if (isHash(e)) {
                h = {};
                for (i in e) {
                    h[this._qualifiedExpression(i, table) + ""] = this._qualifiedExpression(e[i], table);
                }
                return h;
            } else {
                return e;
            }
        }
244140
function baseMatches(source) {
    var props = keys(source);
    return function(object) {
      var length = props.length;
      if (object == null) {
        return !length;
      }
      object = Object(object);
      while (length--) {
        var key = props[length];
        if (!(key in object &&
              baseIsEqual(source[key], object[key], undefined, UNORDERED_COMPARE_FLAG | PARTIAL_COMPARE_FLAG)
            )) {
          return false;
        }
      }
      return true;
    };
  }
46635
function LineStatGetter(model, params, opts) {
  BaseStatGetter.call(this, model, params, opts);

  var schema = Interface.Schemas.schemas[model.name];
  var timeRange = params['time_range'].toLowerCase();

  function getAggregateField() {
    // NOTICE: As MySQL cannot support COUNT(table_name.*) syntax, fieldName
    //         cannot be '*'.
    var fieldName = params['aggregate_field'] || schema.primaryKeys[0] ||
      schema.fields[0].field;
    return schema.name + '.' + fieldName;
  }

  function getGroupByDateField() {
    var fieldName = params['group_by_date_field'];
    return schema.name + '.' + fieldName;
  }

  var groupByDateField = getGroupByDateField();

  function getGroupByDateFieldFormatedForMySQL(timeRange) {
    switch (timeRange) {
      case 'day':
        return opts.sequelize.fn('DATE_FORMAT', opts.sequelize.col(groupByDateField),
          '%Y-%m-%d 00:00:00');
      case 'week':
        var groupByDateFieldFormated = '`' + groupByDateField.replace('.', '`.`') + '`';
        return opts.sequelize.literal('DATE_FORMAT(DATE_SUB(' + groupByDateFieldFormated +
          ', INTERVAL ((7 + WEEKDAY(' + groupByDateFieldFormated + ')) % 7) DAY), ' +
          '\'%Y-%m-%d 00:00:00\')');
      case 'month':
        return opts.sequelize.fn('DATE_FORMAT', opts.sequelize.col(groupByDateField),
          '%Y-%m-01 00:00:00');
      case 'year':
        return opts.sequelize.fn('DATE_FORMAT', opts.sequelize.col(groupByDateField),
          '%Y-01-01 00:00:00');
    }
  }

  function getGroupByDateFieldFormatedForMSSQL(timeRange) {
    switch (timeRange) {
      case 'day':
        return opts.sequelize.fn('FORMAT', opts.sequelize.col(groupByDateField),
          'yyyy-MM-dd 00:00:00');
      case 'week':
        var groupByDateFieldFormated = '[' + groupByDateField.replace('.', '].[') + ']';
        return opts.sequelize.literal('FORMAT(DATEADD(DAY, -DATEPART(dw,' +
          groupByDateFieldFormated + '),' + groupByDateFieldFormated + '), \'yyyy-MM-dd 00:00:00\')');
      case 'month':
        return opts.sequelize.fn('FORMAT', opts.sequelize.col(groupByDateField),
          'yyyy-MM-01 00:00:00');
      case 'year':
        return opts.sequelize.fn('FORMAT', opts.sequelize.col(groupByDateField),
          'yyyy-01-01 00:00:00');
    }
  }

  function getGroupByDateFieldFormatedForSQLite(timeRange) {
    switch (timeRange) {
      case 'day': {
        return opts.sequelize.fn('STRFTIME', '%Y-%m-%d',
          opts.sequelize.col(groupByDateField));
      }
      case 'week': {
        return opts.sequelize.fn('STRFTIME', '%Y-%W',
          opts.sequelize.col(groupByDateField));
      }
      case 'month': {
        return opts.sequelize.fn('STRFTIME', '%Y-%m-01',
          opts.sequelize.col(groupByDateField));
      }
      case 'year': {
        return opts.sequelize.fn('STRFTIME', '%Y-01-01',
          opts.sequelize.col(groupByDateField));
      }
    }
  }

  function getGroupByDateInterval() {
    if (Database.isMySQL(opts)) {
      return [getGroupByDateFieldFormatedForMySQL(timeRange), 'date'];
    } else if (Database.isMSSQL(opts)) {
      return [getGroupByDateFieldFormatedForMSSQL(timeRange), 'date'];
    } else if (Database.isSQLite(opts)) {
      return [getGroupByDateFieldFormatedForSQLite(timeRange), 'date'];
    } else {
      return [
        opts.sequelize.fn('to_char',
          opts.sequelize.fn('date_trunc', params['time_range'],
            opts.sequelize.literal('"' + getGroupByDateField()
              .replace('.', '"."') + '" at time zone \'' + params.timezone + '\'')),
          'YYYY-MM-DD 00:00:00'
        ),
        'date'
      ];
    }
  }

  function getFormat() {
    switch (timeRange) {
      case 'day': return 'DD/MM/YYYY';
      case 'week': return '[W]w-YYYY';
      case 'month': return 'MMM YY';
      case 'year': return 'YYYY';
    }
  }

  function fillEmptyDateInterval(records) {
    if (records.length) {
      var sqlFormat = 'YYYY-MM-DD 00:00:00';
      if (Database.isSQLite(opts) && timeRange === 'week') {
        sqlFormat = 'YYYY-WW';
      }

      var firstDate = moment(records[0].label, sqlFormat);
      var lastDate = moment(records[records.length - 1].label, sqlFormat);

      for (var i = firstDate ; i.toDate() <= lastDate.toDate() ;
        i = i.add(1, timeRange)) {

        var label = i.format(sqlFormat);
        if (!_.find(records, { label: label })) {
          records.push({ label: label, values: { value: 0 }});
        }
      }

      records = _.sortBy(records, 'label');
      return _.map(records, function (record) {
        return {
          label: moment(record.label, sqlFormat).format(getFormat()),
          values: record.values
        };
      });
    } else {
      return records;
    }
  }

  function getAggregate() {
    return [
      opts.sequelize.fn(params.aggregate.toLowerCase(),
        opts.sequelize.col(getAggregateField())),
      'value'
    ];
  }

  function getIncludes() {
    var includes = [];
    _.values(model.associations).forEach(function (association) {
      if (['HasOne', 'BelongsTo'].indexOf(association.associationType) > -1) {
        includes.push({
          model: association.target.unscoped(),
          as: association.associationAccessor,
          attributes: []
        });
      }
    });

    return includes;
  }

  function getGroupBy() {
    return Database.isMSSQL(opts) ? [getGroupByDateFieldFormatedForMSSQL(timeRange)] : [opts.sequelize.literal('1')];
  }

  function getOrder() {
    return Database.isMSSQL(opts) ? [getGroupByDateFieldFormatedForMSSQL(timeRange)] : [opts.sequelize.literal('1')];
  }

  this.perform = function () {
    return model.unscoped().findAll({
      attributes: [getGroupByDateInterval(), getAggregate()],
      include: getIncludes(),
      where: this.getFilters(),
      group: getGroupBy(),
      order: getOrder(),
      raw: true
    })
    .then(function (records) {
      return P.map(records, function (record) {
        return {
          label: record.date,
          values: { value: parseInt(record.value) }
        };
      });
    })
    .then(function (records) {
      return { value: fillEmptyDateInterval(records) };
    });
  };
}
810507
public void getMatchesSQLClause(StringBuilder builder, String sqlExpression, IPAddressSQLTranslator translator) {
		getSection().getStartsWithSQLClause(builder, sqlExpression, translator);
	}
357018
function match(node, type) {
            let innerStatementNode = node;

            while (innerStatementNode.type === "LabeledStatement") {
                innerStatementNode = innerStatementNode.body;
            }
            if (Array.isArray(type)) {
                return type.some(match.bind(null, innerStatementNode));
            }
            return StatementTypes[type].test(innerStatementNode, sourceCode);
        }
849194
function(db) {
		var methods_uuid = uuid();
		debug.assert(methods_uuid).is('uuid');

		return db.query('CREATE SEQUENCE methods_seq')
			.query(['CREATE TABLE IF NOT EXISTS methods (',
					"	id uuid PRIMARY KEY NOT NULL default uuid_generate_v5('"+methods_uuid+"', nextval('methods_seq'::regclass)::text),",
					'	types_id uuid REFERENCES types,',
					'	type text NOT NULL,',
					'	name text NOT NULL,',
					'	body text NOT NULL,',
					'	meta json NOT NULL,',
					'	active BOOLEAN DEFAULT TRUE,',
					'	created timestamptz NOT NULL default now(),',
					'	modified timestamptz NOT NULL default now(),',
					'	CONSTRAINT active_not_false CHECK(active != false)',
					')'
				].join('\n'))
			.query('ALTER SEQUENCE methods_seq OWNED BY methods.id')
			.query('CREATE INDEX methods_types_id ON methods (types_id)')
			.query('CREATE INDEX methods_types_id_name ON methods (types_id,name)')
			.query('CREATE INDEX methods_type ON methods (type)')
			.query('CREATE INDEX methods_type_name ON methods (type,name)')
			.query('CREATE UNIQUE INDEX ON methods USING btree(types_id, name, active nulls LAST);')
			.query('CREATE TRIGGER methods_modified BEFORE UPDATE ON methods FOR EACH ROW EXECUTE PROCEDURE moddatetime (modified)')
			.query(
				[
	'CREATE OR REPLACE FUNCTION ensure_only_one_active_row_trigger()',
	'RETURNS trigger',
	'AS $function$',
	'BEGIN',
	  // Disable the currently enabled row
	"  IF (TG_OP = 'UPDATE') THEN",
	     // Nothing to do if updating the row currently enabled'
	"    IF (OLD.active = true AND OLD.name = NEW.name) THEN",
	'      RETURN NEW;',
	'    END IF;',
	"    EXECUTE format('UPDATE %I.%I SET active = null WHERE active = true AND name = %L;', TG_TABLE_SCHEMA, TG_TABLE_NAME, OLD.name);",
	"    IF (OLD.name != NEW.name) THEN",
	"      EXECUTE format('UPDATE %I.%I SET active = null WHERE active = true AND name = %L;', TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW.name);",
	'    END IF;',
	'  ELSE',
	"    EXECUTE format('UPDATE %I.%I SET active = null WHERE active = true AND name = %L;', TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW.name);",
	'  END IF;',
	   // Enable new row
	'  NEW.active := true;',
	'  RETURN NEW;',
	'END;',
	'$function$',
	'LANGUAGE plpgsql'
				].join('\n')
			)
			.query('CREATE TRIGGER methods_only_one_active_row'+
				' BEFORE INSERT OR UPDATE OF active ON methods'+
				' FOR EACH ROW WHEN (NEW.active = true)'+
				' EXECUTE PROCEDURE ensure_only_one_active_row_trigger()'
			);
	}
558531
def replace_subqueries(ex,tables,table_ctor):
  ""
  # http://www.postgresql.org/docs/9.1/static/sql-expressions.html#SQL-SYNTAX-SCALAR-SUBQUERIES
  # see here for subquery conditions that *do* use multi-rows. ug. http://www.postgresql.org/docs/9.1/static/functions-subquery.html
  if isinstance(ex,sqparse2.SelectX):
    old_tables, ex.tables = ex.tables, [] # we *don't* recurse into tables because selects in here get transformed into tables
  for path in treepath.sub_slots(ex, lambda x:isinstance(x,sqparse2.SelectX)):
    ex[path] = sqparse2.Literal(flatten_scalar(run_select(ex[path], tables, table_ctor)))
  if isinstance(ex,sqparse2.SelectX): ex.tables = old_tables
  return ex
1341084
def select_functions(expr):
    
    body = Group(expr)
    return Group(
        function("timestamp", body, caseless=True)
        | function("ts", body, caseless=True)
        | function("utctimestamp", body, caseless=True)
        | function("utcts", body, caseless=True)
        | function("now", caseless=True)
        | function("utcnow", caseless=True)
    ).setResultsName("function")
1036746
def as_sql(self, qn, connection):
        
        engine = connection.settings_dict['ENGINE'].rsplit('.', -1)[-1]
        if engine.startswith('postgres'):
            XOR_OPERATOR = '#'
        elif engine.startswith('sqlite'):
            raise NotImplementedError
        else:
            XOR_OPERATOR = '^'

        if self.bit:
            return ("%s.%s | %d" % (qn(self.table_alias), qn(self.column), self.bit.mask),
                    [])
        return ("%s.%s %s %d" % (qn(self.table_alias), qn(self.column), XOR_OPERATOR, self.bit.mask),
                [])
485522
function pgsqlBulkUpsert(model, data) {
  var queries = [];

  // Transform data
  _.each(data, function(d) {
    // TODO: Postgres does not have easy UPSERT syntax, the ON CONFLICT UPDATE requires specific columns
    var query = "INSERT INTO " + model.tableName + " ([[COLUMNS]]) VALUES ([[VALUES]])";
    query = query.replace("[[COLUMNS]]", _.keys(d).join(", "));
    query = query.replace("[[VALUES]]", _.map(d, function(v) {
      return sequelizeSS.escape(v, undefined, "postgres");
    }).join(", "));
    queries.push(query);
  });

  // Create final output and format
  return queries.join("; ");
}