--- name: "sql" section: "7l" platform: "linux" package: "postgresql" version: "6.5.3-27" release: "Debian 2.2 (Potato)" platforms: ["linux"] url: "https://new.carta.tech/sql/7l" --- # sql(7l) ## Section 4 - SQL Commands (COMMANDS) ## General Information ## DESCRIPTION The following is a description of the general syntax of SQL. Individual SQL statements and commands are treated separately in the document; this section describes the syntactic classes from which the constituent parts of SQL statements are drawn. ## Comments A *comment* is an arbitrary sequence of characters following double dashes up to the end of the line. We also support double-slashes as comments, e.g.: ``` -- This is a standard SQL comment // And this is another supported comment style, like C++ ``` We also support C-style comments, e.g.: ``` /* multi line comment */ ``` ## Names *Names* in SQL are sequences of less than NAMEDATALEN alphanumeric characters, starting with an alphabetic character. By default, NAMEDATALEN is set to 32, but at the time the system is built, NAMEDATALEN can be changed by changing the #ifdef in src/backend/include/postgres.h. Underscore (“\_”) is considered an alphabetic character. ## Keywords The following identifiers are reserved for use as *keywords* and may not be used otherwise: ``` ``` ``` ``` In addition, all Postgres classes have several predefined attributes used by the system. ## Constants There are six types of *constants* for use in SQL. They are described below. ## String Constants *Strings* in SQL are arbitrary sequences of ASCII characters bounded by single quotes (' '). Uppercase alphabetics within strings are accepted literally. Non-printing characters may be embedded within strings by prepending them with a backslash, e.g., \`\\n'. Also, in order to embed quotes within strings, it is necessary to prefix them with \`\\' . The same convention applies to \`\\' itself. Because of the limitations on instance sizes, string constants are currently limited to a length of a little less than 8192 bytes. Larger objects may be created using the Postgres Large Object interface. ## Integer Constants *Integer constants* in SQL are collection of ASCII digits with no decimal point. Legal values range from −2147483647 to +2147483647. This will vary depending on the operating system and host machine. ## Floating Point Constants *Floating point constants* consist of an integer part, a decimal point, and a fraction part or scientific notation of the following format: ``` {} .{} [e [+-] {}] ``` Where \ is a digit. You must include at least one \ after the period and after the \[+-\] if you use those options. An exponent with a missing mantissa has a mantissa of 1 inserted. There may be no extra characters embedded in the string. Floating point constaints are of type float4. ## Constants of Postgres User-Defined Types A constant of an *arbitrary* type can be entered using the notation: ``` ``` or ``` CAST 'string' AS type-name ``` The value inside the string is passed to the input conversion routine for the type called type-name. The result is a constant of the indicated type. The explicit typecast may be omitted if there is no ambiguity as to the type the constant must be, in which case it is automatically coerced. ## Array constants *Array constants* are arrays of any Postgres type, including other arrays, string constants, etc. The general format of an array constant is the following: ``` {} ``` Where *\* is the delimiter for the type stored in the “pg\_type” class. (For built-in types, this is the comma character, “,”.) An example of an array constant is ``` {{1,2,3},{4,5,6},{7,8,9}} ``` This constant is a two-dimensional, 3 by 3 array consisting of three sub-arrays of integers. Individual array elements can and should be placed between quotation marks whenever possible to avoid ambiguity problems with respect to leading white space. ## FIELDS AND COLUMNS ## Fields A *field* is either an attribute of a given class or one of the following: ``` oid xmin xmax cmin cmax ``` *Oid* stands for the unique identifier of an instance which is added by Postgres to all instances automatically. Oids are not reused and are 32 bit quantities. *Xmin, cmin, xmax* and *cmax* stand respectively for the identity of the inserting transaction, the command identifier within the transaction, the identity of the deleting transaction and its associated deleting command. For further information on these fields consult \[STON87\]. Times are represented internally as instances of the “abstime” data type. Transaction and command identifiers are 32 bit quantities. Transactions are assigned sequentially starting at 512. ## Columns A *column* is a construct of the form: ``` Instance-variable{.composite_field}.field `['number`]' ``` *Instance-variable* identifies a particular class and can be thought of as standing for the instances of that class. An instance variable is either a class name, a surrogate for a class defined by means of a *from* clause, or the keyword **new** or **current.** New and current can only appear in the action portion of a rule, while other instance variables can be used in any SQL statement. *Composite\_field* is a field of of one of the Postgres composite types indicated in the *pgbuiltin(l)* section, while successive composite fields address attributes in the class(s) to which the composite field evaluates. Lastly, *field* is a normal (base type) field in the class(s) last addressed. If *field* is of type array, then the optional *number* designator indicates a specific element in the array. If no number is indicated, then all array elements are returned. ## Operators Any built-in system, or user-defined operator may be used in SQL. For the list of built-in and system operators consult [**pgbuiltin**(3)](/pgbuiltin/3.md). For a list of user-defined operators consult your system administrator or run a query on the pg\_operator class. Parentheses may be used for arbitrary grouping of operators. ## Expressions (a\_expr) An *expression* is one of the following: ``` ( a_expr ) constant attribute a_expr binary_operator a_expr a_expr right_unary_operator left_unary_operator a_expr parameter functional expressions aggregate expressions ``` We have already discussed constants and attributes. The two kinds of operator expressions indicate respectively binary and left\_unary expressions. The following sections discuss the remaining options. ## Parameters A *parameter* is used to indicate a parameter in a SQL function. Typically this is used in SQL function definition statement. The form of a parameter is: ``` ´$' number ``` For example, consider the definition of a function, DEPT, as ``` create function DEPT (name) returns dept as 'select * from dept where name=$1' language 'sql' ``` ## Functional Expressions A *functional expression* is the name of a legal SQL function, followed by its argument list enclosed in parentheses, e.g.: ``` fn-name (a_expr{ , a_expr}) ``` For example, the following computes the square root of an employee salary. ``` sqrt(emp.salary) ``` ## Aggregate Expression An *aggregate expression* represents a simple aggregate (i.e., one that computes a single value) or an aggregate function (i.e., one that computes a set of values). The syntax is the following: ``` aggregate.name (attribute) ``` Here, *aggregate\_name* must be a previously defined aggregate. ## Target\_list A *target list* is a parenthesized, comma-separated list of one or more elements, each of which must be of the form: ``` a_expr[AS result_attname] ``` Here, result\_attname is the name of the attribute to be created (or an already existing attribute name in the case of update statements.) If *result\_attname* is not present, then *a\_expr* must contain only one attribute name which is assumed to be the name of the result field. In Postgres default naming is only used if *a\_expr* is an attribute. A *qualification* consists of any number of clauses connected by the logical operators: ``` not and or ``` A clause is an *a\_expr* that evaluates to a Boolean over a set of instances. ## From List The *from list* is a comma-separated list of *from expressions*. Each *from expression* is of the form: ``` [class_reference] instance_variable {, [class_ref] instance_variable...} ``` where *class\_reference* is of the form ``` class_name [*] ``` The *from expression* defines one or more instance variables to range over the class indicated in *class\_reference*. One can also request the instance variable to range over all classes that are beneath the indicated class in the inheritance hierarchy by postpending the designator “\*”. ## SEE ALSO insert(l), delete(l), execute(l), update(l), select(l), [psql(1)](/psql/1.md).