Thread: select 2 random rows
I'd like to select 2 random rows from a table. At the moment I'm returning a couple of hundred rows into a perl hash and randomising it there, but it would be great if I could do it in the database call. Each row has an id number created using the SERIAL type. Thanks for any help. Rory -- Rory Campbell-Lange <rory@campbell-lange.net> <www.campbell-lange.net>
is there a limit to how long a *line* can be in a command? i understand that a command can continue across several lines, but is there a limit to the length of a line?
Rory, > I'd like to select 2 random rows from a table. > At the moment I'm returning a couple of hundred rows into a perl hash > and randomising it there, but it would be great if I could do it in the > database call. > > Each row has an id number created using the SERIAL type. This is actually quite easy: SELECT *, RANDOM() as random_sort FROM the_table ORDER BY random_sort LIMIT 2; -- -Josh Berkus
Stev, > is there a limit to how long a *line* can be in a command? i understand > that a command can continue across several lines, but is there a limit to > the length of a line? No, there is no limit. Well, maybe 32,565 characters, but not less than that. However, if you are talking about PSQL, your terminal application (such as xterm or cygwin) may have a limit to the length of a line. -- -Josh Berkus
>Stev, > >> is there a limit to how long a *line* can be in a command? i understand >> that a command can continue across several lines, but is there a limit to >> the length of a line? > >No, there is no limit. Well, maybe 32,565 characters, but not less than that. i am using a C interface into the database, based on the example code. occasionally, one of my INSERT's fails, with: INSERT command failed: PGRES_FATAL_ERROR. other inserts work correctly. re-entering the data doesnt seem to help, and a comparison with a successful INSERT doesnt yield anything obvious. is there any way to get a more granular answer for what it didnt like?
Stev, > i am using a C interface into the database, based on the example code. > occasionally, one of my INSERT's fails, with: INSERT command failed: > PGRES_FATAL_ERROR. > > other inserts work correctly. re-entering the data doesnt seem to help, and > a comparison with a successful INSERT doesnt yield anything obvious. is > there any way to get a more granular answer for what it didnt like? Yes. Watch the PostgreSQL Log file, assuming that you started Postgres with a log file. If you didn't, then re-start Postgres with the output funneled to a log. If necessary, up the debug level. See the online administrator's guide. -- -Josh Berkus
> >Stev, > >> i am using a C interface into the database, based on the example code. >> occasionally, one of my INSERT's fails, with: INSERT command failed: >> PGRES_FATAL_ERROR. >> >> other inserts work correctly. re-entering the data doesnt seem to help, and >> a comparison with a successful INSERT doesnt yield anything obvious. is >> there any way to get a more granular answer for what it didnt like? > >Yes. Watch the PostgreSQL Log file, assuming that you started Postgres with >a log file. If you didn't, then re-start Postgres with the output funneled >to a log. If necessary, up the debug level. thanx. i used psql to load the record one field (OK, 5 fields:) at a time, and figured it out. the problem records have words like "don't" in them, which is screwing with the delimiters on the command line i am passing in, so i need to change them to be "don\'t". but thanks, i will turn on the log, it should help in the future . . . . BTW, i appreciate the quick answers to postings here . . . .
stev knowles <stev@Precision.Guesswork.COM> writes:
> i am using a C interface into the database, based on the example code.
> occasionally, one of my INSERT's fails, with: INSERT command failed:
> PGRES_FATAL_ERROR.
You might want to change your code so it prints the PQresultErrorMessage,
and not (or at least not only) PQresultStatus.
regards, tom lane
On Fri, 28 Jun 2002 15:33:29 -0400, stev knowles <stev@Precision.Guesswork.COM> wrote: >the problem records have words like "don't" in them, >which is screwing with the delimiters on the command line i am passing in, >so i need to change them to be "don\'t". Stev, in case you have not found it yet, PQescapeString(char *to, const char *from, size_t length) does that for you. Servus Manfred
>in case you have not found it yet, > PQescapeString(char *to, const char *from, size_t length) >does that for you. so, this is in version 7.2 of postgresql. i have 7.1 installed. from the release notes, it appears that i need to save out,and restore, the databases i have active. that seems like alot for a minor release. is this a safety issue, or is there something else going on?
i have an application. occasionally, a user will select Submit without filling out any fields to search for. the lookup fails,and PQfinish(conn) is called. starting recently, when this happens, it seems that PQfinish() is not setting conn to NULL, since my init() routine returns without connecting to the database (upon entering, it only connects if (conn == (PGconn *)NULL) ). i looked thru the code, and free() is being called in freePGconn(PGconn *conn), but my fist call to the database just exists, and takes the application with it. this *used* to work. the application has not been used in a few weeks, we just added about 60 new records, and now there is interest. any ideas would be appreciated.