Dear Postgres - Hackdot

Re:Dear Postgres by kuschku on Friday October 13, 2017 @10:30AM (#15465975)

Postgres has similar SQL commands.

Commands that start with \ are shortcuts in the psql client that are translated into SQL commands.

You can always use SELECT * FROM pg_tables; and operate on those tables as if it was a normal SQL relation (because it is).

Additionally, if you use psql -E, it shows you the SQL it runs for such shortcuts. For \d (Show relations) it runs:

    SELECT n.nspname as "Schema",
      c.relname as "Name",
      CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
      pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
    FROM pg_catalog.pg_class c
         LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relkind IN ('r','v','m','S','f','')
          AND n.nspname <> 'pg_catalog'
          AND n.nspname <> 'information_schema'
          AND n.nspname !~ '^pg_toast'
      AND pg_catalog.pg_table_is_visible(c.oid)
    ORDER BY 1,2;

[ Parent | on HN ]

Re:Dear Postgres by ape4 on Friday October 13, 2017 @10:37AM (#15466053)

Good thing there is a shortcut :)

Can you do `alter table` on pg_tables?

[ Parent | on HN ]

Re:Dear Postgres by kuschku on Friday October 13, 2017 @10:39AM (#15466073)

pg_tables is a view, which only loads data from a few other internal tables, but you can modify many of them, yes. (And many extensions do that).

Specifically, pg_tables is defined as

    CREATE VIEW pg_tables AS SELECT n.nspname AS schemaname,
        c.relname AS tablename,
        pg_get_userbyid(c.relowner) AS tableowner,
        t.spcname AS tablespace,
        c.relhasindex AS hasindexes,
        c.relhasrules AS hasrules,
        c.relhastriggers AS hastriggers,
        c.relrowsecurity AS rowsecurity
       FROM ((pg_class c
         LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
         LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace)))
      WHERE (c.relkind = ANY (ARRAY['r'::"char", 'p'::"char"]));

[ Parent | on HN ]

Re:Dear Postgres by ape4 on Friday October 13, 2017 @10:46AM (#15466143)

Cool. I like the Posgres is written in Postgres. Of course, the normal commands for changing the schema are a better approach.

[ Parent | on HN ]

Re:Dear Postgres by fleetside72 on Friday October 13, 2017 @10:40AM (#15466088)

yes and they are under transactions that your can roll-back, does MySQL do that?

[ Parent | on HN ]