Functions, Dates, Views and Procedures

Key points

1. 1

   String positions are 1-based. length counts characters and octet_length counts bytes, which differ for UTF-8 text.

2. 2

   || returns NULL if either side is NULL; concat and concat_ws skip NULLs. Integer / truncates, and mod/% take the sign of the dividend.

3. 3

   timestamptz stores an instant and displays it in the session TimeZone; timestamp stores wall-clock time. AT TIME ZONE converts between them in both directions.

4. 4

   now() is fixed for the whole transaction; clock_timestamp() reads the real clock. Month arithmetic clamps to the month end, and adding days vs hours differs across DST changes.

5. 5

   Plain views store only a query (SELECT * is expanded at creation). Simple views are updatable; WITH CHECK OPTION blocks writes the view could not see. REFRESH … CONCURRENTLY needs a unique index.

6. 6

   Functions run inside queries and cannot COMMIT; procedures are invoked with CALL and may commit when called outside a transaction block. Index and generated-column expressions must be IMMUTABLE.

7. 7

   In dynamic SQL use format() with %I for identifiers and %L for literals, or better, EXECUTE … USING parameters.

Read the source

• PostgreSQL: Date/Time Functions and Operators
• PostgreSQL: String Functions and Operators
• PostgreSQL: CREATE VIEW