JOIN pg_namespace source_ns ON source_ns.oid = source_table. JOIN view_oids on source_table.oid = view_oids.view_oid JOIN pg_class as source_table ON pg_depend.refobjid = source_table.oid JOIN pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespaceĭependent_ns.nspname as dependent_schema,ĭependent_view.relname as dependent_view, JOIN pg_class as dependent_view ON pg_rewrite.ev_class = dependent_view.oid JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid Get a list of views that have dependencies on other views Here's a query that will bring up your materialized views as well and show you the views' dependencies. LEFT JOIN pg_catalog.pg_stat_all_tables s ON (c.oid = s.relid) LEFT JOIN pg_catalog.pg_attribute a ON (c.oid = a.attrelid AND a.attnum > 0 AND NOT a.attisdropped) THEN pg_catalog.pg_get_viewdef(c.oid, true) Pg_catalog.obj_description(c.oid, 'pg_class') AS comments, If you want more detail you can modify the following to suit your needs: SELECT n.nspname AS table_schema, LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace) Use the SELECT statement to query table information from the pg_catalog.pg_tables catalog.Pg_catalog.pg_get_userbyid(c.relowner) AS table_owner,.Use the \dt or \dt+ command in psql to show tables in a specific database.If you omit the WHERE clause, you will get many tables including the system tables. In this query, we used a condition in the WHERE clause to filter system tables. Schemaname != 'information_schema' Code language: SQL (Structured Query Language) ( sql ) It will add the size and description columns: postgres= # \dt+ Code language: PHP ( php ) Showing tables using pg_catalog schemaĪnother way to show tables in PostgreSQL is to use the SELECT statement to query data from the PostgreSQL catalog as follows: SELECT * To get more information on tables, you can use the \dt+ command. Third, use the \dt command from the PostgreSQL command prompt to show tables in the dvdrental database: postgres= # \dt Code language: PHP ( php ) In this command, you connect to the dvdrental database using the postgres user. In this tutorial, read about how you can use psql to list databases and tables in PostgreSQL. In this command, the -d flag means database. Postgres comes with a powerful command line tool called psql. Note that you can connect to a specific database when you log in to the PostgreSQL database server: $ psql -U postgres -d dvdrental You are now connected to database "dvdrental" as user "postgres". Third, switch to a database e.g., dvdrental: postgres= # \c dvdrental Second, enter the password for the user postgres and press the Enter keyboard: Password for user postgres: In this command, you use the postgres user to log in to the PostgreSQL database server. The -U flag stands for the user and -W option requires you to provide the password. Showing tables from PostgreSQL using psqlįirst, connect to PostgreSQL using the psql tool. PostgreSQL does not support the SHOW TABLES statement directly but provides you with an alternative. Using \dt+ tablename will provide more information, such as about storage and compression. \d tablename Describing the authors table. This will list the columns, indexes, and any references to other tables. If you are coming from MySQL, you may want to use the popular SHOW TABLES statement that displays all tables in a specific database. To see more information about a particular table, you can use the describe table command: \d tablename. Summary: in this tutorial, you will learn how to show tables in PostgreSQL using psql tool and pg_catalog schema.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |