PostgreSQL Convert .sql file to .csv file

Question

In PostgreSQL I have backed up records of Table users to users.sql.

After some changes I made to data in other tables in the Database, PostgreSQL is not allowing me to restore the data from users.sql.

The only workaround available to me at this stage is to convert users.sql to .csv file and then copy & paste the data into Table users.

Is there a way to convert PostgreSQL backup file users.sql to users.csv?

You don't specify what error you're getting, but as it's due to changes made to other tables, it's presumably a foreign key constraint that is being violated. If so, converting to a CSV will not help - when you try to restore that data, it's still going to run into the same constraint violations. — AdamKG
– AdamKG, Commented Mar 23, 2021 at 13:40
Yes, What you say is correct. There is no way that I can go back and undo changes I made in other tables. So the best solution now is to convert users.sql to users.csv. Hence, looking for a solution to understand the file format of PostreSQL .sql file or a tool to convert it to .csv file format or .txt file format. So is there a way? — Raky
– Raky, Commented Mar 23, 2021 at 13:44
Not without restoring the table, no. Which is to say, the way to convert a .sql to a .csv is "restore the SQL and then do a CSV export". What about editing the .sql manually to: A) remove any CREATE TABLE/ALTER TABLE statements, B) change the table name being restored to (like, users_no_fks), and then creating a table in your DB with the same columns as users but with no constraints? At that point, you can restore to that FK-less table & then COPY users_no_fks to /path/to/users.csv CSV HEADER;. — AdamKG
– AdamKG, Commented Mar 23, 2021 at 14:28
The users.sql dump file is in Binary format. If it had been text, then there is no issue at all. — Raky
– Raky, Commented Mar 23, 2021 at 14:30
Oh, then it's easy! Use pg_restore --section=data -f /path/to/new_users.sql /path/to/existing/users.sql, which will create a new_users.sql with only a COPY statement with the table data. Edit the table name there, create the corresponding FK-less table in your DB, restore & re-export, and you have your CSV. (pg_restore cannot convert to CSV directly; the best you can do is --section=data to get a plain-text dump of just the table data before proceeding with the load & re-export steps.) — AdamKG
– AdamKG, Commented Mar 23, 2021 at 14:36

Raky · Accepted Answer · 2021-03-23 14:55:33Z

1

Thanks to AdamKG. The solution has been given to me by AdmKG.

pg_restore --section=data -f /path/to/new_users.sql /path/to/existing/users.sql

For those who didn't understand the /path/to/, it is the path to the new target file and /path/to/existing is the path where one has saved the PostgreSQL users.sql file on the hard disk or any other storage media.

This solution is not mine but of AdamKG.

This Post is for sake of convenience of other users like me.

answered Mar 23, 2021 at 14:55

Raky

9087 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user330315 Over a year ago

It is extremely unusual to give the result of a "custom" dump file (pg_dump -Fc ...) the extension .sql

Raky Over a year ago

The pg4Admin that comes with Postrgres 9.6.6 provides for BackUp to .sql and .bak. Once downloaded, the workaround provided by @AdamKG helps in generating a .sql file where in the data are Tab Separated. The Tab values can be searched and replaced with command to convert the data to .csv format and restore the data with ease. It worked for me very well. The Best part is that I trained my professional software Developers also on this method of data recovery from .sql dump.

Collectives™ on Stack Overflow

PostgreSQL Convert .sql file to .csv file

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related