1

I am using following code:

query1=" SELECT distinct copy meta.amz_payment1(\"Date\", settlement_id, type, order_id, "
      + "sku, description, quantity, marketplace, fulfillment, order_city, order_state, "
      + "order_postal, product_sales, shipping_credits, promotional_rebates, "
      + "sales_tax_collected, selling_fees, fba_fees, other_transaction_fees,other, "
      + "total_fmt)"
      + "INTO meta.amz_payment1_copy\n" 
      + "from meta.amz_payment1";
stmt.executeUpdate(query1);  // line 153

But it shows the following error:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "."
  Position: 27
  at 
  at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:331)
  at import_payment.amz_payment.importData(amz_payment.java:153)
  at import_payment.amz_payment.<init>(amz_payment.java:69)
Anders R. Bystrup
  • 15,729
  • 10
  • 59
  • 55
  • Do you get on which line number you get the error? – Partha Bisoi May 13 '15 at 09:42
  • 2
    `SELECT distinct copy` is invalid SQL (unless you have a column named `copy`) . And if you want to create a new table it's better to use `create table new_table as select...` instead of the deprecated syntax `select .. into new_table` –  May 13 '15 at 09:51
  • 3
    I think the error is in the SQL statement, not in Java. Can you try executing the SQL in the console, and see what happens? – David S. May 13 '15 at 09:54

2 Answers2

0

Try to use this query

INSERT INTO meta.amz_payment1_copy SELECT NEXTVAL('sequenceid'),Date,settlement_id, type, order_id,sku, description,  quantity, marketplace, fulfillment, order_city, order_state,order_postal, product_sales, shipping_credits, promotional_rebates,sales_tax_collected, selling_fees, fba_fees, other_transaction_fees,other,total_fmt
FROM meta.amz_payment1
Elango
  • 415
  • 3
  • 7
  • 18
0

The Postgres syntax for COPY is eg.

COPY ( select * from yaddayadda where <something> ) TO `filename`

And copies data from a table/view/query to a file. If you want to copy to another table, you should use

create table yadda_copy ... as select * from yadda

Cheers,

Anders R. Bystrup
  • 15,729
  • 10
  • 59
  • 55