SQL Syntax Term for 'Where (Col1, Col2) < (Val1, Val2)'

SQL syntax term for 'WHERE (col1, col2) (val1, val2)'

The common short term is just "Row values". Or "Row value comparison" for the operation you demonstrate. That feature has been in the SQL standard since SQL-92 (!). Postgres is currently the only major RDBMS that supports it in all aspects - especially also with optimal index support.

In particular, the expression (col1, col2) < (1, 2) is just shorthand for ROW(col1, col2) < ROW(1, 2) in Postgres.
The expression ROW(col1, col2) is also called row constructor - just like ARRAY[col1, col2] is an array constructor.

It is conveniently short for the more verbose, equivalent expression:

col1 < 1 OR (col1 = 1 AND col2 < 2)

... and Postgres can use an index on (col1, col2) or (col1 DESC, col2 DESC) for this.

And notably distinct from: (!)

col1 < 1 AND  AND col2 < 2

Consider example: (1,1) ...

Here is a presentation by Markus Winand that discusses the feature for pagination in detail:

"Pagination done the PostgreSQL way" on use-the-index-luke.com.

Row value comparison starts on page 20. The support matrix I have been referring to is on page 45.

_{I am in no way affiliated to Markus Winand.}

How do I write an SQL query that will safely insert a record into a variable table name?

If you have the following parameters:

• table - table name
• columns - array of column names or an object with properties
• values - array of corresponding column values

then the simplest approach within pg-promise syntax is as follows:

function insert(table, columns, values) {
    const query = 'INSERT INTO ${table:name} (${columns:name}) VALUES(${values:csv})'; 
    return db.query(query, {table, columns, values});
}

or a shorter syntax:

function insert(table, columns, values) {
    const query = 'INSERT INTO ${table~} (${columns~}) VALUES(${values:csv})'; 
    return db.query(query, {table, columns, values});
}

See SQL Names, CSV Filter.

And from version 7.5.0 it gets even simpler for dynamic objects:

function insert(table, obj) {
    const query = 'INSERT INTO ${table:name} (${obj:name}) VALUES(${obj:csv})'; 
    return db.query(query, {table, obj});
}

Under SQL Names, the first example shows how a column name can be inserted dynamically. Is this insertion something that is done by your library, or does the replacement happen on the Postgres side?

PostgreSQL server does not allow dynamic SQL names, pg-promise implements it internally, providing safe escaping to protect against SQL injection.

SQL: Simple if-then-else-statement without any select

OK, with little bit of searching I found the answer.

On mysql use insert ... on duplicate key update ... command. All you need to do is create a pk or unique index on col1 and col2.

In postgresql you can use upsert (insert ... on conflict update ...) to achieve the same result.

order definition for comparisons in SQL ROW subqueries?

This is documented in the chapter Row Constructor Comparison of the Postgres manual:

For the <, <=, > and >= cases, the row elements are compared
left-to-right, stopping as soon as an unequal or null pair of elements
is found. If either of this pair of elements is null, the result of
the row comparison is unknown (null); otherwise comparison of this
pair of elements determines the result. For example, ROW(1,2,NULL) < ROW(1,3,0) yields true, not null, because the third pair of elements
are not considered.

And:

Note: Prior to PostgreSQL 8.2, the <, <=, > and >= cases were not
handled per SQL specification. A comparison like ROW(a,b) < ROW(c,d)
was implemented as a < c AND b < d whereas the correct behavior is
equivalent to a < c OR (a = c AND b < d).

Which clarifies that the behavior of modern Postgres is according to the SQL standard.

It's basically the same logic as in the ORDER BY clause of a SELECT query: items are compared left to right until the first inequality is found - except for NULL values, which sort last in default ascending order.

Related:

• SQL syntax term for 'WHERE (col1, col2) < (val1, val2)'

java.sql.SQLException Parameter index out of range (1 number of parameters, which is 0)

You will get this error when you call any of the setXxx() methods on PreparedStatement, while the SQL query string does not have any placeholders ? for this.

For example this is wrong:

String sql = "INSERT INTO tablename (col1, col2, col3) VALUES (val1, val2, val3)";
// ...

preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, val1); // Fail.
preparedStatement.setString(2, val2);
preparedStatement.setString(3, val3);

You need to fix the SQL query string accordingly to specify the placeholders.

String sql = "INSERT INTO tablename (col1, col2, col3) VALUES (?, ?, ?)";
// ...

preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, val1);
preparedStatement.setString(2, val2);
preparedStatement.setString(3, val3);

Note the parameter index starts with 1 and that you do not need to quote those placeholders like so:

String sql = "INSERT INTO tablename (col1, col2, col3) VALUES ('?', '?', '?')";

Otherwise you will still get the same exception, because the SQL parser will then interpret them as the actual string values and thus can't find the placeholders anymore.

See also:

• JDBC tutorial - prepared statements

MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET

As far as I can tell, both syntaxes are equivalent. The first is SQL standard, the second is MySQL's extension.

So they should be exactly equivalent performance wise.

http://dev.mysql.com/doc/refman/5.6/en/insert.html says:

INSERT inserts new rows into an existing table. The INSERT ... VALUES and INSERT ... SET forms of the statement insert rows based on explicitly specified values. The INSERT ... SELECT form inserts rows selected from another table or tables.

string replacement problems in mysqldb.exeute

You want to pass the arguments using

cur.execute(stmt, v)

and not

cur.execute(stmt % v)

If you have

"INSERT INTO tab (col1, col2) VALUES (%s, %s)"

and you pass two strings ('val1', 'val2') using %, it will become:

"INSERT INTO tab (col1, col2) VALUES (val1, val2)"

which is interpreted as column names, which obviously do not exist.

If you do

cur.execute(stmt, v)

it will be interpreted correctly as

"INSERT INTO tab (col1, col2) VALUES ('val1', 'val2')"

You can also use

print cur.mogrify(stmt, v)

to see the correctly formatted query as it could be executed in your DB.

Insert Data to SQL Server Compact

Well, for starters: when targeting SQL Server Compact Edition (the *.sdf file), you need to use SqlCeConnection and SqlCeCommand (not SqlConnection or SqlCommand - those are for the full-blown SQL Server, non-compact)

Secondly: your INSERT statement is incorrect - the correct SQL syntax would be:

INSERT INTO Table (col1, col2, ..., colN) 
VALUES(val1, val2, ..., valN)

---

Related Topics