Search PostgreSQL documentation

23505SQLSTATEReference23505 Class 23 · Integrity Constraint Violation 10 unique_violation 11 12 13 14 15 16 17 18 19 20 9.0 9.1 9.2 9.3 9.4 9.5 9.6 ## At a glance {#at-a-glance} `23505` is PostgreSQL's `unique_…SQLSTATE · Class 23 · Integrity Constraint Violation \dpsql CommandsPG18\d[Sx+] [ pattern ] For each relation (table, view, materialized view, index, sequence, or foreign table) or composite type matching the pattern, show all columns, their types, the tablespac…psql › Usage › Meta-Commands CREATE TABLESQL CommandsReferenceCREATE TABLE Tables & Views 10 define a new table CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [ { column_name data_type [ COLLATE colla…SQL Commands · Tables & Views EXPLAINSQL CommandsReferenceEXPLAIN Queries & Data 10 show the execution plan of a statement EXPLAIN [ ( option [, ...] ) ] statement EXPLAIN [ ANALYZE ] [ VERBOSE ] statement where option can be one of: ANALYZE [ boo…SQL Commands · Queries & Data jsonbData TypesPG18jsonb binary JSON data, decomposedData Types jsonb_setFunctionsReferencejsonb_set Json Functions And Operators 10 Returns target with the section designated by path replaced by new_value, or with new_value added if create_missing is true (default is true) and th…Functions · Json Functions And Operators pg_dumpCommand-line ToolsPG18pg_dump pg_dump — export a PostgreSQL database as an SQL script or to other formats Synopsis pg_dump [connection-option...] [option...] [dbname]pg_dump pg_stat_activityCatalogs & ViewsReferencepg_stat_activity Statistics views 10 Several predefined views, listed in Table 28.1, are available to show the current state of the system. There are also several other views, listed in Tabl…System Catalogs · Statistics views postgisExtensionsExtensionsPostGIS geometry and geography spatial types and functions postgis GeospatialGeospatial · Extensions SELECTSQL CommandsPG18Empty SELECT Lists The list of output expressions after SELECT can be empty, producing a zero-column result table. This is not valid syntax according to the SQL standard. PostgreSQL allows i…SELECT › Compatibility › Empty SELECT Lists SELECTSQL CommandsReferenceSELECT Queries & Data 10 retrieve rows from a table or view [ WITH [ RECURSIVE ] with_query [, ...] ] SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] [ * | expression [ [ AS ] output…SQL Commands · Queries & Data shared_buffersConfigurationReferenceshared_buffers Resource Usage 10 Sets the amount of memory the database server uses for shared memory buffers. The default is typically 128 megabytes (128MB), but might be less if your kerne…Configuration · Resource Usage vectorExtensionsExtensionsvector data type and ivfflat and hnsw access methods pgvector AI and vectorsAI and vectors · Extensions wal_levelConfigurationReferencewal_level Write-Ahead Log 10 wal_level determines how much information is written to the WAL. The default value is replica, which writes enough data to support WAL archiving and replication,…Configuration · Write-Ahead Log work_memConfigurationReferencework_mem Resource Usage 10 Specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files. The value defaults to four megabytes …Configuration · Resource Usage !FunctionsPG18! boolean → boolean Boolean NOT jsonb_path_query('[1, 3, 7]', '$[*] ? (!(@ < 5))') → 7JSON Functions and Operators › The SQL/JSON Path Language › SQL/JSON Path Operators and Methods !!FunctionsPG18!! tsquery → tsquery Negates a tsquery, producing a query that matches documents that do not match the input query. !! 'cat'::tsquery → !'cat'Text Search Functions and Operators !=FunctionsPG18value != value → boolean value <> value → boolean Non-equality comparison jsonb_path_query_array('[1, 2, 1, 3]', '$[*] ? (@ != 1)') → [2, 3] jsonb_path_query_array('["a", "b", "c"]', '$[*] ?…JSON Functions and Operators › The SQL/JSON Path Language › SQL/JSON Path Operators and Methods !~FunctionsPG18text !~ text → boolean String does not match regular expression, case sensitively 'thomas' !~ 't.*max' → tPattern Matching › POSIX Regular Expressions !~*FunctionsPG18text !~* text → boolean String does not match regular expression, case-insensitively 'thomas' !~* 'T.*ma' → fPattern Matching › POSIX Regular Expressions %FunctionsPG18number % number → number Modulo (remainder) jsonb_path_query('[32]', '$[0] % 10') → 2JSON Functions and Operators › The SQL/JSON Path Language › SQL/JSON Path Operators and Methods %%FunctionsPG18%% hstore → text[] Converts hstore to an array of alternating keys and values. %% 'a=>foo, b=>bar'::hstore → {a,foo,b,bar}hstore — hstore key/value datatype › hstore Operators and Functions %>FunctionsPG18text %> text → boolean Commutator of the <% operator.pg_trgm — support for similarity of text using trigram matching › Functions and Operators %>>FunctionsPG18text %>> text → boolean Commutator of the <<% operator.pg_trgm — support for similarity of text using trigram matching › Functions and Operators &FunctionsPG18bit & bit → bit Bitwise AND (inputs must be of equal length) B'10001' & B'01101' → 00001Bit String Functions and Operators &&FunctionsPG18cube && cube → boolean Do the cubes overlap?cube — a multi-dimensional cube data type › Usage &<FunctionsPG18geometric_type &< geometric_type → boolean Does first object not extend to the right of second? Available for box, polygon, circle. box '(1,1),(0,0)' &< box '(2,2),(0,0)' → tGeometric Functions and Operators &<|FunctionsPG18geometric_type &<| geometric_type → boolean Does first object not extend above second? Available for box, polygon, circle. box '(1,1),(0,0)' &<| box '(2,2),(0,0)' → tGeometric Functions and Operators &>FunctionsPG18geometric_type &> geometric_type → boolean Does first object not extend to the left of second? Available for box, polygon, circle. box '(3,3),(0,0)' &> box '(2,2),(0,0)' → tGeometric Functions and Operators *FunctionsPG18interval * double precision → interval Multiply an interval by a scalar interval '1 second' * 900 → 00:15:00 interval '1 day' * 21 → 21 days interval '1 hour' * 3.5 → 03:30:00Date/Time Functions and Operators +FunctionsPG18date + integer → date Add a number of days to a date date '2001-09-28' + 7 → 2001-10-05Date/Time Functions and Operators -FunctionsPG18- interval → interval Negate an interval - interval '23 hours' → -23:00:00Date/Time Functions and Operators ->FunctionsPG18cube -> integer → float8 Extracts the n-th coordinate of the cube (counting from 1).cube — a multi-dimensional cube data type › Usage ->>FunctionsPG18json ->> integer → text jsonb ->> integer → text Extracts n'th element of JSON array, as text. '[1,2,3]'::json ->> 2 → 3JSON Functions and Operators › Processing and Creating JSON Data -|-FunctionsPG18anyrange -|- anyrange → boolean Are the ranges adjacent? numrange(1.1,2.2) -|- numrange(2.2,3.3) → tRange/Multirange Functions and Operators /FunctionsPG18interval / double precision → interval Divide an interval by a scalar interval '1 hour' / 1.5 → 00:40:00Date/Time Functions and Operators 00000SQLSTATEReference00000 Class 00 · Successful Completion 10 successful_completion 11 12 13 14 15 16 17 18 19 20 9.0 9.1 9.2 9.3 9.4 9.5 9.6 # 00000 — successful_completion ## At a glance {#at-a-glance} `000…SQLSTATE · Class 00 · Successful Completion 01000SQLSTATEReference01000 Class 01 · Warning 10 warning 11 12 13 14 15 16 17 18 19 20 9.0 9.1 9.2 9.3 9.4 9.5 9.6 ## At a glance {#at-a-glance} `01000` is the generic `warning` condition in Class 01, `Warning…SQLSTATE · Class 01 · Warning 01003SQLSTATEReference01003 Class 01 · Warning 10 null_value_eliminated_in_set_function 11 12 13 14 15 16 17 18 19 20 9.0 9.1 9.2 9.3 9.4 9.5 9.6 # 01003 — null_value_eliminated_in_set_function ## At a glance {#…SQLSTATE · Class 01 · Warning 01004SQLSTATEReference01004 Class 01 · Warning 10 string_data_right_truncation 11 12 13 14 15 16 17 18 19 20 9.0 9.1 9.2 9.3 9.4 9.5 9.6 # 01004 — string_data_right_truncation ## At a glance {#at-a-glance} `010…SQLSTATE · Class 01 · Warning