Home > Enterprise >  How to properly insert from stdin in postgresql?
How to properly insert from stdin in postgresql?

Time:03-09

normal insert:

insert into tfreeze(id,s) values(1,'foo');

I tried the following ways, both not working:

copy tfreeze(id,s ) from stdin;
1 foo
\.

copy tfreeze(id,s ) from stdin;
    1 'foo'
    \.

Only a few questions related from stdin in stackoverflow. https://stackoverflow.com/search?q=Postgres Insert statements from stdin

-- error code:

ERROR:  22P02: invalid input syntax for type integer: "1 foo"
CONTEXT:  COPY tfreeze, line 1, column id: "1 foo"
LOCATION:  pg_strtoint32, numutils.c:320

I get code from this(https://postgrespro.ru/education/books/internals) book.
code source: https://prnt.sc/eEsRZ5AK-tjQ
So far I tried:
1, foo, 1\t'foo', 1\tfoo

CodePudding user response:

First, you have to use psql for that (you are already doing that).

You get that error because you use the default text format, which requires that the values are separated by tabulator characters (ASCII 9).

I recommend that you use the CSV format and separate the values with commas:

COPY tfreeze (id, s) FROM STDIN (FORMAT 'csv', FREEZE);
1,foo
\.
  • Related