Re: [SQL] Join Advice and Assistance
To expand on Rob's reply:
If you want to return a single row for each user, regardless of the number
of email addresses, you might use ARRAY() with a subquery, eg (haven't
tested this to make sure it completely works):
SELECT u.*, um.*, ARRAY(SELECT emailaddr FROM user_emailaddrs em WHERE
em.userid = u.userid AND em.is_active) AS email_addresses
FROM users u INNER JOIN usermetas um ON u.userid = um.userid;
Of course, this will return the addresses as a character varying[], with
output like {u...@domain.tld,u...@domain.tld}, and would require some minor
contortions to present it to users cleanly. The array_to_string function may
help you make it easier to display the results.
http://www.postgresql.org/docs/8.4/static/functions-array.html#ARRAY-FUNCTIONS-TABLE
Hope this helps,
--Stephen Belcher
On Mon, Feb 22, 2010 at 12:05 PM, Rob Sargent wrote:
> My mistake. Should answer these things late at night.
>
> I think you will find that arrays will be your friend[s]
>
>
> On 02/22/2010 08:51 AM, Gary Chambers wrote:
>
>> Rob,
>>
>> Thanks for the reply...
>>
>> If you want records for user without email addresses you will need an
>>> outer
>>> join on user_emailaddrs
>>>
>>> /* untested */
>>> select u.userId, u.lname, u.lastname ,m.startdate, a.emailaddr
>>> from users u
>>> join usermetas m on u.userid = m.userid
>>> left join user_emailaddrs a on m.userid = a.userid
>>>
>>
>> My question was related more toward eliminating the query returning a
>> record for each record in the one-to-many table. I see now that I'm
>> going to have to aggregate the e-mail addresses in order to return a
>> single row. Thanks again.
>>
>> -- Gary Chambers
>>
>> /* Nothing fancy and nothing Microsoft! */
>>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
Re: [SQL] question about reg. expression
Another way to match multiple occurrences is to use curly brackets with a
number, like:
select 'ab' ~ '^[a-z]{2}$';
It can be done with a range of numbers as well:
select 'ab' ~ '^[a-z]{2,4}$';
select 'abab' ~ '^[a-z]{2,4}$';
I believe, however, that the curly brackets notation was introduced in 9.0
and is not available in earlier versions.
--Stephen
On Wed, Jan 19, 2011 at 5:21 AM, Samuel Gendler
wrote:
> I'd think you need to indicate multiple alphabetic matches. Your first
> regex actually matches only b followed by end of string and the second is
> really only matching start of string followed by a. The third is looking
> for a single character string.
>
> Try this: select 'ab' ~ '^[a-z]+$'
> or this: select 'ab' ~ '^[a-z]*$'
>
> or if looking only for 2 character strings: select 'ab' ~ '^[a-z][a-z]$'
>
>
> On Tue, Jan 18, 2011 at 3:41 PM, andrew1 wrote:
>
>> hi all,
>>
>> these return t:
>> select 'ab' ~ '[a-z]$'
>> select 'ab' ~ '^[a-z]'
>>
>> select 'ab' ~ '^[a-z]$' returns f
>>
>> Can't I use ^ and $ at the same time to match, in this case?
>> thanks.
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>>
>
>
Re: [SQL] Inherited table identification possible
Yes, the system column "tableoid" identifies the actual table in which the row is stored. If you cast this to "regclass" you'll get the name of the table that the row is stored in: SELECT tableoid::regclass FROM base_table; There's more documentation on this available at http://www.postgresql.org/docs/9.1/static/ddl-inherit.html (for version 9.1, at any rate: season to taste with your version of PG) Hope this helps, --Stephen On Thu, May 24, 2012 at 2:01 PM, George Woodring wrote: > Is it possible to identify which inherited table data came from in a > query? We have a table that has 3 inherited tables attached to it. I am > looking for a way to identify the source of the data. > > My only thought would be to add a column to the tables that identify the > table. I was just checking if there was a way to do it without the column. > > Thanks, > George > > > > -- > iGLASS Networks > www.iglass.net >
