Open-source APMGolang ORMGolang HTTP routerBlog
Golang ClickHouse

ClickHouse SQL Placeholders

Introduction

go-clickhouse recognizes ? in queries as placeholders and replaces them with provided args. It quotes and escapes stringly values and removes null bytes.

Basic and positional placeholders

To use basic placeholders:

// SELECT 'foo', 'bar'
db.ColumnExpr("?, ?", 'foo', 'bar')

To use positional placeholders:

// SELECT 'foo', 'bar', 'foo'
db.ColumnExpr("?0, ?1, ?0", 'foo', 'bar')

ch.Ident

To quote SQL identifiers, for example, a column or a table name, use ch.Ident:

q.ColumnExpr("? = ?", ch.Ident("foo"), "bar")

"foo" = 'bar'

ch.Safe

To disable quotation altogether, use ch.Safe:

q.TableExpr("(?) AS foo", ch.Safe("numbers(1000)"))

FROM (numbers(1000)) AS foo

IN

Provides a ch.In helper to generate IN (...) queries:

// WHERE foo IN ('hello', 'world')
q.Where("foo IN (?)", ch.In([]string{"hello", "world"}))

For composite (multiple) keys you can use nested slices:

// WHERE (foo, bar) IN (('hello', 'world'), ('hell', 'yeah'))
q.Where("(foo, bar) IN (?)", ch.In([][]string{
	{"hello", "world"},
	{"hell", "yeah"},
}))

Model placeholders

go-clickhouse also supports the following model placeholders:

  • ?TableName - model table name, for example, "users".
  • ?TableAlias - model table alias, for example, "user".
  • ?PKs - table primary keys, for example, "id"
  • ?TablePKs - table primary keys with the alias, for example, "user"."id"
  • ?Columns - table columns, for example, "id", "name", "emails".
  • ?TableColumns - table columns with the alias, for example, "user"."id", "user"."name", "user"."emails".

See placeholdersopen in new window example for details.

Global placeholders

Ch also supports global placeholders:

// db1 and db2 share the same *sql.DB, but have different named args.
db1 := db.WithNamedArg("DB", "foo")
db2 := db.WithNamedArg("DB", "bar")

// FROM foo.table
db1.NewSelect().TableExpr("?DB.table")

// FROM bar.table
db2.NewSelect().TableExpr("?DB.table")
Last Updated: