Re:Dear Postgres
by ape4 on Friday October 13, 2017 @10:27AM
(#15465955)
I prefer Mysql/MariaDB's `show tables` vs Postgres' `\dt`. And other commands. More like sql.
[ Parent
| on HN ]
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;Good thing there is a shortcut :)
Can you do `alter table` on pg_tables?
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"]));Cool. I like the Posgres is written in Postgres.
Of course, the normal commands for changing the schema are a better approach.
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?
Re:Dear Postgres
by always_good on Friday October 13, 2017 @10:39AM
(#15466070)
Damned with faint praise.