CODEWITHSUNDEEP
postgresql
lps
lps

Reputation: 1408

How do you filter table column names in postgres?

The use case is, say you have a table with a lot of columns (100+) and you want to see if a certain column name exists in the table. Another use case is, say there is a name scheme for the columns in the table that allows me to search for a term that will show all fields with that name - e.g. all fields related to a payment card are prefixed with "card_".

In MySQL I could handle both cases above by doing a show fields in <table_name> like '%<search_term>%'. I've googled for a solution but have only found results related to filtering actual table names and showing table schemas (e.g. \d+), which is not what I am looking for. I've also tried variations of the MySQL command in the psql shell, but no luck.

I'm looking for a way to do this with SQL or with some other Postgres built-in way. Right now I'm resorting to copying the table schema to a text file and searching through it that way.

Upvotes: 1

Views: 5031

Answers (1)

Kristj&#225;n
Kristj&#225;n

Reputation: 18803

You can query information_schema.columns using table_name and column_name. For example:

>= select table_name, column_name
   from information_schema.columns
   where table_name = 'users'
     and column_name like '%password%';

 table_name |      column_name
------------+------------------------
 users      | encrypted_password
 users      | reset_password_token
 users      | reset_password_sent_at

Upvotes: 1

Related Questions