[GENERAL] PSQL woes
What I do is just invoke upper(text(field))... that converts the varchar to text, which the upper function can then use. -jag -- There's so much beauty in the world. Sometimes I feel like I'm seeing it all at once, and it's too much; my heart fills up like a balloon that's about to burst. And then I remember to relax, and stop trying to hold on to it, and then it flows through me like rain, and I can't feel anything but gratitude for every single moment of my stupid little life. -Alan Ball
[GENERAL] insert into table from select..
Hi all, I'm having some trouble with the insert function and would appreciate anyones woords of wisdom... What I'm trying to do in a nutshell: 1. populate a 'working' table from a file (this is fine) 2. run sanity checks on the data (this is fine) 3. merge 'working' table into the 'main' table. (AARRGGG - not fine) eg. raw datafile: --- 1996-12-12 12:12:12|bill 1997-03-02 23:12:45|ted ..etc... --- sql statements: (table log has already been created with same parameters as 'logtmp') - create table logtmp (outdate timestamp,luser char(8)); copy logtmp from '/tmp/file' using delimiters '|'; \echo various syntax checking to make sure data is good \echo \echo Update main table with new data \set fieldnames 'outdate,luser' insert into log (:fieldnames) select :fieldnames from logtmp; - Everything works OK except for the last line... I know the variable 'fieldnames' works though on 'select :fieldnames from logtmp' When I actually use this in the production environment, the main table will have _one_ extra field as a serial sequence (this is why I used the fieldnames variable, otherwise I have about 20 fields), I thought this was my problem at first, but when I simplified the data right down, it still fails. Any thoughts?? Thanks in Advance. -- Dale Walker [EMAIL PROTECTED]
Re: [GENERAL] Re: Lock up on 7.0.2 involving CREATE FUNCTION/INDEX..
Yes... it is odd... especially since the following works fine:
SELECT UPPER(field) FROM test;
-philip
On Wed, 9 Aug 2000, Lamar Owen wrote:
> Philip Hallstrom wrote:
> > CREATE INDEX test_idx ON test (UPPER(field));
>
> > devloki=> create index foo on rolo_entry (UPPER(fname));
> > ERROR: DefineIndex: function 'upper(varchar)' does not exist
> > devloki=> create index foo on rolo_entry (UPPER(varchar(fname)));
> > ERROR: parser: parse error at or near "varchar"
> > devloki=> create index foo on rolo_entry (UPPER(text(fname)));
> > ERROR: parser: parse error at or near "("
> > devloki=> create index foo on rolo_entry (UPPER(text fname));
> > ERROR: parser: parse error at or near "fname"
> > devloki=> create index foo on rolo_entry (UPPER(fname::text));
> > ERROR: parser: parse error at or near "::"
> > devloki=> create index foo on rolo_entry (UPPER(CAST(fname AS TEXT)));
> > ERROR: parser: parse error at or near "cast"
>
> > So, by creating a function such as UPPER(varchar) instead of the built-in
> > UPPER(text), I can do what I want.
>
> > What's odd, is that I can create the function UPPER(varchar) which then
> > calls UPPER(text) and use it all I want. However, if I then try to create
> > an index (like my first example above) it locks up the entire machine.
>
> That is wild. I'd say bring this up in the hackers list -- as upper
> should also work with varchar by default.
>
> --
> Lamar Owen
> WGCR Internet Radio
> 1 Peter 4:11
>
Re: [GENERAL] Re: Lock up on 7.0.2 involving CREATE FUNCTION/INDEX..
Philip Hallstrom wrote:
> CREATE INDEX test_idx ON test (UPPER(field));
> devloki=> create index foo on rolo_entry (UPPER(fname));
> ERROR: DefineIndex: function 'upper(varchar)' does not exist
> devloki=> create index foo on rolo_entry (UPPER(varchar(fname)));
> ERROR: parser: parse error at or near "varchar"
> devloki=> create index foo on rolo_entry (UPPER(text(fname)));
> ERROR: parser: parse error at or near "("
> devloki=> create index foo on rolo_entry (UPPER(text fname));
> ERROR: parser: parse error at or near "fname"
> devloki=> create index foo on rolo_entry (UPPER(fname::text));
> ERROR: parser: parse error at or near "::"
> devloki=> create index foo on rolo_entry (UPPER(CAST(fname AS TEXT)));
> ERROR: parser: parse error at or near "cast"
> So, by creating a function such as UPPER(varchar) instead of the built-in
> UPPER(text), I can do what I want.
> What's odd, is that I can create the function UPPER(varchar) which then
> calls UPPER(text) and use it all I want. However, if I then try to create
> an index (like my first example above) it locks up the entire machine.
That is wild. I'd say bring this up in the hackers list -- as upper
should also work with varchar by default.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11
Re: [GENERAL] Re: Lock up on 7.0.2 involving CREATE FUNCTION/INDEX..
I know... using your example below, try the following:
CREATE INDEX test_idx ON test (UPPER(field));
On my system I get the following errors:
devloki=> create index foo on rolo_entry (UPPER(fname));
ERROR: DefineIndex: function 'upper(varchar)' does not exist
devloki=> create index foo on rolo_entry (UPPER(varchar(fname)));
ERROR: parser: parse error at or near "varchar"
devloki=> create index foo on rolo_entry (UPPER(text(fname)));
ERROR: parser: parse error at or near "("
devloki=> create index foo on rolo_entry (UPPER(text fname));
ERROR: parser: parse error at or near "fname"
devloki=> create index foo on rolo_entry (UPPER(fname::text));
ERROR: parser: parse error at or near "::"
devloki=> create index foo on rolo_entry (UPPER(CAST(fname AS TEXT)));
ERROR: parser: parse error at or near "cast"
So, by creating a function such as UPPER(varchar) instead of the built-in
UPPER(text), I can do what I want.
What's odd, is that I can create the function UPPER(varchar) which then
calls UPPER(text) and use it all I want. However, if I then try to create
an index (like my first example above) it locks up the entire machine.
I just realized this after someone mentioned there was probably a
recursive loop, but wouldn't that affect simple select statements as well?
Oh well... I've renamed my function with a prefix which I'll probably just
do all the time as it makes it easy to know what's mine and what's not.
-philip
On Wed, 9 Aug 2000, Mike Mascari wrote:
> Philip Hallstrom wrote:
> >
> > Is there another function that will uppercase? Or is there some way to
> > call the other UPPER function? Or something within plpgsql I don't know
> > about.
> > Thanks!
> > -philip
>
> I don't understand this. UPPER() is a built-in function:
>
> stocks=# create table test (field varchar(16));
> CREATE
> stocks=# insert into test values ('hello');
> INSERT 1788137 1
> stocks=# select upper(field) from test;
> upper
> ---
> HELLO
> (1 row)
>
> The oid for upper is 871. Do you not have this in your pg_proc?
>
> --
>
> Cheers,
>
> Mike Mascari
>
Re: [GENERAL] Re: Lock up on 7.0.2 involving CREATE FUNCTION/INDEX..
Mike Mascari <[EMAIL PROTECTED]> writes:
> Philip Hallstrom wrote:
> >
> > Is there another function that will uppercase? Or is there some way to
> > call the other UPPER function? Or something within plpgsql I don't know
> > about.
> > Thanks!
> > -philip
>
> I don't understand this. UPPER() is a built-in function:
>
> stocks=# create table test (field varchar(16));
> CREATE
> stocks=# insert into test values ('hello');
> INSERT 1788137 1
> stocks=# select upper(field) from test;
> upper
> ---
> HELLO
> (1 row)
>
> The oid for upper is 871. Do you not have this in your pg_proc?
I think his original question was if one could create an index based
on the upper() of a table attribute. He stated he tried it and
couldn't and it trying some alternatives.
--
Prasanth Kumar
[EMAIL PROTECTED]
Re: [GENERAL] Re: Lock up on 7.0.2 involving CREATE FUNCTION/INDEX..
Philip Hallstrom wrote:
>
> Is there another function that will uppercase? Or is there some way to
> call the other UPPER function? Or something within plpgsql I don't know
> about.
> Thanks!
> -philip
I don't understand this. UPPER() is a built-in function:
stocks=# create table test (field varchar(16));
CREATE
stocks=# insert into test values ('hello');
INSERT 1788137 1
stocks=# select upper(field) from test;
upper
---
HELLO
(1 row)
The oid for upper is 871. Do you not have this in your pg_proc?
--
Cheers,
Mike Mascari
Re: [GENERAL] Re: Lock up on 7.0.2 involving CREATE FUNCTION/INDEX..
Philip Hallstrom wrote: > Oh... Duh!!! Geesh... for some reason I figured it would call the > "built-in" UPPER, but obviously it won't. ha ha ha. *sigh* > My next question then is how to get around this? I could just rename my > function but it's nice to leave it UPPER since that is what it does. Is > there another function that will uppercase? Or is there some way to > call the other UPPER function? Or something within plpgsql I don't know Uh, maybe I'm missing something, but, just _why_ do you need a pl/pgsql function named UPPER that does nothing but call the built-in upper()? Is there a type mismatch problem I'm not seeing? Why do you need to do this? -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
[GENERAL] Re: Lock up on 7.0.2 involving CREATE FUNCTION/INDEX..
In article <[EMAIL PROTECTED]>, Prasanth A. Kumar <[EMAIL PROTECTED]> wrote: >Philip Hallstrom <[EMAIL PROTECTED]> writes: > >> Hi - >> The following statements lock up my machine completely (I can >> ping, but can't telnet, nothing). This is FreeBSD 3.4-STABLE running >> 7.0.2. >> >> rolo_entry.fname is of type VARCHAR(30). >> >> devloki=> CREATE FUNCTION upper(VARCHAR) RETURNS TEXT AS ' >> devloki'> BEGIN >> devloki'> RETURN UPPER($1); >> devloki'> END; >> devloki'> ' LANGUAGE 'plpgsql'; >> CREATE >> devloki=> CREATE INDEX foo_idx ON rolo_entry (upper(fname)); >> >> If I rename the function to say "am_upper" it works just fine. >> >> ??? > >I'm guessing that since sql is case insensitive, that results in >infinite recursion because you have a function upper() which calls >UPPER(). Oh... Duh!!! Geesh... for some reason I figured it would call the "built-in" UPPER, but obviously it won't. ha ha ha. *sigh* My next question then is how to get around this? I could just rename my function but it's nice to leave it UPPER since that is what it does. Is there another function that will uppercase? Or is there some way to call the other UPPER function? Or something within plpgsql I don't know about. Thanks! -philip
Re: [GENERAL] Lock up on 7.0.2 involving CREATE FUNCTION/INDEX..
Philip Hallstrom <[EMAIL PROTECTED]> writes: > Hi - > The following statements lock up my machine completely (I can > ping, but can't telnet, nothing). This is FreeBSD 3.4-STABLE running > 7.0.2. > > rolo_entry.fname is of type VARCHAR(30). > > devloki=> CREATE FUNCTION upper(VARCHAR) RETURNS TEXT AS ' > devloki'> BEGIN > devloki'> RETURN UPPER($1); > devloki'> END; > devloki'> ' LANGUAGE 'plpgsql'; > CREATE > devloki=> CREATE INDEX foo_idx ON rolo_entry (upper(fname)); > > If I rename the function to say "am_upper" it works just fine. > > ??? I'm guessing that since sql is case insensitive, that results in infinite recursion because you have a function upper() which calls UPPER(). -- Prasanth Kumar [EMAIL PROTECTED]
[GENERAL] initdb fail
Hi !!! "unknown type 'ame'" ? [postgres@sql pgsql]$ initdb --pgdata=`pwd` This database system will be initialized with username "postgres". This user will own all the data files and must also own the server process. Fixing permissions on pre-existing data directory /var/lib/pgsql Creating database system directory /var/lib/pgsql/base Creating database XLOG directory /var/lib/pgsql/pg_xlog Creating template database in /var/lib/pgsql/base/template1 000809.18:00:29.388 [15141] ERROR: Error: unknown type 'ame'. 000809.18:00:29.388 [15141] ERROR: Error: unknown type 'ame'. Creating global relations in /var/lib/pgsql/base 000809.18:00:29.461 [15145] ERROR: Error: unknown type 'ame'. 000809.18:00:29.461 [15145] ERROR: Error: unknown type 'ame'. Adding template1 database to pg_database /usr/bin/initdb: line 481: 15150 Falha de segmentação (core dumped) "$PGPATH"/postgres $BACKENDARGS template1 <"$TEMPFILE" initdb failed. Removing /var/lib/pgsql. rm: não foi possível remover diretório `/var/lib/pgsql': Permissão negada Failed. Removing temp file /tmp/initdb.15115. [postgres@sql pgsql]$ -- Paulo Henrique Rodrigues Pinheiro <[EMAIL PROTECTED]> Usuário Linux registrado com o número 173191 Faça seu computador feliz: LINUX nele !!! http://www.conectiva.com.br/~nulo
[GENERAL] Lock up on 7.0.2 involving CREATE FUNCTION/INDEX..
Hi - The following statements lock up my machine completely (I can ping, but can't telnet, nothing). This is FreeBSD 3.4-STABLE running 7.0.2. rolo_entry.fname is of type VARCHAR(30). devloki=> CREATE FUNCTION upper(VARCHAR) RETURNS TEXT AS ' devloki'> BEGIN devloki'> RETURN UPPER($1); devloki'> END; devloki'> ' LANGUAGE 'plpgsql'; CREATE devloki=> CREATE INDEX foo_idx ON rolo_entry (upper(fname)); If I rename the function to say "am_upper" it works just fine. ???
[GENERAL] How to create an index using a function???
Hi -
I have a table that has a varchar field (fname). I'd like to
create an index on UPPER(fname), but am running into problems...
What I don't understand is that I can do "SELECT UPPER(fname) FROM
mytable" and it works just fine. I also tried creating a SQL function
that did upper for me, but then the create index complains I can't use SQL
functions this way.
Hmm... I just tried creating a plpgsql function and now I can create the
index just fine...
Is this the only way to do it? How come there's no
UPPER(varchar) function?
Just curious...
Thanks!
-philip
devloki=> create index foo on rolo_entry (UPPER(fname));
ERROR: DefineIndex: function 'upper(varchar)' does not exist
devloki=> create index foo on rolo_entry (UPPER(varchar(fname)));
ERROR: parser: parse error at or near "varchar"
devloki=> create index foo on rolo_entry (UPPER(text(fname)));
ERROR: parser: parse error at or near "("
devloki=> create index foo on rolo_entry (UPPER(text fname));
ERROR: parser: parse error at or near "fname"
devloki=> create index foo on rolo_entry (UPPER(fname::text));
ERROR: parser: parse error at or near "::"
devloki=> create index foo on rolo_entry (UPPER(CAST(fname AS TEXT)));
ERROR: parser: parse error at or near "cast"
devloki=>
devloki=> create function varcharupper(varchar) returns text as '
devloki'> begin
devloki'> return upper($1);
devloki'> end;
devloki'> ' LANGUAGE 'plpgsql';
CREATE
devloki=> select varcharupper('test');
varcharupper
--
TEST
(1 row)
devloki=> create index foo on rolo_entry (varcharupper(fname));
CREATE
devloki=>
Re: [GENERAL] ORDERING alphabetically
On Wed, 9 Aug 2000, abe wrote: > Does anyone know how to get results from a query in alphabetical order > (for one field such as surname). I skimmed over the documentation and > no luck. Use the ORDER BY clause in your query: SELECT surname, firstname FROM names ORDER BY surname; Brett W. McCoy http://www.chapelperilous.net/~bmccoy/ --- But soft you, the fair Ophelia: Ope not thy ponderous and marble jaws, But get thee to a nunnery -- go! -- Mark "The Bard" Twain
Re: [GENERAL] ORDERING alphabetically
SELECT * FROM table ORDER BY table; that seems to do the trick for me when I need to order things alphabeticly. Mike - Original Message - From: "abe" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, August 09, 2000 10:15 AM Subject: [GENERAL] ORDERING alphabetically > Hi there, > > Does anyone know how to get results from a query in alphabetical order (for one > field such as surname). I skimmed over the documentation and no luck. > > The field type for surname is varchar - Maybe this is why order by didn't work. > > Thankyou, > Abe >
[GENERAL] ORDERING alphabetically
Hi there, Does anyone know how to get results from a query in alphabetical order (for one field such as surname). I skimmed over the documentation and no luck. The field type for surname is varchar - Maybe this is why order by didn't work. Thankyou, Abe
