pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Help : insert a bytea data into new table
9+ messages / 3 participants
[nested] [flat]
* Re: Help : insert a bytea data into new table
@ 2010-03-09 03:46 Ben Morrow <ben@morrow.me.uk>
0 siblings, 2 replies; 9+ messages in thread
From: Ben Morrow @ 2010-03-09 03:46 UTC (permalink / raw)
To: dennis@teltel.com; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Help : insert a bytea data into new table
@ 2010-03-09 07:45 dennis <dennis@teltel.com>
parent: Ben Morrow <ben@morrow.me.uk>
1 sibling, 0 replies; 9+ messages in thread
From: dennis @ 2010-03-09 07:45 UTC (permalink / raw)
To: pgsql-sql
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
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Help : insert a bytea data into new table
@ 2010-03-09 11:14 Ben Morrow <ben@morrow.me.uk>
parent: Ben Morrow <ben@morrow.me.uk>
1 sibling, 2 replies; 9+ messages in thread
From: Ben Morrow @ 2010-03-09 11:14 UTC (permalink / raw)
To: dennis@teltel.com; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Help : insert a bytea data into new table
@ 2010-03-10 02:43 dennis <dennis@teltel.com>
parent: Ben Morrow <ben@morrow.me.uk>
1 sibling, 0 replies; 9+ messages in thread
From: dennis @ 2010-03-10 02:43 UTC (permalink / raw)
To: pgsql-sql
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
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Help : insert a bytea data into new table
@ 2010-03-10 15:25 Ben Morrow <ben@morrow.me.uk>
parent: Ben Morrow <ben@morrow.me.uk>
1 sibling, 2 replies; 9+ messages in thread
From: Ben Morrow @ 2010-03-10 15:25 UTC (permalink / raw)
To: dennis@teltel.com; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Help : insert a bytea data into new table
@ 2010-03-11 04:09 dennis <dennis@teltel.com>
parent: Ben Morrow <ben@morrow.me.uk>
1 sibling, 0 replies; 9+ messages in thread
From: dennis @ 2010-03-11 04:09 UTC (permalink / raw)
To: pgsql-sql
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
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Help : insert a bytea data into new table
@ 2010-03-11 11:46 Ben Morrow <ben@morrow.me.uk>
parent: Ben Morrow <ben@morrow.me.uk>
1 sibling, 2 replies; 9+ messages in thread
From: Ben Morrow @ 2010-03-11 11:46 UTC (permalink / raw)
To: dennis@teltel.com; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Help : insert a bytea data into new table
@ 2010-03-11 16:07 dennis <dennis.mails@gmail.com>
parent: Ben Morrow <ben@morrow.me.uk>
1 sibling, 0 replies; 9+ messages in thread
From: dennis @ 2010-03-11 16:07 UTC (permalink / raw)
To: pgsql-sql
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
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Help : insert a bytea data into new table
@ 2010-03-11 18:00 Ben Morrow <ben@morrow.me.uk>
parent: Ben Morrow <ben@morrow.me.uk>
1 sibling, 0 replies; 9+ messages in thread
From: Ben Morrow @ 2010-03-11 18:00 UTC (permalink / raw)
To: dennis.mails@gmail.com; pgsql-sql
[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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2010-03-11 18:00 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-03-09 03:46 Re: Help : insert a bytea data into new table Ben Morrow <ben@morrow.me.uk>
2010-03-09 07:45 ` dennis <dennis@teltel.com>
2010-03-09 11:14 ` Ben Morrow <ben@morrow.me.uk>
2010-03-10 02:43 ` dennis <dennis@teltel.com>
2010-03-10 15:25 ` Ben Morrow <ben@morrow.me.uk>
2010-03-11 04:09 ` dennis <dennis@teltel.com>
2010-03-11 11:46 ` Ben Morrow <ben@morrow.me.uk>
2010-03-11 16:07 ` dennis <dennis.mails@gmail.com>
2010-03-11 18:00 ` Ben Morrow <ben@morrow.me.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox