![]() LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relamĪND pg_catalog.pg_table_is_visible(c.oid) LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace Pg_catalog.pg_get_userbyid(c.relowner) as "Owner" Run psql -E and then run (for example): \dtĪnd the output is then: ********* QUERY **********ĬASE 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 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type", This is equivalent to setting theĬommand(s) are a great way of learning the internals of PostgreSQL commands! ![]() Use this to study psql's internal operations. I'm surprised nobody's mentioned this before, but these commands (from the manual here): -EĮcho the actual queries generated by \d and other backslash commands. Schema1 | t1 | table | postgres | 0 bytes | Public | usr | table | postgres | 5008 kB | Public | test | table | postgres | 16 kB | Public | outable2 | table | ordinaryuser | 0 bytes | Public | outable | table | ordinaryuser | 0 bytes | Public | name_log | table | postgres | 8192 bytes | Public | food | table | postgres | 48 kB | Public | asset_attribute | table | postgres | 8192 bytes | Public | asset | table | postgres | 16 kB | Public | all_units | table | postgres | 0 bytes | Schema | Name | Type | Owner | Size | Description Test=# set search_path to schema1, public Schema1 | postgres | postgres=UC/postgres | Public | postgres | postgres=UC/postgres | standard public schema Name | Owner | Access privileges | Description \d to list all tables in current search_path schema in current database. Test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 13 MB | pg_default | Template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres | 5985 kB | pg_default | default template for new databases Template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres | 5785 kB | pg_default | unmodifiable empty database Slonslave | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 32 MB | pg_default | Slonmaster | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 1401 MB | movespace | Postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 6073 kB | pg_default | default administrative connection database Pgbench | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 29 MB | pg_default | Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description To gain more info on database and table list, You can do : Then, to exit the psql terminal, type \q and press enter. Use the command \d meaning show all tables, views, and sequences kurz_prod=# \d This brings you to a psql terminal: kurz_prod=# You have to specify a database before you can list the tables in that database. In PSQL these commands list the tables available Template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres Template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres Postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | Pgadmin | pgadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | ![]() Kurz_prod | pgadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | Name | Owner | Encoding | Collate | Ctype | Access privileges Those commands print this on the terminal: List of databases Or the command stated more simply: psql -U pgadmin -l Template1 | postgres | UTF8 | C.UTF-8 | C.In Postgresql these terminal commands list the databases available /bin/psql -h localhost -username=pgadmin -list Template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres Postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 | This should give an output similar to: List of databases Step 3: Execute the SQL file \i /home/me/projects/run-sql-tut/list-databases.sql Step 2: Log into the psql shell sudo -iu postgres psql home/me/projects/run-sql-tut/list-databases.sql Locate the SQL file you want to run and copy its location. To learn how to do this, follow the steps below. If that user doesn’t use peer authentication, you’ll have to run the SQL file in the psql shell. You may also want to run these commands as a specific user. When using PostgreSQL DBMS, you may need to run some SQL commands to populate a database or run specific commands. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |