onprema

PostgreSQL: Notes

Last modified 1 month, 1 week ago.

When using the \d command in psql, it lists the schema, tables, views, and sequences.

postgres=# \d
                         List of relations
 Schema |                Name                |   Type   |  Owner
--------+------------------------------------+----------+----------
 public | dag_run                            | table    | postgres
 public | dag_run_id_seq                     | sequence | postgres
 public | dag_run_note                       | table    | postgres
 public | failed_tasks                       | view     | postgres
Term Think of it as...
Schema A namespace for organizing the database. The default is called "public".
Table The actual data storage: rows and columns. Like a spreadsheet.
View A saved query that looks like a table, but doesn't actually store data. Like a filter.
Sequence An auto-incrementing number generator, handled by Postgres.

I noticed that \d table_name refers to the table as public.table_name. For example:

postgres=# \d xcom;
                             Table "public.xcom"
   Column   |           Type           | Collation | Nullable |    Default
------------+--------------------------+-----------+----------+---------------
 dag_run_id | integer                  |           | not null |
 task_id    | character varying(250)   |           | not null |
...

public is the name of the schema. It's the default name (like how "default" is for Kubernetes).

PostgreSQL uses this naming convention so table names are not ambiguous, because it's possible to have tables with the same name in different schemas. This might occur if the database is being shared, or if there is a testing schema:

public.xcom       # Main xcom table
analytics.xcom    # Maybe a transformed view of xcom data
staging.xcom      # Testing version

Here's how you can create a schema:

-- Create a schema
CREATE SCHEMA analytics;

-- Create schema with specific owner
CREATE SCHEMA staging AUTHORIZATION your_username;

-- Create schema only if it doesn't exist
CREATE SCHEMA IF NOT EXISTS testing;

You can create a table within the new schema like this:

-- Create table in specific schema
CREATE TABLE analytics.dag_summary AS 
SELECT dag_id, COUNT(*) FROM public.dag_run GROUP BY dag_id;

-- Query from specific schema
SELECT * FROM analytics.dag_summary;

You can target a specific schema by SETting the search_path config (similar to kubectl --namespace analytics ...):

-- Switch your default schema for the session
SET search_path = analytics, public;
-- Now "SELECT * FROM dag_summary;" will look in analytics first

#postgresql