Re: [GENERAL] SELECT issue with references to different tables
On Jun 2, 2012, at 14:50, Alexander Reichstadt wrote: > Hi, > > I have a query I cannot figure out in postgres or actually in any other way > than using the client front end, which I would prefer not to do. > > So, I have 4 tables > > pets > persons > companies > pets_reference > > pets have owners, the owner at any point in time is either a persons or a > company, never both at the same time. > > So, the pets_reference table has the fields: > > refid_petsmatching table pets, field id > refid_personsmatching table persons, field id > refid_companiesmatching table companies, field id > ownersincewhich is a timestamp > > A pet owner can change to persons A, resulting in a record in pets_reference > connecting pet and person with a timestamp, setting refid_companies to zero > and refid_persons to person A's record's id value. If the owner changes to > some other person B, then another record is added to pets_reference. Or if > the owner for that pet changes to a company, then a new record is added with > refid_persons being zero and refid_companies being the id value of that > companies id field value. So at the end of the day pets_reference results in > a history of owners. > > Now, the problem is with displaying a table with pets and only their current > owners. I can't figure out two things. > For one it seems I would need to somehow build a query which uses an if-then > branch to check if companies is zero or persons is zero to ensure to either > reference a persons or a companies record. > The second issue is that I only need the max(ownersince) record, because I > only need the current owner and not past owners. > > I toyed around with DISTINCT max(ownersince) and GROUP BY, but it only > results in errors. I am not the SQL guru, I know my way around so far and am > learning, but this is kind of another league and I can't really show any good > results I've come up with so far. Please, can someone help? > > Thanks > Alex > > While you can solve the problem as structured have you considered an "entity" table that is a super-type of both person and company? The entity id would then be the foreign key. For you immediate problem you have to perform a UNION query. The first sub-query will output records where personid is not null and the second sub-query will output records where companyid is not null. If you are using 8.4 or above after the union you can use a window function (rank) on the ordered ownersince date and then in an outer query filter so that only rank=1 records are kept. David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SELECT issue with references to different tables
On Sun, Jun 3, 2012 at 4:50 AM, Alexander Reichstadt wrote: > So, I have 4 tables > > pets > persons > companies > pets_reference > > pets have owners, the owner at any point in time is either a persons or a > company, never both at the same time. > > A pet owner can change to persons A, resulting in a record in pets_reference > connecting pet and person with a timestamp, setting refid_companies to zero > and refid_persons to person A's record's id value. I'd use the SQL NULL value rather than zero here. You can then make use of foreign key constraints easily. > Now, the problem is with displaying a table with pets and only their current > owners. I can't figure out two things. > For one it seems I would need to somehow build a query which uses an if-then > branch to check if companies is zero or persons is zero to ensure to either > reference a persons or a companies record. > The second issue is that I only need the max(ownersince) record, because I > only need the current owner and not past owners. This sounds like a good job for an outer join. Something like this: SELECT * from pets_reference JOIN pets ON (refid_pets = pets.id) LEFT JOIN persons ON (refid_persons = persons.id) LEFT JOIN companies ON (refid_companies = companies.id) That will give you the pet record plus any associated person and/or company data. When refid_companies is NULL, all fields that come from the companies table will be NULL also (that's what the outer join does). The second issue is a little tricky to solve in standard SQL, and there are various techniques that can be used. Here's one involving Postgres's window functions: SELECT refid_pets,first_value(ownersince) over w,first_value(refid_persons) over w,first_value(refid_companies) over w FROM pets_reference WINDOW w AS (partition refid_pets order by ownersince desc) I'm sure there's an easier way to do this, but I'm not an expert with window functions. Hope that helps! Chris Angelico -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SELECT issue with references to different tables
Hi, I have a query I cannot figure out in postgres or actually in any other way than using the client front end, which I would prefer not to do. So, I have 4 tables pets persons companies pets_reference pets have owners, the owner at any point in time is either a persons or a company, never both at the same time. So, the pets_reference table has the fields: refid_pets matching table pets, field id refid_persons matching table persons, field id refid_companies matching table companies, field id ownersince which is a timestamp A pet owner can change to persons A, resulting in a record in pets_reference connecting pet and person with a timestamp, setting refid_companies to zero and refid_persons to person A's record's id value. If the owner changes to some other person B, then another record is added to pets_reference. Or if the owner for that pet changes to a company, then a new record is added with refid_persons being zero and refid_companies being the id value of that companies id field value. So at the end of the day pets_reference results in a history of owners. Now, the problem is with displaying a table with pets and only their current owners. I can't figure out two things. For one it seems I would need to somehow build a query which uses an if-then branch to check if companies is zero or persons is zero to ensure to either reference a persons or a companies record. The second issue is that I only need the max(ownersince) record, because I only need the current owner and not past owners. I toyed around with DISTINCT max(ownersince) and GROUP BY, but it only results in errors. I am not the SQL guru, I know my way around so far and am learning, but this is kind of another league and I can't really show any good results I've come up with so far. Please, can someone help? Thanks Alex -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [PERFORM] Array fundamentals
On Sat, 2012-06-02 at 10:05 -0700, idc danny wrote:
> Now, if I want do do the following:
> select CombineStrings(ARRAY[SplitString2Array("SomeTextColumn"), 'New
> string to add']) from "SomeTable"
>
> i get the following error:
> array value must start with "{" or dimension information
This discussion is better suited to another list, like -general, so I'm
moving it there.
In the fragment:
ARRAY[SplitString2Array("SomeTextColumn"), 'New string to add']
The first array element is itself an array of strings, but the second is
a plain string. Array elements must all be the same type.
What you want to do is replace that fragment with something more like:
array_append(SplitString2Array("SomeTextColumn"), 'New string to add')
If that still doesn't work, we'll need to see the exact definitions of
your functions.
Also, as a debugging strategy, I recommend that you look at the pieces
that do work, and slowly build up the fragments until it doesn't work.
That will allow you to see the inputs to each function, and it makes it
easier to see why it doesn't work.
Regards,
Jeff Davis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] select current_setting('transaction_isolation')
JDBC does not query the database for the transaction isolation level for
the current session/connection on its own unless you application [or even
it could be hibernate] queries the same by calling :
connection.getTransactionIsolation()
method.
I doubt even if Hibernate would do that since it does not need to query
each time what the transaction isolation level of the current connection is
and would most probably cache that info in each Session instance.
So, it would be better you verify if your application specific code is
relying on some particular transaction isolation level and is ensuring that
the every query run on the database is actually running under that
particular transaction isolation level.
Regards,
Samba
On Wed, May 30, 2012 at 5:35 AM, David Kerr wrote:
> Howdy,
>
> I recently did a log_min_duration_statement=0 run on my app, and found
> ~3million copies of
> "select current_setting('transaction_isolation')"
>
> I'm a Java + Hibernate stack. Does anyone know if this is a Hibernate
> artifact? or a jdbc artifact?
> or something else (implicit to some query pattern or trigger)?
>
> Thanks
>
> Dave
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
Re: [GENERAL] Updateable Views or Synonyms.
On 2012-05-29, Tim Uckun wrote: > I am wondering if either of these features are on the plate for > postgres anytime soon? I see conversations going back to 2007 on > updateable views and some conversations about synonyms but obviously > they have never been added to the database for some reason or another. updateable views can be implemented using rules > With regards to synonyms. It seems to me I could kind of achieve the > same functionality by creating a dblink into the same database. Would > that be an insane? what sorts of operations do synonyms need to support? truncate? alter table? reindex? -- ⚂⚃ 100% natural -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] timestamps, formatting, and internals
On 2012-05-30, David Salisbury wrote: > > > On 5/30/12 9:42 AM, Adrian Klaver wrote: >> Think I realize where the confusion is now. When Jasen mentioned integer >> datetimes he was referring to the internal storage format Postgres uses >> to record the datetime value. Via the magic of programming(others will >> have to fill that part in) the internal format can represent time down >> to microseconds even though the value is actually stored as an >> eight-byte integer. When you do an explicit cast of a timestamp value to >> integer you are asking that the value be only a whole number and the >> decimal portion is discarded. In other words the internal integer >> encodes the decimal values the external integer does not. > > Thanks! I was looking for some sort of verification along these lines. > So in my mind, the internal storage of a timestamp would be the number > of milliseconds since 1970 ( or similar ). But to me, if I cast something > that is an integer into an integer it would still be an integer ;) , and > still hold the milliseconds. It's internally stored as int8, but treated arithmetically as a number of millionths. "Fixed point" is the apropiate term I think. > Perhaps if I cast a datetime into a bigint it'll > still hold the number of ms? only if you multiply it by 100 > Some sort of parameter setting for dates > would be nice to be able to default a date/time format down to the ms, w/o > having to explicitly format it with every select... imho. sounds like a potential foot-gun to me. -- ⚂⚃ 100% natural -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Disable Streaming Replication without restarting either master or slave
On 2012-05-29, Fujii Masao wrote: > > You'd like to restart the *promoted* standby server as the standby again? > To do this, a fresh base backup must be taken from the master onto > the standby before restarting it, even if there has been no update since > the standby had been promoted. > I'd like to add that for this purpose "rsync" will likely outperform "tar" by a very large margin. -- ⚂⚃ 100% natural -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] timestamps, formatting, and internals
On 2012-05-29, David Salisbury wrote: > > > On 5/27/12 12:25 AM, Jasen Betts wrote: >> The query: "show integer_datetimes;" should return 'on' which means >> timestamps are microsecond precision if it returns 'off' your database >> was built with floating point timstamps and equality tests will be >> unreliable, > > I find that rather interesting. I was told that I was losing microseconds > when I extracted an epoch from the difference between two timestamps and > casted > that value to an integer. So if I have integer timestamps ( your case above ) > I get microseconds, but integer epochs is without microseconds? yeah, the microseconds appear as fractions of seconds, so in the conversion to integer epoch they get rounded off. > > Thanks, > > -ds > -- ⚂⚃ 100% natural -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
