[SQL] storing special characters

[BORGULYA Gergely]

Helo,

I'm using postgresql 7.1, with suse linux 7.1 on i386.
I'm programming in python and I'm going to store many, long (approx. 600 
bytes) python-variables in a postgres database. There is a way to convert 
python-variables into string (either binary or text) format to make them 
possible to be stored in files or databases. The string converted from the 
variable is full of characters that are treated specially by the 
query-language, so I always get error messages if I try them to store with 
the INSERT INTO command.
To save them to files, COPY them one-by-one to a temporary table then move 
them to the appropriate table and row, delete the file, drop the table seems 
to be a very long procedure.
Is there a simple and smart way to store such strings in databases? I could 
not find solution for such a problem in the documentation.

Gergely BORGULYA

 

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Re: [ADMIN] need urgent help

[Tom Lane]

Jie Liang <[EMAIL PROTECTED]> writes:
> when I reload my database from pg_dumpall:
> COPY "urlinfo" FROM stdin;
> ERROR:  copy: line 11419, MemoryContextAlloc: invalid request size
> 4294967271
> PQendcopy: resetting connection

Postgres version?  Table schema?  Input data?

regards, tom lane

---(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] Re: [ADMIN] need urgent help

[Tom Lane]

Jie Liang <[EMAIL PROTECTED]> writes:
> the original schema includes another constraint which using a user defined
> function to check whether url is validate or not.

So are you saying it was just a bug in this user-defined function?
Or is there something we need to investigate?

regards, tom lane

---(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] Re: [ADMIN] need urgent help

[Tom Lane]

Jie Liang <[EMAIL PROTECTED]> writes:
> I vacuumed urlinfo already.

VACUUM ANALYZE, or just VACUUM?

> urlinfo_pkey is primary key (id), urlinfo_ukey is unique key (url)
> when I:
> SELECT id FROM urlinfo WHERE url='http://*.yahoo.com';
> It seems taking me longer than before.

What does EXPLAIN say about it?

regards, tom lane

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] casts and conversions

[Ross J. Reedstrom]

On Sat, Jun 16, 2001 at 01:07:29AM -0400, Craig Longman wrote:

numeric casts>

Tom Lane has already addressed the 'why is it this way' question. I'll
address your proposed work arounds.

> 
> the only solution i can think of, if it is possible, is to have a script
> that the client would need to run to declare the missing parameter
> combinations for the standard operators.  this isn't very desireable,
> but it is the only real solution i can see so far. any other
> suggestions?

Yup, that's pretty much the only way to do this. Either run scripts as
part of the install, or (if there's a logical place for it in the workflow)
you could much around to see if the declarations are already there, and only
run the script if their missing.

> 
> why does this happen?  i have run into this before, and was able to
> perform the explicit cast (the code was postgresql only), but it seems
> like this is always going to be a problem, unless i'm missing something.
> are there plans for more thorough dynamic-casting logic, or does one

Check the HACKERS archives: there've been _long_ discussions about how to
do dynimic casting correctly, in the face of a user extensible type system.
Not trivial, at all.

> just need to go through all the combinations of datatypes and make sure
> that every combination is entered in the operator mapping tables?  i
> guess it would need to be entered both ways also ( int*float8,
> float8*int )?

I'd test with all the combinations of types you need, then only add
enough new mappings to get everything working.

Ross

---(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] casts and conversions

[Craig Longman]

On 17 Jun 2001 21:02:03 -0500, Ross J. Reedstrom wrote:
> On Sat, Jun 16, 2001 at 01:07:29AM -0400, Craig Longman wrote:
> 
> numeric casts>
> 
> Tom Lane has already addressed the 'why is it this way' question. I'll
> address your proposed work arounds.

he did.  i tried looking in pg-hackers for most of 2000, but was unable
to find the thread(s).  i'll keep looking though.

so, i presume then that this is a problem in the database because no-one
has sat down and made sure that all the possible combinations of
built-in datatypes are handled for all the various built-in
functions/operators?  or is there another reason for it?  perhaps this
is something where i can help then.

> > the only solution i can think of, if it is possible, is to have a script
> > that the client would need to run to declare the missing parameter
> > combinations for the standard operators.  this isn't very desireable,
> > but it is the only real solution i can see so far. any other
> > suggestions?
> 
> Yup, that's pretty much the only way to do this. Either run scripts as
> part of the install, or (if there's a logical place for it in the workflow)
> you could much around to see if the declarations are already there, and only
> run the script if their missing.
> 
> > just need to go through all the combinations of datatypes and make sure
> > that every combination is entered in the operator mapping tables?  i
> > guess it would need to be entered both ways also ( int*float8,
> > float8*int )?
> 
> I'd test with all the combinations of types you need, then only add
> enough new mappings to get everything working.

this statement makes we think that there is some other reason as to why
ALL the basic mappings weren't included.  does it just slow things down
too much?

finally, can you point me to where i can find out HOW to do this?  i
hope there isn't actual C code to write, but i suspect that there might
be.

as i said previously, if this is simply a case of setting up all the
mappings being a low-priority thing, then i would be happy (?!) to offer
some time to sort that out once and for all.

thanks!

-- 

CraigL->Thx();
Be Developer ID: 5852
Check out !



---(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] casts and conversions

[Tom Lane]

Craig Longman <[EMAIL PROTECTED]> writes:
> so, i presume then that this is a problem in the database because no-one
> has sat down and made sure that all the possible combinations of
> built-in datatypes are handled for all the various built-in
> functions/operators?

No, that's not it, and that would be quite the wrong way to go about it.
We have, hmm, six different built-in numeric datatypes (int2, int4,
int8, float4, float8, numeric; not to count quasi-numerics like OID,
XID, "char", etc).  Does it make sense to field thirty-six variants of
"plus", thirty-six of "minus", etc?  How many would you need to add to
support even one additional user-defined numeric type ("complex", say)?
Nope, it just doesn't scale.

What we need is some logic that decides on a common datatype to promote
the two inputs to and then apply a single-data-type operator.  The
mechanics are there to do this, what we haven't got is the rule that
allows a unique choice to be made when there are several possibilities.
For example, in your float8 * numeric case, the system hasn't got a way
to decide between using float8 multiply or numeric multiply, although it
can do either one if you coax it by supplying a cast.  Interestingly,
it can do all the other cases, such as int4 * float8, just fine --- the
problem is that both float8 and numeric are marked as "preferred types",
leaving the poor thing with no way to make a choice.  The real issue
here is that the "preferred type" heuristic doesn't encode enough
knowledge to deal with all the numeric datatypes.  We need a more
general approach.

You can find more about this in the pghackers archives, eg thread
"type conversion discussion" around 5/14/2000.

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])