Skip to main content
20 votes
Accepted

How to list all the indexes along with their type (BTREE, BRIN, HASH etc...)

The types of indexes in Postgresql are stored in the pg_am catalog table. So to get a list of all tables and their indexes with the index type (or access method ("am") as PostgreSQL calls it) you can ...
maxTrialfire's user avatar
  • 1,094
14 votes

List all columns for a specified table

psql on PostgreSQL 11+ If you're looking for the column types on a query, you can use psql's \gdesc SELECT NULL AS zero, 1 AS one, 2.0 AS two, 'three' AS three, $1 AS four, ...
Evan Carroll's user avatar
  • 63.8k
14 votes

How to get the schema name of a table of type regclass in PostgreSQL?

To further simplify, you could use a cast to regnamespace - another object identifier type introduced with Postgres 9.5 SELECT relnamespace::regnamespace::text FROM pg_catalog.pg_class WHERE oid = ...
Erwin Brandstetter's user avatar
12 votes

Display user-defined types and their details

Try execute this code: SELECT pg_type.typname, pg_enum.enumlabel FROM pg_type JOIN pg_enum ON pg_enum.enumtypid = pg_type.oid;
Santos L. Victor's user avatar
11 votes
Accepted

How do you directly modify the system catalogs in SQL Server 2017?

!!   BE SURE TO READ THE WARNING, IN BOLD, AT THE BOTTOM   !! The server configuration option of allow updates was rendered non-functional starting in SQL Server 2005. The documentation ...
Solomon Rutzky's user avatar
11 votes
Accepted

How to get the schema name of a table of type regclass in PostgreSQL?

Can I just implement this function with e.g. split_part(_tbl::text, '.', 1)? I'm not sure that would be safe. Instead, I would use, SELECT nspname FROM pg_catalog.pg_class AS c JOIN pg_catalog....
Evan Carroll's user avatar
  • 63.8k
10 votes

List all columns for a specified table

