Joshua's Docs - PostgreSQL Cheatsheet

WIP

Schemas

Schemas are kind of like a combination of the concept of a namespace and scoped silo. Schemas allow you to define subsections of a single DB, which are unique in a few different ways:

  • DB component/object separation:
    • You can separate tables, views, and more, by schema
    • In this way, a schema can be thought of acting like a subdirectory or sub-database of the main.
  • Namespace separation
    • With schemas, you can have multiple tables with identical table names, as long as they are separated by schemas.
      • Without schemas this is not possible
    • The separate namespaces makes it ideal when integrating third-party connectors / sources, since you don't have to worry about name collisions - just use sep. schema.
  • Scoped access and separation
    • You can actually separate users by schema and only grant access to some vs all.
    • This is kind of where schema acts like a sub-database, since normally, you can't scope sections of a database (tables, views, etc.) to certain users; it is more of an all or nothing thing.

Primary Key and Sequences

Assuming an auto-incrementing integer primary key, there are a few things to know:

Retrieving the current sequence

  • You can use SELECT MAX(id) FROM {tableName} to get the current max ID
  • You can use a sequence manipulation function to get lower level info
    • LASTVAL() - get the last value returned by nextval
      • basically, "where is the sequence at?"
    • CURRVAL(regclass) - same as lastval, but you can use regclass to specify target
    • NEXTVAL(regclass) - advances the sequence 1 step, and returns value
    • SETVAL(regclass, bigint) - Sets the sequence's current value to whatever you want
  • If the above fail due to cross-session issues, here are some more tricks:
    • SELECT last_value FROM {sequenceName}
    • SELECT MAX(id) FROM {tableName}
    • Use a function and/or stored procedure (see below)

Here is a custom function I whipped up:

CREATE OR REPLACE FUNCTION get_last_id(TABLE_NAME TEXT)
	RETURNS BIGINT AS $id$
DECLARE
	SEQ_NAME TEXT;
	SEQ_INT BIGINT;
BEGIN
	SEQ_NAME := PG_GET_SERIAL_SEQUENCE(TABLE_NAME, 'id');
	EXECUTE CONCAT('SELECT last_value FROM ', SEQ_NAME)
		INTO SEQ_INT;
	RETURN SEQ_INT;
END;
$id$
LANGUAGE PLPGSQL;

/* Once you create, you can call with: */
/* SELECT get_last_id('my_table_name'); */

Regclass note

You can either access by CURRVAL('{schema}.{table}_id_seq'::regclass), or, without needing to know seq name: CURRVAL(PG_GET_SERIAL_SEQUENCE({tableName}, 'id'))

For more details, see the docs on Sequence Manipulation Functions.

Resetting the sequence

If the sequence gets screwed up due to migrations / rollbacks / etc., you have some options for fixing. For bulk resets, see https://wiki.postgresql.org/wiki/Fixing_Sequences.

For individual fixes, you can always use SETVAL combined with SELECT MAX(), like so:

SELECT SETVAL('{sequenceName}',SELECT MAX(id) FROM {tableName})

Auto generated sequence names

With an auto-increment PK, generally PG will automatically create a named sequence, that looks something like: {schemaName}.{tableName}_id_seq. So, for example: admin.master_log_id_seq.

You can manually get the name with SELECT PG_GET_SERIAL_SEQUENCE('{tableName}', 'id');


Various issues / common FAQ

  • RAISE NOTICE has no effect
    • relies on certain logging features being enabled. Maybe the client you are using does not support them?
    • For example, the HeidiSQL desktop client does not support them.
  • I can't seem to return anything, and/or getting return cannot have parameter
    • Are you trying to return from an anonymous code block, and not a function?
      • You can basically only return from a function
      • You definitely cannot return from a DO block / anonymous block
    • There are two workarounds if you can't use functions:
      • Use RAISE NOTICE to log the output
      • Create a temp table and save the results there, then select and output, and then cleanup
  • How do I drop all rows from all tables?
    • Indiscriminately dropping all rows from a table is known as "truncating"
    • Here is a nice PgSQL function you can use to accomplish this: S/O Answer
Markdown Source Last Updated:
Fri Jun 14 2024 03:36:50 GMT+0000 (Coordinated Universal Time)
Markdown Source Created:
Mon Oct 21 2019 05:23:13 GMT+0000 (Coordinated Universal Time)
© 2024 Joshua Tzucker, Built with Gatsby
Feedback