[SQL] COPY command
Hi, i try to load a file into temporary table but it gives me an error . do u have any idea/solutions ?? the command that i've running as below : dwnc=# copy biosadm.custdo_temp dwnc-# from '/home/bios/customer_data/CustomerDO_new.CSV' dwnc-# WITH DELIMITER ',' ; ERROR: copy: line 141, Extra data after last expected column FYI, my file was in comma delimiter type (csv) . My table structure as following : dwnc-# \dbiosadm.custdo_temp Table "biosadm.custdo_temp" Column | Type | Modifiers --+---+--- dono | character varying(13) | dodate | date | custname | character varying(70) | custlo | character varying(40) | attnto | character varying(80) | Please guide me . thanks
[SQL] COPY command
Hi, i try to load a file into temporary table but it gives me an error . do u have any idea/solutions ?? the command that i've running as below : dwnc=# copy biosadm.custdo_temp dwnc-# from '/home/bios/customer_data/CustomerDO_new.CSV' dwnc-# WITH DELIMITER ',' ; ERROR: copy: line 141, Extra data after last expected column FYI, my file was in comma delimiter type (csv) . My table structure as following : dwnc-# \dbiosadm.custdo_temp Table "biosadm.custdo_temp" Column | Type | Modifiers --+---+--- dono | character varying(13) | dodate | date | custname | character varying(70) | custlo | character varying(40) | attnto | character varying(80) | Please guide me . thanks ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] data loading
Hi, i try to load data from flat file (comma delimiter format) into temporary table . i use COPY command as below: dwnc=# copy biosadm.custdo_temp dwnc-# from '/home/bios/customer_data/CustomerDO_new.CSV' dwnc-# WITH DELIMITER ',' ; ERROR: copy: line 141, Extra data after last expected column do u have any idea what causes the above error ???or is there any command can be used to load data as requested. tq
Re: [SQL] COPY command
Hi , yes now i can do the copy after replace comma delimiter with tab delimiter which is default copy with delimiter . btw i've another question : how do we run the COPY command from file which has several null columns in each field. thanks. example : dwnc=# copy biosadm.custinv_temp dwnc-# from '/home/bios/customer_data/CustomerInvoice_5Dec03_tab.txt' ; ERROR: copy: line 1, Missing data for column "subsidiary" p/s: my subsidiary column has several null data. "Gregory S. Williamson" <[EMAIL PROTECTED]> 07:06 PM PST Yesterday To: <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> cc: Subject: RE: [SQL] COPY command This sounds as if the last character in each line might be a delimiter (a ",") which is standard for data unloaded from some sources; if this is the case try removing it and your data should load. Or you may have a comma in a character field and that is throwing off the count for the line in question ? HTH, Greg Williamson DBA GlobeXplorer LLC -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tue 1/6/2004 10:19 PM To: [EMAIL PROTECTED] Cc: Subject: [SQL] COPY command Hi, i try to load a file into temporary table but it gives me an error . do u have any idea/solutions ?? the command that i've running as below : dwnc=# copy biosadm.custdo_temp dwnc-# from '/home/bios/customer_data/CustomerDO_new.CSV' dwnc-# WITH DELIMITER ',' ; ERROR: copy: line 141, Extra data after last expected column FYI, my file was in comma delimiter type (csv) . My table structure as following : dwnc-# \dbiosadm.custdo_temp Table "biosadm.custdo_temp" Column | Type | Modifiers --+---+--- dono | character varying(13) | dodate | date | custname | character varying(70) | custlo | character varying(40) | attnto | character varying(80) | Please guide me . thanks
[SQL] Missing data for column
Hi, i got an error below after running copy command . the table structure as following : Table "biosadm.custinv_temp Column | Type | -+---+- yr | integer | custname | text | invstatus | text | custlo | text | invno | integer | invdate | date | amount | numeric(10,2) | acc | text | salesperson | text | ERROR: copy: line 1, Missing data for column "subsidiary" Does anybody have an idea??? TQ
Re: [SQL] Missing data for column
Hi, Sorry for the late respond. i've solved my problem. the error comes out when the data has subsidiary column but there is no subsidiary column in table. tq Michael Glaesemann <[EMAIL PROTECTED]> 01/09/2004 06:54 PM ZE9 To: [EMAIL PROTECTED] cc: [EMAIL PROTECTED] Subject: Re: [SQL] Missing data for column On Jan 9, 2004, at 4:15 PM, [EMAIL PROTECTED] wrote: > Hi, > > i got an error below after running copy command . the table structure > as > following : > > Table "biosadm.custinv_temp > Column | Type | > -+---+- > yr | integer | > custname | text | > invstatus | text | > custlo | text | > invno | integer | > invdate | date | > amount | numeric(10,2) | > acc | text | > salesperson | text | > > ERROR: copy: line 1, Missing data for column "subsidiary" Could you include the COPY command you're using, as well as the first couple of lines of the file you're copying from? It's hard to know what the problem is without this. Regards, Michael Glaesemann grzm myrealbox com
[SQL] Insert into
Hi,
i've structure table like below and want to load data from temp table into table below :
dwnc=> \d cust_lo_dim
Table "biosadm.cust_lo_dim"
Column | Type | Modifiers
---+---+--
lo_key | integer | not null default nextval('clo_seq'::text)
lo_no | character varying(25) | not null
lo_date | date |
rcvdate | date |
lo_status | character varying(15) |
Indexes: cust_lo_dim_pkey primary key btree (lo_key)
my temp table as below:
dwnc=> \d custlo_temp;
Table "biosadm.custlo_temp"
Column | Type | Modifiers
--+---+---
lono | text |
lodate | text |
rcvdate | text |
loamount | numeric(10,2) |
custname | text |
status | text |
My SELECT STATEMENT :
dwnc=> insert into cust_lo_dim
dwnc-> (lo_no,lo_date,rcvdate,lo_status)
dwnc-> select c.lono,c.lodate,c.rcvdate,c.status
dwnc-> from custlo_temp c ;
ERROR: column "lo_date" is of type date but _expression_ is of type text
You will need to rewrite or cast the _expression_
Questions :
1) How to rewrite /cast the _expression_ above ??? same goes to others column .
2) lo_key is the column which values comes from sequence clo_seq. what should i do first b4 insert into cust_lo_dim
i appreciate for the help. TQ
Re: [SQL] Insert into:Bad date external representation
Hi,
got an error as below :
dwnc=> insert into biosadm.custdo_dim
dwnc-> (do_key,do_no,do_date,attnto,custlo)
dwnc-> select nextval('cdo_seq'),c.dono::varchar,c.dodate::date,
dwnc-> c.attnto, c.custlo from custdo_temp c;
ERROR: Bad date external representation ''
Table structure (custdo_dim) :
dwnc(> \d custdo_dim
Table "biosadm.custdo_dim"
Column | Type | Modifiers
-+-+---
do_key | integer | not null default nextval('cdo_seq'::text)
do_no | text |
do_date | date |
custlo | text |
attnto | text |
Indexes: custdo_dim_pkey primary key btree (do_key)
Table structure(custdo_temp):
Table "biosadm.custdo_temp"
Column | Type | Modifiers
--+--+---
dono | text |
dodate | text |
custname | text |
custlo | text |
attnto | text |
pls help me thanks
[SQL] time series data
Hi,
i want to create time table & the structure as below :
Column
Type
Modifiers
time_key
yr_id
month_id
month_desc
day_id
integer
integer
integer
text
integer
not null default nextval('time_seq'::text)
Example of data in time table:
Time_key
yr_id
month_id
month_desc
day_id
1999
1
Jan
1
1999
1
Jan
2
2000
1
Jan
1
time_key data will keep increment reading from sequence. i'm going to have a data from yr_id =1994 --> 2009 , month_id =1 -->12
and day_id =1 -->31 for each month. So is there any solution (using function, or else) that can give me the above data
in Postgresql??.
thanks in advance.
Re: [SQL] time series data
Hi,
thanks for the info..btw can u pls explain a little bit detail since i can't get thru yr solution.
thanks in advance
Dnia 2004-01-21 10:37, Użytkownik Tomasz Myrta napisał:
> Use this integer sequence and interval datatype to get date result:
> your_date='1994-01-01'::date+'1 day'::integer * time_key
^^^
Sorry, use interval here.
Regards,
Tomasz Myrta
[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
01/21/2004 04:53 PM
To: [EMAIL PROTECTED]
cc:
Subject: [SQL] time series data
Hi,
i want to create time table & the structure as below :
Column
Type
Modifiers
time_key
yr_id
month_id
month_desc
day_id
integer
integer
integer
text
integer
not null default nextval('time_seq'::text)
Example of data in time table:
Time_key
yr_id
month_id
month_desc
day_id
1999
1
Jan
1
1999
1
Jan
2
2000
1
Jan
1
time_key data will keep increment reading from sequence. i'm going to have a data from yr_id =1994 --> 2009 , month_id =1 -->12
and day_id =1 -->31 for each month. So is there any solution (using function, or else) that can give me the above data
in Postgresql??.
thanks in advance.
Re: [SQL] time series data
Hi,
thanks for the feedback. btw i've run the statement below & got the following result :
time_key | yr_id | month_id | month_desc | day_id
--+---+--++
193 | 1994 | 7 | jul | 13
(1 row)
actually if i'm going to have a result as below how could i did in my statement ???
The result should appear as :
time_key | yr_id | month_id | month_desc |day_id
--+---+--+---
1 | 1994 | 1 | Jan
2 | 1994 | 2 | Feb
3 | 1994 | 3 | Mac
4 | 1994 | 4 | Apr
5 | 1994 | 5 | May
6 | 1994 | 6 | Jun
7 | 1994 | 7 | July
8 | 1994 | 8 | Aug
9 | 1994 | 9 | Sept
10 | 1994 | 10 | Oct
11 | 1994 | 11 | Nov
12 | 1994 | 12 | Dec
.
.
.
the data for day_id should be incremental from 1->31 for each month for specific year. meaning to say Every month should have the day_id
from 1>31 . (follow exactly the day of the month)
Time_key
Yr_id
Month_id
Month_desc
Day_id(1-30/31 days)
1
1994
1
Jan
1
2
1994
1
Jan
2
3
1994
1
Jan
3
4
1994
1
Jan
4
5
1994
1
Jan
5
6
1994
1
Jan
6
7
1994
1
Jan
7
8
1994
1
Jan
8
9
1994
1
Jan
9
pls guide /help me to solve the above problem . thanks in advance.
Tomasz Myrta <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/27/2004 10:56 AM CET
To: [EMAIL PROTECTED]
cc: [EMAIL PROTECTED]
Subject: Re: [SQL] time series data
Dnia 2004-01-27 02:42, Użytkownik [EMAIL PROTECTED] napisał:
>
>
> Hi,
>
> thanks for the info..btw can u pls explain a little bit detail since
> i can't get thru yr solution.
> thanks in advance
insert into time_table (time_key,year_id,month_id,month_desc,day_id)
select
newid,
extract('year' from your_date),
extract('month' from your_date),
to_char(your_date,'mon'),
extract('day' from your_date)
...
from (select nextval('time_seq') as newid,
'1994-01-01'::date+'1 day'::interval * currval('time_seq') as your_date) x;
Everything you need is to execute query above as many times as you need.
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[SQL] designer tool connect to PostgreSQL
Hi, i use postgresql as my database. does anyone know the designer tool that can connect to postgeSQL ??? meaning to say the tools can handle design task like create table , etc . appreciate if u can give the specific URL. thanks in advance.
[SQL] pg_hba.conf
Hi, i try to connect to my database as $ psql dwnc biosadm but got the error "psql: FATAL: No pg_hba.conf entry for host localhost, user biosadm, database dwnc" Below are the detail in pg_hba.conf # TYPE DATABASEUSERIP-ADDRESSIP-MASK METHOD local all all trust host all all 127.0.0.1 255.255.255.255 trust host allall 192.168.1.45 255.255.255.0 trust host dwncbiosadm 192.168.1.45 255.255.255.0trust the line in bold has been deleted before i connect to database . then i shutdown the database using following command : pg_ctl -D /usr/local/pgsql/data stop -m fast followed by startup database : postmaster -D /usr/local/pgsql/data > logg 2>&1 & Does anybody know why does the above error occur when trying to connect database and try connect through client using pgadmin??? thanks in advance ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] pg_hba.conf
Hi, by the way i've settle my connection problem between client application and server itself . this is happen because of editing the parameter in pg_hba.conf while the environment(database was running) was active. tq [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12:12 PM Today To: [EMAIL PROTECTED] cc: Subject: [SQL] pg_hba.conf Hi, i try to connect to my database as $ psql dwnc biosadm but got the error "psql: FATAL: No pg_hba.conf entry for host localhost, user biosadm, database dwnc" Below are the detail in pg_hba.conf # TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD local all all trust host all all 127.0.0.1 255.255.255.255 trust host all all 192.168.1.45 255.255.255.0 trust host dwnc biosadm 192.168.1.45 255.255.255.0 trust the line in bold has been deleted before i connect to database . then i shutdown the database using following command : pg_ctl -D /usr/local/pgsql/data stop -m fast followed by startup database : postmaster -D /usr/local/pgsql/data > logg 2>&1 & Does anybody know why does the above error occur when trying to connect database and try connect through client using pgadmin??? thanks in advance ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Database reporting tool
Hi, i'm using PostgreSQL as my database and now i'm looking for a good reporting tools(can do ad-hoc queries & ease of use) that can connect to several database instead of PostgreSQL. thanks in advance
Re: [SQL] designer tool connect to PostgreSQL
Hi, thanks to all for the responnd...i've look into all the tools mentioned and found many tools which has a great feautre such as designing/modelling the object/table but it seems look like that there is no tools that can do the ETL process. thanks in advance Rich Hall <[EMAIL PROTECTED]> 03/08/2004 10:55 AM EST To: [EMAIL PROTECTED] cc: Subject: Re: [SQL] designer tool connect to PostgreSQL EMS has PGManager, a great tool. Rick [EMAIL PROTECTED] wrote: > > Hi, > > i use postgresql as my database. does anyone know the designer tool > that can connect to postgeSQL ??? meaning to say the tools > can handle design task like create table , etc . appreciate if u can > give the specific URL. thanks in advance. -- _ Richard Hall Developer MicroPatent LLC 250 Dodge Avenue East Haven, Connecticut 06512 Tel: 203.868.3321 Fax: 203.466.5054 Eml: [EMAIL PROTECTED] Web: www.micropat.com MicroPatent is an Information Holdings Inc. company (NYSE:IHI).
[SQL] sqlloader features
Hi , does postgresql support for the sql loader functionality like oracle does ??? if no, is there any tools specific to do this task connect to postgresql database??? i'm looking into open-source tools that can do import & export facility. thanks in advance
