[SQL] Inserting Multiple Random Rows
All, I have a table of user addresses that I'm trying to randomly populate with data from a cities table. Using the following query: INSERT INTO useraddrs(userid, addrdesc, city, stprov) SELECT u.userid, 'Home', c.cityname, c.stateabbr FROM users u, cities c WHERE u.userid NOT IN (SELECT userid FROM useraddrs) AND cid=(SELECT cid FROM cities ORDER BY RANDOM() LIMIT 1); I am able to achieve most of what I am trying to accomplish, but once the random number is selected, it doesn't change. What am I missing? Thank you in advance. -- Gary Chambers /* Nothing fancy and nothing Microsoft! */ -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Inserting Multiple Random Rows
Gary Chambers writes: > INSERT INTO useraddrs(userid, addrdesc, city, stprov) > SELECT u.userid, 'Home', c.cityname, c.stateabbr > FROM users u, cities c > WHERE u.userid NOT IN (SELECT userid FROM useraddrs) > AND cid=(SELECT cid FROM cities ORDER BY RANDOM() LIMIT 1); > I am able to achieve most of what I am trying to accomplish, but once > the random number is selected, it doesn't change. What am I missing? I believe the sub-SELECT will only get executed once, since it has no dependency on the outer query. What were you expecting to happen? regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Inserting Multiple Random Rows
That won't work because Order by does not recompute Random() once gets a number it stops you need to generate a series of random numbers then select each record one at a time out of cities table . You will have to write a plsql function to do this As any join will cause the result to be ordered. Here is example of pl-sql procedure that inserts records randomly into a table from a another table. This is an excerpt from the function. There is more logic that limits the random result set size and rules not to repeat a given number. --First create cursor of the source records OPEN _questions SCROLL for (Select *from questions where quest_expire > now()::date and trim( both '' from quest_type) = _classexams.exam_quest_type and trim( both '' from quest_level) = _classexams.exam_level order by quest_id ); --need to limit the number range created by random so not to exceed the record count created by the Cursor select count(quest_id) into _rcount from educate.questions where quest_expire > now()::date and trim( both '' from quest_type) = _classexams.exam_quest_type and trim( both '' from quest_level) = _classexams.exam_level ; Generate a Random list of of numbers for _randlist IN (Select num from ( select round(random()*1000)::int as num from generate_series(1,10)) rand where num <= _rcount and num > 0 ) LOOP FETCH ABSOLUTE _randlist.num from _questions into _quest ; Next Insert into Into the destination Table end loop; On 4/28/2010 12:22 PM, Gary Chambers wrote: > All, > > I have a table of user addresses that I'm trying to randomly populate > with data from a cities table. Using the following query: > > INSERT INTO useraddrs(userid, addrdesc, city, stprov) > SELECT u.userid, 'Home', c.cityname, c.stateabbr > FROM users u, cities c > WHERE u.userid NOT IN (SELECT userid FROM useraddrs) > AND cid=(SELECT cid FROM cities ORDER BY RANDOM() LIMIT 1); > > I am able to achieve most of what I am trying to accomplish, but once > the random number is selected, it doesn't change. What am I missing? > Thank you in advance. > > -- Gary Chambers > > /* Nothing fancy and nothing Microsoft! */ > > All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately. Thank you. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Inserting Multiple Random Rows
Tom, > I believe the sub-SELECT will only get executed once, since it has no > dependency on the outer query. That seems to be the behavior its exhibiting. > What were you expecting to happen? The clouds parting, choirs of angels singing, and fireworks celebrating the veil of my obtuseness being lifted, and my grasp and command of SQL to be complete and infinite. None of which appears will ever happen... -- Gary Chambers /* Nothing fancy and nothing Microsoft! */ -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Inserting Multiple Random Rows
On 4/28/2010 1:48 PM, Gary Chambers wrote: > pen? > > The clouds parting, choirs of angels singing, and fireworks > celebrating the veil of my obtuseness being lifted, and my grasp and > command of SQL to be complete and infinite. None of which appears > will ever happen... > > -- Gary Chambers > > /* Nothing fancy and nothing Microsoft! */ > > You now what *_might _*work Insert into useraddrs(userid, addrdesc, city, stprov) select u.userid, 'Home', c.cityname, c.stateabbr FROM users u, cities c WHERE u.userid NOT IN (SELECT userid FROM useraddrs) and cid IN (select round(random()*1)::int as num from generate_series(1,10)) rand ) As you don't care if a record repeats All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately. Thank you.
Re: [SQL] Inserting Multiple Random Rows
Justin, Thanks for the reply! > you need to generate a series of random numbers then select each record > one at a time out of cities table . You will have to write a plsql > function to do this As any join will cause the result to be ordered. After modifying my search terms at Google, I've discovered that I'm not alone in this endeavor. There are a couple of informative articles, the most beneficial of which is written by depesz at http://www.depesz.com/index.php/2007/09/16/my-thoughts-on-getting-random-row/ and the one that referred me to it at http://blog.rhodiumtoad.org.uk/2009/03/08/selecting-random-rows-from-a-table/ > Here is example of pl-sql procedure that inserts records randomly into a > table from a another table. I could have been done with this in pretty short order if I had just continued with writing a quick PL/pgSQL function to do it. It's all Quassnoi's fault that I abandoned my original plan and tried to solve the problem with a query. After all, I need to learn to think in sets and get out of the procedural mindset, but I still haven't heard the click! :) Thanks for the suggestion in your follow-up e-mail. I'll post the solution upon discovering it. -- Gary Chambers /* Nothing fancy and nothing Microsoft! */ -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Inserting Multiple Random Rows
Justin (et al), > You now what might work In the interest of efficiency, I abandoned the quest for the perfect query and wrote my own function and used a modified version of depesz's get_random_id() function: CREATE OR REPLACE FUNCTION gen_fake_addresses() RETURNS INTEGER AS $gen_fake_addresses$ DECLARE v_uid BIGINT; v_cid INTEGER; v_cst RECORD; v_count BIGINT := 0; BEGIN FOR v_uid IN SELECT userid FROM users WHERE userid NOT IN (SELECT userid FROM useraddrs) LOOP SELECT INTO v_cid get_random_city(); SELECT INTO v_cst cityname, stateabbr FROM cities WHERE cid = v_cid; INSERT INTO useraddrs(userid, addrdesc, city, stprov) VALUES (v_uid, 'Home', v_cst.cityname, v_cst.stateabbr); v_count := v_count + 1; END LOOP; RETURN v_count; END; $gen_fake_addresses$ LANGUAGE plpgsql VOLATILE; /* This is depesz's */ CREATE OR REPLACE FUNCTION get_random_city() RETURNS INT4 AS $get_random_city$ DECLARE id_range RECORD; reply INT4; try INT4 := 0; BEGIN SELECT min(cid), max(cid) - min(cid) + 1 AS range INTO id_range FROM cities; WHILE (try < 10) LOOP try := try + 1; reply := FLOOR(RANDOM() * id_range.range) + id_range.min; PERFORM cid FROM cities WHERE cid = reply; IF FOUND THEN RETURN reply; END IF; END LOOP; RAISE EXCEPTION 'No record found in % tries', try; END; $get_random_city$ LANGUAGE plpgsql STABLE; I'd like to thank Justin Graf for his time, effort, and assistance with this problem. -- Gary Chambers /* Nothing fancy and nothing Microsoft! */ -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SPAM]-D] [SQL] How to find broken UTF-8 characters ?
Hi,
while writing the reply below I found it sounds like beeing OT but it's
actually not.
I just need a way to check if a collumn contains values that CAN NOT be
converted from Utf8 to Latin1.
I tried:
Select convert_to (my_column::text, 'LATIN1') from my_table;
It raises an error that says translated:
ERROR: character 0xe28093 in encoding »UTF8« has no equivalent in »LATIN1«
I'd like to select all those records.
When I know which record has faulty content I can correct it.
If this is really OT on the SQL list then please tell me where to ask.
Am 28.04.2010 15:18, schrieb Justin Graf:
On 4/26/2010 8:41 AM, Andreas wrote:
How can I get rid of them?
iconv -c
AFAIK iconv would translate on file system level but I would think
that messed up a allready messed up Excel workmap even further.
I'd be glad to handle csv, too.
I would look at a macro/script to have excel dump the data out in CSV
then move data to into Postgres
It's like this.
I load the spreadsheet into an Access-DB and let a VBA skript stuff the
data into PG via ADODB/ODBC.
Often I have to clean up more obvious things than obscure characters or
amend the info out of other sources before I can upload it to PG.
Now these are not illegal UTF chars. If those values where wacky
Postgresql would not have allowed you insert the record.
Ô = utf code 212, Ç = utf code 199, Ä = utf code 196
Those are even in Latin1.
They were only 1 example. I suppose where I find them the 3 codes form a
multibyte code that can't be displayd or don't get displayd as a usual
letter but some symbol or asian-looking thing which definately doesn't
belong there.
I saw occasionally that such a wacky symbol replaced some other signes
that are language specific like ä, ö, ü. Then the next sign is missing
too, so something is mixing up the encoding and combines 2 chars into 1
utf8-code.
To force a string into a specific encoding we have the Covert,
Convert_From and Cover_to see section 9.5 in the help files
The problem is, that pgAdmin complains those signes aren't convertible
and drops the whole record out of the result of the select that I'd like
to dump into a csv.
Select covert('MyUtf8', 'UTF8', 'LATIN')
or
Select covert_to('MyUtf8', 'LATIN')
I found them before but didn't understand their output.
e.g.
Select convert('1aäßx', 'utf8', 'LATIN1') ;
Result = "1a\344\337x"
so it translated ä = 344 and ß = 337. The other 3 are just as they
were before.
How can this be valid in a single byte charset like Latin1?
Especially as ä, ß are E4 and DF.
Why do they come out as escaped codes when they are in Latin1 aswell as
1, a and x?
What ever pg client library used to move Excel data to PG my have
incorrectly converted some of the data or moved formatting information
into the database. I have seen Access and Excel do mightily odd
things when connecting to DB's I don't know about current versions
but 2000 and 2003 Excels did really stupid things when trying to write
to DB's including MSSQL.
Cute ... we use Access 2000 and 2003 :(
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] problem converting strings to timestamps with time zone
Hi,
I have a table with varchar and timestamp with time zone columns, among
others. Insertions into this table are usually done by a java program;
and there are many tens of thousands of records in the table.
Recently, after changes in the java software, many timestamps values
inadvertently ended up in one of the varchar columns. Rather than
writing some more java to rectify the situation, I was hoping it could
be done with one update statement.
However I'm having no luck constructing such a statement; I can't find a
way to include the timezones in the update. It seems like this would be
a rather common situation, but I've found no answers in the
documentation nor the archives.
If anyone knows how to do this, I would be most grateful.
Here is a sandbox example of what I mean.
CREATE TABLE test_0
(
string_value varchar(2047),
timestamp_value timestamp with time zone
);
insert into test_0
(string_value)
values
('2010-03-12 17:06:21-0800'),
('2009-08-14 16:47:40+0500'),
('2010-03-22 22:45:59-0400');
As expected, select * from test_0; , produces the following:
string_valuetimestamp_value
2010-03-12 17:06:21-0800
2009-08-14 16:47:40+0500
2010-03-22 22:45:59-0400
I would like to parse the strings into their equivalent timestamps
and put them in the timestamp_value column.
My attempt, so far, to update the table:
update value
set
timestamp_value =
to_timestamp(string_value, '-MM-DD HH24:MI:SS-');
^
|
|
The X's just mark where I would like to specify a time zone. But
apprently to_timestamp has no way of inputting time zones. Does anyone
know of another way?
Thanks,
Edward Ross
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] problem converting strings to timestamps with time zone
Edward Ross writes:
> Here is a sandbox example of what I mean.
> CREATE TABLE test_0
> (
> string_value varchar(2047),
> timestamp_value timestamp with time zone
> );
> insert into test_0
>(string_value)
> values
>('2010-03-12 17:06:21-0800'),
>('2009-08-14 16:47:40+0500'),
>('2010-03-22 22:45:59-0400');
> As expected, select * from test_0; , produces the following:
> string_value timestamp_value
> 2010-03-12 17:06:21-0800
> 2009-08-14 16:47:40+0500
> 2010-03-22 22:45:59-0400
> I would like to parse the strings into their equivalent timestamps
> and put them in the timestamp_value column.
> My attempt, so far, to update the table:
> update value
> set
> timestamp_value =
> to_timestamp(string_value, '-MM-DD HH24:MI:SS-');
Forget to_timestamp; just cast the string to timestamptz. The
regular timestamp input converter will handle that format fine.
regards, tom lane
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
