Обсуждение: Re: Help : insert a bytea data into new table
Quoth dennis <dennis@teltel.com>: > > I need to copy some data to new table. > But I encounter some error message. > the table structure > Table A: > c1 char > c2 bytea > > Table B: > c1 char > c2 bytea > > > My sql command: > insert into B as select * from a where c1=xxx 'AS' isn't valid there. What is xxx? Is it a field you haven't shown us, or is it a quoted string? > error: > operator does not exist: text || bytea That command (with 'xxx' quoted and the AS removed) doesn't give that error with those table definitions, so you will need to show us your actual query. Ben
Quoth dennis <dennis@teltel.com>: > here is example > > table name is "mail": > column | type > ------------------------- > sender |char > subject |char I presume you mean 'varchar'? > content |bytea > > > I want copy some record into new table 'mail_new'. > > sql: > create table mail_new as select * from mail sender='dennis' You omitted the WHERE. It's very hard to see what's actually going on when you keep mis-typing the commands you used. > result has an error: > operator does not exist: text || bytea > > > But if my sql statement has no column "content" > the sql works. > sql: > create table mail_new as select sender,subject from mail sender='dennis' No, it still doesn't give that error for me. Show us something you've *actually* *tried*. Ben
Quoth dennis <dennis@teltel.com>:
> Hi Ben
>
> here is my function , it's for fix missing chunk problem.
> It has same problem ,please take look
>
>
> thank for you help
>
> -------------table----------------------
>
>
> db=# \d usersessiontable;
> Table "public.usersessiontable"
> Column | Type | Modifiers
> -----------+------------------------+-----------
> serverid | character varying(100) |
> sessionid | character varying(50) |
> data | bytea |
> Indexes:
> "usersessiontable_idx" btree (sessionid)
> db=#
>
> db=# \d usersessiontable_test;
> Table "public.usersessiontable"
> Column | Type | Modifiers
> -----------+------------------------+-----------
> serverid | character varying(100) |
> sessionid | character varying(50) |
> data | bytea |
>
> ------------------function--------------------------------
>
>
> CREATE OR REPLACE FUNCTION check_missing_chunk_table_usersessiontable()
> RETURNS integer AS
> $BODY$
> declare
> begin
> records = 0;
> OPEN curs1 FOR EXECUTE 'SELECT * FROM usersessiontable ORDER BY
> sessionid';
> loop
> FETCH curs1 INTO rowvar;
> IF NOT FOUND THEN
> EXIT;
> END IF;
> begin
> a_sql = 'insert into
> usersessiontable_test(sessionid,serverid,data)
> values('''||rowvar.sessionid||''','''||rowvar.serverid||''',E'''||rowvar.data||''')';<<--my
You are trying to concatenate ''',E''' (of type text) and rowvar.data
(of type bytea). This is where the error is coming from. (This actually
works in 8.4, so I presume you're using an earlier version?) In any
case, this is not a safe way to interpolate into an SQL string: you need
the quote_literal function.
a_sql = 'insert into usersessiontable (sessionid, serverid, data) ' || 'values (' ||
quote_literal(rowvar.sessionid)|| ', ' || quote_literal(rowvar.serverid) || ', ' ||
quote_literal(rowvar.data)|| ')';
(Is there a function which will do %-interpolation the way RAISE does?
It would be much clearer in cases like this.)
Ben
Quoth dennis <dennis@teltel.com>: > Dear Ben > > thanks for you anwser. > I try to add function quote_literal on my sql statement . > > but it raise other error message (quote_literal not support bytea format): > function quote_literal(bytea) does not exist Which Postgres version are you using? Ben
[quoting fixed]
Quoth dennis <dennis.mails@gmail.com>:
> Ben Morrow wrote:
> > Quoth dennis<dennis@teltel.com>:
> >> Dear Ben
> >>
> >> thanks for you anwser.
> >> I try to add function quote_literal on my sql statement .
> >>
> >> but it raise other error message (quote_literal not support bytea format):
> >> function quote_literal(bytea) does not exist
> >
> > Which Postgres version are you using?
>
> Postgres : 8.1.4
Then I think you want
create function quote_literal (bytea) returns text immutable strict language plpgsql as $$
begin return 'E''' || replace(encode($1, 'escape'), E'\\', E'\\\\')
|| ''''; end; $$;
Ben
here is example table name is "mail": column | type ------------------------- sender |char subject |char content |bytea I want copy some record into new table 'mail_new'. sql: create table mail_new as select * from mail sender='dennis' result has an error: operator does not exist: text || bytea But if my sql statement has no column "content" the sql works. sql: create table mail_new as select sender,subject from mail sender='dennis' Ben Morrow 提到: > Quoth dennis <dennis@teltel.com>: >> I need to copy some data to new table. >> But I encounter some error message. >> the table structure >> Table A: >> c1 char >> c2 bytea >> >> Table B: >> c1 char >> c2 bytea >> >> >> My sql command: >> insert into B as select * from a where c1=xxx > > 'AS' isn't valid there. What is xxx? Is it a field you haven't shown us, > or is it a quoted string? > >> error: >> operator does not exist: text || bytea > > That command (with 'xxx' quoted and the AS removed) doesn't give that > error with those table definitions, so you will need to show us your > actual query. > > Ben > >
Hi Ben
here is my function , it's for fix missing chunk problem.
It has same problem ,please take look
thank for you help
-------------table----------------------
db=# \d usersessiontable; Table "public.usersessiontable" Column | Type | Modifiers
-----------+------------------------+----------- serverid | character varying(100) | sessionid | character varying(50)
| data | bytea |
Indexes: "usersessiontable_idx" btree (sessionid)
db=#
db=# \d usersessiontable_test; Table "public.usersessiontable" Column | Type | Modifiers
-----------+------------------------+----------- serverid | character varying(100) | sessionid | character varying(50)
| data | bytea |
------------------function--------------------------------
CREATE OR REPLACE FUNCTION check_missing_chunk_table_usersessiontable() RETURNS integer AS
$BODY$
declare
begin records = 0; OPEN curs1 FOR EXECUTE 'SELECT * FROM usersessiontable ORDER BY
sessionid'; loop FETCH curs1 INTO rowvar; IF NOT FOUND THEN EXIT; END IF;
begin a_sql = 'insert into
usersessiontable_test(sessionid,serverid,data)
values('''||rowvar.sessionid||''','''||rowvar.serverid||''',E'''||rowvar.data||''')';<<--my
problem execute a_sql; exception when others then raise notice '/* NUM:%,
DETAILS:%*/', SQLSTATE, SQLERRM; raise notice 'select * from % order by % limit 1
offset %',v_old_table,v_old_order_by,records; end; records=records+1; end loop; return records;
end;$BODY$ LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
ALTER FUNCTION check_missing_chunk_table_usersessiontable() OWNER TO
postgres;
----------------------run function ---------------------------
select check_missing_chunk_table_usersessiontable();
result:
NOTICE: /* NUM:42883, DETAILS:operator does not exist: text || bytea */ <<<<<my error message
NOTICE: select * from usersessiontable order by sessionid limit 1 offset 1 check_missing_chunk_table_usersessiontable
-------------------------------------------- 1
(1 row)
Ben Morrow 提到:
> Quoth dennis <dennis@teltel.com>:
>> here is example
>>
>> table name is "mail":
>> column | type
>> -------------------------
>> sender |char
>> subject |char
>
> I presume you mean 'varchar'?
>
>> content |bytea
>>
>>
>> I want copy some record into new table 'mail_new'.
>>
>> sql:
>> create table mail_new as select * from mail sender='dennis'
>
> You omitted the WHERE. It's very hard to see what's actually going on
> when you keep mis-typing the commands you used.
>
>> result has an error:
>> operator does not exist: text || bytea
>>
>>
>> But if my sql statement has no column "content"
>> the sql works.
>> sql:
>> create table mail_new as select sender,subject from mail sender='dennis'
>
> No, it still doesn't give that error for me. Show us something you've
> *actually* *tried*.
>
> Ben
>
>
Dear Ben
thanks for you anwser.
I try to add function quote_literal on my sql statement .
but it raise other error message (quote_literal not support bytea format): function quote_literal(bytea) does not
exist
Ben Morrow 提到:
> Quoth dennis <dennis@teltel.com>:
>> Hi Ben
>>
>> here is my function , it's for fix missing chunk problem.
>> It has same problem ,please take look
>>
>>
>> thank for you help
>>
>> -------------table----------------------
>>
>>
>> db=# \d usersessiontable;
>> Table "public.usersessiontable"
>> Column | Type | Modifiers
>> -----------+------------------------+-----------
>> serverid | character varying(100) |
>> sessionid | character varying(50) |
>> data | bytea |
>> Indexes:
>> "usersessiontable_idx" btree (sessionid)
>> db=#
>>
>> db=# \d usersessiontable_test;
>> Table "public.usersessiontable"
>> Column | Type | Modifiers
>> -----------+------------------------+-----------
>> serverid | character varying(100) |
>> sessionid | character varying(50) |
>> data | bytea |
>>
>> ------------------function--------------------------------
>>
>>
>> CREATE OR REPLACE FUNCTION check_missing_chunk_table_usersessiontable()
>> RETURNS integer AS
>> $BODY$
>> declare
>> begin
>> records = 0;
>> OPEN curs1 FOR EXECUTE 'SELECT * FROM usersessiontable ORDER BY
>> sessionid';
>> loop
>> FETCH curs1 INTO rowvar;
>> IF NOT FOUND THEN
>> EXIT;
>> END IF;
>> begin
>> a_sql = 'insert into
>> usersessiontable_test(sessionid,serverid,data)
>> values('''||rowvar.sessionid||''','''||rowvar.serverid||''',E'''||rowvar.data||''')';<<--my
>
> You are trying to concatenate ''',E''' (of type text) and rowvar.data
> (of type bytea). This is where the error is coming from. (This actually
> works in 8.4, so I presume you're using an earlier version?) In any
> case, this is not a safe way to interpolate into an SQL string: you need
> the quote_literal function.
>
> a_sql = 'insert into usersessiontable (sessionid, serverid, data) '
> || 'values (' || quote_literal(rowvar.sessionid) || ', '
> || quote_literal(rowvar.serverid) || ', '
> || quote_literal(rowvar.data) || ')';
>
> (Is there a function which will do %-interpolation the way RAISE does?
> It would be much clearer in cases like this.)
>
> Ben
>
>
Postgres : 8.1.4
Dennis
Ben Morrow wrote:
> Quoth dennis<dennis@teltel.com>:
>> Dear Ben
>>
>> thanks for you anwser.
>> I try to add function quote_literal on my sql statement .
>>
>> but it raise other error message (quote_literal not support bytea format):
>> function quote_literal(bytea) does not exist
>
> Which Postgres version are you using?
>
> Ben
>
>