Order by not working
Hello, I am using "PostgreSQL 9.6.5 on x86_64-apple-darwin, compiled by i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build 5658) (LLVM build 2336.11.00), 64-bit" I am having trouble with a create select statement’s order by clause. The input table, “household_complete_data", (1st 10 rows) looks like this (data hidden for privacy): household_name, first_name, street_address, city, state, zip, home_phone, home_email, cell, personal_email_primary, personal_email_secondary "Armstrong” "” "x” "” "” "" "Armstrong” "” "” "” "” "” "" "” "" "Bauer” "” "” "” "” “” “" "Bauer” "” "” "” "” "” "” "” "" "Berst” "” "” "” "” "” "" "Berst” "” "” "” "” "” "" "Berst” "” "” "” "” "” "" "Berst” "” "” "” "” "” "” "” "" "Berst” "” "” "” "” "” "" “” "" "Berst” "” "” "” "” "2” ” “" To this table I apply the following SQL statement: CREATE TABLE "household_data" AS SELECT household_name, string_agg(household_complete_data.first_name, ', ') AS family_list, street_address, city, state, zip, string_agg(COALESCE(household_complete_data.home_phone, '') || ',' || COALESCE(household_complete_data.cell, ''), ',') AS phone_list, string_agg(COALESCE(household_complete_data.home_email, '') || ',' || COALESCE(household_complete_data.personal_email_primary, '') || ',' || COALESCE(household_complete_data.personal_email_secondary, ''), ',') AS email_list FROM "household_complete_data" GROUP BY household_name, street_address, city, state, zip ORDER BY household_name; The result is (only the first column is shown): household_name "Garcia" "Armstrong" "Armstrong" "Bauer" "Bauer" "Berst" "Berst" "Minch ()" "Berst" “Besel" The ORDER BY clause doesn’t seem to work properly (note: “Minch ()” is an entry for the household name that has the first name in parentheses). All through the table there are random insertions of rows that are out of order with respect to the household_name. This has me stumped. Can anyone give me a hint of what might be going wrong? Regards, Dan Nessett
Re: Order by not working
Thanks Peter. The listing of the result is from pg-admin 4.30 using view/edit data applied to the household_data table. In the past this has always returned the table contents in the ORDR BY sort order. Do I need to specify some preference in pg_admin to guarantee this? Dan > On Feb 16, 2021, at 11:34 AM, Peter Coppens wrote: > > Not sure how you select the household > >> >> The result is (only the first column is shown): >> >> household_name >> >> "Garcia" >> "Armstrong" >> "Armstrong" >> "Bauer" >> "Bauer" >> "Berst" >> "Berst" >> "Minch ()" >> "Berst" >> “Besel” > > > but unless you select from the resulting table using again an order by, the > rows will be returned in an undetermined order. Such is the nature of the > relational model - there is no order you can rely on when selecting, unless > you specify it > > Hth, > > Peter >
Re: Order by not working
Thanks, Dan > On Feb 16, 2021, at 12:11 PM, Ron wrote: > > What would you tell pgadmin? "Order this particular query -- out of all the > billion queries I might write -- in this particular manner?" > > No, that's not how things work. Just add an ORDER BY when you query the > table. > > On 2/16/21 12:48 PM, Dan Nessett wrote: >> Thanks Peter. The listing of the result is from pg-admin 4.30 using >> view/edit data applied to the household_data table. In the past this has >> always returned the table contents in the ORDR BY sort order. Do I need to >> specify some preference in pg_admin to guarantee this? >> >> Dan >> >>> On Feb 16, 2021, at 11:34 AM, Peter Coppens >>> <mailto:peter.copp...@datylon.com> wrote: >>> >>> Not sure how you select the household >>> >>>> The result is (only the first column is shown): >>>> >>>> household_name >>>> >>>> "Garcia" >>>> "Armstrong" >>>> "Armstrong" >>>> "Bauer" >>>> "Bauer" >>>> "Berst" >>>> "Berst" >>>> "Minch ()" >>>> "Berst" >>>> “Besel” >>> >>> but unless you select from the resulting table using again an order by, the >>> rows will be returned in an undetermined order. Such is the nature of the >>> relational model - there is no order you can rely on when selecting, unless >>> you specify it >>> >>> Hth, >>> >>> Peter >>> >> >> > > -- > Angular momentum makes the world go 'round.
Re: Order by not working
Thanks to those who responded. I have solved my problem by noting the advice to use a select with order by. In particular, I need to export the data to a csv file anyway, so I use the following copy command: COPY (SELECT household_name, family_list, street_address, city, state, zip, phone_list, email_list FROM "household_data" ORDER BY household_name ) TO '/tmp/household_data.csv' WITH (FORMAT CSV, HEADER); This works. Regards, Dan > On Feb 16, 2021, at 12:35 PM, David G. Johnston > wrote: > > > On Tuesday, February 16, 2021, Dan Nessett <mailto:dness...@yahoo.com>> wrote: > Thanks Peter. The listing of the result is from pg-admin 4.30 using view/edit > data applied to the household_data table. In the past this has always > returned the table contents in the ORDR BY sort order. Do I need to specify > some preference in pg_admin to guarantee this? > > pgAdmin4 might be keying off of the presence of an index, which this table > doesn’t have. > > David J.
Getting unexpected results from regexp_replace
I freely admit this may be my problem. Writing regular expression patterns is more an art than a skill. However, I am getting an unexpected result from regex_replace(). I have a table that is partially defined as follows (names and email addresses hidden for privacy): user_name user_email “A" “B” “b(x)" “C” "ct(home)" “D" “E" "ae(home)” The second entry is an email address - b - followed by the name of an individual (x) in parentheses. The email address for C and E have the word “home” in parentheses appended to the email address. I want to delete the parenthetical expression including the parentheses for all email addresses. I also have a column (not shown) called email_list that contains a comma separated list of all email addresses associated with each name or NULL if there is no list. I create a table: CREATE TABLE "households_with_email" AS SELECT user_name, regexp_replace(user_email, E'\(.*\)', '') AS user_email, family_list, street_address, city, state, zip, phone_list, email_list FROM "household_data" WHERE email_list != ‘'; I expected the regex_replace to the parenthetical text with the null character. Instead, it replaces the whole string in user_email with the null string: user_name user_email "Rodriguez” "" "Armstrong" "" "Bauer" "" "Berst" "" "Berst” "" I realize there may be some characteristic such as greedy matching that is causing this result, but if so, I don’t see how. The pattern indicates first find the ‘(‘ character, then match all characters until a ‘)’ character arrives. Those characters, including the parentheses should then be replaced with the null string. Or am I misinterpreting the pattern? Dan
Re: Getting unexpected results from regexp_replace
Thanks. Doubling the backslashes did the trick. I tried to use the original expression without the E, but postgres threw an error and said to use the “E” version of the pattern. Dan > On Feb 21, 2021, at 8:50 AM, Tom Lane wrote: > > Dan Nessett writes: >> SELECT user_name, regexp_replace(user_email, E'\(.*\)', '') AS user_email, >> family_list, street_address, city, state, zip, phone_list, email_list >> FROM "household_data" >> WHERE email_list != ‘'; > > Because you used E'...', the backslashes are eaten by the string literal > parser. So the pattern seen by regexp_replace() is just '(.*)', in > which the parens are capturing parens not literal characters. Thus it > matches the whole string. > > Personally I'd leave off the E, but if you must use it then double the > backslashes. > > regards, tom lane > >
