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