Prepstellar
Get the app

SQL Fundamentals · SQL Foundations

19 cards

SQL Lexical Structure

Swipe, scroll or use ← →

  1. Commands, tokens, and comments

    Before the server can act on anything it has to cut the text you sent into pieces. SQL input is a sequence of commands, and each command is a sequence of tokens terminated by a semicolon or by the end of the input stream. That is why a script can hold many statements in one file: the semicolon says where one ends and the next begins.

    A token can be a keyword, an identifier, a quoted identifier, a literal, or a special-character symbol.

    Token Example
    Keyword SELECT, UPDATE
    Identifier orders, total_price
    Quoted identifier "select", "SalesTotal"
    Literal (constant) 42, 'Dianne'
    Special-character symbol ,, (, *

    Whitespace normally separates tokens, although separation is unnecessary where the boundary is unambiguous — a comma or a parenthesis already marks the end of what came before it.

    1 / 19

  2. Quick check

    A script holds several statements in one file. What normally marks the end of each command?

    1. AA comma between the statements

      A comma separates items inside a statement, such as entries of a select list, and never closes the command.

    2. BA double dash before the next statement

      A double dash opens a comment that runs to the end of the line; it terminates nothing.

    3. CA semicolon, or the end of the input stream

      Right. A command is a sequence of tokens terminated by a semicolon or by the end of the input stream.

    2 / 19

  3. Commands, tokens, and comments

    Comments are the one piece of text the parser is allowed to throw away. Comments are not tokens; they act like whitespace after being removed before syntax analysis. So a comment can sit anywhere a space could sit, and it never becomes part of the command.

    There are two forms:

    Form Where it ends
    A double dash, -- like this At the end of its line
    A slash and asterisk, /* like this */ At the matching asterisk followed by a slash

    A double dash starts a comment that runs to the end of its line. A slash followed by an asterisk opens a block comment, which ends at the matching asterisk followed by a slash, and PostgreSQL block comments can nest. Nesting matters in practice: a block comment wrapped around code that already contains one is closed correctly instead of ending early.

    3 / 19

  4. Quick check

    How does PostgreSQL treat a comment before syntax analysis?

    1. AIt removes the comment, which then acts like whitespace

      Right. Comments are not tokens: they are removed and effectively become whitespace before the syntax is analysed.

    2. BIt evaluates the comment as a string constant

      A comment is never evaluated; only a quoted constant produces a value.

    3. CIt keeps the comment as a token so the parser can skip it

      Comments are explicitly not tokens, so nothing keeps them for the parser to skip over.

    4 / 19

  5. Identifiers and keywords

    Two kinds of word look alike and behave very differently. Keywords have fixed meanings, while identifiers name tables, columns, or other database objects. SELECT is a keyword; orders is an identifier.

    Unquoted identifiers and keywords are case-insensitive in PostgreSQL, and PostgreSQL folds unquoted names to lower case. So Orders, ORDERS, and orders all arrive at the parser as orders, and a keyword can be typed in any case.

    Written Understood as
    SalesTotal salestotal
    SALESTOTAL salestotal
    "SalesTotal" SalesTotal, exactly as typed

    Folding is convenient until a name has to survive intact, and that is where quoting comes in.

    5 / 19

  6. Quick check

    A column is created unquoted as SalesTotal. Which statement about that name is true?

    1. AIts capital letters are preserved, because names are stored as typed

      Nothing preserves the capitals of an unquoted name; PostgreSQL does not store it as typed.

    2. BIt is folded to lower case, because unquoted names are case-insensitive

      Right. Unquoted identifiers are case-insensitive and PostgreSQL folds them to lower case.

    3. CIt becomes a keyword, because it was written without any quoting

      Keywords have fixed meanings of their own; writing a name unquoted never turns it into one.

    6 / 19

  7. Keep your progress in the app

    That’s 3 of 9 quick checks. In the app they stay answered, and every lesson remembers where you left off.

    Get the app Keep reading here

  8. Identifiers and keywords

    Double quotes are the identifier delimiter, and they do two jobs at once.

    A double-quoted token is always an identifier rather than a keyword. That is what lets a column be called select without colliding with the command of the same name.

    Quoting also makes an identifier case-sensitive, so foo, "Foo", and "FOO" need not identify the same object. To include a double quote inside a quoted identifier, write two adjacent double quotes.

    One portability note: PostgreSQL's lower-case folding differs from the SQL standard's upper-case folding, so portable code should consistently quote a particular name or never quote it. Mixing the two habits for the same name is what breaks when the code moves.

    7 / 19

  9. Quick check

    A query must refer to a column named select and another named SalesTotal, keeping both names exactly. Which select list works?

    1. A"select", "SalesTotal"

      Right. Double quotes make select an identifier rather than a keyword and preserve the mixed case of SalesTotal.

    2. B'select', 'SalesTotal' in single quotes

      Single quotes produce string constants, so the query would select two pieces of text instead of two columns.

    3. Cselect, SalesTotal

      Unquoted, select collides with the keyword and SalesTotal is folded to lower case, so neither name survives.

    8 / 19

  10. String literals

    Single quotes do for text what double quotes do for names. A regular string constant is enclosed in single quotes, as in 'Dianne'.

    The obvious problem is a value that itself contains an apostrophe. To place a single quote inside that string, write two adjacent single quotes. The doubled pair is read as one character, so 'Dianne''s horse' stores the text Dianne's horse.

    Delimiter What it creates
    '…' A regular string constant
    '' inside a string One embedded single quote
    "…" An identifier, never a string

    Keeping the two delimiters apart is the single most useful habit in this section: quotes are not interchangeable.

    9 / 19

  11. String literals

    PostgreSQL adds two extensions to that regular form, and knowing they are extensions is part of using them well.

    PostgreSQL escape strings use an E immediately before the opening single quote, and backslash begins a C-style escape sequence inside them. With standard_conforming_strings enabled, backslash escapes are recognized only in escape string constants, so a backslash inside a plain '…' string is just a backslash.

    PostgreSQL dollar-quoted strings preserve their content literally and are not part of the SQL standard. Writing $$Dianne's horse$$ keeps every character as it stands, which is handy for bodies of code but is not portable syntax.

    Form Standard SQL? Behaviour
    'Dianne''s horse' Yes The doubled quote is one apostrophe
    E'Dianne\'s horse' No, PostgreSQL escape string Backslash starts a C-style escape
    $$Dianne's horse$$ No, PostgreSQL extension Content preserved literally
    10 / 19

  12. Quick check

    A value must contain the text Dianne's horse using the regular SQL string form, with no PostgreSQL-specific extension. Which expression fits?

    1. AE'Dianne\'s horse', which escapes the apostrophe with a backslash

      An escape string is a PostgreSQL extension, and its backslash escapes are recognized only in that form.

    2. B$$Dianne's horse$$, which preserves its content literally

      Dollar quoting keeps the content literally but is not part of the SQL standard, so it fails the second requirement.

    3. C'Dianne''s horse'

      Right. The regular form uses single-quote delimiters and writes the embedded apostrophe as two adjacent single quotes.

    11 / 19

  13. Numeric, Boolean, and explicitly typed literals

    A number written into a statement also arrives with a type, chosen before anything else looks at it.

    First, a sign is not part of the number. A leading plus or minus sign is an operator applied to a numeric constant rather than part of the constant.

    Then the constant is typed by fit. A numeric constant without a decimal point or exponent starts as integer if it fits, then bigint if it fits, and otherwise numeric. A numeric constant with a decimal point or exponent initially has type numeric.

    Written Initial type
    42 integer
    9000000000 bigint (too large for integer)
    1.23 numeric (it has a decimal point)

    Context can coerce that initial type, and an explicit cast can force an intended type, so the initial choice is a starting point rather than a verdict.

    12 / 19

  14. Quick check

    A statement contains the whole-number constant 42, which fits comfortably in integer. What type does it start with?

    1. Anumeric, the type used for every literal number

      numeric is the initial type of a constant written with a decimal point or an exponent, and the fallback when neither integer type fits.

    2. Binteger, the narrowest size that fits it

      Right. A whole-number constant starts as integer when it fits, then bigint, and otherwise numeric.

    3. Cbigint, because whole numbers start at the widest size

      The sizes are tried from the narrowest that fits, so bigint is reached only when integer is too small.

    13 / 19

  15. Numeric, Boolean, and explicitly typed literals

    Truth values and dates are supplied as literals too, and each has a form that states what it is.

    PostgreSQL examples use true and false as Boolean argument values. They are written bare: quoting one turns it into something else, since 'true' is a string constant and "true" is an identifier.

    For other types there are three ways to make the intention explicit. A literal of another intended type can use the form type 'string', the PostgreSQL form 'string'::type, or CAST ('string' AS type).

    Form Example
    type 'string' DATE '2030-01-15'
    'string'::type '2030-01-15'::date
    CAST ('string' AS type) CAST ('2030-01-15' AS date)

    Prefixing a date or time string with its intended type makes that type explicit instead of leaving it to contextual inference, which is why DATE '2030-01-15' is dependable where a bare '2030-01-15' depends on where it is used.

    14 / 19

  16. Quick check

    A Boolean argument and a date literal must both be written so their types are unmistakable. Which pair does that?

    1. Atrue, and DATE '2030-01-15'

      Right. Boolean values are supplied as the bare tokens true and false, and prefixing the string with DATE states the intended type instead of leaving it to context.

    2. B'true', and the plain string '2030-01-15'

      Quoting true makes it a string constant, and a bare quoted date leaves its type to contextual inference.

    3. C"true", and 2030-01-15

      Double quotes make true an identifier, and an unquoted date is not a string constant at all.

    15 / 19

  17. Numeric, Boolean, and explicitly typed literals

    Two of those three forms request the same conversion, but they do not travel equally well. The CAST form conforms to SQL, while the double-colon form is historical PostgreSQL syntax.

    Form Portable When it reads best
    CAST ('1.23' AS numeric) Yes, SQL-conforming Code that has to move between systems
    '1.23'::numeric No, PostgreSQL-specific Short expressions in PostgreSQL-only code
    numeric '1.23' Yes, for simple literals Prefixing a plain constant with its type

    The type 'string' prefix works on a simple literal written out in the statement, while CAST accepts an expression as well. So the question to ask is not only which type do I want but does this code need to be portable.

    16 / 19

  18. Quick check

    An expression must state an exact numeric target type using the SQL-conforming conversion syntax rather than a PostgreSQL shorthand. Which form fits?

    1. A1.23, relying on the initial type the constant is given

      A bare constant leans on its initial typing and on context, so no target type is stated at all.

    2. BCAST (1.23 AS numeric)

      Right. CAST states the numeric target explicitly and is the conversion form that conforms to SQL.

    3. C1.23::numeric, which names the target after a double colon

      The double-colon form does request numeric, but it is historical PostgreSQL syntax rather than the SQL-conforming one.

    17 / 19

  19. Key takeaways

    • A semicolon terminates an SQL command, and the end of the input stream does the same for the last one.
    • Comments are not tokens: they are removed before syntax analysis and act like whitespace.
    • Double quotes delimit identifiers; single quotes delimit regular string constants — and an unquoted name is folded to lower case.
    • An embedded single quote in a regular string is written as two adjacent single quotes.
    • Whole-number constants are typed by fit: integer, then bigint, then numeric; a decimal point or exponent starts at numeric.
    • true and false supply Boolean values, and a type 'string' prefix states an intended date or time type.
    • CAST is the SQL-conforming explicit conversion form, while the double-colon form is historical PostgreSQL syntax.
    18 / 19

  20. Quick check

    Which summary of SQL's lexical rules is right?

    1. AA comma ends a command, and single quotes delimit the names of database objects

      A comma separates items inside a statement, and single quotes delimit string constants rather than object names.

    2. BA semicolon ends a command, double quotes delimit identifiers, and a doubled single quote is an apostrophe inside a regular string

      Right. Those are the three habits that keep commands, names, and text apart from one another.

    3. CA double dash ends a command, and an unquoted name keeps whatever capitals it was written with

      A double dash opens a comment to the end of the line, and an unquoted name is folded to lower case.

    19 / 19

  21. 9 quick checks · then the test

    In the app, finishing the quick checks opens this lesson’s 10-question test, and the ones you miss come back exactly when you’re about to forget them.

    Take this lesson’s test → Next lesson: Values, Types, and ExpressionsGet the app
← Previous Keep your progress · Get the app Next card →

The whole course, on your phone

Lessons you can read, audio you can listen to on the way to work, and practice that remembers what you got wrong.

Get the app