-2

I am able to generate insert statements for any table using this query:

select 'insert into table values('||id||','||bnrcno||','||chargetype||','||domno||','||coa_id||','||liability_gl||','||revenue_gl||','||taxno||')' 
from bill_charges_map where  domno=5

But the bnrcno column is empty (no values in it). When I use that column I get an entirely null output. If i exclude that column, I get the right output. Is there any way to get out of this problem?

When I use the query:

select 'INSERT INTO table values ('||id||', '||brcno||','||bnrcno||','||chargetype||','||domno||','||coa_id||','||liability_gl||','||revenue_gl||','||taxno||')'
   from bill_charges_map where brcno =194 and domno =5;

I do not get any output, and it's not throwing a syntax error. There are rows that match brcno=194 and domno=5. The brcno column is of integer type, where most of values in it are null.

When the value is null how can I convert it into a SQL string?

4

Use the format() function with placeholder for literals: %L

select format('insert into table values(%s, %L, %L, %s, %s, %L, %L, %L)', 
               id, bnrcno, chargetype, domno, coa_id, liability_gl, revenue_gl, taxno) 
from bill_charges_map 
where domno=5;

For numeric columns (integer, numeric, float etc) use %s as the placeholder. For all other types use %L as the placeholder to quote them properly.


Note that it is good coding practice to explicitly list the columns in the target table: insert into the_table (col1, col2, col3) values (...)

  • That's pretty. Actually when i am changing the specifiers(%s,%L), The problem gets solved. Thanks for the info...!!!!! – akhilesh kedarisetty Nov 8 at 5:20
-1

select 'insert into table values('||id||','||bnrcno||','||chargetype||','||domno||','||coa_id||','||liability_gl||','||revenue_gl||','||taxno||')' from bill_charges_map where domno=5

I think what you're looking for is this:

insert into table1 
select id, bnrcno, chartype, domno, coa_id, liability_gl, revenue_gl, taxno 
from bill_charges_map 
where domno = 5 ; 
  • What Data Type is brncno? If it's any character type, then you need wrap the value in the constructed SQL in quotes, just like any other SQL, character literal: 'insert into tables1 values ('||id||','''||bnrcno||''', ... You may also have to check for NULLS explicitly and build those into the constructed SQL string as well. As jjanes says, you might be better off looking into Postgres's tools for reading data from external files. – Phill W. Sep 5 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.