Postgresql – What’s an “object”

postgresqlterminology

The Postgres documentation frequently uses the term "object". For instance, the descriptions of various \d commands at https://www.postgresql.org/docs/current/app-psql.html (like \dt for listing tables or \da for listing aggregate functions) use a phrase like:

By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects.

From this, I can infer that at least the specific things listed by each of those commands – like aggregate functions, tables, character set encodings, schemas, and a whole bunch of other stuff – are "objects" in Postgres's nomenclature. I can also make a decent guess that some of the things that can be listed with \d commands, like roles, are not objects, given that in their descriptions the word "object" has been avoided.

But what exactly does that mean? Presently I wouldn't be able to tell whether any particular thing (like a table row, or a table column, or a role) is properly considered to be an "object" except by happening across a spot in the Postgres documentation in which it's referred to as such. What's the underlying definition that the docs are using?

Best Answer

I can also make a decent guess that some of the things that can be listed with \d commands, like roles, are not objects, given that in their descriptions the word "object" has been avoided.

No, because roles are also refered to as objects in certain parts of the documentation. For instance: https://www.postgresql.org/docs/current/catalog-pg-shdepend.html:

The catalog pg_shdepend records the dependency relationships between database objects and shared objects, such as roles. This information allows PostgreSQL to ensure that those objects are unreferenced before attempting to delete them.

(shared in this context means shared across databases).

I would suggest as a baseline that everything in Postgres that must have an OID can be qualified as an object, and everything that doesn't have an OID is probably not an object. On top of that, some exceptions may exist: for example, it might be argued whether a database can be called an "object", depending on the context, although a database has an OID.

The oid is part of the system columns. It used to be present in older versions in every table and even every row, but this usage has been deprecated. This deprecation is an ongoing process: the next version of Postgres will transform the oid columns in the catalogs into normal columns.

Just like the use of oid is evolving , what can be qualified as an object is evolving too because new kinds of objects are added to Postgres in pretty much every new version, which concur to the lack of a formal definition.

Related Solutions

Postgresql – find all JSON scalars in PostgreSQL 9.3

Horrible as it is, you could write a PL/PgSQL function that attempts to extract an element from the object, traps the exception if it fails, and returns true on success or false on exception.

Untested:

create or replace function json_isobject(obj json)
returns boolean immutable language plpgsql as $$
begin
    begin
        perform obj->'';
    exception
      when invalid_parameter_value then
        return false;
      when others then
        raise;
    end;
    return true;
end;
$$;

This'll be pretty inefficient, since it's creating a sub-transaction for each call.

Or you could just update to 9.4.

On 9.4 I can't reproduce your reported error. Trying to look up a key in a json scalar returns null.

test=> select ('{"k":"v"}'::json) -> 'k' -> 'blah';
 ?column? 
----------

(1 row)

... and the same with using ->> to dereference a scalar.

So I'm not sure if this function will work on 9.3, and it won't on 9.4. To be 9.4-compatible you'd have to test if current_setting('pg_version_num') >= 90400 and return a result based on json_typeof instead.

You might need to adapt it to use the same expression you use to trigger your reported error.

PostgreSQL – Select Table Name Returning CSV of the Row

For each table that is created, Postgres creates a corresponding composite type:

Whenever you create a table, a composite type is also automatically created, with the same name as the table, to represent the table's row type.

When you use the table name in the select list, you are essentially using a single column of that composite type with several fields as part of the record (this is a good example on the difference between row and record and column and field).

The "CSV style" just happens to be the default text representation of a composite type.

A row constructor is closely related to this.

This is also the reason why select (a,b,c) from some_table is something different than select a,b,c from some_table in Postgres.

Best Answer

Related Solutions

Postgresql – find all JSON scalars in PostgreSQL 9.3

PostgreSQL – Select Table Name Returning CSV of the Row

Related Question