0

I cannot upload the script on PGADMN. Please help me in resolving error.

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

SET search_path = public, pg_catalog;

SET default_tablespace = '  ';

SET default_with_oids = false;
ALTER TABLE public.pricemessage_archive OWNER TO postgres;
COPY pricemessage_archive (event_id, event_date, description, device_id, toll_zone, price) FROM stdin;
103462022 2017-03-15 22:41:02-05 Price VERIFIED V35ES02A Z35ES02 0.00
103462023   2017-03-15 22:41:02-05  Price VERIFIED  V35EN13 Z35EN01 0.00
Vivek Gupta
  • 1
  • 3
  • You didn't say what the error was, so you should add that if you can. I've never tried to do a COPY FROM stdin before. You might want to convert to INSERT statement. – bfris Sep 19 '19 at 20:06
  • 2
    pgAdmin doesn't support `copy from stdin` if I'm not mistaken –  Sep 19 '19 at 21:24

1 Answers1

0

A more typical way to do this is with INSERT statements. INSERT may also be better because COPY statement is not in the SQL standard.

USING SINGLE INSERTS

INSERT INTO pricemessage_archive
      (event_id, event_date, description, device_id, toll_zone, price)
VALUES(103462022, '2017-03-15 22:41:02-05', 'Price VERIFIED', 'V35ES02A', 'Z35ES02',
       0.00);

INSERT INTO pricemessage_archive
      (event_id, event_date, description, device_id, toll_zone, price)
VALUES(103462023, '2017-03-15 22:41:02-05', 'Price VERIFIED', 'V35EN13', 'Z35EN01',
       0.00);

Using multirow row VALUES syntax

INSERT INTO pricemessage_archive
      (event_id, event_date, description, device_id, toll_zone, price)
VALUES(103462022, '2017-03-15 22:41:02-05', 'Price VERIFIED', 'V35ES02A', 'Z35ES02',
       0.00)
      (103462023, '2017-03-15 22:41:02-05', 'Price VERIFIED', 'V35EN13', 'Z35EN01',
       0.00);
bfris
  • 5,272
  • 1
  • 20
  • 37
  • Thanks for your response. I have the data from a source and it has the same format. It has 30 million rows and wont be able to add delimiter. Any other way? 103462022 2017-03-15 22:41:02-05 Price VERIFIED V35ES02A Z35ES02 0.00 103462023 2017-03-15 22:41:02-05 Price VERIFIED V35EN13 Z35EN01 0.00 same format for 30 million rows – Vivek Gupta Sep 20 '19 at 18:49
  • As @a_horse_with_no_name thought and [this answer](https://stackoverflow.com/a/32279203/9705687) confirms, you cannot copy from stdin in pgadmin. Look at that answer for some more ideas for performing your import. I don't think pgAdmin is going to be the right tool for 30M records. – bfris Sep 20 '19 at 23:15