agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedRe: need to pass the csv file name as value to the column file_id
4+ messages / 1 participants
[nested] [flat]
* Re: need to pass the csv file name as value to the column file_id
@ 2014-06-11 13:38 ` David G Johnston <david.g.johnston@gmail.com>
2014-06-11 14:35 ` Re: need to pass the csv file name as value to the column file_id David G Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: David G Johnston @ 2014-06-11 13:38 UTC (permalink / raw)
To: pgsql-sql
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-i...
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: need to pass the csv file name as value to the column file_id
2014-06-11 13:38 ` Re: need to pass the csv file name as value to the column file_id David G Johnston <david.g.johnston@gmail.com>
@ 2014-06-11 14:35 ` David G Johnston <david.g.johnston@gmail.com>
2014-06-11 16:03 ` Re: need to pass the csv file name as value to the column file_id David G Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: David G Johnston @ 2014-06-11 14:35 UTC (permalink / raw)
To: pgsql-sql
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-i...
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: need to pass the csv file name as value to the column file_id
2014-06-11 13:38 ` Re: need to pass the csv file name as value to the column file_id David G Johnston <david.g.johnston@gmail.com>
2014-06-11 14:35 ` Re: need to pass the csv file name as value to the column file_id David G Johnston <david.g.johnston@gmail.com>
@ 2014-06-11 16:03 ` David G Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: David G Johnston @ 2014-06-11 16:03 UTC (permalink / raw)
To: pgsql-sql
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-i...
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: need to pass the csv file name as value to the column file_id
@ 2014-06-11 13:42 ` David G Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: David G Johnston @ 2014-06-11 13:42 UTC (permalink / raw)
To: pgsql-sql
kranthi wrote
> FROM 's3://testing/game-raw/2014060923.csv'
Also, PostgreSQL will be unable to resolve a URL, especially one specifying
the "s3" protocol.
Perhaps you are using Amazon RedShift and they have customized their source
to enhance the COPY command?
You particular problem is core PostgreSQL and has the same answer - somehow
you have to make the name of the file part of the file content seen by the
COPY command.
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-i...
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2014-06-11 16:03 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-06-11 13:38 ` David G Johnston <david.g.johnston@gmail.com>
2014-06-11 14:35 ` David G Johnston <david.g.johnston@gmail.com>
2014-06-11 16:03 ` David G Johnston <david.g.johnston@gmail.com>
2014-06-11 13:42 ` David G Johnston <david.g.johnston@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox