Thread: PG Admin - Import from URL?

Hi all,

Bit of a random query this, but I have the below query which creates a
table ready to import some data into it:

CREATE TABLE busroutes_raw(
id SEQUENCE PRIMARY KEY,
route VARCHAR,
run INTEGER,
sequence INTEGER,
stop_name VARCHAR,
loation_easting INTEGER,
location_northing INTEGER,
);

I then import the data as below:

COPY busroutes_raw from 'C:/Program Files
(x86)/PostgreSQL/8.4/data/BusRoutes.csv' DELIMITERS ',' CSV;

But I wonder whether it is possible to put a URL as the origin of the
file rather than a path?

Thanks

James

James David Smith <james.david.smith@gmail.com> wrote:
> I then import the data as below:
>
> COPY busroutes_raw from 'C:/Program Files
> (x86)/PostgreSQL/8.4/data/BusRoutes.csv' DELIMITERS ',' CSV;
>
> But I wonder whether it is possible to put a URL as the origin of the
> file rather than a path?

Sure, use tools like wget or other to fetch the file from internet and
raise it to stdout, pipe that to psql -c "\copy ... from 'stdin'"

(something like that, untestet)


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

James David Smith <james.david.smith@gmail.com> wrote:

> Hi Andreas,
>
> Thanks for your reply, but I've no idea what WGET or STDOUT is. As the
> mailing list suggests, I'm a pgsql novice... :-)
>
> No worries anyway, it was more a query/question than a particular problem.

1st: abswer to the list, not to me
2nd: please void top-posting, it's hard to read
3rd:

a simple example:

kretschmer@tux:~$ cat data.csv
1,data1
2,data2
3,data3
kretschmer@tux:~$ cp data.csv public_html/
kretschmer@tux:~$ echo "create table download (col1 int, col2 text)" | psql -X test
CREATE TABLE
kretschmer@tux:~$ wget http://localhost/~kretschmer/data.csv -O - -q | psql -X -c "copy download from stdin delimiter
','"test 
kretschmer@tux:~$
kretschmer@tux:~$
kretschmer@tux:~$ psql test -c "select * from download";
 col1 | col2
------+-------
    1 | data1
    2 | data2
    3 | data3
(3 rows)


I have a file called 'data.csv', with "," as delimiter. I copied that to my local webserver-webspace.
With wget i can download the file, option -O means 'write the content to stdout, and piped that into
psql. The table contains now the content of my file. That's all, easy, right?



>
> Cheers
>
> James
>
>
>
> On 15 March 2012 16:04, Andreas Kretschmer <akretschmer@spamfence.net> wrote:
> > James David Smith <james.david.smith@gmail.com> wrote:
> >> I then import the data as below:
> >>
> >> COPY busroutes_raw from 'C:/Program Files
> >> (x86)/PostgreSQL/8.4/data/BusRoutes.csv' DELIMITERS ',' CSV;
> >>
> >> But I wonder whether it is possible to put a URL as the origin of the
> >> file rather than a path?
> >
> > Sure, use tools like wget or other to fetch the file from internet and
> > raise it to stdout, pipe that to psql -c "\copy ... from 'stdin'"
> >
> > (something like that, untestet)
> >
> >
> > Andreas
> > --
> > Really, I'm not out to destroy Microsoft. That will just be a completely
> > unintentional side effect.                              (Linus Torvalds)
> > "If I was god, I would recompile penguin with --enable-fly."   (unknown)
> > Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°
> >
> > --
> > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-novice


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°