PostgreSQL only This is somewhat hokey but could be a contender if you are looking for the shortest possible SQL: SELECT json_object_keys(to_json(json_populate_record(NULL::schema_name.table_name, '{...
oᴉɹǝɥɔ's user avatar
10 votes

Check if postgresql database exists (case insensitive way)

If you want a true/false answer you can use select exists( SELECT datname FROM pg_catalog.pg_database WHERE lower(datname) = lower('dbname') );
Thomas Turner's user avatar
6 votes
Accepted

What is the significance of the principal_id in sys.schemas?

It should be the default "owner" of all objects in the schema. Schema-bound objects have a principal_id column that is NULL by default, in which case it uses the principal_id of the Schema that the ...
Solomon Rutzky's user avatar
5 votes
Accepted

How do I query INFORMATION_SCHEMA.COLUMNS on Amazon RDS?

PG_TABLE_DEF Amazon considers the internal functions that INFORMATION_SCHEMA.COLUMNS is using Leader-Node Only functions. Rather than being sensible and redefining the standardized INFORMATION_SCHEMA....
Evan Carroll's user avatar
  • 63.8k
5 votes

Is it a bad practice to query pg_type for enums on a regular basis?

What @Jack said. Plus, if all you need is the list of registered values for an enum type, there are some Enum Support Functions to do that. Based on Jack's example: SELECT enum_range(null::mood); ...
Erwin Brandstetter's user avatar
4 votes
Accepted

Is it a bad practice to query pg_type for enums on a regular basis?

Is this bad practice? No, not at all, modifying the catalog is bad practice but there is no reason not to query it. But in your query… SELECT distinct pg_type.typname AS enum_type FROM pg_type JOIN ...
Jack Douglas's user avatar
4 votes
Accepted

postgres system catalog query columns from table

Just select from information_schema.columns instead. SELECT table_catalog, table_schema, table_name, data_type FROM information_schema.tables WHERE table_schema='{schema}'; FOR GLORY For whatever ...
Evan Carroll's user avatar
  • 63.8k
4 votes

How do you directly modify the system catalogs in SQL Server 2017?

Short answer: You don't. Longer answer: Note that you should never do this on a production system. You can and likely will mess things up pretty bad, and MS will tell you to pound sand if you call ...
db2's user avatar
  • 9,668
4 votes

Display user-defined types and their details

With psql: \dT show list of user-defined types. \dT+ <type_name> show given user-defined type, with details. \dT <type_name> show given user-defined type, without details. \d <...
Eric's user avatar
  • 253
4 votes

Display user-defined types and their details

This is a very simple alternative, but enough for simple use cases. When the column's type is UDT, we use the udt_name column from information_schema.columns table. select column_name, case ...
jgrocha's user avatar
  • 365
3 votes

Restore All Databases Script

there is a very good script on the link below: How to generate database restore script in SQL Server Based on that script I have created my stored procedure below, which works very well, although it ...
Marcello Miorelli's user avatar
3 votes

Permission to view execution report in SSIS Catalog

Commenting out the WHERE clause in these views from SSISDB: SSISDB.catalog.executions SSISDB.catalog.event_messages SSISDB.Catalog.folders and provide the DB_READER access to the user/group in ...
Balaji Rajaram's user avatar
3 votes

Get column names and data types of a query, table or view

If you have accesss to the pg_catalog and use PgAdmin3, I highly recommend a solution that I found on Valentine's Tech blog (http://tech.valgog.com/2011/02/pgadmin-iii-macros-get-table-fields.html). ...
marcopolo's user avatar
3 votes

How to list all the indexes along with their type (BTREE, BRIN, HASH etc...)

Simple Just use the System Catalog Information Function pg_get_indexdef(index oid): Reconstructs the creating command for an index. (This is a decompiled reconstruction, not the original text of the ...
Erwin Brandstetter's user avatar
3 votes

How does the sys.objects Catalog View pull from itself?

sys.objects is not a table, it is a system catalog view. The definition shown in the question even shows Create VIEW .... Also, sys.objects is not the same thing as sys.objects$: You cannot select ...
Solomon Rutzky's user avatar
3 votes
Accepted

Missing "public" schema when converting a "regclass" value to text

This is not related to the schema public per se - which is just another schema with no special powers except that it's created by default - and included in the search_path by default. The current ...
Erwin Brandstetter's user avatar
3 votes
Accepted

Select FK constraints with ON UPDATE NO ACTION

For the "update action" as in: FOREIGN KEY (foo_id) REFERENCES foo(foo_id) ON UPDATE CASCADE ON DELETE CASCADE FOREIGN KEY (foo_id) REFERENCES foo(foo_id) ON UPDATE DELETE ON DELETE CASCADE FOREIGN ...
Erwin Brandstetter's user avatar
3 votes
Accepted

Is pg_database a catalog or a table?

These are called System Catalogs. And Chapter 52. System Catalogs says that they are represented by regular tables in PostgreSQL: The system catalogs are the place where a relational database ...
Stanislav Bashkyrtsev's user avatar
2 votes

Why am I getting "Invalid object name 'sys.sysobjvalues'."

This is one of the tables that can only be accessed over DAC, from this blog They had been moved to the imageval column of sys.sysobjvalues, which can only be accessed when using the Dedicated ...
Evan Carroll's user avatar
  • 63.8k
2 votes
Accepted

SQL Server Buffer Pool Extension in FullText Index Population

How to check the buffer pool usage of SQL Server ( I want to know if the BPE is used )? You can query sys.dm_os_buffer_descriptors An example would be something like: SELECT CONVERT(NUMERIC(38, 2)...
Erik Darling's user avatar
  • 42.1k
2 votes

Permission to view execution report in SSIS Catalog

So this is an issues that I have run into over the last few days, and I have to say this post has definitely helped, although none of the answer were exactly what helped. So like this post states, I ...
Chap Wolff's user avatar
2 votes

How to list all the indexes along with their type (BTREE, BRIN, HASH etc...)

Adding the schema in case it's needed . SELECT n.nspname, tab.relname, cls.relname, am.amname FROM pg_index idx JOIN pg_class cls ON cls.oid=idx.indexrelid JOIN pg_class tab ON tab.oid=idx.indrelid ...
detzu's user avatar
  • 396
2 votes
Accepted

How do I navigate PostgreSQL documentation to find a list of all functions that work on a specific type?

There are a few ways, but let's try and address all three of them.. man pages There are no man pages that will address these questions. There is a lot of PostgreSQL documentation but currently ...
Evan Carroll's user avatar
  • 63.8k
2 votes

Amazon Postgres RDS: Update pg_attribute to update my custom attribute

If you want to change the length of many varchar columns (which isn't actually a custom attribute), you are better off using ALTER TABLE commands. You don't risk having unwanted side-effects, just in ...
joanolo's user avatar
  • 13.5k

Only top scored, non community-wiki answers of a minimum length are eligible