S-SQL Reference Manual

→ string

Convert the given form (a list starting with a keyword) to an SQL query string at compile time, according to the rules described here. For example:

(sql (:select '* :from 'country :where (:= 'a 1)))

 "(SELECT * FROM country WHERE (a = 1))"

but

(sql '(:select '* :from 'country :where (:= 'a 1)))

would throw an error. For the later case you need to use sql-compile.

→ string

This is the run-time variant of the sql macro. It converts the given list to an SQL query, with the same rules except that symbols in this list do not have to be quoted to be interpreted as identifiers. For example:

(sql-compile '(:select '* :from 'country :where (:= 'a 1)))

\"(SELECT * FROM country WHERE (a = 1))\"

but

(sql (:select '* :from 'country :where (:= 'a 1)))

would throw an error. For the later case you need to use sql.

In cases where you do need to build the query at run time, yet you do not want to re-compile it all the time, this function can be used to compile it once and store the result. It takes an S-SQL form, which may contain \[ placeholder symbols, and returns a function that takes one argument for every \]. When called, this returned function produces an SQL string in which the placeholders have been replaced by the values of the arguments.

Modifies the current readtable to add a #Q syntax that is read as (sql …). The character to use can be overridden by passing an argument.

→ string

Escapes a string for inclusion in a PostgreSQL query. A quoted symbol will generate an error. Example:

(sql-escape-string \"Puss in 'Boots'\")

\"E'Puss in ''Boots'''\"

→ string

A generalisation of sql-escape-string looks at the type of the value passed, and properly writes it out it for inclusion in an SQL query. Symbols will be converted to SQL names. Examples:

(sql-escape "tr'-x")

"E'tr''-x'"

(sql-escape (/ 1 13))

"0.0769230769230769230769230769230769230"

(sql-escape #("Baden-Wurttemberg" "Bavaria" "Berlin" "Brandenburg"))

"ARRAY[E'Baden-Wurttemberg', E'Bavaria', E'Berlin', E'Brandenburg']"

When converting symbols to strings, whether to downcase the symbols is set here. The default is to downcase symbols.

Used to configure whether S-SQL will use standard SQL strings (just replace #\' with ''), or backslash-style escaping. Setting this to NIL is always safe, but when the server is configured to allow standard strings (compile-time parameter 'standard_conforming_strings' is 'on', which will become the default in future versions of PostgreSQL), the noise in queries can be reduced by setting this to T.

A set of all Postgresql's reserved words, for automatic escaping. Probably not a good idea to use these words as identifiers anyway. '("all" "analyse" "analyze" "and" "any" "array" "as" "asc" "asymmetric" "authorization" "between" "binary" "both" "case" "cast" "check" "collate" "column" "concurrently" "constraint" "create" "cross" "current-catalog" "current-date" "current-role" "current-schema" "current-time" "current-timestamp" "current-user" "default" "deferrable" "desc" "distinct" "do" "else" "end" "except" "false" "fetch" "filter" "for" "foreign" "freeze" "from" "full" "grant" "group" "having" "ilike" "in" "initially" "inner" "intersect" "into" "is" "isnull" "join" "lateral" "leading" "left" "like" "limit" "localtime" "localtimestamp" "natural" "new" "not" "notnull" "nowait" "null" "off" "offset" "old" "on" "only" "or" "order" "outer" "overlaps" "placing" "primary" "references" "returning" "right" "select" "session-user" "Share" "similar" "some" "symmetric" "table" "then" "to" "trailing" "true" "union" "unique" "user" "using" "variadic" "verbose" "when" "where" "window" "with"))

Determines whether double quotes are added around column, table, and ** function names in queries. Valid values:

• T, in which case every name is escaped,
• NIL, in which case no name is escape,
• :auto, which causes only reserved words to be escaped, or.
• :literal which is the same as :auto except it has added consequence in to-sql-name (see below).

The default value is :auto.

Be careful when binding this with let and such ― since a lot of SQL compilation tends to happen at compile-time, the result might not be what you expect. Mixed case sensitivity is not currently well supported. Postgresql itself will downcase unquoted identifiers. This will be revisited in the future if requested.

→ string

Transform a lisp type into a string containing something SQL understands. Default is to just use the type symbol's name.

→ string

Convert a symbol or string into a name that can be a sql table, column, or operation name. Add quotes when escape-p is true, or escape-p is :auto and the name contains reserved words. Quoted or delimited identifiers can be used by passing :literal as the value of escape-p. If escape-p is :literal, and the name is a string then the string is still escaped but the symbol or string is not downcased, regardless of the setting for *downcase-symbols* and the hyphen and forward slash characters are not replaced with underscores.

Ignore-reserved-words is only used internally for column names which are allowed to be reserved words, but it is not recommended.

→ keyword

Convert a string that represents an SQL identifier to a keyword by uppercasing it and converting the underscores to dashes.

Define simple SQL operators. Arity is one of :unary (like 'not'), :unary-postfix (the operator comes after the operand), :n-ary (like '\+': the operator falls away when there is only one operand), :2+-ary (like '=', which is meaningless for one operand), or :n-or-unary (like '-', where the operator is kept in the unary case). After the arity may follow any number of operators, either just a keyword, in which case the downcased symbol name is used as the SQL operator, or a two-element list containing a keyword and a name string.

This is a type of which only the keyword :null is a member. It is used to represent NULL values from the database.

Creates a select query. The arguments are split on the keywords found among them. The group of arguments immediately after :select is interpreted as the expressions that should be selected. After this, an optional :distinct may follow, which will cause the query to only select distinct rows, or alternatively :distinct-on followed by a group of row names. Next comes the optional keyword :from, followed by at least one table name and then any number of join statements.

Join statements start with one of :join, :left-join, :right-join, :inner-join, :outer-join, :cross-join (or those with -lateral, e.g :join-lateral, :left-join-lateral, :right-join-lateral, :inner-join-lateral, :outer-join-lateral). S-sql will accept :join, but best usage is to explicitly use :inner-join instead.

Then comes a table name or subquery,

Then there is an optional :with-ordinality or :with-ordinality-as alisa

Then the keyword :on or :using, if applicable, and then a form. A join can be preceded by :natural (leaving off the :on clause) to use a natural join.

After the joins an optional :where followed by a single form may occur.

Finally :group-by and :having can optionally be specified. The first takes any number of arguments, and the second only one.

A few examples:

(query (:select 'item :distinct
        :from 'item-table
        :where (:= 'col1 "Albania")))

(query (:select (:+ 'field-1 100) 'field-5
        :from (:as 'my-table 'x)
        :left-join 'your-table
        :on (:= 'x.field-2 'your-table.field-1)
        :where (:not-null 'a.field-3)))

(query (:order-by
         (:select 'regions.name
            (:count 'regions.name)
          :from 'countries 'regions
          :where (:= 'regions.id 'countries.region-id)
          :group-by 'regions.name)
        'regions.name))

(query (:select (:count 'c.id) 'r.name
        :from (:as 'countries 'c)
        :inner-join (:as 'regions 'r)
        :on (:= 'c.region-id 'r.id)
        :group-by 'r.name
        :having (:< (:count 'c.id) 10)))

(query (:select 'i.* 'p.*
        :from (:as 'individual 'i)
        :inner-join (:as 'publisher 'p)
        :using ('individualid)
        :left-join-lateral (:as 'anothertable 'a)
        :on (:= 'a.identifier 'i.individualid)
        :where (:= 'a.something \"something\")))

(query (:select 't1.id 'a.elem 'a.nr
        :from (:as 't12 't1)
        :left-join (:unnest (:string-to-array 't1.elements ","))
        :with-ordinality-as (:a 'elem 'nr)
        :on 't))

Other examples can be found in s-sql/tests/tests.lisp

Allowable join keywords are:

• :left-join
• :right-join
• :inner-join
• :outer-join
• :cross-join
• :join-lateral
• :left-join-lateral (left join with an additional sql keyword LATERAL)
• :right-join-lateral (right join with an additional sql keyword LATERAL)
• :inner-join-lateral (inner join with an additional sql keyword LATERAL)
• :outer-join-lateral (outer join with an additional sql keyword LATERAL)
• :cross-join-lateral (cross join with an additional sql keyword LATERAL)

The lateral joins will not be discussed separately.

These are expanded as infix operators. When meaningful, they allow more than two arguments. :- can also be used as a unary operator to negate a value. Note that the arguments to :union, :union-all, :intersect, and :except should be queries (:select forms).

Note that you'll have to escape pipe characters to enter them as keywords. S-SQL handles the empty keyword symbol (written :||) specially, and treats it like :\|\|, so that it can be written without escapes. With :\|, this doesn't work.

We also have registered operators for Postgis, hstore and cube operations. See the following registration examples from the source code which might be useful if you need operators for other extensions:

  ;; PostGIS operators
(register-sql-operators :2+-ary :&& :&< :|&<\|| :&> :<< :|<<\|| :>> :|@| :|\|&>|
                        :|\|>>| :~= :|@>| :|@<|)

;; hstore operators
(register-sql-operators :2+-ary :-> :=> :? :?& :?\| :|<@| :|#=| :unary :%% :%#)

;; cube operators
(register-sql-operators :2+-ary :&& :|@>| :|<@| :-> :~> :<-> :<#> :<=>)

(query (:select 'countries.name
                :from 'countries 'regions
                :where (:and
                        (:or (:= 'regions.name "North America")
                             (:= 'regions.name "Central America"))
                        (:= 'regions.id 'countries.region-id))))

or using parameterized queries

(query (:select 'countries.name
                :from 'countries 'regions
                :where (:and
                        (:or (:= 'regions.name '$1)
                             (:= 'regions.name '$2))
                        (:= 'regions.id 'countries.region-id)))
  "North America" "Central America")

:Call calls a Postgresql procedure. Remember that Postgresql procedures are not called within a select statement. The procedure name must be quoted or be a variable with a value of a quoted procedure name. Assuming a procedure named set_x_procedure1 exists, an example of calling it with parameters would be:

(query (:call 'set_x_procedure1 1 13))

(let ((a 1) (b 2) (p 'my-proc))
  (query (:call p a b 3)))

For more discussion, see Calling Postgresql Stored Functions and Procedures.

Intersect produces a result contain rows that appear on all the sub-selects.

(query (:intersect (:select 'countries.name
                            :from 'countries
                            :where (:< 'latitude 16.44))
                   (:select 'countries.name
                            :from 'countries 'regions
                            :where (:and (:= 'region-id 'regions.id)
                                         (:= 'regions.name "Caribbean")))))

The union operation generally eliminates what it thinks are duplicate rows. The union-all operation preserves duplicate rows. The examples below use the union-all operator, but the syntax would be the same with union.

(query (:select 'id 'name
                :from (:as (:union-all
                            (:select 'id 'name :from 'countries
                                     :where (:<= 'name "B" ))
                            (:select 'id 'name :from 'countries
                                     :where (:>= 'name "V" )))
                           'a)))

(query (:select 'a.id 'a.name 'a.region
                :from (:as (:union-all
                            (:select 'countries.id 'countries.name
                                     (:as 'regions.name 'region)
                                     :from 'countries 'regions
                                     :where (:and
                                             (:<= 'countries.name "B" )
                                             (:= 'regions.id 'countries.region-id )))
                            (:select 'countries.id 'countries.name
                                     (:as 'regions.name 'region)
                                     :from 'countries 'regions
                                     :where (:and
                                             (:>= 'countries.name "V" )
                                             (:= 'regions.id 'countries.region-id ))))
                           'a)
                :group-by 'a.id 'a.region 'a.name))

:except removes all matches. :except-all is slightly different. If the first select statement has two rows that match a single row in the second select statement, only one is removed.

(query (:except (:select 'id 'name
                         :from 'countries
                         :where (:like 'name "%New%"))
                (:select 'id 'name
                         :from 'countries
                         :where (:like 'name "%Zealand%"))))

(query (:except-all (:select '* :from 'clients) (:select '* :from 'vips)))

Unary operators for bitwise and logical negation.

(query (:order-by (:select 'recommendedby
                           (:count '*)
                           :from 'cd.members
                           :where (:not (:is-null 'recommendedby))
                           :group-by 'recommendedby)
                  'recommendedby))

Any needs to be considered as a special case. Quoting Marijn Haverbeke here,"Postgres has both a function-call-style any and an infix any, and S-SQL's syntax doesn't allow them to be distinguished." As a result, postmodern has a regular :any sql-op and a :any* sql-op, which expand slightly differently.

  (sql (:select 'name :from 'employee :where (:= 'id (:any '$1))))
"(SELECT name FROM employee WHERE (id = ANY $1))"

  (sql (:select 'name :from 'employee :where (:= 'id (:any* '$1))))
"(SELECT name FROM employee WHERE (id = ANY($1)))"

In general, the any qualifier in an sql statement looks at a subquery and does a comparison against that subquery. Sticking with our countries table, we have latitude, longitude data for every country (I'm not sure whether my table pulled the capital cities or the geographic center) and some designated a region for each country, so we have a region-id that matches the primary key 'id' in a regions table.

Out of curiosity, let's determine which countries in "North America" have a longitude less than any country in "South America". The standard sql could look like this:

(query "select countries.name
        from countries,regions
        where regions.id=region_id
              and regions.name='North America'
              and longitude > any(select longitude
                                         from countries, regions
                                         where region_id = regions.id
                                         and regions.name='South America')")
(("Bermuda") ("Greenland"))

This can be re-phrased in s-sql as

(query
 (:select 'countries.name
          :from 'countries 'regions
          :where (:and (:= 'regions.id 'region-id)
                       (:= 'regions.name "North America")
                       (:> 'longitude
                           (:any
                            (:select 'longitude
                                     :from 'countries 'regions
                                     :where (:and (:= 'regions.id 'region-id)
                                                  (:= 'regions.name "South America"))))))))
(("Bermuda") ("Greenland"))

Subselects work fine in both regular sql and s-sql. If you have already calculated your subselect and put it in a variable, for versions of Postmodern before 1.33.7 that variable needs to be a vector and whether you should use the :any sql-op or the :any* sql-op depends on your phrasing. (Notice that the second variation has an asterisk). (If you try to use a list, you will trigger an error message that you cannot convert that into an sql literal.) For versions of Postmodern 1.33.7 or higher, you can use a list.

The SQL keyword ANY can be used in a parameterized sql statement if you provide it with a vector (for Postmodern versions pre 1.33.7) or (for Postmodern versions 1.33.7 or higher) either a vector or a list. The following two toy examples work in RAW sql.

(query "select name from countries where id=any($1)"
       (vector 21 22))
(("Iceland") ("US"))

(let ((toy-query (vector 21 22)))
     (query "select name from countries where id=any($1)"
       toy-query))
(("Iceland") ("US"))

Now using s-sql and keeping with the toy example, notice that using :any does not work, but using :any* does work.

(let ((toy-query (vector 21 22)))
  (query (:select 'name
                  :from 'countries
                  :where (:= 'id (:any '$1)))
         toy-query))
; Evaluation aborted on #<CL-POSTGRES-ERROR:SYNTAX-ERROR-OR-ACCESS-VIOLATION {10030AF6A1}>.

(let ((toy-query (vector 21 22)))
  (query (:select 'name
                  :from 'countries
                  :where (:= 'id (:any* '$1)))
         toy-query))
(("Iceland") ("US"))

;; For Postmodern versions 1.33.7 or higher, you can also use a list
(let ((toy-query (list 21 22)))
  (query (:select 'name
                  :from 'countries
                  :where (:= 'id (:any* '$1)))
         toy-query))
(("Iceland") ("US"))

Going back to our earlier example, remember that I said that for Postmodern versions before 1.33.7 unless you use a subselect, you need to provide a vector to :any or :any*. A standard query returns a list, not a vector. So you would need to coerce the variable into a vector before you pass it to :any*. See below as an example, noting the use of passing a :column parameter to the postmodern query function so that we just get back a list of the longitude, not a list of lists of longitude. For Postmodern versions 1.33.7 or higher, you do not have to coerce it to a vector, but you would still need to pass the :column parameter in the first select.

(let ((South-America
       (coerce
         (query (:select 'longitude
                         :from 'countries 'regions
                         :where (:and (:= 'regions.id 'region-id)
                                      (:= 'regions.name "South America")))
                 :column))
        'vector))
  (query (:select 'countries.name
                  :from 'countries 'regions
                  :where (:and (:= 'regions.id 'region-id)
                               (:= 'regions.name "North America")
                               (:> 'longitude
                                   (:any* South-America))))))
(("Bermuda") ("Greenland"))

Create a stored procedure. The argument and return types are interpreted as type names and not evaluated. Stability should be one of :immutable, :stable, or :volatile (see the PostgreSQL documentation). For example, a function that gets foobars by id:

(:function 'get-foobar (integer) foobar :stable (:select '* :from 'foobar :where (:= 'id '$1)))

Regular expression matching operators. The exclamation mark means 'does not match', the asterisk makes the match case-insensitive.

(query (:select (:regexp_match "foobarbequebaz" "bar.*que")) :single)

#("barbeque")

(query (:select (:regexp_match "foobarbequebaz" "bar.~que")) :single)

:NULL

(query (:select (:~ "foobarbequebaz" "bar.*que") ) :single)

t

(query (:select (:!~ "foobarbequebaz" "bar.*que") ) :single)

nil

(query (:select (:~ "foobarbequebaz" "barque") ) :single)

nil

(query (:select (:~ "foobarbequebaz" "barbeque") ) :single)

t

(query (:select (:~ "foobarBequebaz" "barbeque") ) :single)

nil

(query (:select (:~* "foobarBequebaz" "barbeque") ) :single)

t

(query (:select 'id 'text :from 'text-search :where (:~ 'text "sushi")))

Simple SQL string matching operators (:ilike is case-insensitive).

(query (:select 'id 'name
                :from 'countries
                :where (:like 'name "%New%")))

Fast Text Search match operator.

Used to invert the meaning of an operator in an :order-by clause.

(query (:order-by
        (:select 'location 'time 'report
                 :distinct-on 'location
                 :from 'weather-reports)
        'location  (:desc 'time)))

Used to determine where :null values appear in an :order-by clause.

Also known in some explanations as "alias". This assigns a name to a column or table in a :select form. When fields are given, they are added after the name, in parentheses. For example, (:as 'table1 't1 'foo 'bar) becomes table1 AS t1(foo, bar). When you need to specify types for the fields, you can do something like (:as 'table2 't2 ('foo integer)). Note that names are quoted, types are not (when using sql-compile or sql-template, you can leave out the quotes entirely).

(query (:select (:as 'countries.name 'country)
                (:as 'regions.name 'region)
                :from 'countries 'regions
                       :where (:and (:= 'regions.id 'region-id)
                                     (:= 'regions.name "Central America")))
        :alists)

(((:COUNTRY . "Belize") (:REGION . "Central America")) ((:COUNTRY . "Costa Rica")
(:REGION . "Central America")) ((:COUNTRY . "El Salvador")
(:REGION . "Central America")) ((:COUNTRY . "Guatemala")
(:REGION . "Central America")) ((:COUNTRY . "Panama") (:REGION . "Central America"))
((:COUNTRY . "Nicaragua") (:REGION . "Central America")))

The following uses aliases for both columns and tables in the from and inner-join clauses:

(query (:order-by
        (:select (:as 'recs.firstname 'firstname)
                 (:as 'recs.surname 'surname)
                 :distinct
                 :from (:as 'cd.members 'mems)
                 :inner-join (:as 'cd.members 'recs)
                 :on (:= 'recs.memid 'mems.recommendedby))
        'surname 'firstname))

Note: Postmodern does not allow you to create an unescaped string alias. In other words, you cannot generate this:

"select sum(slots as "Total Slots" from cd.bookings"

without using :raw

The CAST operator. Takes a query as an argument, and returns the result explicitly cast by postgresql to a specific type. Unlike :type, :cast can pass the type as a variable.

(query (:select (:cast (:as "20" 'integer)))
        :single)
20

(let ((type 'text))
   (query (:select (:cast (:as "20" type)))
     :single))
"20"

(let ((type 'integer))
   (query (:select (:cast (:as "20" type)))
     :single))
20

(query (:union (:select (:as 1 'real))
               (:select (:cast (:as "2.2" 'real)))))
((1.0) (2.2))

Is similar to cast but uses the postgresql :: formating. Unlike cast it will not accept a variable as the type.

E.g.

(sql (:select (:as (:- (:type (:now) 'date) 'x) 'some-date) :from (:as (:generate-series 1 10) 'x)))

"(SELECT (now()::DATE - x) AS some_date FROM generate_series(1, 10) AS x)"

Add a type declaration to a value, as in in "4.3::real". The second argument is not evaluated normally, but put through sql-type-name to get a type identifier.

(query (:select (:type 1.0 int)))

Creates a composite type with a type-name and two or more columns. E.g.

(query (:create-composite-type 'fullname (first-name text) (last-name text)))

The EXISTS operator. Takes a query as an argument, and returns true or false depending on whether that query returns any rows. In the example below, it is applied to a subquery.

(query (:select 'id 'name
                :from 'regions
                :where (:exists
                        (:select 'region-id
                         :from 'countries
                         :where (:and
                                  (:= 'countries.name "Costa Rica")
                                  (:= 'regions.id 'countries.region-id))))))

Test whether a boolean value is false.

(query (:select 'ta :from 'a :where (:is-false 'ta)))
(select-dao 'account (:is-false 'active))

Test whether a boolean value is true.

(query (:select 'ta :from 'a :where (:is-true 'ta)))
(select-dao 'account (:is-true 'active))

Test whether a value is null.

(query (:select 'ta :from 'a :where (:not (:is-null 'ta))))

Test whether a value is not null.

(query (:select 'ta :from 'a :where (:not-null 'ta)))

Test whether a value is in a set of values.

(query (:select 'name
                :from 'countries
                :where (:in 'id
                            (:set 20 21 23))))

(query (:select 'region 'product (:as (:sum 'quantity) 'product-units)
                (:as (:sum 'amount) 'product-sales)
                :from 'orders
                :where (:in 'region (:select 'region :from 'top-regions))
                :group-by 'region 'product))

Beginning in Postmodern v. 1.33.7, you can use lists with :in.

(query (:select '* :from 'employee :where (:in 'id '(1 3 4))))

(let ((emp-ids '(1 2)))
  (query (:select 'name :from 'employee :where (:in 'id emp-ids))))

IMPORTANT REGARDING PARAMETERIZED QUERIES: For Postmodern versions before 1.33.7, you cannot use a list in a parameterized statement. You have to convert the list to a vector and use :any* rather than :in. See S-SQL#sql-op-any for more details.

(query (:select 'name :from 'employee :where (:= 'id (:any* '$1)))
            #(1 3) :column)
 '("Jason" "Celia")

;; Beginning with Postmodern version 1.33.7 you can also use lists

(query (:select 'name :from 'employee :where (:= 'id (:any* '$1)))
            '(1 3) :column)
 '("Jason" "Celia")

Inverse of the above.

(query (:select '* :from 'table1 :where (:not-in 'id (:set 1 2 3))))

Beginning in Postmodern v. 1.33.7, you can use lists with :in.

(query (:select '* :from 'table1 :where (:not-in 'name '("alpha" "beta" "ceta"))))

Denote a set of values. This operator has two interfaces. When the elements are known at compile-time, they can be given as multiple arguments to the operator. When they are not, a single argument that evaluates to a list should be used.

The following would be the syntax in postmodern sql where the set is a list. If you want to use a vector, then you need to use Any:

The following are equivalent

(query (:select 'name
                :from 'countries
                :where (:in 'id
                            (:set 20 21 23))))

(let ((x (list 20 21 23)))
  (query (:select 'name
                  :from 'countries
                  :where (:in 'id
                              (:set x)))))

(query (:select 'name
                :from 'countries
                :where (:in 'id (:set (list 20 21 23)))))

However, the following will generate an error.

(query (:select 'name
                :from 'countries
                :where (:in 'id
                            (:set '(20 21 23)))))

Now with selecting a dao

(select-dao 'countries
            (:in 'id
                 (:set (list 20 21 23

Now with selecting from a vector. Note both the use of any* and := instead of :in.

(let ((x (vector 20 21 23)))
  (query (:select 'name
                  :from 'countries
                  :where (:= 'id (:any* x)))))

Note that the responses will still come back in a list of lists

IMPORTANT: :SET DOES NOT WORK IN PARAMETERIZED QUERIES. YOU NEED TO USE :ANY* RATHER THAN :IN. See S-SQL for more details.

This is used when calling a select query into an array. See array-notes.html for more detailed notes on the use of arrays.

(query (:order-by
        (:select 'r.rolename
                 (:as (:array
                       (:select 'b.rolename
                                :from (:as 'pg_catalog.pg-auth-members 'm)
                                :inner-join (:as 'pg-catalog.pg-roles 'b)
                                :on (:= 'm.roleid 'b.oid)
                                :where (:= 'm.member 'r.oid )))
                      'memberof)

                 :from (:as 'pg-catalog.pg-roles 'r))
        1))

This is the general operator for arrays. It also handles statements that include functions in the query such as (:+ 1 2), (:pi) in the array. See array-notes.html for more detailed notes on the use of arrays.

(query (:select (:array-prepend 1 (:array[] 2 3))))

((#(1 2 3)))

(query (:select (:array-prepend 1 (:array[] 2 3)))
       :single)

#(1 2 3)

Dereference an array value. If end is provided, extract a slice of the array. Sample usage below, but also see array-notes.html for more detailed notes on the use of arrays.

(query (:select 'receipe-id (:[] 'tags 2 3)
                :from 'receipe-tags-array
                :where (:= 'receipe-id 3)))

Extract a field from a date/time value. For example, (:extract :month (:now)).

(query (:order-by
        (:select 'facid
                 (:as (:extract 'month 'starttime) 'month)
                 (:as (:sum 'slots) 'total-slots)
                 :from 'cd.bookings
                 :where (:and (:>= 'starttime "2012-01-01")
                              (:< 'starttime "2013-01-01"))
                 :group-by 'facid 'month)
        'facid 'month))

A conditional expression. Clauses should take the form (test value). If test is :else, an ELSE clause will be generated.

(query (:select 'name
                (:as (:case ((:> 'monthlymaintenance 100) "expensive")
                            (:else "cheap")) 'cost)
                :from 'cd.facilities))

Test whether a value lies between two other values.

(query (:select 'name
                :from 'countries
                :where (:between 'latitude -10 10))
       :column)

Works like :between, except that the start value is not required to be less than the end value.

Can be used to combine multiple names into a name of the form A.B to refer to a column in a table, or a table in a schema. Note that you can also just use a symbol with a dot in it.

Insert a string as-is into the query. This can be useful for doing things that the syntax does not support, or to re-use parts of a query across multiple queries:

(let* ((test (sql (:and (:= 'foo 22) (:not-null 'bar))))
       (rows (query (:select '* :from 'baz :where (:raw test)))))
  (query (:delete-from 'baz :where (:raw test)))
  (do-stuff rows))

Fetch is a more efficient way to do pagination instead of using limit and offset. Fetch allows you to retrieve a limited set of rows, optionally offset by a specified number of rows. In order to ensure this works correctly, you should use the order-by clause. If the amount is not provided, it assumes you only want to return 1 row. https://www.postgresql.org/docs/current/sql-select.html

Examples:

(query (:fetch (:order-by (:select 'id :from 'historical-events) 'id) 5))

((1) (2) (3) (4) (5))

(query (:fetch (:order-by (:select 'id :from 'historical-events) 'id) 5 10))

((11) (12) (13) (14) (15))

In S-SQL limit is not part of the select operator, but an extra operator that is applied to a query (this works out better when limiting the union or intersection of multiple queries, same for sorting). It limits the number of results to the amount given as the second argument, and optionally offsets the result by the amount given as the third argument.

(query (:limit (:order-by (:select 'surname :distinct :from 'cd.members) 'surname) 10))

Order the results of a query by the given expressions. See :desc for when you want to invert an ordering. Note: This is not the same as passing an :order-by parameter to an aggregation operator. For that see Aggregation Operators.

(query (:order-by (:select 'id 'name 'city 'salary (:every (:like 'name "J%"))
                                          :from 'employee
                                          :group-by 'name 'id 'salary 'city)
                                 'name))

Values computes a row value or set of row values for use in a specific query. See the postgresql docs at: https://www.postgresql.org/docs/current/static/queries-values.html and https://www.postgresql.org/docs/current/static/sql-values.html Example:

(query (:select '*
                :from (:as (:values (:set 1 "one")
                                    (:set 2 "two")
                                    (:set 3 "three"))
                           (:t1 'num 'letter))))

(query (:select 'a 'b 'c (:cast (:as (:* 50 (:random)) 'int))
                :from (:as (:values (:set "a") (:set "b")) (:d1 'a))
                (:as (:values (:set "c") (:set "d")) (:d2 'b))
                (:as (:values (:set "e") (:set "f")) (:d3 'c))))

(query
 (:with-recursive
  (:as (:t1 'n)
       (:union-all (:values (:set 1))
                   (:select (:+ 'n 1)
                            :from 't1
                            :where (:< 'n 100))))
  (:select (:sum 'n) :from 't1))
 :single)

This is a fudge. It returns a string "()" where something like '() would return "false" or :() would throw an error. Example:

(query (:select 'appnumber 'day (:sum 'inserts)
                (:sum 'updates) (:sum 'deletes) (:sum 'transactions)
                :from 'db-details
                :group-by (:grouping-sets (:set 'appnumber 'day (:empty-set)))))

https://www.postgresql.org/docs/current/static/queries-table-expressions.html#QUERIES-GROUPING-SETS The GROUP BY Clause is used to group together those rows in a table that have the same values in all the columns listed. The order in which the columns are listed does not matter. The effect is to combine each set of rows having common values into one group row that represents all rows in the group. This is done to eliminate redundancy in the output and/or compute aggregates that apply to these groups. Example:

(query (:order-by
        (:select 'mems.surname 'mems.firstname 'mems.memid (:as (:min 'bks.starttime) 'starttime)
                 :from (:as 'cd.bookings 'bks)
                 :inner-join (:as 'cd.members 'mems)
                 :on (:= 'mems.memid 'bks.memid)
                 :where (:>= 'starttime "2012-09-01")
                 :group-by 'mems.surname 'mems.firstname 'mems.memid)
        'mems.memid))

https://www.postgresql.org/docs/current/static/queries-table-expressions.html#QUERIES-GROUPING-SETS More complex grouping operations are possible using the concept of grouping sets. The data selected by the FROM and WHERE clauses is grouped separately by each specified grouping set, aggregates computed for each group just as for simple GROUP BY clauses, and then the results returned. This operator requires postgresql 9.5 or later. For example:

(query (:select 'city (:as (:extract 'year 'start-date)  'joining-year) (:as (:count 1) 'employee_count)
                :from 'employee
                :group-by (:grouping-sets (:set 'city (:extract 'year 'start-date)))))

Creates an interval data type, generally represented in postmodern as an alist

Takes lists of (time-unit value) and returns a timestamp type. Example:

(query (:select (:make-interval ("days" 4) ("hours" 10) ("secs" 1.2)))
       :single)

Takes lists of (time-unit value) and returns a timestamptz type. Example:

(query (:select
          (:make-timestamptz ("year" 2014) ("month" 1) ("mday" 13)
                             ("hour" 21) ("min" 50) ("sec" 0)))
       :single)

Takes lists of (time-unit value) and returns a timestamptz type. Example:

(query (:select
          (:make-timestamptz ("year" 2014) ("month" 1) ("mday" 13)
                             ("hour" 21) ("min" 50) ("sec" 0) ("timezone" "Asia/Tokyo")))
       :single)

Count returns the number of rows for which the expression is not null. It can be the number of rows collected by the select statement as in:

(query (:select (:count '*)
                :from 'table1
                :where (:= 'price 100)))

or it can be a smaller number of rows based on the allowed keyword parameters :distinct and :filter or some other type of condition as in:

(query (:select (:count 'memid :distinct)
                :from 'cd.bookings))

or

(query (:select (:as (:count '* :distinct) 'unfiltered)
                (:as (:count '* :filter (:= 1 'bid))
                     'filtered)
                :from 'testtable))

Note that if used, the filter must be last in the count args. If distinct is used, it must come before filter. Unlike standard sql, the word 'where' is not used inside the filter clause. E.g.

(query (:select (:count '*)
                (:count '* :filter (:= 1 'bid))
                'id
                :from 'pbbench-history))

See tests.lisp for examples.

Avg calculates the average value of a list of values. Note that if the filter keyword is used, the filter must be last in the avg args. If distinct is used, it must come before filter. E.g. See tests.lisp for more examples.

(query (:select (:avg '*) (:avg '* :filter (:= 1 'bid)) 'id
                :from 'pbbench-history))

Sum calculates the total of a list of values. Note that if the keyword filter is used, the filter must be last in the sum args. If distinct is used, it must come before filter. Unlike standard sql, the word 'where' is not used inside the filter clause (s-sql will properly expand it). See tests.lisp for more examples.

(query (:select (:sum '*) (:sum '* :filter (:= 1 'bid)) 'id
                :from 'pbbench-history))

max returns the maximum value of a set of values. Note that if the filter keyword is used, the filter must be last in the max args. If distinct is used, it must come before filter. Unlike standard sql, the word 'where' is not used inside the filter clause (s-sql will properly expand it). See tests.lisp for more examples.

(query (:select (:max '*) (:max '* :filter (:= 1 'bid)) 'id
                :from 'pbbench-history))

min returns the minimum value of a set of values. Note that if the filter keyword is used, the filter must be last in the min args. If distinct is used, it must come before filter. Unlike standard sql, the word 'where' is not used inside the filter clause (s-sql will properly expand it). See tests.lisp for more examples.

(query (:select (:min '*) (:min '* :filter (:= 1 'bid)) 'id
                :from 'pbbench-history))

Every returns true if all input values are true, otherwise false. Note that if the filter keyword is used, the filter must be last in the every args. If distinct is used, it must come before filter. Unlike standard sql, the word 'where' is not used inside the filter clause (s-sql will properly expand it). See tests.lisp for more examples.

(query (:select '* (:every (:like 'studname "%h"))
                :from 'tbl-students
                :group-by 'studname 'studid 'studgrades))

Requires Postgresql 9.4 or higher. Percentile-cont returns a value corresponding to the specified fraction in the ordering, interpolating between adjacent input items if needed. There are two required keyword parameters :fraction and :order-by. If the fraction value is an array, then it returns an array of results matching the shape of the fractions parameter, with each non-null element replaced by the value corresponding to that percentile. Examples:

(query (:select (:percentile-cont :fraction 0.5 :order-by 'number-of-staff)
                :from 'schools))

(query (:select (:percentile-cont :fraction array[0.25 0.5 0.75 1]
                                  :order-by 'number-of-staff)
                :from  'schools))

Requires Postgresql 9.4 or higher. There are two required keyword parameters :fraction and :order-by. Percentile-dist returns the first input value whose position in the ordering equals or exceeds the specified fraction. If the fraction parameter is an array eturns an array of results matching the shape of the fractions parameter, with each non-null element replaced by the input value corresponding to that percentile. Examples:

(query (:select (:percentile-dist :fraction 0.5
                                  :order-by 'number-of-staff)
                :from 'schools))

(query (:select (:percentile-dist :fraction array[0.25 0.5 0.75 1]
                                  :order-by 'number-of-staff)
                :from  'schools))

The corr function returns the correlation coefficient between a set of dependent and independent variables. Example:

(query (:select (:corr 'height 'weight)
                :from 'people))

The covar-pop function returns the population covariance between a set of dependent and independent variables. Example:

(query (:select (:covar-pop 'height 'weight)
                :from 'people))

(query (:select (:covar-samp 'height 'weight)
                :from 'people))

The covar-samp function returns the sample covariance between a set of dependent and independent variables. Example:

String-agg allows you to concatenate strings using different types of delimiter symbols. Allowable optional keyword parameters are :distinct, :order-by and :filter Note that order-by in string-agg requires postgresql 9.0 or later. Filter requires postgresql 9.4 or later. See tests.lisp for more examples.

(query (:select (:as (:string-agg 'bp.step-type \",\" )
                     'step-summary)
                :from 'business-process))

(query (:select 'mid (:as (:string-agg  'y \",\" :distinct :order-by (:desc 'y))
                          'words)
                :from 'moves))

(query (:select (:string-agg  'name "," :order-by (:desc 'name) :filter (:< 'id 4))
                :from 'employee))

Array-agg returns a list of values concatenated into an arrays. Allowable optional keyword parameters are :distinct, :order-by and :filter.

Note that order-by in array-agg requires postgresql 9.0 or later. Filter requires postgresql 9.4 or later. See array-notes.html for more detailed notes on the use of arrays.

Example with Filter:

(query (:select 'g.id
                (:as (:array-agg 'g.users :filter (:= 'g.canonical \"Y\"))
                     'canonical-users)
                (:as (:array-agg 'g.users :filter (:= 'g.canonical \"N\"))
                     'non-canonical-users)
                :from (:as 'groups 'g)
                :group-by 'g.id))

Mode is used to find the most frequent input value in a group. See e.g. https://www.postgresql.org/docs/10/static/functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE and article at https://tapoueh.org/blog/2017/11/the-mode-ordered-set-aggregate-function

(query (:select (:mode 'items)
                :from 'item-table))

The regr_avgx function returns the average of the independent variable (sum(X)/N) Example:

(query (:select (:regr_avgx 'height 'weight)
                :from 'people))

The regr_avgy function returns the average of the dependent variable (sum(Y)/N). Example:

(query (:select (:regr_avgy 'height 'weight) :from 'people))

The regr_count function returns the number of input rows in which both expressions are nonnull. Example:

(query (:select (:regr_count 'height 'weight)
                :from 'people))

The regr_intercept function returns the y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs. Example:

(query (:select (:regr_intercept 'height 'weight)
                :from 'people))

The regr_r2 function returns the square of the correlation coefficient. Example:

(query (:select (:regr_r2 'height 'weight)
                :from 'people))

The regr_slope function returns the slope of the least-squares-fit linear equation determined by the (X, Y) pairs. Example:

(query (:select (:regr_slope 'height 'weight)
                :from 'people))

The regr_sxx function returns the sum(X^2) - sum(X)^2/N (“sum of squares” of the independent variable). Example:

(query (:select (:regr_sxx 'height 'weight)
                :from 'people))

The regr_sxy function returns the sum(X*Y) - sum(X) * sum(Y)/N (“sum of products” of independent times dependent variable). Example:

(query (:select (:regr_sxy 'height 'weight)
                :from 'people))

The regr_syy function returns the sum(Y^2) - sum(Y)^2/N (“sum of squares” of the dependent variable). Example:

(query (:select (:regr_syy 'salary 'age)
                :from 'employee))

The stddev function returns the the sample standard deviation of the input values. It is a historical alias for stddev-samp. Example:

(query (:select (:stddev 'salary)
                :from 'employee))

The stddev-pop function returns the population standard deviation of the input values. Example:

(query (:select (:stddev-pop 'salary)
                :from 'employee))

The stddev-samp function returns the sample standard deviation of the input values. Example:

(query (:select (:stddev-samp 'salary)
                :from 'employee))

Variance is a historical alias for var_samp. The variance function returns the sample variance of the input values (square of the sample standard deviation). Example:

(query (:select (:variance 'salary)
                :from 'employee))

The var-pop function returns the population variance of the input values (square of the population standard deviation). Example:

(query (:select (:var-pop 'salary)
                :from 'employee)
       :single)

The var-samp function returns the sample variance of the input values (square of the sample standard deviation). Example:

(query (:select (:var-samp 'salary)
                :from 'employee)
       :single)

Window Functions

Range-between allows window functions to apply to different segments of a result set. It accepts the following keywords: :order-by, :rows-between, :range-between, :unbounded-preceding, :current-row and :unbounded-following. Use of :preceding or :following will generate errors. See https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS for Postgresql documentation on usage.

An example which calculates a running total could look like this:

(query
 (:select (:as 'country 'country-name)
          (:as 'population 'country-population)
          (:as (:over (:sum 'population)
                      (:range-between :order-by 'country
                                      :unbounded-preceding :current-row))
               'global-population)
  :from 'population
  :where (:and (:not-null 'iso2)
               (:= 'year 1976))))

Rows-between allows window functions to apply to different segments of a result set. It accepts the following keywords: :order-by, :rows-between, :range-between, :preceding, :unbounded-preceding, :current-row, :unbounded-following and :following. See https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS for Postgresql documentation on usage.

An example could look like this :

(query
 (:select (:as 'country 'country-name)
          (:as 'population 'country-population)
          (:as (:over (:sum 'population)
                      (:rows-between :order-by 'country :preceding 2 :following 2))
               'global-population)
  :from 'population
  :where (:and (:not-null 'iso2)
               (:= 'year 1976))))

Over, partition-by and window are so-called window functions. A window function performs a calculation across a set of table rows that are somehow related to the current row and adds that as an additional column to the result. The following collects individual salaries and the total salaries.

(query (:select 'salary (:over (:sum 'salary))
                :from 'empsalary))

A more complicated version that calculates a running total might look like:

(query
 (:select 'name
          (:as 'salary 'individual-salary)
          (:as (:over (:sum 'salary)
                      (:range-between :order-by 'name :unbounded-preceding
                       :current-row))
               'running-total-salary)
  :from 'empsalary))

Args is a list of one or more columns to partition by, optionally followed by other keywords. Partition-by accepts the following keywords: :order-by, :rows-between, :range-between, :preceding, :unbounded-preceding, :current-row, :unbounded-following and :following. See https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS for Postgresql documentation on usage.

(query (:select 'depname 'subdepname 'empno 'salary
                (:over (:avg 'salary)
                       (:partition-by 'depname 'subdepname))
                :from 'empsalary))

Note the use of :order-by without parens:

(query (:select 'depname 'empno 'salary
                (:over (:rank)
                       (:partition-by 'depname :order-by (:desc 'salary)))
                :from 'empsalary))

The following example shows a query for country population in 1976 with running total population by region.

(query
 (:select (:as 'population.country 'country-name)
          (:as 'population 'country-population)
          'region-name
          (:as (:over (:sum 'population)
                      (:partition-by 'region-name :order-by 'region-name
                       :rows-between :unbounded-preceding :current-row))
               'regional-population)
          :from 'population
          :inner-join 'regions
          :on (:= 'population.iso3 'regions.iso3)
          :where (:and (:not-null 'population.iso2)
                       (:= 'year 1976))))

With provides a way to write auxillary statements for use in a larger query, often referred to as Common Table Expressions or CTEs.

(query (:with (:as 'upd
                   (:parens
                    (:update 'employees :set 'sales-count (:+ 'sales-count 1)
                             :where (:= 'id
                                        (:select 'sales-person
                                                 :from 'accounts
                                                 :where (:= 'name "Acme Corporation")))
                             :returning '*)))
              (:insert-into 'employees-log
                            (:select '* (:current-timestamp) :from
                                     'upd))))

Recursive modifier to a WITH statement, allowing the query to refer to its own output.

(query (:with-recursive
      (:as (:t1 'n)
           (:union-all (:values (:set 1))
                       (:select (:+ 'n 1)
                                :from 't1
                                :where (:< 'n 100))))
      (:select (:sum 'n) :from 't1)))

(query (:with-recursive
      (:as (:included_parts 'sub-part 'part 'quantity)
           (:union-all
            (:select 'sub-part 'part 'quantity
                     :from 'parts
                     :where (:= 'part "our-product"))
            (:select 'p.sub-part 'p.part 'p.quantity
                     :from (:as 'included-parts 'pr)
                     (:as 'parts 'p)
                     :where (:= 'p.part 'pr.sub-part) )))
      (:select 'sub-part (:as (:sum 'quantity) 'total-quantity)
               :from 'included-parts
               :group-by 'sub-part)))

(query (:with-recursive
      (:as (:search-graph 'id 'link 'data 'depth)
           (:union-all (:select 'g.id 'g.link 'g.data 1
                                :from (:as 'graph 'g))
                       (:select 'g.id 'g.link 'g.data (:+ 'sg.depth 1)
                                :from (:as 'graph 'g) (:as 'search-graph 'sg)
                                :where (:= 'g.id 'sg.link))))
      (:select '* :from 'search-graph)))

(query (:with-recursive
      (:as (:search-graph 'id 'link 'data'depth 'path 'cycle)
           (:union-all
            (:select 'g.id 'g.link 'g.data 1
                     (:[] 'g.f1 'g.f2) nil
                     :from (:as 'graph 'g))
            (:select 'g.id 'g.link 'g.data (:+ 'sg.depth 1)
                     (:|| 'path (:row 'g.f1 'g.f2))
                     (:= (:row 'g.f1 'g.f2)
                         (:any* 'path))
                     :from (:as 'graph 'g)
                     (:as 'search-graph 'sg)
                     :where (:and (:= 'g.id 'sg.link)
                                  (:not 'cycle)))))
      (:select '* :from 'search-graph)))

Selects can use :with-ordinality or :with-ordinality-as parameters. Postgresql will give the new ordinality column the name of ordinality. :with-ordinality-as allows you to set different names for the columns in the result set.

(query (:select '*
        :from (:generate-series 4 1 -1)
        :with-ordinality))

(query (:select 't1.*
        :from (:json-object-keys "{\"a1\":\"1\",\"a2\":\"2\",\"a3\":\"3\"}")
        :with-ordinality-as (:t1 'keys 'n)

Locks the selected rows against concurrent updates. This will prevent the rows from being modified or deleted by other transactions until the current transaction ends. The :of keyword should be followed by one or more table names. If provided, PostgreSQL will lock these tables instead of the ones detected in the select statement. The :nowait keyword should be provided by itself (with no argument attached to it), after all the :of arguments. If :nowait is provided, PostgreSQL will throw an error if a table cannot be locked immediately, instead of pausing until it's possible.

(query (:for-update (:select :* :from 'foo 'bar 'baz) :of 'bar 'baz :nowait))

Similar to :for-update, except it acquires a shared lock on the table, allowing other transactions to perform :for-share selects on the locked tables.

You can use insert-into when you are:

1. Inserting from a select clause and you do not need to specify specific columns:

(query (:insert-into 'table1
         (:select 'c1 'c2 :from 'table2)))

1. Inserting from a select clause and you specifying the columns which will be filled with values from the select clause

(query (:insert-into 't11
        :columns 'region 'subregion 'country
        (:select (:as 'region-name 'region)
                 (:as 'sub-region-name 'subregion)
                 'country
         :from 'regions)))

or

1. You are alternating specific columns and values for a single row:

(query (:insert-into 'my-table :set 'field-1 42 'field-2 "foobar"))

You can use parameterized variables in the insert statement.

(let ((name "test-cat4"))
  (query (:insert-into 'categories :set 'name '$1) name))

It is possible to add :returning, followed by a list of field names or expressions, at the end of the :insert-into form. This will cause the query to return the values of these expressions as a single row.

(query (:insert-into 'my-table
        :set 'field-1 42 'field-2 "foobar"
        :returning '*))

(query (:insert-into 'my-table
        :set 'field-1 42 'field-2 "foobar"
        :returning 'id))

In Postgresql versions 9.5 and above, it is possible to add :on-conflict-do-nothing (if the item already exists, do nothing). If you want to specify the unique column to be checked for conflict, use :on-conflict 'column-name :do-nothing. If you do not want to specify the unique column name, use :on-conflict-do-nothing.

(query (:insert-into 'test-table :set 'column-A '$1 'column-B '$2
                     :on-conflict 'column-A :do-nothing
                     :where (:= 'test-table.column-A '$1)
                     :returning '*)
        "c" 37)

(query (:insert-into 'test-table :set 'column-A '$1 'column-B '$2
                     :on-conflict-do-nothing 'column-A
                     :where (:= 'test-table.column-A '$1)
                     :returning '*)
        "c" 37)

If your insertion is setting a column that is an identity column with a value normally created by the system and you want to override that, you can use the :overriding-system-value keyword:

(query (:insert-into 'table1
        :set 'c1 "A" 'c2 "B"
        :overriding-system-value))

To create what is commonly known as an upsert, use :on-conflict-update (if the item already exists, update the values) followed by a list of field names which are checked for the conflict then using :update-set followed by a list of field names or expressions following the syntax for updating a table. This is sometimes called an "upsert". Note that as per the postgresql sql documentation you must prepend the table name to the column in the where statement if you are updating.

(query (:insert-into 'test-table :set 'column-A '$1 'column-B '$2
                     :on-conflict-update 'column-A
                     :update-set 'column-B '$2
                     :where (:= 'test-table.column-A '$1)
                     :returning '*)
        "c" 37)

If the destination table has identity columns and you want to override those identity columns with specific values, you should specify :overriding-system-value.

(query (:insert-into 'test-table
        :set 'column-A '$1 'column-B '$2
        :overriding-system-value
        :on-conflict-update 'column-A
        :update-set 'column-B '$2
        :where (:= 'test-table.column-A '$1)
        :returning '*)
        "c" 37)

If you are selecting from another table which has column names the same as your destination table and you want to keep the destination table's identity column values, then you can use :overriding-user-value. E.g.

(query (:insert-into 'table1
         :overriding-user-value
         (:select 'c1 'c2 :from 'table2)))

Insert-rows-into provides the ability to insert multiple rows into a table without using a select statement. (Insert-rows-into keeps the VALUES key word in the resulting sql. If you do use a select statement, Postgresql requires that it only return one row.)

Specify the columns first with the keyword :columns then provide a list of lists of the values as a parameter to the keyword :values. Example:

(query (:insert-rows-into 'my-table
        :columns 'field-1 'field-2
        :values '((42 "foobar") (23 "foobaz"))))

An example using a select statement returning one row:

(squery (:insert-rows-into 't6
         :columns 'tags
         :values '(((:select 'id
                     :from 't5)))))

If you will use the default columns, this can be simplified and the :columns parameters can be dropped. Example:

(query (:insert-rows-into 'my-table
        :values '((42 "foobar") (23 "foobaz"))))

If your insertion is setting a column that is an identity column with a value normally created by the system and you want to override that, you can use the :overriding-system-value keyword:

(query (:insert-rows-into 'table1
                   :columns 'c1 'c2
                   :overriding-system-value
                   :values '((1 "a") (2 "b"))))

(query (:insert-rows-into 'table1
        :overriding-system-value
        :values '(((:select 'c1 'c2 :from 'table2)))))

Similarly to :insert-into, :insert-rows-into allows the "upsert" use of :on-conflict. Again, if you want to specify the unique column to be checked for conflict, use :on-conflict 'column-name :do-nothing. If you do not want to specify the unique column name, use :on-conflict-do-nothing. The following example uses :on-conflict-do-nothing

(query (:insert-rows-into 'distributors
        :columns 'did 'dname
        :values '((10 "Conrad International"))
        :on-conflict-do-nothing
        :where 'is-active))

(query (:insert-rows-into 'distributors
        :columns 'did 'dname
        :values '((10 "Conrad International"))
        :on-conflict 'did
        :do-nothing
        :where 'is-active))

or :on-conflict-update

(query (:insert-rows-into 'distributors
        :columns 'did 'dname
        :values '((5 "Gizmo Transglobal") (6 "Associated Computing Inc."))
        :on-conflict-update 'did
        :update-set 'dname 'excluded.dname))

You can use :on-conflict-on-constraint to check for conflicts on constraints.

(query (:insert-rows-into 'test :columns 'some-key 'some-val
                                :values '(("a" 3) ("b" 6) ("c" 7))
                                :on-conflict-on-constraint 'somekey
                                :do-nothing
                   :returning '*))

(query (:insert-rows-into 'test :columns 'some-key 'some-val
                                :values '(("a" 2) ("b" 6) ("c" 7))
                                :on-conflict-on-constraint 'somekey
                                :update-set 'some-val 'excluded.some-val
                   :returning '*))

Update values in a table. There are two ways to update the values

The first method uses the keyword :set and any number of alternating field names and values, like for :insert-into. Next comes the optional keyword :from, followed by at least one table name and then any number of join statements, like for :select. After the joins, an optional :where keyword followed by the condition, and :returning keyword followed by a list of field names or expressions indicating values to be returned as query result.

(query (:update 'weather
        :set 'temp-lo (:+ 'temp-lo 1)
             'temp-hi (:+ 'temp-lo 15)
             'prcp :default
        :where (:and (:= 'city "San Francisco")
                     (:= 'date "2003-07-03"))
        :returning 'temp-lo 'temp-hi 'prcp))

The second method uses the :columns keyword to specify which columns get created and allows the use of either :set or :select (both of which need to be enclosed in a form) to provide the values, allowing update queries like:

(query (:update 'weather
        :columns 'temp-lo 'temp-hi 'prcp
                 (:set (:+ 'temp-lo 1)  (:+ 'temp-lo 15) :DEFAULT)
        :where (:and (:= 'city "San Francisco")
                     (:= 'date "2003-07-03"))))

(query (:update 't1
        :columns 'database-name 'encoding
                 (:select 'x.datname 'x.encoding
                  :from (:as 'pg-database 'x)
                  :where (:= 'x.oid 't1.oid))))

Delete rows from the named table. Can be given a :where argument followed by a condition, and a :returning argument, followed by one or more expressions that should be returned for every deleted row.

(query (:delete-from 'cd.bookings :where (:= 'id 5)))

The following more complicated version shows the application of the :using and :where clauses:

(query (:delete-from 'members
        :using 'producers
        :where (:and (:= 'members.id 'producers.id)
                     (:= 'members.name "Steve"))))

(sql (:delete-from 'members
      :using 'producers 'films
                   :where (:and (:= 'members.id 'producers.id)
                                (:= 'members.name "Steve")
                                (:= 'producers.films-id 'films.id)))

Create a new table. The simplest example would pass two parameters, the table name and a list of lists providing information for each column. For example:

(query (:create-table 'george ((id :type integer))))

where 'george is the name of the table, it has 1 column named id which is limited to integers. There are no indexes or keys in this example.

See create-tables.html for more detailed examples.

Alters named table. Currently changing a column's data type is not supported. The meaning of args depends on action:

• :add-column

Adds column to table. args should be a column in the same form as for :create-table.

(query (:alter-table "packages" :add-column 'system-data-p :type (or boolean db-null)))

• :set-default

Adds or changes a default value for a column

(query (:alter-table 'countries :alter-column 'updated-at :set-default  (:now)))

• :drop-column

Drops a column from the table.

(query (:alter-table "test-uniq" :drop-column 'address))

• :add-constraint

Adds a named constraint to the table.

(query (:alter-table "test-uniq" :add-constraint silly-key :primary-key 'code 'title))

(query (:alter-table enemy :add-constraint enemy-age-check :check (:> 'age 21)))

• :drop-constraint

Drops constraint. First of args should name a constraint to be dropped; second, optional argument specifies behaviour regarding objects dependent on the constraint and it may equal :cascade or :restrict.

(query (alter-table enemy :drop-constraint enemy-age-check))

• :add

Adds an unnamed constraint to table. args should be a constraint in the same form as for :create-table. (This is for backwards-compatibility, you should use named constraints.)

• :rename

Adds the ability to rename a table.

• :rename-column

Adds the ability to rename a column of a table.

(query (:alter-table "test-uniq" :rename-column 'address 'city))

Drops the named table. You may optionally pass :if-exists before the name to suppress the error message if the table does not exist. You can also optionally pass :cascade after the name to indicate that it should also drop any other tables, indices, etc which depend on that table. Accepts strings, variable or symbol as the identifier.

(query (:drop-table 'table1))
(query (:drop-table :if-exists 'table1))
(query (:drop-table :if-exists 'table1 :cascade))
(query (:drop-table (:if-exists 'table1-with-longer-name) :cascade))
(let ((table-var1 "table1"))
    (is (equal (sql (:drop-table :if-exists table-var1 :cascade))
               "DROP TABLE IF EXISTS table1 CASCADE"))
(let ((table-var1 'table-1))
    (is (equal (sql (:drop-table :if-exists table-var1 :cascade))
               "DROP TABLE IF EXISTS table_1 CASCADE"))

Truncates one or more tables, deleting all the rows. Optional keyword arguments are allowed in the following order. Note that :continue-identity and :restart-identity make no sense if both are included.

• :only (if not specified, the table and its descendants are truncated).
• :continue-identity (the values of sequences will not be changed. This is the default)
• :restart-identity (the values of sequences owned by the table(s) will be restarted)
• :cascade (will cascade the truncation through tables using foreign keys.)

Example calls would be:

(query (:truncate 'bigtable 'fattable))

(query (:truncate 'bigtable 'fattable :only))

(query (:truncate 'bigtable 'fattable :only :continue-identity))

(query (:truncate 'bigtable 'fattable :restart-identity))

(query (:truncate 'bigtable 'fattable :only :restart-identity :cascade ))

Create an index on a table. After the name of the index the keyword :on should follow, with the table name after it. Then the keyword :fields, followed by one or more column names. Other optional parameters are :using (to use index methods other than b-tree) :with (for storage parameters) or :where (for a condition can be added at the end to make a partial index). See S-SQL-C for more examples.

(query (:create-index 'films_idx :on 'films :fields 'title))

(query (:create-index 'films-idx :on "films" :fields 'title 'id))

(query (:create-index 'gin-idx :on "historical-events" :using 'gin :fields 'data))

Expressions can be used in the :fields specification:

(query (:create-index 'films_idx :on "films" :fields (:lower 'title)))

Opclasses can be used within the fields specification:

(query (:create-index 'pointloc2 :on 'points :using 'gist
        :fields (:asc (:box 'location 'location)) (:nulls-last 'name)))

Storage parameters can be set using a :with clause:

(query (:create-index 'gin-idx :on 'documents-table :using gin
        :fields 'locations :with (:= 'deduplicate-items off) (:= 'fillfactor 70)))

Partial indexes can be set using a :where clause:

(query (:create-index 'orders_unbilled_idx :on 'orders :fields 'order-nr
        :where (:and (:is-not-true 'billed)
                            (:< 'order_nr 1000)))))

Indexing a jsonb column named metadata:

(query (:create-index 'items-idx :on 'test-items :using 'gin
        :fields (:jsonb-path-ops 'metadata)))

Works like :create-index, except that the index created is unique.

Drop an index. Takes :if-exists and/or :cascade arguments like :drop-table. Accepts strings, variable or symbol as the identifier.

(query (:drop-index 'index1))
(query (:drop-index :if-exists 'index1))
(query (:drop-index :if-exists 'index1 :cascade))
(let ((table-var1 "table1"))
    (is (equal (sql (:drop-index :if-exists table-var1 :cascade))
               "DROP INDEX IF EXISTS table1 CASCADE"))

Create a sequence with the given name. The rest of the arguments control the way the sequence selects values.

Alters a sequence. See Postgresql documentation for parameters.

• :increment

Sets the amount by which each subsequent increment will be increased.

• :min-value
• :max-value
• :no-min
• :no-max
• :start
• :restart
• :cache
• :cycle
• :no-cycle
• :owned-by
• :if-exists before the name to suppress the error message.

Drop a sequence. Takes :if-exists and/or :cascade arguments like :drop-table. Accepts strings, variable or symbol as the identifier.

(query (:drop-sequence 'sequence1))
(query (:drop-sequence :if-exists 'sequence1))
(query (:drop-sequence :if-exists 'sequence1 :cascade))

Create a view from an S-SQL-style query.

Drop a view. Takes optional :if-exists argument. Accepts strings, variable or symbol as the identifier.

Configure whether deferrable constraints should be checked when a statement is executed, or when the transaction containing that statement is completed. The provided state must be either :immediate, indicating the former, or :deferred, indicating the latter. The constraints must be either the names of the constraints to be configured, or unspecified, indicating that all deferrable constraints should be thus configured.

Tell the server to listen for notification events on channel channel, a string, on the current connection.

Stop listening for events on channel.

Signal a notification event on channel channel, a string. The optional payload string can be used to send additional event information to the listeners.

Create a new role (user). Following the role name are optional keywords arguments:

• :options

One or more of the no-parameter options to PostgreSQL's CREATE ROLE SQL command.

• :password

Sets the role's password. (A password is only of use for roles having the LOGIN attribute, but you can nonetheless define one for roles without it.) If you do not plan to use password authentication you can omit this option. If no password is specified, the password will be set to null and password authentication will always fail for that user.

• :connection-limit

If role can log in, this specifies how many concurrent connections the role can make. -1 (the default) means no limit.

• :valid-until

The :valid-until clause sets a date and time after which the role's password is no longer valid. If this clause is omitted the password will be valid for all time.

• :role

Lists one or more existing roles which are automatically added as members of the new role. (This in effect makes the new role a “group”.)

• :in-role

Lists one or more existing roles to which the new role will be immediately added as a new member.

Here is an example of a :create-role form:

(query (:create-role 'user23
                     :options 'SUPERUSER 'NOINHERIT 'LOGIN
                     :password "mypassword"
                     :connection-limit 100 :role 'users))

Create a new database with the given name.

Drops the named database. You may optionally pass :if-exists before the name to suppress the error message. Examples:

(query (:drop-database 'database-name))
(query (:drop-database :if-exists 'database-name))
(let ((var 'my-database)) (query (:drop-database var)))

Move data between Postgres tables and filesystem files. Table name is required followed by one or more of the following keyword arguments. Documentation for the copy command provides a full reference. An example from the Greenplum tutorial:

(query
   (:copy 'faa.d_airlines
    :columns 'airlineid 'airline_desc
    :from "/home/gpadmin/gpdb-sandbox-tutorials/faa/L_AIRLINE_ID.csv"
    :on-segment t
    :binary t
    :oids t
    :header t
    :delimiter ","
    :null "NULL"
    :escape "my-escape-string"
    :newline "CR"
    :csv t
    :log-errors t
    :segment-reject-limit 100 'ROWS))

The question gets asked how to build dynamic or composable queries in postmodern. First we need to understand the context - is the programmer building the query or are you taking data from a user and using that to build a query?