Taking first record off recordset 1

[jisoo23]

Does anyone know how to do this? For the life of me I can't remember =/ I don't think I have to run a subquery of the recordset, I think there's a function that will allow me to take the first record as the result right?

Thanks,
Jisoo23

 

[ceco]

I don't understand what exactly do you mean try with example

P.S.
is it
SELECT * FROM table_name WHERE .... ORDER BY some_field LIMIT 1
the thing you are looking

 

[adonis547]

I'm running postgesql 7.4.1 on a FreeBSD system.

I've been so stupid as define an incorrect datatype for field (set varchar instead of int) - the table already has loads od data so I cannot just drop and re-create. Is there a way of changing the type ? Or perhaps create a new column, copy over the data and drop the old column - and then rename the newly created column ?

 

[ceco]

it is another question it was better to put it in another thread

anyway, the only way is

add new column,

update table_name set new_column = old_column;

drop the old column, and rename the new one to the name of the old
see here for details on the commands

http://www.postgresql.org/docs/7.4/interactive/sql-altertable.html

 

[jisoo23]

A-ha! LIMIT is what I was looking for. Used it once before but completely forgot it hehe. Thanks ceco.

 

[adonis547]

To ceco !

Argh.. Sorry for being a jackass - That's how it goes when you post a question while you're on the way out of the door

Thanks for the answer.

However, when running the update commmand, I get the following error:
ERROR: column "temp_qty" is of type integer but expression is of type character varying
HINT: You will need to rewrite or cast the expression.

How do I get around this ?

 

[ceco]

if the new column is of type int4 then do it like that

update table_name set new_column = old_column::int4;

 

[ceco]

if the new column is int4, do it like that

update table_name set new_column = old_column::int4;

 

[adonis547]

Thanks for your reply.

I actually solved it meanwhile. I could not type cast varchar to int - so I ended type casting varchar to text and text to int.

My final sql looks like this:

update my_table set new_column = column::text::int;


Sorry again for screwing up this thread (you see, nothing good can come from Bush's re-election .. )