[SQL] Type conversion from TEXT to DOUBLE PRECISION
Hi all, Thank you for reading this mail. I am trying to do the following: Extract the first half of _aaa and put it in column _bbb Here is the table, named: _table: Varchar[10] | Double Precision _aaa_bbb _ 1234567890 I used two functions to do it: substring() and to_number(). The SQL is like this: UPDATE _table SET _bbb = to_number(substring(_aaa from 1 for 5), '9'); The machine fails me and said ERROR: invalid input syntac for type numeric: " " I guess the machine can not treat TEXT as CHAR[5]. I tries to CAST TEXT as CHAR[5]. It also doesnt allow me to do so. Can anyone give me some hints on this? regards, Daniel ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[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] Type conversion from TEXT to DOUBLE PRECISION
Daniel Lau <[EMAIL PROTECTED]> writes:
> I used two functions to do it: substring() and to_number(). The SQL is
> like this:
> UPDATE _table SET _bbb = to_number(substring(_aaa from 1 for 5), '9');
> The machine fails me and said
> ERROR: invalid input syntac for type numeric: " "
Works for me ...
regression=# create table _table (_aaa varchar(10), _bbb double precision);
CREATE TABLE
regression=# insert into _table values('1234567890', null);
INSERT 180987 1
regression=# UPDATE _table SET _bbb = to_number(substring(_aaa from 1 for 5), '9');
UPDATE 1
regression=# select * from _table;
_aaa| _bbb
+---
1234567890 | 12345
(1 row)
regression=#
I don't think you have accurately described what you did.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Type conversion from TEXT to DOUBLE PRECISION
On Friday 09 January 2004 07:35, Daniel Lau wrote: > Hi all, > > Thank you for reading this mail. > > I am trying to do the following: > Extract the first half of _aaa and put it in column _bbb > Varchar[10] | Double Precision > _aaa _bbb > 1234567890 > I used two functions to do it: substring() and to_number(). The SQL is > like this: > UPDATE _table SET _bbb = to_number(substring(_aaa from 1 for 5), '9'); > The machine fails me and said > ERROR: invalid input syntac for type numeric: " " Works here. richardh=# \d foo Table "richardh.foo" Column | Type | Modifiers +---+--- _aaa | character varying(10) | _bbb | double precision | richardh=# UPDATE foo SET _bbb = to_number(substring(_aaa from 1 for 5),'9'); UPDATE 1 richardh=# SELECT * FROM foo richardh-# ; _aaa| _bbb +--- 1234567890 | 12345 (1 row) Since your error seems to be complaining about a space, I'd guess you've got other than numeric values in _aaa. -- Richard Huxton ---(end of broadcast)--- TIP 8: explain analyze is your friend
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 ---(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] Type conversion from TEXT to DOUBLE PRECISION
Richard Huxton <[EMAIL PROTECTED]> writes:
> Since your error seems to be complaining about a space, I'd guess you've got
> other than numeric values in _aaa.
In fact, with a bit of experimentation I see the same error message:
regression=# select to_number('12345', '9');
to_number
---
12345
(1 row)
regression=# select to_number('1234 ', '9');
to_number
---
1234
(1 row)
regression=# select to_number(' 1234', '9');
to_number
---
1234
(1 row)
regression=# select to_number(' ', '9');
ERROR: invalid input syntax for type numeric: " "
regression=# select to_number('z', '9');
ERROR: invalid input syntax for type numeric: " "
regression=#
The error message's report of the input string seems a tad misleading,
especially in the last case. (Karel, is this fixable?) But anyway,
it sure looks like the problem is bad input data.
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] Type conversion from TEXT to DOUBLE PRECISION
On Fri, 9 Jan 2004, Tom Lane wrote:
> Richard Huxton <[EMAIL PROTECTED]> writes:
> > Since your error seems to be complaining about a space, I'd guess you've got
> > other than numeric values in _aaa.
>
> In fact, with a bit of experimentation I see the same error message:
>
> regression=# select to_number('12345', '9');
> to_number
> ---
> 12345
> (1 row)
>
> regression=# select to_number('1234 ', '9');
> to_number
> ---
> 1234
> (1 row)
>
> regression=# select to_number(' 1234', '9');
> to_number
> ---
> 1234
> (1 row)
>
> regression=# select to_number(' ', '9');
> ERROR: invalid input syntax for type numeric: " "
> regression=# select to_number('z', '9');
> ERROR: invalid input syntax for type numeric: " "
> regression=#
>
> The error message's report of the input string seems a tad misleading,
> especially in the last case. (Karel, is this fixable?) But anyway,
> it sure looks like the problem is bad input data.
>
> regards, tom lane
>
Thanks Tom and Richard. Yes, it is the problem of bad input data. I have
4000 rows of data and there are 10 rows containing blank string ('
'). I have to add a Where clause to carry out the SQL:
UPDATE _table
SET _bbb = to_number(substring(_aaa from 1 for 5), '9')
WHERE _aaa <> ' ';
I guess a function checking if a string contains only numbers would be
betteroff. But I find no such functions. Checking that it's not blank
would be the only solution I can think of.
Thanks again.
regards,
Daniel Lau
---(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] data loading
On Friday 09 January 2004 02:13, [EMAIL PROTECTED] wrote: > 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 Does line 141 (or nearby) have a comma somewhere in its data? That would fool the COPY into mis-counting the columns. See the COPY entry in the SQL Command Reference chapter of the manuals - you'll want to escape any commas with a backslash: \, -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] data loading
On Friday 09 January 2004 02:13, [EMAIL PROTECTED] wrote: > Hi, > Just realised this question is also posted under COPY command. Ignore me. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] how to show table structure?
Greetings, How can I see the layout of a table in PostgreSQL 7.4? I've checked several books and on-line documents, but was not able to figure out how PostgreSQL does 'describe ' like it's done in other databases. Thanks in advance for any help. Bing ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] how to show table structure?
--On Friday, January 09, 2004 17:05:37 -0600 Bing Du <[EMAIL PROTECTED]> wrote: Greetings, How can I see the layout of a table in PostgreSQL 7.4? I've checked several books and on-line documents, but was not able to figure out how PostgreSQL does 'describe ' like it's done in other databases. \d table in psql. LER Thanks in advance for any help. Bing ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 pgp0.pgp Description: PGP signature
Re: [SQL] how to show table structure?
On Fri, 9 Jan 2004, Bing Du wrote: > Greetings, > > How can I see the layout of a table in PostgreSQL 7.4? I've checked > several books and on-line documents, but was not able to figure out how > PostgreSQL does 'describe ' like it's done in other databases. If in psql, use the \d commands (\? will show you all of them. However, if you've not got psql to do it, you can look through the information_schema for anything like that, like so: select * from information_schema.tables; and so on. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] how to show table structure?
use this
Last login: Fri Jan 9 16:54:14 from 192.168.2.117
[EMAIL PROTECTED] ssakkaravel]$ psql -E training test
* QUERY **
SELECT usesuper FROM pg_user WHERE usename = 'test'
**
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
training=# \d books
* QUERY **
SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
FROM pg_class WHERE relname='books'
**
* QUERY **
SELECT a.attname, format_type(a.atttypid, a.atttypmod), a.attnotnull,
a.atthasdef, a.attnu
m
FROM pg_class c, pg_attribute a
WHERE c.relname = 'books'
AND a.attnum > 0 AND a.attrelid = c.oid
ORDER BY a.attnum
**
* QUERY **
SELECT substring(d.adsrc for 128) FROM pg_attrdef d, pg_class c
WHERE c.relname = 'books' AND c.oid = d.adrelid AND d.adnum = 1
**
Table "books"
Column| Type | Modifiers
-+---+--
--
--
bid | integer | not null default
nextval('test.books_bid_seq'::text
)
bname | character varying(20) |
price | money |
publication | date |
- Original Message -
From: "scott.marlowe" <[EMAIL PROTECTED]>
To: "Bing Du" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Saturday, January 10, 2004 4:40 AM
Subject: Re: [SQL] how to show table structure?
> On Fri, 9 Jan 2004, Bing Du wrote:
>
> > Greetings,
> >
> > How can I see the layout of a table in PostgreSQL 7.4? I've checked
> > several books and on-line documents, but was not able to figure out how
> > PostgreSQL does 'describe ' like it's done in other databases.
>
> If in psql, use the \d commands (\? will show you all of them.
>
> However, if you've not got psql to do it, you can look through the
> information_schema for anything like that, like so:
>
> select * from information_schema.tables;
>
> and so on.
>
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
