I ran the just the insert statements from the file named: The next thing is a prompt which asks the password, in the case above I provided the postgres password but any other user should work fine which has the read capability. $ /usr/local/bin/pg_dump -h the_db_host_address -d the_db_name -table=schema_name.table_name -data-only -column-inserts -U postgres -p the_port_number -v -f /Users/the_user/folder_name/insrt_stmts_file_name.sql The below statement did the trick which I feel will be useful for people who end up here for similar answer. Generate the insert statements and dump into a file I did see some useful arguments to the the pg_dump above but still ended up devising for a way to I was in need of a way where I can generate insert statements without creating a temp table (in production). I haven't tested this against injection attacks, please let me know if the quote_literal call isn't sufficient for that.Īlso it only works for columns that can be simply cast to ::text and back again.Īlso this is for Greenplum but I can't think of a reason why it wouldn't work on Postgres, CMIIW. Invoked thus: SELECT distinct dv_util.gen_insert_statement('pg_temp_' || sess_id::text,'my_data') Valquery := substring(valquery,1,length(valquery)-1) || ')' Selquery := substring(selquery,1,length(selquery)-1) || ')' 'SELECT CASE WHEN ' || quote_ident(lumn_name) || ' IS NULL' || Selquery := selquery || quote_ident(lumn_name) || ',' Selquery := 'INSERT INTO ' || quote_ident(p_schema) || '.' || quote_ident(p_table) CREATE OR REPLACE FUNCTION dv_util.gen_insert_statement(IN p_schema text, IN p_table text) It only works for a single row, so I create a temporary view that just selects the row I want, and then replace the pg_temp.temp_view with the actual table that I want to insert into. I just knocked up a quick procedure to do this. Ps : I found an equivalent for mysql on the WEB. My test run correctly for about 9M lines, looks like it fail just before 18 minutes of running. That's why the CAST in TEXT type is needed. Tested on my postgres 9.1, with a table with mixed field datatype (text, double, int,timestamp without time zone, etc). SELECT dump('public', 'my_table','true') SELECT dump('public', 'my_table','my_id between 123459') debug: RETURN NEXT dumpquery_0 || dumpquery_1 || ' -' || selquery execute SELECT built and loop on each rowĮLSE selvalue := quote_literal(selvalue) ĭumpquery_1 := dumpquery_1 || selvalue || ',' ĭumpquery_1 := substring(dumpquery_1 ,1,length(dumpquery_1)-1) || ') ' Selquery := selquery || ' WHERE '||p_where Selquery := selquery || ' FROM ' ||quote_ident(p_schema)||'.'||quote_ident(p_table) Selquery := substring(selquery ,1,length(selquery)-1) || '] AS MYARRAY' Selquery := selquery || 'CAST(' || quote_ident(lumn_name) || ' AS TEXT),' ĭumpquery_0 := substring(dumpquery_0 ,1,length(dumpquery_0)-1) || ')' ĭumpquery_0 := dumpquery_0 || ' VALUES (' WHERE table_name = p_table and table_schema = p_schemaĭumpquery_0 := dumpquery_0 || quote_ident(lumn_name) || ',' CREATE OR REPLACE FUNCTION dump(IN p_schema text, IN p_table text, IN p_where text)ĭumpquery_0 := 'INSERT INTO ' || quote_ident(p_schema) || '.' || quote_ident(p_table) || '(' įOR colrec IN SELECT table_schema, table_name, column_name, data_type I tried to write a procedure doing that, based on codes, on a different way. ![]() This means thatįile accessibility and privileges are those of the local user, not the server, and no SQL superuser privileges are required. The data between the server and the local file system. Writing the specified file, psql reads or writes the file and routes SQL COPY command, but instead of the server reading or ![]() If both run on the same machine, it doesn't matter much, but it does for remote connections. To import the same to another Postgres table of matching structure anywhere (columns in same order, data types compatible!): COPY other_tbl FROM '/path/to/file.csv' ĬOPY writes and reads files local to the server, unlike client programs like pg_dump or psql which read and write files local to the client. You get a file with one table row per line as plain text (not INSERT commands). You can export a whole table, only selected columns, or the result of a query as demonstrated. To export data only use COPY: COPY (SELECT * FROM nyummy.cimory WHERE city = 'tokio') TO '/path/to/file.csv'
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |