Postgres snippets¶
long snippets¶
short snippets¶
SELECT * FROM _ WHERE _;
SELECT
DISTINCT ON (_)
*
, _ AS _
FROM _
WHERE _ AND _
GROUP BY _
HAVING _
ORDER BY _ DESC
LIMIT 1 OFFSET 0
;
(?:(?:)?? __) -- makes the *outer* () group non-greedy
$$||util.quote_typed(_)||$$
:= true
[[:alnum:]_]+
ALTER TABLE _ ALTER COLUMN _ TYPE _ USING _;
ANALYZE VERBOSE
AND
= ANY('{_,_}'::text[])
NOT (_ = ANY('{_,_}'::text[]))
anyarray
anyelement
array_agg
array_out
ARRAY['_', '_']
'{}'::text[]
'{_,_}'::text[]
backup
benign error
BETWEEN _ AND _
(?<=[[:blank:]])
[[:blank:]]+
bool_and bool_or
boolean DEFAULT true/false
broken/OK
CASCADE
cascadingly delete
CASE WHEN true THEN _ ELSE _ END
CAST(NULL AS _)
::_
character
CHECK constraint
CLUSTER _ USING _;
COALESCE(_, '')
collision
COMMENT ON VIEW _ IS '_';
concat_ws
constraint
COPY FROM
COUNT(*)
COUNT(NULLIF(_, false))
CREATE CAST (_ AS _) WITH INOUT AS IMPLICIT;
CREATE OR REPLACE VIEW _ AS _;
CREATE TABLE _ (LIKE _ INCLUDING ALL); INSERT INTO _ SELECT * FROM _;
CREATE TEMP TABLE _ AS _;
SELECT util.create_if_not_exists($$ $$);
CROSS JOIN LATERAL (_) s
cstring
CSV
("current_schema"()::text)
data dictionary
database
datasources
DEFAULT NULL
definition
DELETE FROM _ WHERE _ IN (SELECT __);
denormalized denormalization
dependent views
derived columns
disabled
double precision
DROP ROLE
.eml.txt
ERROR
SELECT util.eval($$ $$)
exception
EXECUTE
EXISTS
EXPLAIN
expr
foreign key
FULL JOIN
function
Google spreadsheet
hstore
idempotent
IF _ THEN _ ELSIF _ THEN _ ELSE _ END IF;
IMMUTABLE
import
**IMPORTANT:**
IN (_)
index scan
infinite recursion
inheritance
inner join
INSERT INTO _ DEFAULT VALUES;
::integer
IS NOT DISTINCT FROM
IS NULL
IS NOT NULL
LANGUAGE plpgsql
LEFT JOIN _ USING (_)
LEFT JOIN _ ON _
left-join
LIKE
local
[[:lower:]]+
mailto:see_popup
metadata
NATURAL LEFT JOIN
nextval('_')
normalized normalization
NOT
NOTICE
NULL::void
NULLIF
::oid
operator
optional
OR
OUT
PERFORM
pg_catalog
pg_get_expr(pg_node_tree, oid, /*pretty-print:*/true)
pg_temp
pg_typeof
phpPgAdmin
PL/pgSQL
postgis.st_asewkt
Postgres
primary key
query queries
query planner
quote_ident
quote_nullable/quote_literal
util.quote_typed
SELECT util.raise('NOTICE', _);
SELECT util.raise('WARNING', _);
record
refresh
::regclass
(SELECT regexp_matches(string, pattern))[1]
regexp_replace(string, pattern, replacement)
::regprocedure
::regtype
remove
RENAME
required
RESET
result
RETURN QUERY EXECUTE
RETURNING
RETURNS SETOF record
RIGHT JOIN
ROW
ORDER BY row_number() OVER () DESC
%ROWTYPE
-- runtime: _ min ("_") @_
schema comment
SET LOCAL _ = DEFAULT;
SET check_function_bodies = false;
SET client_min_messages = NOTICE;
SET client_min_messages = WARNING;
SET enable_seqscan = off;
SET enable_sort = off;
SET enable_hashjoin = off; -- better to keep this on if possible
SET enable_mergejoin = off;
SET enable_nestloop = off; SET enable_seqscan = off; -- force merge join (not necessarily faster)
SET extra_float_digits = 3; -- the maximum
SET join_collapse_limit = 1; -- turn it off
SET search_path = _;
ALTER FUNCTION _ SET search_path = pg_temp;
SQL
STABLE
staging tables
statement
STRICT
SELECT string_agg(expr(var), '_') FROM unnest($1) var
TABLE _
::text
timestamp with time zone
\timing on
trigger
TRUNCATE
%TYPE
UNION ALL
unique constraint
unknown
unnest
UPDATE _ SET _;
[[:upper:]]+
.url
usage:
WHERE
VACUUM ANALYZE VERBOSE
value
(VALUES (_), (_)) v (col, ...)
VARIADIC param text[]
verbatim
verbosity
::void
VOLATILE
**WARNING:**
Redmine code block¶
<pre><code class="SQL"></code></pre>
see also:¶