SQL (PostgreSQL Variant)
Functions
date_part(field, source)
- Get the component of a
date
- date_part was a Postgresql-specific wrapper around the SQL standard
extract()
. Starting with PostgreSQL 14,EXTRACT()
was changed to return typenumeric
instead offloat8
to avoid loss-of-precision issues (See StackOverflow and PostgreSQL 14.0 Release Notes - See Neon PostgreSQL Tutorial: DATE_PART() Function
- Get the component of a
alter table table_name rename to new_table_name;
- Rename a table
- See Neon PostgreSQL Tutorial:PostgreSQL Rename Table
agg_func(<expr>) FILTER (WHERE <condition>)
- Aggregate values which match a condition
- Could also be done with a
case
statement - Crunchy Data: Using Postgres FILTER
coalesce(value1, value2, ...)
- Returns the first non-null argument
- Sleep Functions
pg_sleep_for(interval)
- Sleep for a specified interval (e.g., ‘5 minutes’, ‘1.5 seconds’)pg_sleep(seconds)
- Sleep for a specified number of seconds (can be fractional)pg_sleep_until(timestamp)
- Sleep until a specific timestamp- See PostgreSQL Date/Time Functions - Delaying Execution
- Division of integers
- By default, the division of two integers is integer division
- To do floating point division, you need to cast at least of the integers to numeric
- See PostgreSQL Numeric Division
Client Instructions
set
vsset local
set
changes configuration parameters for the entire session (connection)set local
changes configuration parameters only for the current transactionset local
settings are automatically reverted when the transaction commits or rolls back. For this reason, it must be executed within a transaction block.
plpgsql.extra_errors
- Controls additional error checking in PL/pgSQL stored procedures and functions
- Possible values:
'none'
(default),'all'
, or comma-separated list of specific checks - Available checks:
'strict_multi_assignment'
ensures variable count matches value count in assignments'shadowed_variables'
warns about variable name conflicts in nested scopes'too_many_rows'
raises errors when SELECT INTO returns multiple rows- Generally used during development to catch potential bugs
- See PostgreSQL PL/pgSQL Extra Checks
client_min_messages
- Controls which message types are sent from the server to the client
- Possible values (from lowest to highest):
debug5
,debug4
,debug3
,debug2
,debug1
,log
,notice
(default),warning
,error
- Setting to
warning
means only WARNING, ERROR, FATAL, and PANIC messages are shown - Lower settings show more verbose output, higher settings show fewer messages
- See PostgreSQL Client Connection Defaults
statement_timeout
- Sets a maximum execution time limit for individual SQL statements
- Possible values: time intervals (e.g.,
'10ms'
,'1s'
,'5min'
) or0
(no timeout, default) - Any statement that runs longer than the specified time will be automatically cancelled
- See PostgreSQL Statement Timeout
transaction_timeout
- Sets a maximum execution time limit for entire transactions (added in PostgreSQL 17)
- Possible values: time intervals (e.g.,
'1min'
,'5min'
,'1h'
) or0
(no timeout, default) - Terminates any session with a transaction that runs longer than the specified time
- Applies to both explicit transactions (BEGIN/COMMIT) and implicit single-statement transactions
- See PostgreSQL Transaction Timeout
Snippets
Print all the keys and values of a record (Useful for debugging)
for _key, _value in select key, value from jsonb_each_text(to_jsonb(_record)) loop
raise warning '% : %', _key, _value;
end loop;
Watch out for
Null Equality Always Returns Null
select null='Hello' , null!='Hello', null=null,null!=null;
gives (null, null, null, null)
To do:
- Add
Using
- Add
Distinct on
- row_to_json