blob: ddb814ae16b721bf823257ee43e816b08d4f1bfb (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>Part II. The SQL Language</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="tutorial-conclusion.html" title="3.7. Conclusion" /><link rel="next" href="sql-syntax.html" title="Chapter 4. SQL Syntax" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">Part II. The SQL Language</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="tutorial-conclusion.html" title="3.7. Conclusion">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="index.html" title="PostgreSQL 16.3 Documentation">Up</a></td><th width="60%" align="center">PostgreSQL 16.3 Documentation</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-syntax.html" title="Chapter 4. SQL Syntax">Next</a></td></tr></table><hr /></div><div class="part" id="SQL"><div class="titlepage"><div><div><h1 class="title">Part II. The SQL Language</h1></div></div></div><div class="partintro" id="id-1.5.2"><div></div><p>
This part describes the use of the <acronym class="acronym">SQL</acronym> language
in <span class="productname">PostgreSQL</span>. We start with
describing the general syntax of <acronym class="acronym">SQL</acronym>, then
explain how to create the structures to hold data, how to populate
the database, and how to query it. The middle part lists the
available data types and functions for use in
<acronym class="acronym">SQL</acronym> commands. The rest treats several
aspects that are important for tuning a database for optimal
performance.
</p><p>
The information in this part is arranged so that a novice user can
follow it start to end to gain a full understanding of the topics
without having to refer forward too many times. The chapters are
intended to be self-contained, so that advanced users can read the
chapters individually as they choose. The information in this
part is presented in a narrative fashion in topical units.
Readers looking for a complete description of a particular command
should see <a class="xref" href="reference.html" title="Part VI. Reference">Part VI</a>.
</p><p>
Readers of this part should know how to connect to a
<span class="productname">PostgreSQL</span> database and issue
<acronym class="acronym">SQL</acronym> commands. Readers that are unfamiliar with
these issues are encouraged to read <a class="xref" href="tutorial.html" title="Part I. Tutorial">Part I</a>
first. <acronym class="acronym">SQL</acronym> commands are typically entered
using the <span class="productname">PostgreSQL</span> interactive terminal
<span class="application">psql</span>, but other programs that have
similar functionality can be used as well.
</p><div class="toc"><p><strong>Table of Contents</strong></p><dl class="toc"><dt><span class="chapter"><a href="sql-syntax.html">4. SQL Syntax</a></span></dt><dd><dl><dt><span class="sect1"><a href="sql-syntax-lexical.html">4.1. Lexical Structure</a></span></dt><dt><span class="sect1"><a href="sql-expressions.html">4.2. Value Expressions</a></span></dt><dt><span class="sect1"><a href="sql-syntax-calling-funcs.html">4.3. Calling Functions</a></span></dt></dl></dd><dt><span class="chapter"><a href="ddl.html">5. Data Definition</a></span></dt><dd><dl><dt><span class="sect1"><a href="ddl-basics.html">5.1. Table Basics</a></span></dt><dt><span class="sect1"><a href="ddl-default.html">5.2. Default Values</a></span></dt><dt><span class="sect1"><a href="ddl-generated-columns.html">5.3. Generated Columns</a></span></dt><dt><span class="sect1"><a href="ddl-constraints.html">5.4. Constraints</a></span></dt><dt><span class="sect1"><a href="ddl-system-columns.html">5.5. System Columns</a></span></dt><dt><span class="sect1"><a href="ddl-alter.html">5.6. Modifying Tables</a></span></dt><dt><span class="sect1"><a href="ddl-priv.html">5.7. Privileges</a></span></dt><dt><span class="sect1"><a href="ddl-rowsecurity.html">5.8. Row Security Policies</a></span></dt><dt><span class="sect1"><a href="ddl-schemas.html">5.9. Schemas</a></span></dt><dt><span class="sect1"><a href="ddl-inherit.html">5.10. Inheritance</a></span></dt><dt><span class="sect1"><a href="ddl-partitioning.html">5.11. Table Partitioning</a></span></dt><dt><span class="sect1"><a href="ddl-foreign-data.html">5.12. Foreign Data</a></span></dt><dt><span class="sect1"><a href="ddl-others.html">5.13. Other Database Objects</a></span></dt><dt><span class="sect1"><a href="ddl-depend.html">5.14. Dependency Tracking</a></span></dt></dl></dd><dt><span class="chapter"><a href="dml.html">6. Data Manipulation</a></span></dt><dd><dl><dt><span class="sect1"><a href="dml-insert.html">6.1. Inserting Data</a></span></dt><dt><span class="sect1"><a href="dml-update.html">6.2. Updating Data</a></span></dt><dt><span class="sect1"><a href="dml-delete.html">6.3. Deleting Data</a></span></dt><dt><span class="sect1"><a href="dml-returning.html">6.4. Returning Data from Modified Rows</a></span></dt></dl></dd><dt><span class="chapter"><a href="queries.html">7. Queries</a></span></dt><dd><dl><dt><span class="sect1"><a href="queries-overview.html">7.1. Overview</a></span></dt><dt><span class="sect1"><a href="queries-table-expressions.html">7.2. Table Expressions</a></span></dt><dt><span class="sect1"><a href="queries-select-lists.html">7.3. Select Lists</a></span></dt><dt><span class="sect1"><a href="queries-union.html">7.4. Combining Queries (<code class="literal">UNION</code>, <code class="literal">INTERSECT</code>, <code class="literal">EXCEPT</code>)</a></span></dt><dt><span class="sect1"><a href="queries-order.html">7.5. Sorting Rows (<code class="literal">ORDER BY</code>)</a></span></dt><dt><span class="sect1"><a href="queries-limit.html">7.6. <code class="literal">LIMIT</code> and <code class="literal">OFFSET</code></a></span></dt><dt><span class="sect1"><a href="queries-values.html">7.7. <code class="literal">VALUES</code> Lists</a></span></dt><dt><span class="sect1"><a href="queries-with.html">7.8. <code class="literal">WITH</code> Queries (Common Table Expressions)</a></span></dt></dl></dd><dt><span class="chapter"><a href="datatype.html">8. Data Types</a></span></dt><dd><dl><dt><span class="sect1"><a href="datatype-numeric.html">8.1. Numeric Types</a></span></dt><dt><span class="sect1"><a href="datatype-money.html">8.2. Monetary Types</a></span></dt><dt><span class="sect1"><a href="datatype-character.html">8.3. Character Types</a></span></dt><dt><span class="sect1"><a href="datatype-binary.html">8.4. Binary Data Types</a></span></dt><dt><span class="sect1"><a href="datatype-datetime.html">8.5. Date/Time Types</a></span></dt><dt><span class="sect1"><a href="datatype-boolean.html">8.6. Boolean Type</a></span></dt><dt><span class="sect1"><a href="datatype-enum.html">8.7. Enumerated Types</a></span></dt><dt><span class="sect1"><a href="datatype-geometric.html">8.8. Geometric Types</a></span></dt><dt><span class="sect1"><a href="datatype-net-types.html">8.9. Network Address Types</a></span></dt><dt><span class="sect1"><a href="datatype-bit.html">8.10. Bit String Types</a></span></dt><dt><span class="sect1"><a href="datatype-textsearch.html">8.11. Text Search Types</a></span></dt><dt><span class="sect1"><a href="datatype-uuid.html">8.12. <acronym class="acronym">UUID</acronym> Type</a></span></dt><dt><span class="sect1"><a href="datatype-xml.html">8.13. <acronym class="acronym">XML</acronym> Type</a></span></dt><dt><span class="sect1"><a href="datatype-json.html">8.14. <acronym class="acronym">JSON</acronym> Types</a></span></dt><dt><span class="sect1"><a href="arrays.html">8.15. Arrays</a></span></dt><dt><span class="sect1"><a href="rowtypes.html">8.16. Composite Types</a></span></dt><dt><span class="sect1"><a href="rangetypes.html">8.17. Range Types</a></span></dt><dt><span class="sect1"><a href="domains.html">8.18. Domain Types</a></span></dt><dt><span class="sect1"><a href="datatype-oid.html">8.19. Object Identifier Types</a></span></dt><dt><span class="sect1"><a href="datatype-pg-lsn.html">8.20. <code class="type">pg_lsn</code> Type</a></span></dt><dt><span class="sect1"><a href="datatype-pseudo.html">8.21. Pseudo-Types</a></span></dt></dl></dd><dt><span class="chapter"><a href="functions.html">9. Functions and Operators</a></span></dt><dd><dl><dt><span class="sect1"><a href="functions-logical.html">9.1. Logical Operators</a></span></dt><dt><span class="sect1"><a href="functions-comparison.html">9.2. Comparison Functions and Operators</a></span></dt><dt><span class="sect1"><a href="functions-math.html">9.3. Mathematical Functions and Operators</a></span></dt><dt><span class="sect1"><a href="functions-string.html">9.4. String Functions and Operators</a></span></dt><dt><span class="sect1"><a href="functions-binarystring.html">9.5. Binary String Functions and Operators</a></span></dt><dt><span class="sect1"><a href="functions-bitstring.html">9.6. Bit String Functions and Operators</a></span></dt><dt><span class="sect1"><a href="functions-matching.html">9.7. Pattern Matching</a></span></dt><dt><span class="sect1"><a href="functions-formatting.html">9.8. Data Type Formatting Functions</a></span></dt><dt><span class="sect1"><a href="functions-datetime.html">9.9. Date/Time Functions and Operators</a></span></dt><dt><span class="sect1"><a href="functions-enum.html">9.10. Enum Support Functions</a></span></dt><dt><span class="sect1"><a href="functions-geometry.html">9.11. Geometric Functions and Operators</a></span></dt><dt><span class="sect1"><a href="functions-net.html">9.12. Network Address Functions and Operators</a></span></dt><dt><span class="sect1"><a href="functions-textsearch.html">9.13. Text Search Functions and Operators</a></span></dt><dt><span class="sect1"><a href="functions-uuid.html">9.14. UUID Functions</a></span></dt><dt><span class="sect1"><a href="functions-xml.html">9.15. XML Functions</a></span></dt><dt><span class="sect1"><a href="functions-json.html">9.16. JSON Functions and Operators</a></span></dt><dt><span class="sect1"><a href="functions-sequence.html">9.17. Sequence Manipulation Functions</a></span></dt><dt><span class="sect1"><a href="functions-conditional.html">9.18. Conditional Expressions</a></span></dt><dt><span class="sect1"><a href="functions-array.html">9.19. Array Functions and Operators</a></span></dt><dt><span class="sect1"><a href="functions-range.html">9.20. Range/Multirange Functions and Operators</a></span></dt><dt><span class="sect1"><a href="functions-aggregate.html">9.21. Aggregate Functions</a></span></dt><dt><span class="sect1"><a href="functions-window.html">9.22. Window Functions</a></span></dt><dt><span class="sect1"><a href="functions-subquery.html">9.23. Subquery Expressions</a></span></dt><dt><span class="sect1"><a href="functions-comparisons.html">9.24. Row and Array Comparisons</a></span></dt><dt><span class="sect1"><a href="functions-srf.html">9.25. Set Returning Functions</a></span></dt><dt><span class="sect1"><a href="functions-info.html">9.26. System Information Functions and Operators</a></span></dt><dt><span class="sect1"><a href="functions-admin.html">9.27. System Administration Functions</a></span></dt><dt><span class="sect1"><a href="functions-trigger.html">9.28. Trigger Functions</a></span></dt><dt><span class="sect1"><a href="functions-event-triggers.html">9.29. Event Trigger Functions</a></span></dt><dt><span class="sect1"><a href="functions-statistics.html">9.30. Statistics Information Functions</a></span></dt></dl></dd><dt><span class="chapter"><a href="typeconv.html">10. Type Conversion</a></span></dt><dd><dl><dt><span class="sect1"><a href="typeconv-overview.html">10.1. Overview</a></span></dt><dt><span class="sect1"><a href="typeconv-oper.html">10.2. Operators</a></span></dt><dt><span class="sect1"><a href="typeconv-func.html">10.3. Functions</a></span></dt><dt><span class="sect1"><a href="typeconv-query.html">10.4. Value Storage</a></span></dt><dt><span class="sect1"><a href="typeconv-union-case.html">10.5. <code class="literal">UNION</code>, <code class="literal">CASE</code>, and Related Constructs</a></span></dt><dt><span class="sect1"><a href="typeconv-select.html">10.6. <code class="literal">SELECT</code> Output Columns</a></span></dt></dl></dd><dt><span class="chapter"><a href="indexes.html">11. Indexes</a></span></dt><dd><dl><dt><span class="sect1"><a href="indexes-intro.html">11.1. Introduction</a></span></dt><dt><span class="sect1"><a href="indexes-types.html">11.2. Index Types</a></span></dt><dt><span class="sect1"><a href="indexes-multicolumn.html">11.3. Multicolumn Indexes</a></span></dt><dt><span class="sect1"><a href="indexes-ordering.html">11.4. Indexes and <code class="literal">ORDER BY</code></a></span></dt><dt><span class="sect1"><a href="indexes-bitmap-scans.html">11.5. Combining Multiple Indexes</a></span></dt><dt><span class="sect1"><a href="indexes-unique.html">11.6. Unique Indexes</a></span></dt><dt><span class="sect1"><a href="indexes-expressional.html">11.7. Indexes on Expressions</a></span></dt><dt><span class="sect1"><a href="indexes-partial.html">11.8. Partial Indexes</a></span></dt><dt><span class="sect1"><a href="indexes-index-only-scans.html">11.9. Index-Only Scans and Covering Indexes</a></span></dt><dt><span class="sect1"><a href="indexes-opclass.html">11.10. Operator Classes and Operator Families</a></span></dt><dt><span class="sect1"><a href="indexes-collations.html">11.11. Indexes and Collations</a></span></dt><dt><span class="sect1"><a href="indexes-examine.html">11.12. Examining Index Usage</a></span></dt></dl></dd><dt><span class="chapter"><a href="textsearch.html">12. Full Text Search</a></span></dt><dd><dl><dt><span class="sect1"><a href="textsearch-intro.html">12.1. Introduction</a></span></dt><dt><span class="sect1"><a href="textsearch-tables.html">12.2. Tables and Indexes</a></span></dt><dt><span class="sect1"><a href="textsearch-controls.html">12.3. Controlling Text Search</a></span></dt><dt><span class="sect1"><a href="textsearch-features.html">12.4. Additional Features</a></span></dt><dt><span class="sect1"><a href="textsearch-parsers.html">12.5. Parsers</a></span></dt><dt><span class="sect1"><a href="textsearch-dictionaries.html">12.6. Dictionaries</a></span></dt><dt><span class="sect1"><a href="textsearch-configuration.html">12.7. Configuration Example</a></span></dt><dt><span class="sect1"><a href="textsearch-debugging.html">12.8. Testing and Debugging Text Search</a></span></dt><dt><span class="sect1"><a href="textsearch-indexes.html">12.9. Preferred Index Types for Text Search</a></span></dt><dt><span class="sect1"><a href="textsearch-psql.html">12.10. <span class="application">psql</span> Support</a></span></dt><dt><span class="sect1"><a href="textsearch-limitations.html">12.11. Limitations</a></span></dt></dl></dd><dt><span class="chapter"><a href="mvcc.html">13. Concurrency Control</a></span></dt><dd><dl><dt><span class="sect1"><a href="mvcc-intro.html">13.1. Introduction</a></span></dt><dt><span class="sect1"><a href="transaction-iso.html">13.2. Transaction Isolation</a></span></dt><dt><span class="sect1"><a href="explicit-locking.html">13.3. Explicit Locking</a></span></dt><dt><span class="sect1"><a href="applevel-consistency.html">13.4. Data Consistency Checks at the Application Level</a></span></dt><dt><span class="sect1"><a href="mvcc-serialization-failure-handling.html">13.5. Serialization Failure Handling</a></span></dt><dt><span class="sect1"><a href="mvcc-caveats.html">13.6. Caveats</a></span></dt><dt><span class="sect1"><a href="locking-indexes.html">13.7. Locking and Indexes</a></span></dt></dl></dd><dt><span class="chapter"><a href="performance-tips.html">14. Performance Tips</a></span></dt><dd><dl><dt><span class="sect1"><a href="using-explain.html">14.1. Using <code class="command">EXPLAIN</code></a></span></dt><dt><span class="sect1"><a href="planner-stats.html">14.2. Statistics Used by the Planner</a></span></dt><dt><span class="sect1"><a href="explicit-joins.html">14.3. Controlling the Planner with Explicit <code class="literal">JOIN</code> Clauses</a></span></dt><dt><span class="sect1"><a href="populate.html">14.4. Populating a Database</a></span></dt><dt><span class="sect1"><a href="non-durability.html">14.5. Non-Durable Settings</a></span></dt></dl></dd><dt><span class="chapter"><a href="parallel-query.html">15. Parallel Query</a></span></dt><dd><dl><dt><span class="sect1"><a href="how-parallel-query-works.html">15.1. How Parallel Query Works</a></span></dt><dt><span class="sect1"><a href="when-can-parallel-query-be-used.html">15.2. When Can Parallel Query Be Used?</a></span></dt><dt><span class="sect1"><a href="parallel-plans.html">15.3. Parallel Plans</a></span></dt><dt><span class="sect1"><a href="parallel-safety.html">15.4. Parallel Safety</a></span></dt></dl></dd></dl></div></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="tutorial-conclusion.html" title="3.7. Conclusion">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="index.html" title="PostgreSQL 16.3 Documentation">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-syntax.html" title="Chapter 4. SQL Syntax">Next</a></td></tr><tr><td width="40%" align="left" valign="top">3.7. Conclusion </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> Chapter 4. SQL Syntax</td></tr></table></div></body></html>
|
