Date: Wed, 11 Jun 2014 06:38:45 -0700 (PDT)
From: David G Johnston <david.g.johnston@gmail.com>
To: pgsql-sql@postgresql.org
Message-ID: <1402493925433-5806825.post@n5.nabble.com>
In-Reply-To: <1402480834884-5806798.post@n5.nabble.com>
References: <1402480834884-5806798.post@n5.nabble.com>
Subject: Re: need to pass the csv file name as value to the column file_id
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

kranthi wrote
> Hi All,
> I need to copy data from csv file, In that table I have one column as
> file_id. I need to pass the csv file name as value to the column file_id.
> Could please help on this.

Then the name of the CSV file has to be part of the content in the file.


> I tried like below but no luck :-(
> 
> COPY game.screen (
> 	DATETIMESERVER ,
> 	DATETIMELOC ,
>         WEBSESSION ,
>         DEVICEOS ,
>         DEVICEBROWSER ,
>         DEVICEBROWSERVERSION ,
>         USERAGENT ,
>         GAMEVERSION ,
>         file_id as "2014060923.csv"
> )
> FROM 's3://testing/game-raw/2014060923.csv' 
> CREDENTIALS 'aws_access_key_id=XXXXXXX;aws_secret_access_key=xxxxxxx' 
> delimiter ','
> CSV  QUOTE  AS  '"'
> IGNOREHEADER  1
> NULL AS '';
> 
> Error :   [COPY - 0 row(s), 0.000 secs]  [Error Code: 0, SQL State: 42601] 
> ERROR: syntax error at or near "as"
>   Position: 212

Did you read the following?

http://www.postgresql.org/docs/9.2/interactive/sql-copy.html

If so I'm not sure how it is you think the above would work...

1) the column name portion can only contain column names - using AS "data"
is not defined
2) I have no clue where you got CREDENTIALS from
3) the options to copy are all enclosed in parentheses
4) when multiple options are present they are comma-separated
5) What is "IGNOREHEADER"?
6) no "AS" after NULL specifier

I suppose you might be using non-core PostgreSQL but in that case you really
need to share that information upfront.  It is also good form to share the
version you are on - though in the case of COPY  all of the 9.x versions are
similar in syntax and behavior so unless you are on an earlier version the
conclusions would be the same.

If you are on 9.3 you get considerably more flexibility due to the
capability of using "PROGRAM" as a source/target.

If you actually want help solving the problem you need to also share the
platform you are running on (server and/or client).

David J.


--
View this message in context: http://postgresql.1045698.n5.nabble.com/need-to-pass-the-csv-file-name-as-value-to-the-column-file-id-tp5806798p5806825.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql