Thread: Re: need to pass the csv file name as value to the column file_id

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.

David G Johnston wrote
> 
> 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.

One other possibility is to create a temporary table with the appropriate
default for the filename column.

There really isn't any way for you to use programs, like sed/awk/perl, to
add the data to the original file unless you use psql to perform the
transfer (if that is even an option in this context).

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-tp5806798p5806834.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

kranthi wrote
> Actually , we are migrate from vertica to redshift. In redshift  we are
> using PostgreSQL. In vertica we used this type I hope it is possible in
> PostgreSQL also
> 
> In vertica  we used like below. It works fine.
> 
> Copy schema.table(
> Xxxxxxxx
> file_id as '2014061123.CSV'
> Xxxxxx

Not that I am aware of or can find documented.  Neither in RedShift nor
core.

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-tp5806798p5806845.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.