How to generate PostgreSQL data dictionary from SQL script

[@BassamChoudry]

Thanks for the detailed video with Excel Tips !

[@demonnae]

Thank you sir. This video spare me a lot of extra work doing data dictionary for documentation 🥲

[@walterclementsjr.5947]

working with pgadmin 6.8 + postgres 12.11 and this works!

[@chaloemphonthipkasorn4106]

Thank you so much sir. You save my life.

[@DataResearchLabs]

You are most welcome

[@TheMadScientist-Professor]

Thank you sir!

[@Marcompelo]

Excelent.

[@kellythomson2787]

Thank you so much, Is there a way to also export the column descriptions for "Views"? Currently when I run the script I get column descriptions for tables but only basic stats for the columns in the "Views"

[@eofribeiro]

THANK YOU VERY MUCH!!!!

[@DataResearchLabs]

Glad it helped!

[@angelramos3564]

Thank you!

[@DataResearchLabs]

You're welcome!

[@allenabolmaali2129]

Perfect!

[@DataResearchLabs]

Thank you! Happy it was helpful for you.

[@wylietimmerman8705]

For anyone who received the "ERROR: failed to find conversion function from unknown to text" error, I traced the error to the vars table. In the provided code, the v_SchemaName and v_TablesOnly fields had unknown type; once I cast those to text, that solved the issues on my end. The updated template code would be: WITH vars AS ( SELECT CAST('public' AS TEXT) AS v_SchemaName -- Set to the schema whose tables you want in the Data Dictionary , CAST('NO' AS TEXT) AS v_TablesOnly -- YES=Limit To Tables only; NO=Include views too )

[@DataResearchLabs]

Thank you!

[@massaoiwanaga]

Solved to me. Many thanks!!

[@floku9343]

ERROR: FEHLER: failed to find conversion function from unknown to text SQL state: XX000 Hi , I am getting the same error, even though I download the newest version.

[@DataResearchLabs]

Thanks for reporting Flo. I am "flying blind here" because I cannot reproduce the error. Have tried all the general purpose bullet-proofing I can think of. Request to you and others: please assist me with troubleshooting by isolating the failure details including table name, field name, data type, etc. Reply back here with those details...or better yet, take a stab at fixing the script yourself since you can do several fix and test run iterations. Email me the fixes ([e-mail]) and I will post it and give you credit of course. To troubleshoot: 1. Alter the "baseTbl" query's WHERE clause to isolate which table(s) are causing the error 2. Example: - Add something like " AND table_name ~ '^[ABCDEFGHIJKLM].*'" to limit the run to tables starting with characters A-M. - If you don't get an error, then the problem is in tables N-Z, so repeat by modifying the where clause with " AND table_name ~ '^[NOPQRSTUVWXYZ].*'" - If you did get an error, split the table names in half again to " AND table_name ~ '^[ABCDEFG].*'". 3. Continue until you get down to a specific table name that errors. 4. Next repeat the process on field names by altering the WHERE clause on the "metadata" query to limit field names ordinal position. - Example: add something like "WHERE tut.ordinal_position BETWEEN 0 AND 10" to check the first 10 columns. - If it errors, cut it down in to between fields 5 and 10 to isolate which half of field 0-10 is erroring. - Repeat until you identify one (or more) fields. 5. Once you've identified the table name, fields name, and field data types (lookup the latter). Look at the data type, it is probably going to be something unique about it that causes the script to break. Knowing the data type and its properties will go along way towards fixing the script. FYI - Note that nobody using the MS SQL Server, Oracle, My SQL, or Greenplum scripts have reports this error. At least three people using the Postgresql have reported it. Don't know whether it is specific to Postgresql...or just nobody using/reporting on the other platforms. Thanks in advance for your help (I wonder if some folks haven't already run into the problem and resolved it themselves...if so, please post so others can benefit from too.

[@laurencelendres2956]

Hi, I am getting the same error and impossible to find where is the problem ERROR: failed to find conversion function from unknown to text SQL state: XX000 Can you help me ? Thank you

[@DataResearchLabs]

Sorry, I don't know what the root cause is without running the script against your specific database schema. Some possible reasons: 1. Your version of PostgreSQL is earlier than version 13 I am using, so one of the "native" functions is not recognized. 2. Your version of PgAdmin is earlier than version 4 that I am using. 3. Your flavor of PostgreSQL is something other than native PostgreSQL (example: I've used Greenplum at work that supports up to PostgreSQL 8.3; but none of the newer features up through current PostgreSQL 13. Hope this helps. Sorry for the late reply, been very busy at work and with personal issues the past 5 months.

[@manuraghuvanshi2967]

Hii DataResearchLabs,,, i am getting this error while converting in pgadmin 4 ERROR: failed to find conversion function from unknown to text SQL state: XX000

[@DataResearchLabs]

Hi Manu. Thank you for watching the video and using the script. Although I cannot reproduce your error, I believe it is happening and tried some guesses (below) to resolve it. Go ahead and download the newer code and let me know if it resolves the issue (if you are experiencing it, chances are others are too). Latest code is checked into github here: raw.githubusercontent.com/DataResearchLabs/sql_scripts/main/postgresql/data_dictionary/data_dict_dump.sql Items I more defensively coded to try and prevent the error: 1. Found and fixed one CASE...WHEN...END by adding an ELSE clause (probably not the root cause) 2. Added nine CAST(x as varchar(10)) statements around numeric data type sizes in case they aren't auto converting to text like they are for me. 3. In case data_type came back null from query, wrapped it in COALESCE(x, 'Unknown') to prevent concatenating a null

[@DataResearchLabs]

oh, and this URL to GitHub's over/under diff will show you exactly what changes I made in code: github.com/DataResearchLabs/sql_scripts/commit/6252d97a8e3808f1e31a32ec8ad68eb7199ac6ba

[@sachinkumarsingh538]

Hi DataResearchLabs still getting the below error ERROR: failed to find conversion function from unknown to text SQL state: XX000

[@DataResearchLabs]

Okay, hopefully the latest build fixes the issue. I was using " LIKE '%int%' " to catch all flavors of integer and write out the numeric precision wrapper in paranthesis. Well...when I look at the postgresql data type list, "intervals" would also get caught up in that old sql. And I bet intervals do not have a numeric_precision hence the error you receive. So give it a try and hopefully it works. Let me know. Will need to make same/similar changes on the other data platforms.

[@DataResearchLabs]

Here is a diff in github of the exact change: github.com/DataResearchLabs/sql_scripts/commit/f8be0aef7e819781a8d4f3de55d64b7d5a7031ba

[@gotto6812]

te encuentro en la calle te doy un beso