Re: [SQL] Help : insert a bytea data into new table
Quoth dennis : > > 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 -- 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] Help : insert a bytea data into new table
Quoth dennis : > 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 -- 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] Help : insert a bytea data into new table
Quoth dennis : > 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 -- 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] Help : insert a bytea data into new table
Quoth dennis : > 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 -- 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] Help : insert a bytea data into new table
[quoting fixed] Quoth dennis : > Ben Morrow wrote: > > Quoth dennis: > >> 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 -- 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] Help : insert a bytea data into new table
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 : 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 -- 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] Help : insert a bytea data into new table
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 */ NOTICE: select * from usersessiontable order by sessionid limit 1 offset 1 check_missing_chunk_table_usersessiontable 1 (1 row) Ben Morrow 提到: Quoth dennis : 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 -- 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] Help : insert a bytea data into new table
Postgres : 8.1.4 Dennis Ben Morrow wrote: Quoth dennis: 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 -- 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] Help : insert a bytea data into new table
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 : 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql