Varchar and varchar2

HELP!

Alphabetical sorting... is it broken or is this intentional. If this is
not a "feature" anyone have any idea of how to actually sort
alphabetically on a text field?

cmj=> create table test (test text);
CREATE
cmj=> insert into test values ('a');
INSERT 240009 1
cmj=> insert into test values ('A');
INSERT 240010 1
cmj=> insert into test values ('ABLE');
INSERT 240011 1
cmj=> insert into test values ('Able');
INSERT 240012 1
cmj=> insert into test values ('AXIOM');
INSERT 240013 1
select * from test order by test;
test
-----
A
ABLE
AXIOM
Able
a
(5 rows)

Thanks!
Chris

Chris,

a and A are have different acsii codes

try:

select test from test order by lower(test)

This should give you what you want.

- Paul

I've looked through the documentation, but I can't find the size of the
limit of varchar...?

All I found was in the User's Guide:

Character Type Storage Description
---------------------------------------------------
varchar(n) (4+n) bytes variable length with limit
---------------------------------------------------

What does 4+n bytes mean? And what is the limit?

It means that the minimem length will be 5 bytes and the maximum is the
maximum amount of characters that can be contained in a record
(aproximately 8000 bytes), where 4 bytes are most likely used to contain
the actual number of characters. n, in the above expression most likely
represents the number of characters to be stored for a particular
instantition of this field.

Anyone? And is there support for varchar2?

What is varchar2?

...james

I don't know if the code given in the reply (THANK YOU PAUL!!!!) works for
6.3.2 (I'm downloading now to test it), but it failed in 6.3 with the
error 'ERROR: parser: parse error at or near "("' - but it put me on the
right track.

I wound up using the following:
select test, lower(test) as ltest from testtable order by ltest;

which *almost* gets it, but is close enough for me...

test |ltest
-----+-----
a |a
A |a
ABLE |able
Able |able
AXIOM|axiom

- notice that 'a' and 'A' are reversed... there was a 50/50 shot at it
being perfect, but that will happen in so few cases as to be irrelevant
(hopefully nobody will notice ;-)

Thanks all!

Chris

On Mon, 6 Jul 1998, Paul Mullen wrote:

At 5:20 PM 7/6/98, Chris Johnson wrote:

I don't know if the code given in the reply (THANK YOU PAUL!!!!) works for
6.3.2 (I'm downloading now to test it), but it failed in 6.3 with the
error 'ERROR: parser: parse error at or near "("' - but it put me on the
right track.

It seems unlikely that functions called on where clause elements would be
added in a minor release change from 6.3 to 6.3.2, but what do I know?

I wound up using the following:
select test, lower(test) as ltest from testtable order by ltest;

which *almost* gets it, but is close enough for me...

test |ltest
-----+-----
a |a
A |a
ABLE |able
Able |able
AXIOM|axiom

- notice that 'a' and 'A' are reversed... there was a 50/50 shot at it
being perfect, but that will happen in so few cases as to be irrelevant
(hopefully nobody will notice ;-)

Odds are really good that you've figured this out by now, but...

select test, lower(test) as ltest from testtable order by ltest;

Says sort by lowercase of test, so here's how the computer thinks:

Change 'A' into 'a'.
Change 'a' into 'a'. [Not much of a change, but that's a computer for ya]
'a' == 'a'.
Hmmm. Two rows, same thing, exactly equal, no difference in ordering.
I'll just spit 'em out in whichever order they are now.

Change 'Able' into 'able'.
Change 'ABLE' into 'able'.
'able' == 'able'
Hmmm. Two rows, same thing, exactly equal, no difference in ordering.
I'll just spit 'em out in whichever order they are now.

So you do, in fact, have a 50/50 chance of it coming up either way.
Actually, technically, you have an indeterminant chance, since SQL specs
allows the designers to do whatever the hell they please in this instance.
Most likely, they just won't do anything, and the stuff will spit out in
the order it happens to be in the database, which is probably mostly in the
order in which you entered it, unless something else got deleted right
before one of these guys got inserted, and one of these guys took that
empty spot. Ya follow that? Anyway, it will come out whichever way it
happens to end up from its starting point and the sort routine. However,
there is no guarantee that PostgreSQL will do tomorrow what it happens to
be doing today. For instance, in version 7, they could hire a demon from
the 7th level of hell to decide which to give you first, if they so
desired. :-)

Believe it or not, I'm not completely wasting your time. I just thought it
important that you understand why things were coming out the way they were
before I told you how to get what you want. Guess 'cuz I used to teach.
Anyway, you could do:

select test, lower(test) as ltest from testtable order by ltest, test;
to get the 'A' row first, or

select test, lower(test) as ltest from testtable order by ltest, test desc;
to get the 'a' row first.

Now you are clearly stating you want it to sort first by lower-case, and,
in case of a tie, sort by test.

[Is it descending or desc? Maybe DSC. Whatever. RTFM]

The point being that once you tell it to lowercase it, and then to sort by
that, you can hardly expect it to remember that it was uppercase before you
lowercased it unless you ask it to. It's just a stupid computer, after
all. :-)

Whew. Maybe this belonged on novice. Sorry folks, but I answered it where
it got posted. :-^

--
--
-- "TANSTAAFL" Rich lynch@lscorp.com

I am a psql beginner and am using the libpq++ library to write a series of
programs that access a database.

My database is working well, my problem is with:
PgCursor::Declare

PgCursor CourseData(data, "Student_course_record_portal");

I create this cursor and am able to successfully do:
CourseData.Declare(course_query.c_str())
CourseData.Fetch()
CourseData.GetValue(i, course_fnum)

It is when I want to repeat this process that I have problems.

I want to Declare a different string for the same PgCursor.
and then fetch from this new string's value (a select stmt)

If I just do one or the other things work correctly, but the repeating of
Declare and Fetch cause:
NOTICE: (transaction aborted): queries ignored until END
which then leads to the Fetch not working.....

I have also tried to just create a different PgCursor -
but this leads to an error
about not being in the default state.

Does anyone know of a fix - or am I making a stupid mistake?

Thank you for your time,
Summer

<<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>>
Summer Office: Ferris Engineering
CS151 Course Coordinator Phone: 277-9424
Student Computer Consultant e-mail: summerd@unm.edu
University of New Mexico summerd@cs.unm.edu
<<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>>