[SQL] \COPY in psql using \e
I'm connecting to a DB with psql -h host DB once in psql I'd like to \e create temp table t1 ...; \copy t1 from 'filename' ... select * from t1; [ESC]:x what I get is: CREATE TABLE ERROR: syntax error at or near "select" LINE 3: select * from t1; ^ \copy: ERROR: syntax error at or near "select" LINE 3: select * from t1; ^ Now if I do: \e create temp table t1 ...; \copy t1 from 'filename' ... [ESC]:x select * from t1; everything works fine. I've a lot of script files that actually contain mixed \COPY and "plain" SQL statement eg. truncate table t1; \copy t1 from 'filename1' insert into d1...; truncate table t2; \copy t2 from 'filename2 insert into d1...; that I execute as: psql -h host DB < myscript.sql and everything is fine. Of course one solution could be to open a file in vi in a xterm and execute it from another xterm. Anything cleaner? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Number timestamped rows
Dear all, maybe a stupid question, but: I have a table that is ordered like this: user_id|timestamp|event 1 |0:1 |event_a 1 |0:2 |event_b 2 |0:1 |event_b 2 |0:3 |event_c 2 |0:4 |event_b 3 |0:1 |event_a and I would like to number them according to their timestamps like this: user_id|timestamp|event |order 1 |0:1 |event_a |1 1 |0:2 |event_b |2 2 |0:1 |event_b |1 2 |0:3 |event_c |2 2 |0:4 |event_b |3 3 |0:1 |event_a |1 How would I do this with an UPDATE statement (e.g.) in pgsql? Thanks in advance for any hint Jan -- NEU: FreePhone - 0ct/min Handyspartarif mit Geld-zurück-Garantie! Jetzt informieren: http://www.gmx.net/de/go/freephone -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Number timestamped rows
Jan Peters wrote: > Dear all, > maybe a stupid question, but: I have a table that is ordered like this: > > user_id|timestamp|event > 1 |0:1 |event_a > 1 |0:2 |event_b > 2 |0:1 |event_b > 2 |0:3 |event_c > 2 |0:4 |event_b > 3 |0:1 |event_a > > and I would like to number them according to their timestamps like this: > > user_id|timestamp|event |order > 1 |0:1 |event_a |1 > 1 |0:2 |event_b |2 > 2 |0:1 |event_b |1 > 2 |0:3 |event_c |2 > 2 |0:4 |event_b |3 > 3 |0:1 |event_a |1 > untested: select user_id, timestamp, event, row_number() over (partition by user_id order by timestamp) as "order" from your_table > How would I do this with an UPDATE statement (e.g.) in pgsql? I think, you don't need an UPDATE, just a SELECT. 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-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
