QueryBuilder

API reference › yii › db › pgsql › QueryBuilder

class	`yii\db\pgsql\QueryBuilder`
Extends	`yii\db\QueryBuilder` » `yii\base\BaseObject`
Implements	`yii\base\Configurable`
Since	2.0
Source	`framework/db/pgsql/QueryBuilder.php`

QueryBuilder is the query builder for PostgreSQL databases (version 14 and above).

Properties

Property	Type	Description	Defined by
`$db`	`yii\db\Connection`	The database connection. Default: `null`.	`yii\db\QueryBuilder`
`$separator`	`string`	The separator between different fragments of a SQL statement. Default: `' '`.	`yii\db\QueryBuilder`
`$typeMap`	`array`	Mapping from abstract column types (keys) to physical column types (values). Default: [\yii\db\pgsql\Schema::TYPE_PK => 'serial NOT NULL PRIMARY KEY', \yii\db\pgsql\Schema::TYPE_UPK => 'serial NOT NULL PRIMARY KEY', \yii\db\pgsql\Schema::TYPE_BIGPK => 'bigserial NOT NULL PRIMARY KEY', \yii\db\pgsql\Schema::TYPE_UBIGPK => 'bigserial NOT NULL PRIMARY KEY', \yii\db\pgsql\Schema::TYPE_CHAR => 'char(1)', \yii\db\pgsql\Schema::TYPE_STRING => 'varchar(255)', \yii\db\pgsql\Schema::TYPE_TEXT => 'text', \yii\db\pgsql\Schema::TYPE_TINYINT => 'smallint', \yii\db\pgsql\Schema::TYPE_SMALLINT => 'smallint', \yii\db\pgsql\Schema::TYPE_INTEGER => 'integer', \yii\db\pgsql\Schema::TYPE_BIGINT => 'bigint', \yii\db\pgsql\Schema::TYPE_FLOAT => 'double precision', \yii\db\pgsql\Schema::TYPE_DOUBLE => 'double precision', \yii\db\pgsql\Schema::TYPE_DECIMAL => 'numeric(10,0)', \yii\db\pgsql\Schema::TYPE_DATETIME => 'timestamp(0)', \yii\db\pgsql\Schema::TYPE_TIMESTAMP => 'timestamp(0)', \yii\db\pgsql\Schema::TYPE_TIME => 'time(0)', \yii\db\pgsql\Schema::TYPE_DATE => 'date', \yii\db\pgsql\Schema::TYPE_BINARY => 'bytea', \yii\db\pgsql\Schema::TYPE_BOOLEAN => 'boolean', \yii\db\pgsql\Schema::TYPE_MONEY => 'numeric(19,4)', \yii\db\pgsql\Schema::TYPE_JSON => 'jsonb'].	`QueryBuilder`
`$conditionClasses` protected	`array`	Map of condition aliases to condition classes. Default: `[]`. Available since 2.0.14.	`yii\db\QueryBuilder`
`$expressionBuilders` protected	`string[]\|yii\db\ExpressionBuilderInterface[]`	Maps expression class to expression builder class. Default: `[]`. Available since 2.0.14.	`yii\db\QueryBuilder`

Methods

