Output File from PSQL Command

She had an immense curiosity about life, and was constantly staring and wondering.

— Henry James in The Portrait of a Lady.

On a bright sunny day, while you’re on vacation on a remote island, you get an emergency call from your office regarding a failure on production database and you don’t have your workstation with you. And the only access you can get due to slow internet speed is SSH (Secure Shell), you try to access the remote production database using a rented computer and an SSH client; the tools inside the server is only compose of a psql command. How can you export and backup your SQL schema layouts?

Don’t panic and don’t worry we got you covered, thepsql saves your day if you’ll just gonna be exporting SQL schema layouts.

First and foremost, fire up and execute the psql command. Inside the psql environment execute the commands below:

\o dump.txt
SELECT 'postgresql' AS dbms,t.table_catalog,t.table_schema,t.table_name,c.column_name,c.ordinal_position,c.data_type,c.character_maximum_length,n.constraint_type,k2.table_schema,k2.table_name,k2.column_name
  FROM information_schema.tables t
  NATURAL LEFT JOIN information_schema.columns c 
  LEFT JOIN(information_schema.key_column_usage k 
    NATURAL JOIN information_schema.table_constraints n
    NATURAL LEFT JOIN information_schema.referential_constraints r) 
  ON c.table_catalog=k.table_catalog AND c.table_schema=k.table_schema AND c.table_name=k.table_name AND c.column_name=k.column_name
  LEFT JOIN information_schema.key_column_usage k2 
  ON k.position_in_unique_constraint=k2.ordinal_position AND r.unique_constraint_catalog=k2.constraint_catalog AND r.unique_constraint_schema=k2.constraint_schema AND r.unique_constraint_name=k2.constraint_name 
  WHERE t.TABLE_TYPE='BASE TABLE' AND t.table_schema NOT IN('information_schema','pg_catalog');

This command will create a dump file containing all the table structure containing each constraint available, this is usually needed when you need to analyze ER (Entity Relation) diagram. You could example paste the output of this command on Lucid Chart to analyze entity relationship.

A break down of this is, the SELECT command will query all table structure and schema.

Switches Legend:
\o => outputs to file
\O => turns off the output to file feature

The output file will be generated on the same directory where you run the psql command.

So guys, when on vacation what are the things you can do on a slow internet and a rented computer? Hope you enjoyed this article!

By Edward Fitz Abucay

"How long is forever?"

I'm a software engineer with a passion for innovating and creating products, especially for startups in the web3 and blockchain space. I'm always excited to learn and work with new technologies, and I'm committed to delivering high-quality solutions that meet the needs of my clients or users.

In my free time, I enjoy listening to music of all genres, but classical music holds a special place in my heart. I find it both inspiring and calming, and it helps me to stay focused and creative. I'm also an avid reader of books and manga, and I enjoy discovering new authors and stories.

As a software engineer, I have a strong technical background with experience in various programming languages, frameworks, and tools. I'm always striving to improve my skills and stay up-to-date with the latest trends and best practices. I love working with startups, especially those in the web3 and blockchain space, because I believe that these technologies have the potential to revolutionize the way we live and work.

Overall, I'm a dedicated and driven individual with a wide range of interests and skills. I believe that my passion for software engineering, combined with my love of music and reading, makes me a well-rounded and adaptable professional.

Leave a Reply

Your email address will not be published. Required fields are marked *