How to export data from postgresql db as plain sql?-CodePudding

I am building a api that is developed by multiple developers and we require to have the same db for testing purposes. We are using UUID and so it would be ideal if we all use the same UUIDs. I can't seem to find a way to export the db contents as plain executable SQL, preferably with insert statements. NO drop tables, NO recreation of database.

I would like the end result to look something like:

INSERT INTO public.bla_bla(
    id, bla_bla, bla_bla1, bla_bla2, bla_bla3, bla_bla4)
    VALUES (?, ?, ?, ?, ?, ?);
INSERT INTO public.bla_bla(
    id, bla_bla, bla_bla1, bla_bla2, bla_bla3, bla_bla4)
    VALUES (?, ?, ?, ?, ?, ?);
...

I am using pgAdmin4 as ui. But also have Dbeaver.

I have tried using the Backup wizard to export the data.

On the database, - Does not produce a result if only data is selected. If instead the "sections" category sliders are used the result includes drop statements, which is not wanted and no readable insert statements.
on the schema - same as above
on the table - produces a CSV file, which is not ideal.

I tried following the steps here, but they do not yield the produce I need the result I need. How to export Postgres schema/data to plain SQL in PgAdmin 4

At this point I am considering just doing it by hand.

CodePudding user response：

Use pg_dump

pg_dump -Fp -d your_database -U your_db_user --column-inserts --data-only -f db_dump.sql

--data-only will skip the creation of the CREATE TABLE statements (or any other DDL).

If you want, you can add --rows-per-insert <nnnn> to only create a single INSERT statement for multiple rows.

CodePudding user response：

You can try pg_dump with the option format plaintext and --column-inserts .

For more details please read here