[SQL] UTF characters compromising data import.
Hi Everyone, I am trying to import some data (provided to us from an external source) from a CSV file using "\copy " But I get the following error message; invalid byte sequence for encoding "UTF8": 0xfd HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding". I understand the error message - but what I don't know is what I need to set the encoding to - in order to import / use the data. As always - thanks in advance for any help you might be able to provide. Gavin "Beau" Baumanis -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] UTF characters compromising data import.
Hello 2011/2/8 Gavin Beau Baumanis : > Hi Everyone, > > I am trying to import some data (provided to us from an external source) from > a CSV file using "\copy " > > But I get the following error message; > invalid byte sequence for encoding "UTF8": 0xfd > HINT: This error can also happen if the byte sequence does not match the > encoding expected by the server, which is controlled by "client_encoding". > > I understand the error message - but what I don't know is what I need to set > the encoding to - in order to import / use the data. > is impossible to import data without knowledge of encoding. you can use a some utils, that try to select a encoding http://linux.die.net/man/1/enca Regards Pavel Stehule > As always - thanks in advance for any help you might be able to provide. > > > Gavin "Beau" Baumanis > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Get postgresql server ip address
Hello, Is it possible to get postgresql server ip address using sql command? TIA, Geramel - "Share your knowledge. It's a way to achieve immortality" - Dalai Lama -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Get postgresql server ip address
2011/2/8 Gera Mel Handumon > Hello, > > Is it possible to get postgresql server ip address using sql command? > Oh yes, use inet_server_addr() function. Please note, that NULL is returned if connection established via socket. > > TIA, > > Geramel > > > - > "Share your knowledge. It's a way to achieve immortality" - Dalai Lama > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- // Dmitriy.
Re: [SQL] Get postgresql server ip address
gerame...@gmail.com (Gera Mel Handumon) writes:
> Is it possible to get postgresql server ip address using sql command?
You can get the IP address tied to a specific connection...
See the function inet_server_port().
Note, however, that the server may have a multiplicity of IP addresses,
so this may only be a partial answer.
--
output = ("cbbrowne" "@" "gmail.com")
"A lot of people come to this newsgroup and do nothing but complain
about Lisp. I think maybe they are such heavy complainers that they
think they read comp.lain.lisp." -- Erik Naggum
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Cumulative result with increment
Thanks Steve, That did the trick! I appreciate the help Shawn On Mon, 2011-02-07 at 14:13 -0800, Steve Crawford wrote: > On 02/07/2011 01:11 PM, Shawn Tayler wrote: > > Hello, > > > > I am struggling with what is probably a simple problem but I am coming > > up blank. In the interest of full disclosure I am not a very savy > > programmer by any stretch. > > > > I have a table of data from an external source which contains numbers of > > events per operating group per hour per day, kind of like this: > > > > Group | events | duration | timestamp > > > > The Group, events, and duration (milliseconds) are all integers in char > > format. Timestamp is as stated. The records are at 1 hour increments. > > I need to sum the events and duration fields in 8 hour (shifts, mid to > > 8AM, 8AM to 4PM, etc). > > > > Id like the output to be in table format ala: > > > > Group | events | duration | date| shift > > --+-++---+--- > > 100 | 26 |00:00:25.00 |2011-01-01 | Day > > > > I have a function to convert the duration to an interval already and can > > make one to do the shift labeling. Its the rest that has me stumped. > > > > Any suggestions or direction? > > > > As always, your help is greatly appreciated. > > > > I'm not sure exactly what you want but it sounds like you just want an > output column that has the shift instead of timestamp. You can then sum > on that column. Don't know what your shifts are called, but this will > give you dog-, day- and night-shifts based on your times: > > case > when extract(hour from '2011-01-12 1600'::timestamptz)< 8 then 'dog' > when extract(hour from now())< 16 then 'day' > else 'night' > end as shiftname > > This can be used for grouping as well as display. > > Cheers, > Steve > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] data import: 12-hour time w/o AM/PM
I am importing data in CSV format. My technique is to COPY the data into interim tables (starting with a "z") where I analyze them, clean up inconsistencies; eventually I will populate the "proper" tables with these data. (In the example below: COPY CSV into zevent, from there populate event.) The time field of the event data is givng me a headache: it's in 12h format but without the AM/PM qualifier! CREATE TABLE zevent ( id integer, previous integer, "next" integer, location_id integer NOT NULL, datum date NOT NULL, otime character varying(5) NOT NULL ) CREATE TABLE event ( id integer NOT NULL, location_fk integer NOT NULL, otime timestamp without time zone NOT NULL, next_fk integer, previous_fk integer, CONSTRAINT event_pkey PRIMARY KEY (id), CONSTRAINT next_event_fk FOREIGN KEY (next_fk) REFERENCES event (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT previous_event_fk FOREIGN KEY (previous_fk) REFERENCES event (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) From the date and time I want to create a timestamp. I know that - the events take place during the day, say between 10:30 and 22:30 - it's always a set of events at one location spaced about 30min apart - the imported data are chained (have a link to previous/next event) Have you got any idea how I could tackle this problem -- Best Regards, Tarlika Elisabeth Schmitz -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] data import: 12-hour time w/o AM/PM
Howdy, Tarlika. If the data doesn't bring the AM/PM how are you supposed to distinguish between,say, 10pm and 10am ? Does it always start at 10:30am? So 10am never arises? Best, Oliveiros - Original Message - From: "Tarlika Elisabeth Schmitz" To: Sent: Tuesday, February 08, 2011 6:14 PM Subject: [SQL] data import: 12-hour time w/o AM/PM I am importing data in CSV format. My technique is to COPY the data into interim tables (starting with a "z") where I analyze them, clean up inconsistencies; eventually I will populate the "proper" tables with these data. (In the example below: COPY CSV into zevent, from there populate event.) The time field of the event data is givng me a headache: it's in 12h format but without the AM/PM qualifier! CREATE TABLE zevent ( id integer, previous integer, "next" integer, location_id integer NOT NULL, datum date NOT NULL, otime character varying(5) NOT NULL ) CREATE TABLE event ( id integer NOT NULL, location_fk integer NOT NULL, otime timestamp without time zone NOT NULL, next_fk integer, previous_fk integer, CONSTRAINT event_pkey PRIMARY KEY (id), CONSTRAINT next_event_fk FOREIGN KEY (next_fk) REFERENCES event (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT previous_event_fk FOREIGN KEY (previous_fk) REFERENCES event (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) From the date and time I want to create a timestamp. I know that - the events take place during the day, say between 10:30 and 22:30 - it's always a set of events at one location spaced about 30min apart - the imported data are chained (have a link to previous/next event) Have you got any idea how I could tackle this problem -- Best Regards, Tarlika Elisabeth Schmitz -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] data import: 12-hour time w/o AM/PM
On Tuesday 8. February 2011 19.14.00 Tarlika Elisabeth Schmitz wrote: > From the date and time I want to create a timestamp. > I know that > - the events take place during the day, say between 10:30 and 22:30 > - it's always a set of events at one location spaced about 30min apart > - the imported data are chained (have a link to previous/next event) > > > Have you got any idea how I could tackle this problem Add 12 hours if time is in the interval 1:00-10:30? regards, Leif -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] data import: 12-hour time w/o AM/PM
On 9 February 2011 07:14, Tarlika Elisabeth Schmitz wrote: > From the date and time I want to create a timestamp. > I know that > - the events take place during the day, say between 10:30 and 22:30 > - it's always a set of events at one location spaced about 30min apart > - the imported data are chained (have a link to previous/next event) Any chance of seeing actual data? Might be a job for awk/perl rather than the RDBMS. Cheers, Andrej -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] "select c1, method(c2) group by c1" returns all values of c2 for c1
Good afternoon, Is there a method to retrieve the following results: T1(c1 int, c2 varchar(128) ) - (1, val1); (1, val2); (1, val3); (2, val1); (3, val5); (3, val6); select c1, method(c2) group by c1 returns: 1, "val1, val2, val3" 2, "val1" 3, "val5, val6" Thanks a lot! -- Lu Ying -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] "select c1, method(c2) group by c1" returns all values of c2 for c1
Hello you can use a string%agg function if you have a 9.0. On older version there is a array_agg function select c1, array_to_string(array_agg(c2),',') from T1 group by c1 regards Pavel Stehule 2011/2/8 Emi Lu : > Good afternoon, > > Is there a method to retrieve the following results: > > T1(c1 int, c2 varchar(128) ) > - > > > (1, val1); > (1, val2); > (1, val3); > (2, val1); > (3, val5); > (3, val6); > > select c1, method(c2) > group by c1 > > returns: > > 1, "val1, val2, val3" > 2, "val1" > 3, "val5, val6" > > > Thanks a lot! > > -- > Lu Ying > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] "select c1, method(c2) group by c1" returns all values of c2 for c1
SELECT distinct c1,array_to_string(array(SELECT c2 FROM T1 B where A.c1=B.c1),', ') from T1 A order by c1; Give it a Try !!! Rolando A. Edwards MySQL DBA (SCMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM & Skype : RolandoLogicWorx redwa...@logicworks.net http://www.linkedin.com/in/rolandoedwards -Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Emi Lu Sent: Tuesday, February 08, 2011 2:36 PM To: pgsql-sql@postgresql.org Subject: [SQL] "select c1, method(c2) group by c1" returns all values of c2 for c1 Good afternoon, Is there a method to retrieve the following results: T1(c1 int, c2 varchar(128) ) - (1, val1); (1, val2); (1, val3); (2, val1); (3, val5); (3, val6); select c1, method(c2) group by c1 returns: 1, "val1, val2, val3" 2, "val1" 3, "val5, val6" Thanks a lot! -- Lu Ying -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] "select c1, method(c2) group by c1" returns all values of c2 for c1
I'm not saying this is good or best but: select distinct a.c1, array_to_string(array(select c2 from t1 as b where b.c1 = a.c1),',') from t1 as a; c1 | array_to_string +- 1 | val1,val2,val3 2 | val1 3 | val5,val6 (3 rows) On Tue, Feb 8, 2011 at 2:35 PM, Emi Lu wrote: > Good afternoon, > > Is there a method to retrieve the following results: > > T1(c1 int, c2 varchar(128) ) > - > > > (1, val1); > (1, val2); > (1, val3); > (2, val1); > (3, val5); > (3, val6); > > select c1, method(c2) > group by c1 > > returns: > > 1, "val1, val2, val3" > 2, "val1" > 3, "val5, val6" > > > Thanks a lot! > > -- > Lu Ying > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Peter Steinheuser psteinheu...@myyearbook.com
Re: [SQL] "select c1, method(c2) group by c1" returns all values of c2 for c1
On 02/08/2011 02:51 PM, Rolando Edwards wrote: SELECT distinct c1,array_to_string(array(SELECT c2 FROM T1 B where A.c1=B.c1),', ') from T1 A order by c1; Give it a Try !!! Thanks a lot! Very helpful! array_to_string() + array() is exactly what I am looking for! I just wonder that array_to_string() + array() will provide me good performance, right? If the calculation will be based on millions records. Thanks again! -- Lu Ying Rolando A. Edwards MySQL DBA (SCMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM& Skype : RolandoLogicWorx redwa...@logicworks.net http://www.linkedin.com/in/rolandoedwards -Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Emi Lu Sent: Tuesday, February 08, 2011 2:36 PM To: pgsql-sql@postgresql.org Subject: [SQL] "select c1, method(c2) group by c1" returns all values of c2 for c1 Good afternoon, Is there a method to retrieve the following results: T1(c1 int, c2 varchar(128) ) - (1, val1); (1, val2); (1, val3); (2, val1); (3, val5); (3, val6); select c1, method(c2) group by c1 returns: 1, "val1, val2, val3" 2, "val1" 3, "val5, val6" Thanks a lot! -- Lu Ying -- Emi Lu, ENCS, Concordia University, Montreal H3G 1M8 em...@encs.concordia.ca+1 514 848-2424 x5884 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] "select c1, method(c2) group by c1" returns all values of c2 for c1
2011/2/8 Emi Lu : > On 02/08/2011 02:51 PM, Rolando Edwards wrote: >> >> SELECT distinct c1,array_to_string(array(SELECT c2 FROM T1 B where >> A.c1=B.c1),', ') from T1 A order by c1; >> >> Give it a Try !!! > > > Thanks a lot! Very helpful! > > array_to_string() + array() is exactly what I am looking for! > > I just wonder that array_to_string() + array() will provide me good > performance, right? If the calculation will be based on millions records. it depend on number of groups. This is correlated subquery - it must not be a best. Regards Pavel Stehule the best speed gives a string_agg, but it is only in 9.0 > > Thanks again! > -- > Lu Ying > > > > > >> Rolando A. Edwards >> MySQL DBA (SCMDBA) >> >> 155 Avenue of the Americas, Fifth Floor >> New York, NY 10013 >> 212-625-5307 (Work) >> 201-660-3221 (Cell) >> AIM& Skype : RolandoLogicWorx >> redwa...@logicworks.net >> http://www.linkedin.com/in/rolandoedwards >> >> >> -Original Message- >> From: pgsql-sql-ow...@postgresql.org >> [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Emi Lu >> Sent: Tuesday, February 08, 2011 2:36 PM >> To: pgsql-sql@postgresql.org >> Subject: [SQL] "select c1, method(c2) group by c1" returns all values of >> c2 for c1 >> >> Good afternoon, >> >> Is there a method to retrieve the following results: >> >> T1(c1 int, c2 varchar(128) ) >> - >> >> >> (1, val1); >> (1, val2); >> (1, val3); >> (2, val1); >> (3, val5); >> (3, val6); >> >> select c1, method(c2) >> group by c1 >> >> returns: >> >> 1, "val1, val2, val3" >> 2, "val1" >> 3, "val5, val6" >> >> >> Thanks a lot! >> >> -- >> Lu Ying >> > > > -- > Emi Lu, ENCS, Concordia University, Montreal H3G 1M8 > em...@encs.concordia.ca +1 514 848-2424 x5884 > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] data import: 12-hour time w/o AM/PM
On Wed, 9 Feb 2011 08:21:47 +1300 Andrej wrote: >On 9 February 2011 07:14, Tarlika Elisabeth Schmitz > wrote: >> From the date and time I want to create a timestamp. >> I know that >> - the events take place during the day, say between 10:30 and 22:30 >> - it's always a set of events at one location spaced about 30min >> apart >> - the imported data are chained (have a link to previous/next event) > >Any chance of seeing actual data? Might be a job for awk/perl rather >than the RDBMS. attached small CSV sample: at both locations, events start just after twelve o'clock (noon). -- Best Regards, Tarlika Elisabeth Schmitz location_id datum otime id previous next 253 2010-09-08 01:05 942 941 943 253 2010-09-08 01:40 943 942 944 253 2010-09-08 02:10 944 943 945 253 2010-09-08 02:45 945 944 946 253 2010-09-08 03:20 946 945 947 253 2010-09-08 03:50 947 946 (null) 253 2010-09-08 12:30 941 (null) 942 9062 2010-09-08 01:00 858 857 859 9062 2010-09-08 01:35 859 858 860 9062 2010-09-08 02:05 860 859 861 9062 2010-09-08 02:40 861 860 862 9062 2010-09-08 03:15 862 861 863 9062 2010-09-08 03:45 863 862 (null) 9062 2010-09-08 12:25 857 (null) 858 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] data import: 12-hour time w/o AM/PM
On Tue, 8 Feb 2011 18:38:44 - "Oliveiros d'Azevedo Cristina" wrote: >- Original Message - >From: "Tarlika Elisabeth Schmitz" >To: >Sent: Tuesday, February 08, 2011 6:14 PM >Subject: [SQL] data import: 12-hour time w/o AM/PM > > >I am importing data in CSV format. My technique is to COPY the data >into interim tables (starting with a "z") where I analyze them, clean >up inconsistencies; eventually I will populate the "proper" tables with >these data. (In the example below: COPY CSV into zevent, from there >populate event.) > >The time field of the event data is givng me a headache: it's in 12h >format but without the AM/PM qualifier! > >Howdy, Tarlika. > >If the data doesn't bring the AM/PM how are you supposed to >distinguish between,say, 10pm and 10am ? Does it always start at >10:30am? So 10am never arises? > >Best, >Oliveiros > On a given day, between 6-9 events are staged, about 1/2 hour apart. If the set of events spans 12:00, I definitely know that this isn't midnight. I also know the order within a set. Looking at a particular location, which contributes to 80% of the data, there have been no events at 10:?? ever (so far). Looking at it as a human being I can decide in a fraction of a second whether it's AM or PM. I just can't think of an automated procedure how to transform this half-baked 12-hour nonsense into a time. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