Method	Description	Defined by
`__construct()`	Constructor.	`yii\db\QueryBuilder`
`__call()`	Calls the named method which is not a class method.	`yii\base\BaseObject`
`__get()`	Returns the value of an object property.	`yii\base\BaseObject`
`__isset()`	Checks if a property is set, i.e. defined and not null.	`yii\base\BaseObject`
`__set()`	Sets value of an object property.	`yii\base\BaseObject`
`__unset()`	Sets an object property to null.	`yii\base\BaseObject`
`addCheck()`	Creates a SQL command for adding a check constraint to an existing table.	`yii\db\QueryBuilder`
`addColumn()`	Builds a SQL statement for adding a new DB column.	`yii\db\QueryBuilder`
`addCommentOnColumn()`	Builds a SQL command for adding comment to column.	`yii\db\QueryBuilder`
`addCommentOnTable()`	Builds a SQL command for adding comment to table.	`yii\db\QueryBuilder`
`addDefaultValue()`	Creates a SQL command for adding a default value constraint to an existing table.	`yii\db\QueryBuilder`
`addForeignKey()`	Builds a SQL statement for adding a foreign key constraint to an existing table.	`yii\db\QueryBuilder`
`addPrimaryKey()`	Builds a SQL statement for adding a primary key constraint to an existing table.	`yii\db\QueryBuilder`
`addUnique()`	Creates a SQL command for adding an unique constraint to an existing table.	`yii\db\QueryBuilder`
`alterColumn()`	Builds a SQL statement for changing the definition of a column.	`QueryBuilder`
`batchInsert()`	Generates a batch INSERT SQL statement.	`QueryBuilder`
`bindParam()`	Helper method to add $value to $params array using `PARAM_PREFIX`.	`yii\db\QueryBuilder`
`build()`	Generates a SELECT SQL statement from a `Query` object.	`yii\db\QueryBuilder`
`buildColumns()`	Processes columns and properly quotes them if necessary.	`yii\db\QueryBuilder`
`buildCondition()`	Parses the condition specification and generates the corresponding SQL expression.	`yii\db\QueryBuilder`
`buildExpression()`	Builds given $expression	`yii\db\QueryBuilder`
`buildFrom()`		`yii\db\QueryBuilder`
`buildGroupBy()`		`yii\db\QueryBuilder`
`buildHaving()`		`yii\db\QueryBuilder`
`buildJoin()`		`yii\db\QueryBuilder`
`buildLimit()`	Builds the LIMIT and OFFSET clauses for a SELECT query using the PostgreSQL `OFFSET ... FETCH` syntax.	`QueryBuilder`
`buildOrderBy()`		`yii\db\QueryBuilder`
`buildOrderByAndLimit()`	Builds the ORDER BY and LIMIT/OFFSET clauses and appends them to the given SQL.	`yii\db\QueryBuilder`
`buildSelect()`		`yii\db\QueryBuilder`
`buildUnion()`		`yii\db\QueryBuilder`
`buildUnionOrderByAndLimit()`	Builds the global ORDER BY and LIMIT/OFFSET clauses for a UNION query.	`yii\db\QueryBuilder`
`buildWhere()`		`yii\db\QueryBuilder`
`buildWithQueries()`		`yii\db\QueryBuilder`
`canGetProperty()`	Returns a value indicating whether a property can be read.	`yii\base\BaseObject`
`canSetProperty()`	Returns a value indicating whether a property can be set.	`yii\base\BaseObject`
`checkIntegrity()`	Builds a SQL statement for enabling or disabling integrity checks.	`QueryBuilder`
`className()` static	Returns the fully qualified name of this class.	`yii\base\BaseObject`
`createConditionFromArray()`	Transforms $condition defined in array format (as described in `Query::where()` to instance of ConditionInterface according to `$conditionClasses` map.	`yii\db\QueryBuilder`
`createIndex()`	Builds a SQL statement for creating a new index.	`QueryBuilder`
`createTable()`	Builds a SQL statement for creating a new DB table.	`yii\db\QueryBuilder`
`createView()`	Creates a SQL View.	`yii\db\QueryBuilder`
`delete()`	Creates a DELETE SQL statement.	`yii\db\QueryBuilder`
`dropCheck()`	Creates a SQL command for dropping a check constraint.	`yii\db\QueryBuilder`
`dropColumn()`	Builds a SQL statement for dropping a DB column.	`yii\db\QueryBuilder`
`dropCommentFromColumn()`	Builds a SQL command for adding comment to column.	`yii\db\QueryBuilder`
`dropCommentFromTable()`	Builds a SQL command for adding comment to table.	`yii\db\QueryBuilder`
`dropDefaultValue()`	Creates a SQL command for dropping a default value constraint.	`yii\db\QueryBuilder`
`dropForeignKey()`	Builds a SQL statement for dropping a foreign key constraint.	`yii\db\QueryBuilder`
`dropIndex()`	Builds a SQL statement for dropping an index.	`QueryBuilder`
`dropPrimaryKey()`	Builds a SQL statement for removing a primary key constraint to an existing table.	`yii\db\QueryBuilder`
`dropTable()`	Builds a SQL statement for dropping a DB table.	`yii\db\QueryBuilder`
`dropUnique()`	Creates a SQL command for dropping an unique constraint.	`yii\db\QueryBuilder`
`dropView()`	Drops a SQL View.	`yii\db\QueryBuilder`
`executeResetSequence()`	Execute a SQL statement for resetting the sequence value of a table’s primary key.	`yii\db\QueryBuilder`
`getColumnType()`	Converts an abstract column type into a physical column type.	`yii\db\QueryBuilder`
`getExpressionBuilder()`	Gets object of `ExpressionBuilderInterface` that is suitable for $expression.	`yii\db\QueryBuilder`
`hasMethod()`	Returns a value indicating whether a method is defined.	`yii\base\BaseObject`
`hasProperty()`	Returns a value indicating whether a property is defined.	`yii\base\BaseObject`
`init()`	Initializes the object.	`yii\db\QueryBuilder`
`insert()`	Creates an INSERT SQL statement.	`QueryBuilder`
`renameColumn()`	Builds a SQL statement for renaming a column.	`yii\db\QueryBuilder`
`renameTable()`	Builds a SQL statement for renaming a DB table.	`QueryBuilder`
`resetSequence()`	Creates a SQL statement for resetting the sequence value of a table’s primary key.	`QueryBuilder`
`selectExists()`	Creates a SELECT EXISTS() SQL statement.	`yii\db\QueryBuilder`
`setConditionClasses()`	Setter for `$conditionClasses` property.	`yii\db\QueryBuilder`
`setExpressionBuilders()`	Setter for `$expressionBuilders` property.	`yii\db\QueryBuilder`
`truncateTable()`	Builds a SQL statement for truncating a DB table.	`QueryBuilder`
`update()`	Creates an UPDATE SQL statement.	`QueryBuilder`
`upsert()`	Creates an SQL statement to insert rows into a database table if they do not already exist (matching unique constraints), or update them if they do.	`QueryBuilder`
`buildLimitOffsetValue()` protected	Builds a PostgreSQL row-limiting value for `OFFSET` / `FETCH` clauses.	`QueryBuilder`
`concatUnionSql()` protected	Concatenates the first `SELECT` statement with the UNION clauses built by `buildUnion()`.	`yii\db\QueryBuilder`
`defaultConditionClasses()` protected	Contains array of default condition classes. Extend this method, if you want to change default condition classes for the query builder. See `$conditionClasses` docs for details.	`QueryBuilder`
`defaultExpressionBuilders()` protected	Contains array of default expression builders. Extend this method and override it, if you want to change default expression builders for this query builder. See `$expressionBuilders` docs for details.	`QueryBuilder`
`extractAlias()` protected	Extracts table alias if there is one or returns false	`yii\db\QueryBuilder`
`hasLimit()` protected	Checks to see if the given limit is effective.	`yii\db\QueryBuilder`
`hasOffset()` protected	Checks to see if the given offset is effective.	`yii\db\QueryBuilder`
`prepareInsertSelectSubQuery()` protected	Prepare select-subquery and field names for INSERT INTO … SELECT SQL statement.	`yii\db\QueryBuilder`
`prepareInsertValues()` protected	Prepares a `VALUES` part for an `INSERT` SQL statement.	`yii\db\QueryBuilder`
`prepareSelectQuery()` protected	Performs driver-specific preparation after `Query::prepare()`.	`yii\db\QueryBuilder`
`prepareUpdateSets()` protected	Prepares a `SET` parts for an `UPDATE` SQL statement.	`yii\db\QueryBuilder`
`prepareUpsertColumns()` protected		`yii\db\QueryBuilder`
`prepareUpsertSets()` protected	Prepares the `SET` assignments for the update branch of an upsert statement.	`yii\db\QueryBuilder`

Constants

Constant	Value	Description	Defined by
`INDEX_UNIQUE`	`'unique'`	Defines a UNIQUE index for `createIndex()`. Available since 2.0.6.	`QueryBuilder`
`INDEX_B_TREE`	`'btree'`	Defines a B-tree index for `createIndex()`. Available since 2.0.6.	`QueryBuilder`
`INDEX_HASH`	`'hash'`	Defines a hash index for `createIndex()`. Available since 2.0.6.	`QueryBuilder`
`INDEX_GIST`	`'gist'`	Defines a GiST index for `createIndex()`. Available since 2.0.6.	`QueryBuilder`
`INDEX_GIN`	`'gin'`	Defines a GIN index for `createIndex()`. Available since 2.0.6.	`QueryBuilder`
`PARAM_PREFIX`	`':qp'`	The prefix for automatically generated query binding parameters.	`yii\db\QueryBuilder`

Method details

alterColumn()

public function alterColumn(string $table, string $column, yii\db\ColumnSchemaBuilder|string $type): string

Builds a SQL statement for changing the definition of a column.

Supports three input modes for $type.

A ColumnSchemaBuilder produces a structured, multi-action ALTER TABLE statement that changes the column type and applies any nullability, default, CHECK, and UNIQUE state carried by the builder; CHECK and UNIQUE constraints are recreated under stable names derived from the table and column, replacing same-named constraints.
A string starting with a PostgreSQL per-column action keyword; SET ..., DROP ..., RESET (...), RESTART [WITH n], or ADD GENERATED ..., is emitted verbatim as a single action; PostgreSQL parses its contents.
Any other string changes only the column type and no longer implicitly drops DEFAULT or NOT NULL.

Parameters

Parameter	Type	Description
`$table`	`string`	The table whose column is to be changed. The table name will be properly quoted by the method.
`$column`	`string`	The name of the column to be changed. The name will be properly quoted by the method.
`$type`	`yii\db\ColumnSchemaBuilder\|string`	The new column definition. A `ColumnSchemaBuilder` carries the structured column state; a string is resolved through `getColumnType()` to the physical column type unless it is a `SET`/`DROP`/`RESET`/`RESTART`/`ADD GENERATED` action, which is emitted verbatim.

Returns

string: The SQL statement for changing the definition of a column.

batchInsert()

public function batchInsert(mixed $table, mixed $columns, mixed $rows, mixed &$params = []): string

Generates a batch INSERT SQL statement.

For example,

$sql = $queryBuilder->batchInsert('user', ['name', 'age'], [
    ['Tom', 30],
    ['Jane', 20],
    ['Linda', 25],
]);

Note that the values in each row must match the corresponding column names.

The method will properly escape the column names, and quote the values to be inserted.

Parameters

Parameter	Type	Description
`$table`	`mixed`	The table that new rows will be inserted into.
`$columns`	`mixed`	The column names
`$rows`	`mixed`	The rows to be batch inserted into the table
`$params`	`mixed`	The binding parameters. This parameter exists since 2.0.14

Returns

string: The batch INSERT SQL statement

buildLimit()

public function buildLimit(int|yii\db\ExpressionInterface|null $limit, int|yii\db\ExpressionInterface|null $offset): string

Builds the LIMIT and OFFSET clauses for a SELECT query using the PostgreSQL OFFSET ... FETCH syntax.

Parameters

Parameter	Type	Description
`$limit`	`int\|yii\db\ExpressionInterface\|null`	The LIMIT value. `null` or a negative value means no limit; `0` is valid and emits `FETCH NEXT 0 ROWS ONLY`.
`$offset`	`int\|yii\db\ExpressionInterface\|null`	The OFFSET value. `null` or `0` means no offset.

Returns

string: The LIMIT and OFFSET clauses built for PostgreSQL 14+.

checkIntegrity()

public function checkIntegrity(bool $check = true, string $schema = '', string $table = ''): string

Builds a SQL statement for enabling or disabling integrity checks.

Parameters

Parameter	Type	Description
`$check`	`bool`	Whether to enable or disable integrity checks.
`$schema`	`string`	The schema containing the tables.
`$table`	`string`	The table name. An empty string applies the operation to all tables in the schema.

Returns

string: The SQL statement for changing the integrity-check state.

createIndex()

public function createIndex(string $name, string $table, string|array $columns, bool|string $unique = false): string

Builds a SQL statement for creating a new index.

Parameters

Parameter	Type	Description
`$name`	`string`	The name of the index. The name will be properly quoted by the method.
`$table`	`string`	The table that the new index will be created for. The table name will be properly quoted by the method.
`$columns`	`string\|array`	The column(s) that should be included in the index. If there are multiple columns, separate them with commas or use an array to represent them. Each column name will be properly quoted by the method, unless a parenthesis is found in the name.
`$unique`	`bool\|string`	Whether to make this a UNIQUE index constraint. You can pass `true` or `INDEX_UNIQUE` to create a unique index, `false` to make a non-unique index using the default index type, or one of the following constants to specify the index method to use: `INDEX_B_TREE`, `INDEX_HASH`, `INDEX_GIST`, `INDEX_GIN`.

Returns

string: The SQL statement for creating a new index.

See also

https://www.postgresql.org/docs/current/sql-createindex.html

dropIndex()

public function dropIndex(string $name, string $table): string

Builds a SQL statement for dropping an index.

Parameters

Parameter	Type	Description
`$name`	`string`	The name of the index to be dropped. The name will be properly quoted by the method.
`$table`	`string`	The table whose index is to be dropped. The name will be properly quoted by the method.

Returns

string: The SQL statement for dropping an index.

insert()

public function insert(mixed $table, mixed $columns, mixed &$params): string

Creates an INSERT SQL statement.

For example,

$sql = $queryBuilder->insert('user', [
    'name' => 'Sam',
    'age' => 30,
], $params);

The method will properly escape the table and column names.

Parameters

Parameter	Type	Description
`$table`	`mixed`	The table that new rows will be inserted into.
`$columns`	`mixed`	The column data (name => value) to be inserted into the table or instance of Query to perform INSERT INTO … SELECT SQL statement. Passing of Query is available since version 2.0.11.
`$params`	`mixed`	The binding parameters that will be generated by this method. They should be bound to the DB command later.

Returns

string: The INSERT SQL

renameTable()

public function renameTable(string $oldName, string $newName): string

Builds a SQL statement for renaming a DB table.

Parameters

Parameter	Type	Description
`$oldName`	`string`	The table to be renamed. The name will be properly quoted by the method.
`$newName`	`string`	The new table name. The name will be properly quoted by the method.

Returns

string: The SQL statement for renaming a DB table.

resetSequence()

public function resetSequence(string $tableName, int|null $value = null): string

Creates a SQL statement for resetting the sequence value of a table’s primary key.

The sequence will be reset such that the primary key of the next new row inserted will have the specified value or 1.

Parameters

Parameter	Type	Description
`$tableName`	`string`	The name of the table whose primary key sequence will be reset.
`$value`	`int\|null`	The integer value for the primary key of the next new row inserted. If this is not set, the next new row’s primary key will have a value `1`.

Returns

string: The SQL statement for resetting sequence.

Throws

yii\base\InvalidArgumentException: if the table does not exist or there is no sequence associated with the table.

truncateTable()

public function truncateTable(string $table): string

Builds a SQL statement for truncating a DB table.

Explicitly restarts identity for PGSQL to be consistent with other databases which all do this by default.

Parameters

Parameter	Type	Description
`$table`	`string`	The table to be truncated. The name will be properly quoted by the method.

Returns

string: The SQL statement for truncating a DB table.

update()

public function update(mixed $table, mixed $columns, mixed $condition, mixed &$params): string

Creates an UPDATE SQL statement.

For example,

$params = [];
$sql = $queryBuilder->update('user', ['status' => 1], 'age > 30', $params);

The method will properly escape the table and column names.

Parameters

Parameter	Type	Description
`$table`	`mixed`	The table to be updated.
`$columns`	`mixed`	The column data (name => value) to be updated.
`$condition`	`mixed`	The condition that will be put in the WHERE part. Please refer to `Query::where()` on how to specify condition.
`$params`	`mixed`	The binding parameters that will be modified by this method so that they can be bound to the DB command later.

Returns

string: The UPDATE SQL

upsert()

public function upsert(mixed $table, mixed $insertColumns, mixed $updateColumns, mixed &$params): string

Creates an SQL statement to insert rows into a database table if they do not already exist (matching unique constraints), or update them if they do.

For example,

$sql = $queryBuilder->upsert('pages', [
    'name' => 'Front page',
    'url' => 'https://example.com/', // url is unique
    'visits' => 0,
], [
    'visits' => new \yii\db\Expression('visits + 1'),
], $params);

The method will properly escape the table and column names.

Parameters

Parameter	Type	Description
`$table`	`mixed`	The table that new rows will be inserted into/updated in.
`$insertColumns`	`mixed`	The column data (name => value) to be inserted into the table or instance of `Query` to perform `INSERT INTO ... SELECT` SQL statement.
`$updateColumns`	`mixed`	The column data (name => value) to be updated if they already exist. If `true` is passed, the column data will be updated to match the insert column data. If `false` is passed, no update will be performed if the column data already exists.
`$params`	`mixed`	The binding parameters that will be generated by this method. They should be bound to the DB command later.

Returns

string: The resulting SQL.

Throws

yii\base\NotSupportedException: if this is not supported by the underlying DBMS.

Since

2.0.14

See also

https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT

buildLimitOffsetValue()

protected function buildLimitOffsetValue(int|yii\db\Expression $value): string

Builds a PostgreSQL row-limiting value for OFFSET / FETCH clauses.

PostgreSQL allows scalar expressions in OFFSET and FETCH, but arithmetic expressions must be enclosed in parentheses to avoid syntax ambiguity (for example, FETCH NEXT (1 + 1) ROWS ONLY).

Only int and Expression are supported. Other ExpressionInterface implementations (JsonExpression, ArrayExpression, PdoValue) are not valid row-count values and are not handled here; passing them produces undefined SQL.

Parameters

Parameter	Type	Description
`$value`	`int\|yii\db\Expression`	The row-limiting value.

Returns

string: The value SQL.

defaultConditionClasses()

protected function defaultConditionClasses(): array

Contains array of default condition classes. Extend this method, if you want to change default condition classes for the query builder. See $conditionClasses docs for details.

Returns

array

Since

2.0.14

defaultExpressionBuilders()

protected function defaultExpressionBuilders(): array

Contains array of default expression builders. Extend this method and override it, if you want to change default expression builders for this query builder. See $expressionBuilders docs for details.

Returns

array

Since

2.0.14