Re: [SQL] Convert numeric to money
inspect your cast functions with: create view showcasts as select t.typname as source, t1.typname as target, p.proname as function, (select case when c.castcontext = 'e' then 'Must use Explicit Cast' else ( select case when c.castcontext = 'i' then 'Implicit cast for expressions and assignments' else 'Implicit cast only for assignments' end) end ) as casttype from pg_cast c, pg_type t, pg_type t1, pg_proc p where c.castsource = t.oid and c.casttarget = t1.oid and c.castfunc = p.oid; Un saludo. Mauricio Fernandez A. Ingeniero de Sistemas Universidad Autonoma de Manizales [EMAIL PROTECTED] -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Bruno Wolff III Enviado el: viernes, 22 de julio de 2005 19:30 Para: [EMAIL PROTECTED] CC: [email protected] Asunto: Re: [SQL] Convert numeric to money On Fri, Jul 22, 2005 at 11:03:40 -0300, [EMAIL PROTECTED] wrote: > Hi. > I have searched in mailing-list archives about converting types, but I couldn't > found a function or clause that convert a numeric type to money type. > How Can I convert this types? > > => select '1234'::money; >money > > R$1.234,00 > > => select '1234'::numeric::money; > ERROR: cannot cast type numeric to money > > The problem is becouse I have a table with "numeric" field, and I need to show > it like "money" type (R$ 1.234,00). Is there a function to convert it??? Or is > there a function to mask the numeric field to show like money (with > R$x.xxx,xx)??? You probably want to use to_char to convert the numeric value to a string which can be displayed. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] int to date
Hi, I have a string containing hexa dump of an 4 bytes integer, like '6AF4805C'. Is it possible to convert it somehow to date type in plpgsql (v8.0.0-rc1)? Doc says that date is represented as 4 bytes internaly. How can I get internal representation of a date type field? So I would be able to produce hexa dump of it like above... Daniel ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] int to date
On Mon, Jul 25, 2005 at 10:54:42 +0200, Daniel Drotos <[EMAIL PROTECTED]> wrote: > Hi, > > I have a string containing hexa dump of an 4 bytes integer, like > '6AF4805C'. Is it possible to convert it somehow to date type in > plpgsql (v8.0.0-rc1)? Doc says that date is represented as 4 bytes > internaly. > > How can I get internal representation of a date type field? So I would > be able to produce hexa dump of it like above... area=> select x'6AF4805C'::integer::abstime; abstime 2026-11-11 08:49:00-06 (1 row) It looks like the your number probably isn't an offset in seconds from January 1 1970. But with more knowledge about what it is, you should be able to turn it into a date or timestamp. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] update vs unique index
Consider such table: CREATE TABLE test (idx integer); populated by following statements: INSERT INTO test VALUES (1); INSERT INTO test VALUES (2); INSERT INTO test VALUES (3); since idx schould be unique, we need an index CREATE UNIQUE INDEX i_test ON test(idx); Following SQL command fails: UPDATE test SET idx = idx + 1; I can imagine why it fails. Update operates on first row, making 2 out of 1 and that collides with second row (which has 2 as its value already). However, when you look at the update efect as a whole uniqueness is preserved, so index schould not veto update. My question is: is there a chance to bypass this behaviour? Something like controlling the order in which rows go into update. If update would start from last row, it would be successful for sure. regards, -- Jacek Prucia ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] update vs unique index
On Mon, Jul 25, 2005 at 05:58:43AM -0700, [EMAIL PROTECTED] wrote: > I can imagine why it fails. Update operates on first row, making 2 out > of 1 and that collides with second row (which has 2 as its value > already). However, when you look at the update efect as a whole > uniqueness is preserved, so index schould not veto update. > > My question is: is there a chance to bypass this behaviour? Something > like controlling the order in which rows go into update. If update > would start from last row, it would be successful for sure. Yeah, this is a known limitation. Usual workaround is issue two updates instead of one, update foo set a = -a where ; update foo set a = -a + 1 where ; The point is to move all unique keys to an unused interval and then move them back, changed all at a time. It'll eventually be fixed, but don't hold your breath. -- Alvaro Herrera () "No hay ausente sin culpa ni presente sin disculpa" (Prov. francés) ---(end of broadcast)--- TIP 1: 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
[SQL] Closes Match
Hi there, I want to match a string against the database and get the closest (shorter) match. I was asking myself if its possible to realise this in SQL. So if the string is: abcdefg And in the database are entries like: a ab abc I just want to get back 'abc'. I looked around in the internet and the documentation but found no solution. Cheers Christian smime.p7s Description: S/MIME Cryptographic Signature
Re: [SQL] Closes Match
On Mon, Jul 25, 2005 at 19:31:11 +0200, Christian Rusa <[EMAIL PROTECTED]> wrote: > Hi there, > > I want to match a string against the database and get the closest > (shorter) match. > I was asking myself if its possible to realise this in SQL. > > So if the string is: > abcdefg > > And in the database are entries like: > a > ab > abc > > I just want to get back 'abc'. > > I looked around in the internet and the documentation but found no solution. You could ORDER BY the length of the strings descending and use LIMIT to return only one row. You can write a regular expression to do the matching. Something like: ^(a(b(c(d(e(fg?)?)?)?)?)?$ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Closes Match
unsubscribe -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bruno Wolff III Sent: Monday, July 25, 2005 10:50 AM To: Christian Rusa Cc: [email protected] Subject: Re: [SQL] Closes Match On Mon, Jul 25, 2005 at 19:31:11 +0200, Christian Rusa <[EMAIL PROTECTED]> wrote: > Hi there, > > I want to match a string against the database and get the closest > (shorter) match. > I was asking myself if its possible to realise this in SQL. > > So if the string is: > abcdefg > > And in the database are entries like: > a > ab > abc > > I just want to get back 'abc'. > > I looked around in the internet and the documentation but found no solution. You could ORDER BY the length of the strings descending and use LIMIT to return only one row. You can write a regular expression to do the matching. Something like: ^(a(b(c(d(e(fg?)?)?)?)?)?$ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
