Supported SQL statements

Devart ODBC Driver for PostgreSQL supports most of the PostgreSQL SQL dialect and a large part of the SQL standard. The supported statements fall into the following categories: Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), Transaction Control Language (TCL), and utility commands.

Data Manipulation Language (DML)

DML statements manage data stored in database tables.

• SELECT – Retrieves rows from one or more tables, with support for WHERE, HAVING, and JOIN operations, subqueries, and Common Table Expressions (WITH queries).
• INSERT – Adds new rows to a table.
• UPDATE – Modifies existing rows in a table.
• DELETE – Removes existing rows from a table.
• MERGE – Performs conditional INSERT, UPDATE, or DELETE operations on a target table based on the result of a join with a source table.
• TRUNCATE – Removes all rows from a table. This is a DDL statement in the SQL standard but is often grouped with DML because of its effect on data.

Data Definition Language (DDL)

DDL statements define and modify database objects.

• CREATE – Creates database objects such as DATABASE, TABLE, INDEX, VIEW, FUNCTION, TYPE, and others.
• ALTER – Changes the definition of an existing database object.
• DROP – Deletes database objects.
• RENAME – Renames database objects.

Data Control Language (DCL)

DCL statements control access to database objects.

• GRANT – Assigns access privileges on the database and its objects to users or roles.
• REVOKE – Removes previously granted privileges.

Transaction Control Language (TCL)

TCL statements control transactions and help maintain data integrity.

• BEGIN – Starts a transaction.
• COMMIT – Saves all changes made in the current transaction.
• ROLLBACK – Reverts all changes made in the current transaction.
• SAVEPOINT – Creates a named point within a transaction that you can roll back to later.

Utility and other commands

Devart ODBC Driver for PostgreSQL also supports a range of utility commands for administration and runtime control:

• ANALYZE – Collects statistics for the query planner.
• EXPLAIN – Shows the execution plan for a statement.
• PREPARE, EXECUTE, DEALLOCATE – Manage prepared statements.
• CALL – Executes stored procedures.
• COPY – Loads data into a table or exports data from a table.
• LISTEN, NOTIFY – Provide asynchronous notification and messaging.
• LOCK – Acquires explicit table locks.
• VACUUM – Reclaims storage and updates statistics.
• SET, SHOW, RESET – Manage run-time configuration parameters.

For detailed syntax, options, and standard conformance of each statement, see the PostgreSQL official documentation.