COPY ... FROM stdin WITH FORMAT csv

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: COPY ... FROM stdin WITH FORMAT csv
Date: 2017-03-21 17:31:36
Message-ID: CAADeyWiywOUOifPsAiVRujTfpTB4d7xVAwco-WKVC+K0OgVn2g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Good evening,

I keep rereading https://www.postgresql.org/docs/9.6/static/sql-copy.html
but just can't figure the proper syntax to put some records into the table:

words=> \d words_reviews;
Table "public.words_reviews"
Column | Type | Modifiers
---------+--------------------------+-----------
uid | integer | not null
author | integer | not null
nice | integer | not null
review | character varying(255) |
updated | timestamp with time zone | not null
Indexes:
"words_reviews_pkey" PRIMARY KEY, btree (uid, author)
Check constraints:
"words_reviews_check" CHECK (uid <> author)
"words_reviews_nice_check" CHECK (nice = 0 OR nice = 1)
Foreign-key constraints:
"words_reviews_author_fkey" FOREIGN KEY (author) REFERENCES
words_users(uid) ON DELETE CASCADE
"words_reviews_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid)
ON DELETE CASCADE

words=> COPY words_reviews (uid, author, nice, review, updated) FROM stdin
WITH FORMAT 'csv';
ERROR: syntax error at or near "FORMAT"
LINE 1: ...d, author, nice, review, updated) FROM stdin WITH FORMAT 'cs...
^
words=> 1,2,1,'1 is nice by 2','2017-03-01',
words-> 1,3,1,'1 is nice by 3','2017-03-02',
words-> 1,4,1,'1 is nice by 4','2017-03-03',
words-> 2,1,1,'2 is nice by 1','2017-03-01',
words-> 2,3,1,'2 is nice by 3','2017-03-02',
words-> 2,4,0,'2 is not nice by 4','2017-03-03'
words-> \.
Invalid command \.. Try \? for help.
words-> ;
ERROR: syntax error at or near "1"
LINE 1: 1,2,1,'1 is nice by 2','2017-03-01',
^

I am not sure if FORMAT csv or FORMAT 'csv' should be used.

And I have tried adding/removing commas at the end of lines too.

I have looked at pg_dump output, but it does not use csv.

Regards
Alex

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2017-03-21 17:49:02 Re: COPY ... FROM stdin WITH FORMAT csv
Previous Message Klaus P. Pieper 2017-03-21 15:20:17 Re: Why is this functional index not used?