CODEWITHSUNDEEP
postgresql
porgrammer3124
porgrammer3124

Reputation: 85

export table as insert statements from psql shell

I am new to postgres. Is there a way from psql shell to export the table as "INSERT" statements to "test.sql" PostgreSQL docs I tried using

\copy (SELECT * from users) TO 'test.sql'
\copy (SELECT * from users) TO 'test.sql' SQL;

I found a lot of answers online that use pgdump, Ideally, I want without it

Upvotes: 0

Views: 771

Answers (1)

Ando
Ando

Reputation: 56

You can try

   SELECT 'INSERT INTO users (column1,column2,column3) VALUES (' ||
coalesce(quote_literal(column1), 'null') || ',' ||
coalesce(quote_literal(column2), 'null') || ',' ||
coalesce(quote_literal(column3), 'null') || ',' ||
');' FROM users;

or here is good function in psql https://wiki.postgresql.org/wiki/Create_INSERT_statement

and in python https://gist.github.com/agentgt/2568047

Upvotes: 1

Related Questions