How to search a specific value in all tables (PostgreSQL)?

by

How about dumping the contents of the database, then using grep?

$ pg_dump --data-only --inserts -U postgres your-db-name > a.tmp
$ grep United a.tmp
INSERT INTO countries VALUES ('US', 'United States');
INSERT INTO countries VALUES ('GB', 'United Kingdom');

The same utility, pg_dump, can include column names in the output. Just change --inserts to --column-inserts. That way you can search for specific column names, too. But if I were looking for column names, I’d probably dump the schema instead of the data.

$ pg_dump --data-only --column-inserts -U postgres your-db-name > a.tmp
$ grep country_code a.tmp
INSERT INTO countries (iso_country_code, iso_country_name) VALUES ('US', 'United  States');
INSERT INTO countries (iso_country_code, iso_country_name) VALUES ('GB', 'United Kingdom');

More Related Contents:

  1. How to reset postgres’ primary key sequence when it falls out of sync?
  2. PostgreSQL LIKE query performance variations
  3. How to import CSV file data into a PostgreSQL table
  4. Calculating and saving space in PostgreSQL
  5. The forgotten assignment operator “=” and the commonplace “:=”
  6. Connecting to Postgresql in a docker container from outside
  7. How do you create a read-only user in PostgreSQL?
  8. Generating a UUID in Postgres for Insert statement?
  9. How to set value of composite variable field using dynamic SQL
  10. How to generate the “create table” sql statement for an existing table in postgreSQL
  11. PostgreSQL: serial vs identity
  12. Postgres password authentication fails
  13. Generate series of dates – using date type as input
  14. Copy a few of the columns of a csv file into a table
  15. Postgres query to check a string is a number
  16. Using pyspark to connect to PostgreSQL
  17. Returning from a function with OUT parameter
  18. ERROR: permission denied for relation tablename on Postgres while trying a SELECT as a readonly user
  19. How do you create a random string that’s suitable for a session ID in PostgreSQL?
  20. Joining Results from Two Separate Databases
  21. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  22. Update timestamp when row is updated in PostgreSQL
  23. Postgresql query array of objects in JSONB field
  24. How to add a conditional unique index on PostgreSQL
  25. PostgreSQL INSERT ON CONFLICT UPDATE (upsert) use all excluded values
  26. input file appears to be a text format dump. Please use psql
  27. Hashing a String to a Numeric Value in PostgreSQL
  28. Spring Docker container cannot access Postgres Docker container
  29. Format specifier for integer variables in format() for EXECUTE?
  30. PostgreSQL composite primary key

Leave a Comment