Thread: big problem

big problem

From

Frank_Lupo Frank_Lupo

Date:

11 March 2002, 06:23:11

My verions of postgres is 7.2 in window2000.
========================= ==================
select 'aaa'+'aaa';
return :
?column?
----------
?
????????????
select textcat('aaa','aaa');
textcat
---------
aaaaaa
========================= ==================
I have a table which two columns (cod,des) are varchar.

When select :
select cod+des from .....
error

I creata function and operator +:
CREATE FUNCTION "varcharcat"("varchar", "varchar") RETURNS "text" AS 'select textcat($1,$2)' LANGUAGE 'sql';
COMMENT ON FUNCTION "varcharcat"("varchar", "varchar") IS 'concatenate';

CREATE OPERATOR + (PROCEDURE = "varcharcat", LEFTARG = "varchar", RIGHTARG = "varchar");

The resul is ok but :
select 'aaa'+'aaa';
ERROR: Unable to identify an operator '+' for types 'unknown' and 'unknown'
You will have to retype this query using an explicit cast
ERROR: Unable to identify an operator '+' for types 'unknown' and 'unknown'
You will have to retype this query using an explicit cast
========================= ==================
I work in win2000. The setting are:
- language italian;
- time/zone Rome (GMT + 1.00 h)

In postgresql the set are:
PGDATESTYLE="European"
PGTZ="Europe/Rome"

Time win2000 8:26

template1=# select current_time;
timetz
--------------------
07:26:33.518169+00
?????????????????????????????????

Because the time/zone are not equal?

========================= ==================
Problem in insert

CREATE TABLE "irelbcmb" (
"id" int4,
"id_pad" int4,
"desc" int4,
"cod" varchar(40),
"sys_var" varchar(1),
"nom_var" varchar(16)
) WITH OIDS;

gedis30=# insert into irelbcmb (id,id_pad,desc,cod,sys_var,nom_var) values 1,1,1,"pippo","1","pippo");
ERROR: parser: parse error at or near "desc"

========================= ==================
Tanks

Bye !!

--
Frank Lupo (Wolf) !!
------------------------------------------------------------------------
Questo messaggio e' stato inviato con Telemail http://www.telemail.it ,
    La freemail per la comunicazione unificata gratuita

Re: big problem

From

Tom Lane

Date:

11 March 2002, 13:04:01

Frank_Lupo Frank_Lupo <frank_lupo@telemail.it> writes:
> select 'aaa'+'aaa';
> return :
> ?column?
> ----------
> ?

"+" is not the operator for concatenation.  Use the SQL-standard
concatenation operator, "||".

Just FYI, what you seem to be getting is coercion to type "char"
(the single-byte char, not char(N)) and then addition of character
values.

If you really insist on spelling concatenation as "+", try

create operator + (leftarg=text, rightarg=text, procedure=textcat);

This will work better than making one for varchar because text is
the preferred string type.

            regards, tom lane

Re: big problem

From

Stephan Szabo

Date:

11 March 2002, 15:29:34

On Mon, 11 Mar 2002, Frank_Lupo Frank_Lupo wrote:

> My verions of postgres is 7.2 in window2000.
> ========================= ==================
> select 'aaa'+'aaa';
> return :
> ?column?
> ----------
> ?
> ????????????

You probably want || (the concatenation operator)
rather than defining your own.

> I creata function and operator +:
> CREATE FUNCTION "varcharcat"("varchar", "varchar") RETURNS "text" AS 'select textcat($1,$2)' LANGUAGE 'sql';
> COMMENT ON FUNCTION "varcharcat"("varchar", "varchar") IS 'concatenate';
>
> CREATE OPERATOR + (PROCEDURE = "varcharcat", LEFTARG = "varchar", RIGHTARG = "varchar");
>
> The resul is ok but :
> select 'aaa'+'aaa';
> ERROR: Unable to identify an operator '+' for types 'unknown' and 'unknown'
> You will have to retype this query using an explicit cast
> ERROR: Unable to identify an operator '+' for types 'unknown' and 'unknown'
> You will have to retype this query using an explicit cast

The only thing I can think of is that it might have multiple operators
that it's considering and can't decide.  It seemed to work for me if I
defined it over text.

>
> ========================= ==================
> Problem in insert
>
> CREATE TABLE "irelbcmb" (
> "id" int4,
> "id_pad" int4,
> "desc" int4,
> "cod" varchar(40),
> "sys_var" varchar(1),
> "nom_var" varchar(16)
> ) WITH OIDS;
>
> gedis30=# insert into irelbcmb (id,id_pad,desc,cod,sys_var,nom_var) values 1,1,1,"pippo","1","pippo");
> ERROR: parser: parse error at or near "desc"

desc is probably reserved (given order by ... desc), use double quotes
around the name.  In fact, if you create using double quotes, you should
probably always use double quotes around the names.  In addition, you're
missing the open paren after values and you want single quotes not double
on the last thress trings.

Re: big problem

From

Mike Mascari

Date:

11 March 2002, 15:42:10

Frank_Lupo Frank_Lupo wrote:
>
> Problem in insert
>
> CREATE TABLE "irelbcmb" (
> "id" int4,
> "id_pad" int4,
> "desc" int4,
> "cod" varchar(40),
> "sys_var" varchar(1),
> "nom_var" varchar(16)
> ) WITH OIDS;
>
> gedis30=# insert into irelbcmb (id,id_pad,desc,cod,sys_var,nom_var) values 1,1,1,"pippo","1","pippo");
> ERROR: parser: parse error at or near "desc"

PostgreSQL allowed for the CREATE TABLE with a column of "desc" because
you used double-quotes around the name. DESC is a keyword as in:

SELECT name
FROM employees
ORDER BY name DESC;
              ^^^^

Therefore, if you use it as a column, you must put double-quotes around
it in all your queries:

INSERT INTO irelbcmb (id, id_pad, "desc", cod, sys_var, nom_var)
VALUES (1, 1, 1, 'pippo','1','pippo');

Hope that helps,

Mike Mascari
mascarm@mascari.com