Change from 9.6 to 11?
I hope someone here can see something that eludes me. I've recently moved a database from PostgreSQL 9.6 to 11, and there are a few oddities. The following select statement returns zero rows when it should return one. This is one of a small number of records that exist, but are not returned by the query. When I include the main table, event, and any one of the associated tables, the record is returned, but no record is returned with the entire statement. All the primary keys (_pkey) and foreign keys (_fkey) are integers. The field I suspect as the possible culprit, event.InsBy, is a character column I'm converting to do a lookup on a primary key (integer): event.InsBy::int = usr.Usr_pkey. Maybe PG 11 doesn't recognize the same syntax for cast as PG 9.6? Or maybe I'm overlooking something else basic. Thanks for reading! SELECT event.Event_pkey,ombcase.CaseName,status.StatusID,eventtype.EventTypeID,event.datetime AT TIME ZONE 'US/Eastern',event.endtime AT TIME ZONE 'US/Eastern',event.EventDeadline,eventtype.EventIsDeadline,eventtype.OnCalendar,event.EventConfirmed,event.Description,event.PrivateNote,primaryresp.PrimaryrespID,event.IsPrivate,event.Hours,event.EventDone,event.EventDoneDate,event.Importance,event.Urgency,event.InCourt,event.LeadTime,doc.DocID,doctype.DocTypeID,project.ProjectName,usr.UserID,eventtype.Hours,event.Active,event.EditNum,event.InsBy,event.InsDateTime,event.ModBy,event.ModDateTime,event.Case_fkey,event.Eventtype_fkey,event.Project_fkey,event.Doc_fkey,event.Primaryresp_fkey,project.Project_pkey,primaryresp.PrimaryrespHolidayGroup,primaryresp.Usr_pkey,backup.Usr_pkey,doctype.Doctype_pkey,primaryresp.Primaryrespbackup_fkey,ombcase.Status_fkey,event.Parentevent_fkey,event.EventPostRemand FROM event,ombcase,status,eventtype,primaryresp,doc,doctype,project,usr,backup WHERE event.Case_fkey = Case_pkey AND event.Eventtype_fkey = Eventtype_pkey AND event.Project_fkey = Project_pkey AND event.Primaryresp_fkey = primaryresp.Usr_pkey AND event.Doc_fkey = Doc_pkey AND Doctype_fkey = Doctype_pkey AND usr.Backup_fkey = backup.Usr_pkey AND ombcase.Status_fkey = status.Status_pkey AND event.InsBy::int = usr.Usr_pkey AND event.Event_pkey = 1060071 ORDER BY EventDone, DateTime DESC Chuck Martin Avondale Software
Re: Change from 9.6 to 11?
On Thu, Dec 20, 2018 at 10:12 PM Adrian Klaver wrote: > On 12/20/18 5:51 PM, Chuck Martin wrote: > > Please reply to list also. > Ccing list. > > > > > > > On Thu, Dec 20, 2018 at 7:56 PM Adrian Klaver > <mailto:adrian.kla...@aklaver.com>> wrote: > > > > On 12/20/18 12:35 PM, Chuck Martin wrote: > > > I hope someone here can see something that eludes me. I've > recently > > > moved a database from PostgreSQL 9.6 to 11, and there are a few > > > oddities. The following select statement returns zero rows when it > > > should return one. This is one of a small number of records that > > exist, > > > but are not returned by the query. When I include the main table, > > event, > > > and any one of the associated tables, the record is returned, but > no > > > record is returned with the entire statement. All the primary keys > > > (_pkey) and foreign keys (_fkey) are integers. The field I > > suspect as > > > the possible culprit, event.InsBy, is a character column I'm > > converting > > > to do a lookup on a primary key (integer): event.InsBy::int = > > > usr.Usr_pkey. Maybe PG 11 doesn't recognize the same syntax for > > cast as > > > PG 9.6? Or maybe I'm overlooking something else basic. Thanks for > > reading! > > > > So if in the WHERE you leave out the: > > > > AND event.InsBy::int = usr.Usr_pkey > > > > and in the SELECT you add: > > > > event.InsBy, event.InsBy::int AS InsByInt > > > > what do you see? > > > > > > I get 91 copies of the record. One for each record in the usr table. > > But do the event.InsBy, event.InsBy::int AS InsByInt values match each > other? > > Just had a thought, what if you join just the event and usr tables on: > > event.InsBy::int = usr.Usr_pkey > > Trying to determine whether your suspected culprit really is the culprit. Thanks, Adrian. This led me to the problem. The data in InsBy was invalid. That is to say, a join wasn’t possible because no record exists with that primary key. Not sure how that occurred, but now I know why. Had I anticipated this might happen, I would have used an outer join. I appreciate your help solving this minor, but annoying, issue. > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > -- Chuck Martin Avondale Software
getting pg_basebackup to use remote destination
I thought I knew how to do this, but I apparently don't. I have to set up a new server as a standby for a PG 11.1 server. The main server has a lot more resources than the standby. What I want to do is run pg_basebackup on the main server with the output going to the data directory on the new server. But when I give this command: pg_basebackup -D "ssh root@10.0.1.16:/mnt/dbraid/data" -P -v -X s it instead writes to my root drive which doesn't have the space, so it fails and deletes the partial backup. While I think I could figure out how to backup to a local directory then rsync it to the new server, I'd like to avoid that due to the 750GB size. Is there a way to tell pg_basebackup to use a remote destination for output? Or do I have to run pg_basebackup on the standby server? And while I'm asking, has anyone yet written a guide/tutorial for PG 11 replication? Everything I find online is very old. Chuck Martin Avondale Software
Re: getting pg_basebackup to use remote destination
On Sun, Dec 30, 2018 at 11:20 AM Peter Eisentraut < peter.eisentr...@2ndquadrant.com> wrote: > On 29/12/2018 20:04, Chuck Martin wrote: > > I thought I knew how to do this, but I apparently don't. I have to set > > up a new server as a standby for a PG 11.1 server. The main server has a > > lot more resources than the standby. What I want to do is run > > pg_basebackup on the main server with the output going to the data > > directory on the new server. But when I give this command: > > > > pg_basebackup -D "ssh root@10.0.1.16:/mnt/dbraid/data" -P -v -X s > > > > > > it instead writes to my root drive which doesn't have the space, so it > > fails and deletes the partial backup. > > What you might be thinking of is the "old" method of doing base backups > before pg_basebackup: Call pg_start_backup() and then do file system > operations (tar, scp, whatever) to move the data files to where you want > them. This is mostly obsolete. You should run pg_basebackup on the > host where you want to set up your standby Thanks. It’s been a while since I set up replication. Not to mention several Postgres versions. I’ve started pg_basebackup from the standby. It failed once due to an ssh error, but I reloaded sshd and started again. May take a while. It about 750gb. > . > > -- > Peter Eisentraut http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services > > -- Chuck Martin Avondale Software
Re: getting pg_basebackup to use remote destination
Maybe I need to rethink ths and take Jeff's advice. I executed this: pg_basebackup -h [main server's URL] -U postgres -P -v -X s -D /mnt/dbraid/data 8 hours ago, and it is now still at 1%. Should it be that slow? The database in question is about 750 GB, and both servers are on the same GB ethernet network. Chuck Martin Avondale Software On Sun, Dec 30, 2018 at 3:28 PM Jeff Janes wrote: > On Sat, Dec 29, 2018 at 2:05 PM Chuck Martin > wrote: > >> I thought I knew how to do this, but I apparently don't. I have to set up >> a new server as a standby for a PG 11.1 server. The main server has a lot >> more resources than the standby. What I want to do is run pg_basebackup on >> the main server with the output going to the data directory on the new >> server. >> > > pg_basebackup consumes few resources on the standby anyway in the mode you > are running it, other than network and disk. And those are inevitable > given your end goal, so if you could do what you want, I think it still > wouldn't do what you want. > > If you really want to spare the network, you can run compression on the > server side then decompress on the standby. Currently you can't compress > on the server when invoking it on the standby, so: > > pg_basebackup -D - -Ft -X none |pxz | ssh 10.0.1.16 "tar -xJf - -C > /somewhere/data_test" > > Unfortunately you can't use this along with -X stream or -X fetch. > > Really I would probably compress to a file and then use scp/rsync, rather > the streaming into ssh. That way if ssh gets interrupted, you don't lose > all the work. > > Cheers, > > Jeff > >>
Re: getting pg_basebackup to use remote destination
On Mon, Dec 31, 2018 at 12:05 PM Jeff Janes wrote: > On Sun, Dec 30, 2018 at 6:17 PM Chuck Martin > wrote: > >> Maybe I need to rethink ths and take Jeff's advice. I executed this: >> >> pg_basebackup -h [main server's URL] -U postgres -P -v -X s -D >> /mnt/dbraid/data >> >> 8 hours ago, and it is now still at 1%. Should it be that slow? The >> database in question is about 750 GB, and both servers are on the same GB >> ethernet network. >> > > Over gigabit ethernet, it should not be that slow. Unless the network is > saturated with other traffic or something. Might be time to call in the > network engineers. Can you transfer static files at high speeds between > those two hosts using scp or rsync? (Or use some other technique to take > PostgreSQL out of the loop and see if your network is performing as it > should) > > Are you seeing transfers at a constant slow rate, or are their long > freezes or something? Maybe the initial checkpoint was extremely slow? > Unfortunately -P option (even with -v) doesn't make this easy to figure > out. So alas it's back to old school stopwatch and a pen and paper (or > spreadsheet). > > Cheers, > > Jeff > Using iperf, the transfer speed between the two servers (from the main to the standby) was 938 Mbits/sec. If I understand the units correctly, it is close to what it can be. Your earlier suggestion was to do the pg_basebackup locally and rsync it over. Maybe that would be faster. At this point, it is saying it is 6% through, over 24 hours after being started. Chuck Martin Avondale Software
Query help
Sorry if this is too basic a question for this list, but I don't fully get how to use aggregates (sum()) and group-by together. I'm trying to get a list of transactions where the total for a given account exceeds a given number. I'm not sure an example is needed, but if so, consider this simplified data: accountid. name 1 bill 2. james 3 sarah 4 carl transaction id. amount. accountid. name 1. 50. 1 bill 2. 25. 2 james 3 35 4 carl 4. 75. 1 bill 5 25. 1 bill 6 50 3 sarah results wanted-all transactions where account total >= 50 id. amount. accountid.name 1. 50. 1 bill 3. 75. 1 bill 4 25. 1 bill 5 50 3 sarah I've tried to understand how to use GROUP BY and HAVING, but the penny won't drop. I keep getting errors saying that all columns in the SELECT have to also be in the GROUP BY, but nothing I've done seems to produce the correct results. I think because the GROUP BY contains multiple columns, so each row is treated as a group. It also is difficult to parse out since in the real world, many more tables and columns are involved. Chuck Martin Avondale Software
Re: getting pg_basebackup to use remote destination
On Thu, Jan 3, 2019 at 3:46 PM Stephen Frost wrote: > Greetings Chuck, > > * Chuck Martin (clmar...@theombudsman.com) wrote: > > Using iperf, the transfer speed between the two servers (from the main to > > the standby) was 938 Mbits/sec. If I understand the units correctly, it > is > > close to what it can be. > > That does look like the rate it should be going at, but it should only > take about 2 hours to copy 750GB at that rate. That’s what I was expecting. > > How much WAL does this system generate though...? If you're generating > a very large amount then it's possible the WAL streaming is actually > clogging up the network and causing the rate of copy on the data files > to be quite slow. You'd have to be generating quite a bit of WAL > though. It shouldn’t be excessive, but I’ll look closely at that. > > > > Your earlier suggestion was to do the pg_basebackup locally and rsync it > > over. Maybe that would be faster. At this point, it is saying it is 6% > > through, over 24 hours after being started. > > For building out a replica, I'd tend to use my backups anyway instead of > using pg_basebackup. Provided you have good backups and reasonable WAL > retention, restoring a backup and then letting it replay WAL from the > archive until it can catch up with the primary works very well. If you > have a very high rate of WAL then you might consider taking a full > backup and then taking an incremental backup (which is much faster, and > reduces the amount of WAL required to be only that needed for the length > of time that the incremental backup is started until the replica has > caught up to WAL that the primary has). > > There's a few different backup tools out there which can do parallel > backup and in-transit compression, which loads up the primary's CPUs > with process doing compression but should reduce the overall time if the > bottleneck is the network. I’ll check out some solutions this weekend. I appreciate the tips. Chuck > > > Thanks! > > Stephen > -- Chuck Martin Avondale Software
Query help
I'm having trouble formulating a query. This is a simplified version of the
tables:
ombcase
case_pkey integer, primary key
casename varchar
insdatetime timestamp w/o time zone
status_fkey integer, foreign key
status
status_pkey integer, primary key
statusid varchar
statuschange
statuschange_pkey integer, primary key
insdatetime timestamp w/o time zone
ombcase_fkey integer, foreign key
oldstatus_fkey integer, foreign key
newstatus_fkey integer, foreign key
active integer, not nullable
The idea should be obvious, but to explain, insdatetime is set when a new
record is created in any table. All records in ombcase have a foreign key
to status that can't be null. When status changes, a record is created in
statuschange recording the old and new status keys, and the time (etc).
The goal is to find records in ombcase that have not had a status change in
xx days. If the status has not changed, there will be no statuschange
record.
This query returns the age of each ombcase and the last statuschange
record, but only if there is a statuschange record:
--Finds the age and last status change for open cases, but not age of cases
with no status change
SELECT casename, age(ombcase.insdatetime) AS caseage,
age(laststatuschange.created_at) AS statusage
FROM
(SELECT
case_fkey, MAX(insdatetime) AS created_at
FROM
statuschange
GROUP BY
case_fkey) AS laststatuschange
INNER JOIN
ombcase
ON
laststatuschange.case_fkey = case_pkey
RIGHT JOIN status
ON status_fkey = status_pkey
WHERE lower(statusid) NOT LIKE ('closed%')
AND case_pkey <> 0
I want to use coalesce
(age(ombcase.insdatetime),age(statuschange.insdatetime) ) to return the
time that a case has been in a status—or without a status change. But first
I have to find the cases with no statuschange record. I was able to do
that, too, using this query:
--find cases in status too long
SELECT casename, coalesce
(age(ombcase.insdatetime),age(statuschange.insdatetime) )
FROM ombcase
LEFT JOIN statuschange
ON case_fkey = case_pkey
LEFT JOIN status
ON status_fkey = status_pkey
AND lower(statusid) NOT LIKE ('closed%')
AND coalesce ( age(ombcase.insdatetime), age(statuschange.insdatetime) ) >
'2 months'
But this query will return all statuschange records for an ombcase record
that has multiple ones.
Any suggestions on how to combine the two ideas?
Chuck Martin
Avondale Software
--
Chuck Martin
Avondale Software
Re: Query help
Chuck Martin
Avondale Software
On Sun, Jan 27, 2019 at 2:55 PM Adrian Klaver
wrote:
> On 1/26/19 3:04 PM, Chuck Martin wrote:
> [snip]
> Outline form:
>
> 1) If a record is in ombcase it has a status('in a status') by definition.
>
> From query below you are not looking for just records in ombcase, but
> those that have a statusid other then 'closed%' in status table.
>
> 2) For the criteria in 1) you want to find the age of the last
> statuschange.
>
> To me that leads to something like:
>
> SELECT
> case_pkey
> FROM
> ombcase AS
> JOIN
> status
> ON
> ombcase.case_pkey = status.status_fkey
> LEFT JOIN
> statuschange
> ON -- Or statuschange.ombcase_fkey. Not clear from above.
> statuschange.case_fkey = ombcase.status_pkey
> GROUP BY
> ombcase.pkey
> HAVING
> status.LOWER(statusid) NOT LIKE ('closed%')
> AND
> max(coalesce(statuschange.insdatetime, ombcase.insdatetime))
> < 'some date'
>
> Obviously not tested.
>
Thanks, Adrian. This got me a lot closer, but I'm puzzled by the number of
records returned. There are 3120 ombcase records with a statusid that is <>
'closed%':
SELECT count(ombcase.case_pkey)
FROM ombcase,status
WHERE ombcase.status_fkey = status.status_pkey AND lower(status.statusid)
NOT LIKE ('closed%')
But 3378 are returned by:
SELECT ombcase.case_pkey, ombcase.casename,
COALESCE(AGE(statuschange.insdatetime), AGE(ombcase.insdatetime)) AS
age_in_status
FROM ombcase
INNER JOIN status
ON ombcase.status_fkey = status.status_pkey
LEFT JOIN statuschange
ON statuschange.case_fkey = ombcase.case_pkey
GROUP BY ombcase.case_pkey, status.statusid, statuschange.insdatetime,
ombcase.insdatetime
HAVING LOWER(status.statusid) NOT LIKE ('closed%')
AND ombcase.case_pkey <> 0
AND MAX(COALESCE(AGE(statuschange.insdatetime), AGE(ombcase.insdatetime)))
> '2 months'
ORDER BY age_in_status DESC
I don't know where the extra 258 records came from, and I think I need to
keep working on it until the query returns 3120 records.
Re: Query help
On Sun, Jan 27, 2019 at 8:07 AM Peter J. Holzer wrote:
> On 2019-01-26 18:04:23 -0500, Chuck Martin wrote:
> [snip]
> > The idea should be obvious, but to explain, insdatetime is set when a new
> > record is created in any table. All records in ombcase have a foreign
> key to
> > status that can't be null. When status changes, a record is created in
> > statuschange recording the old and new status keys, and the time (etc).
> >
> > The goal is to find records in ombcase that have not had a status change
> in xx
> > days. If the status has not changed, there will be no statuschange
> record.
>
> The easiest way is to use set operations:
>
> select case_pkey from ombcase;
> gives you all the ombcase ids.
>
> select ombcase_fkey from statuschange where insdatetime >= now()::date -
> xx;
> gives you all ombcase ids which had a status change in the last xx days.
>
> Therefore,
> select case_pkey from ombcase
> except
> select ombcase_fkey from statuschange where insdatetime >= now()::date -
> xx;
> gives you all ombcase ids which did /not/ have a status change in the
> last xx days.
>
I was not familiar with set operations, but studied up a bit and thought I
was getting there. Not quite, though. I have two queries that individually
return 1) all ombcase records with no statuschange record, and 2) the
newest statuschange record for each case that has a statuschange record.
But just putting UNION between then doesn't work. Here are my queries:
--First, find all open cases with no statuschange record
SELECT
case_pkey,statuschange_pkey,case_fkey,ombcase.insdatetime,statuschange.insdatetime
FROM
ombcase
LEFT JOIN
statuschange
ON
statuschange.case_fkey = case_pkey
AND case_pkey <> 0
LEFT JOIN
status
ON status_fkey = status_pkey
WHERE lower(statusid) NOT LIKE ('closed%')
AND statuschange.statuschange_pkey IS NULL
UNION
--Now find the last status change record for each case that has one
SELECT DISTINCT ON (case_fkey)
case_pkey,statuschange_pkey,case_fkey,ombcase.insdatetime,statuschange.insdatetime
FROM
statuschange,ombcase,status
WHERE case_fkey = case_pkey
AND status_fkey = status_pkey
AND LOWER(statusid) NOT LIKE ('closed%')
ORDER BY case_fkey, statuschange.insdatetime DESC
If I run each part separately, I get the expected number of records.
When I combine them with UNION, I get "missing FROM-clause entry for
table "statuschange"
So I'm very close here, and these two return the exact number of
records I'm expecting. So I just need to get them added together. Then
I expect I can put the whole thing in a WHERE clause with "AND
ombcase.case_pkey IN ([the combined results])"
>
> Another way would be to use a CTE
> (https://www.postgresql.org/docs/10/queries-with.html) to extract the
> last status change for each ombcase and then do a left join of ombcase
> to that CTE.
>
> hp
>
>
> --
>_ | Peter J. Holzer| we build much bigger, better disasters now
> |_|_) || because we have much more sophisticated
> | | | h...@hjp.at | management tools.
> __/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>
>
Re: Query help
On Sun, Jan 27, 2019 at 5:27 PM Chuck Martin
wrote:
> On Sun, Jan 27, 2019 at 8:07 AM Peter J. Holzer wrote:
>
>> On 2019-01-26 18:04:23 -0500, Chuck Martin wrote:
>> [snip]
>> > The idea should be obvious, but to explain, insdatetime is set when a
>> new
>> > record is created in any table. All records in ombcase have a foreign
>> key to
>> > status that can't be null. When status changes, a record is created in
>> > statuschange recording the old and new status keys, and the time (etc).
>> >
>> > The goal is to find records in ombcase that have not had a status
>> change in xx
>> > days. If the status has not changed, there will be no statuschange
>> record.
>>
>> The easiest way is to use set operations:
>>
>> select case_pkey from ombcase;
>> gives you all the ombcase ids.
>>
>> select ombcase_fkey from statuschange where insdatetime >= now()::date -
>> xx;
>> gives you all ombcase ids which had a status change in the last xx days.
>>
>> Therefore,
>> select case_pkey from ombcase
>> except
>> select ombcase_fkey from statuschange where insdatetime >= now()::date -
>> xx;
>> gives you all ombcase ids which did /not/ have a status change in the
>> last xx days.
>>
>
> I was not familiar with set operations, but studied up a bit and thought I
> was getting there. Not quite, though. I have two queries that individually
> return 1) all ombcase records with no statuschange record, and 2) the
> newest statuschange record for each case that has a statuschange record.
> But just putting UNION between then doesn't work. Here are my queries:
>
> --First, find all open cases with no statuschange record
> SELECT
>
> case_pkey,statuschange_pkey,case_fkey,ombcase.insdatetime,statuschange.insdatetime
> FROM
> ombcase
> LEFT JOIN
> statuschange
> ON
> statuschange.case_fkey = case_pkey
> AND case_pkey <> 0
> LEFT JOIN
> status
> ON status_fkey = status_pkey
> WHERE lower(statusid) NOT LIKE ('closed%')
> AND statuschange.statuschange_pkey IS NULL
> UNION
> --Now find the last status change record for each case that has one
> SELECT DISTINCT ON (case_fkey)
>
> case_pkey,statuschange_pkey,case_fkey,ombcase.insdatetime,statuschange.insdatetime
> FROM
> statuschange,ombcase,status
> WHERE case_fkey = case_pkey
> AND status_fkey = status_pkey
> AND LOWER(statusid) NOT LIKE ('closed%')
> ORDER BY case_fkey, statuschange.insdatetime DESC
>
> If I run each part separately, I get the expected number of records. When I
> combine them with UNION, I get "missing FROM-clause entry for table
> "statuschange"
> So I'm very close here, and these two return the exact number of records I'm
> expecting. So I just need to get them added together. Then I expect I can put
> the whole thing in a WHERE clause with "AND ombcase.case_pkey IN ([the
> combined results])"
>
> This was pretty easy to resolve. Putting parentheses around each half of
the query caused it to return the right results. Then I could reduce the
columns to just ombcase.case_pkey and use an IN statement. I think this
gets me where I need to be. I appreciate the help!
Chuck
HAVING query structured wrong
I am trying to create a query that returns all transactions for each person
who has a balance over a given amount. I thought HAVING was the answer, but
if so, I'm mis-using it. This query returns only transactions exceeding the
given amount rather than transactions for people whose balance is over the
amount:
SELECT
fullname,contactnameaddress.streetaddress,contactnameaddress.towncityname,
contactnameaddress.stateprovabbrev,contactnameaddress.postalcode,
transdate,linkednameid,transreference,
transamount,caseid,transcheckno,lastorcompanyname,firstname
FROM trans,ombcase,client,contactnameaddress,linkedname,status
WHERE transistrust <> 1
AND client_fkey = client_pkey
AND case_fkey = case_pkey
AND clientname_fkey = contactnameaddress.contactname_pkey
AND linkedname_fkey = linkedname.contactname_pkey
AND status_fkey = status_pkey
AND lower(statusid) NOT LIKE ('closed%')
AND transcleared <> 1
GROUP BY case_pkey,contactnameaddress.streetaddress,
contactnameaddress.towncityname,
contactnameaddress.stateprovabbrev,
contactnameaddress.postalcode,
transdate,transreference,transamount,
fullname,linkednameid,
contactnameaddress.lastorcompanyname,
contactnameaddress.firstname,caseid,
transcheckno
HAVING sum(transamount)>= 50
Since that returned the wrong set of records, I created another that
returns the correct set of people with balances over the given amount. But
I can't figure out how to use this to get all the transactions for people
returned by this query:
SELECT case_pkey
FROM trans,ombcase,status
WHERE case_fkey = case_pkey
AND status_fkey = status_pkey
AND statusopen = 1
AND transistrust <> 1
AND transcleared <> 1
GROUP BY case_pkey
HAVING sum(transamount) >= 50
ORDER BY case_pkey
So how do I get all transactions for each case_pkey? I've read the
documentation on WITH clauses (CTEs), but that just left my head spinning.
Chuck Martin
Avondale Software
Re: HAVING query structured wrong
On Mon, Feb 18, 2019 at 12:37 PM Andrew Gierth wrote: > >>>>> "Chuck" == Chuck Martin writes: > > Chuck> I am trying to create a query that returns all transactions for > Chuck> each person who has a balance over a given amount. I thought > Chuck> HAVING was the answer, but if so, I'm mis-using it. This query > Chuck> returns only transactions exceeding the given amount rather than > Chuck> transactions for people whose balance is over the amount: > [snip] > Chuck> Since that returned the wrong set of records, I created another > Chuck> that returns the correct set of people with balances over the > Chuck> given amount. But I can't figure out how to use this to get all > Chuck> the transactions for people returned by this query: > > Chuck> SELECT case_pkey > Chuck> FROM trans,ombcase,status > Chuck> WHERE case_fkey = case_pkey > Chuck> AND status_fkey = status_pkey > Chuck> AND statusopen = 1 > Chuck> AND transistrust <> 1 > Chuck> AND transcleared <> 1 > Chuck> GROUP BY case_pkey > Chuck> HAVING sum(transamount) >= 50 > > Chuck> ORDER BY case_pkey > > Chuck> So how do I get all transactions for each case_pkey? > > You can join the result of any subquery as if it were a table, either > with or without using a CTE: > > SELECT ... > FROM (select case_pkey from ... having ...) AS cases, >trans > WHERE trans.case_fkey = cases.case_pkey; > > (incidentally, please qualify all the column references in your query > with a table name or alias, otherwise people reading your code have no > idea which column is supposed to be in which table) Sorry. That was sloppy. But working with this idea, I got the query working, so I appreciate the pointer. For anyone wanting to see how I did so, here is the working query (replacing the variable for the minimum balance to include with "50"): -- Find transactions for client invoices using subquery to find client total SELECT contactnameaddress.fullname,contactnameaddress.streetaddress,contactnameaddress.towncityname, contactnameaddress.stateprovabbrev,contactnameaddress.postalcode, trans.transdate,linkedname.linkednameid,trans.transreference, trans.transamount,ombcase.caseid,trans.transcheckno,contactnameaddress.lastorcompanyname,contactnameaddress.firstname FROM trans,ombcase,client,contactnameaddress,linkedname,status , (SELECT case_pkey FROM ombcase,trans , status WHERE trans.case_fkey = ombcase.case_pkey AND ombcase.status_fkey = status_pkey AND status.statusopen = 1 AND trans.transistrust <> 1 AND trans.transcleared <> 1 GROUP BY ombcase.case_pkey HAVING sum(trans.transamount) >= 50) AS cases WHERE trans.case_fkey = cases.case_pkey AND trans.transistrust <> 1 AND ombcase.client_fkey = client.client_pkey AND client.clientname_fkey = contactnameaddress.contactname_pkey AND trans.linkedname_fkey = linkedname.contactname_pkey AND ombcase.status_fkey = status.status_pkey AND status.statusopen = 1 AND trans.transcleared <> 1 AND trans.Case_fkey = ombcase.case_pkey GROUP BY ombcase.case_pkey,contactnameaddress.streetaddress, contactnameaddress.towncityname, contactnameaddress.stateprovabbrev, contactnameaddress.postalcode, trans.transdate,trans.transreference,trans.transamount, contactnameaddress.fullname,linkedname.linkednameid, contactnameaddress.lastorcompanyname, contactnameaddress.firstname,ombcase.caseid, trans.transcheckno I'll try to learn the other methods using your examples. Again, I appreciate the help. or with a CTE, > > WITH cases AS (select ... from ... having ...) > SELECT ... > FROM cases, trans > WHERE trans.case_fkey = cases.case_pkey; > > There's also a third method with window functions instead of GROUP BY, > which is to do something like > > SELECT ... > FROM (select ..., >sum(transamount) over (partition by case_pkey) as total_amt > from ...) s > WHERE total_amt > 50; > > -- > Andrew (irc:RhodiumToad) > >
Re: Where to store Blobs?
I store them as bytea in the database despite the fact that there are benefits to storing them in the file system. The reason is that it is easier to secure access to the database than to secure both the database and provide secure access to the file system. Chuck Martin Avondale Software On Wed, Mar 13, 2019 at 10:34 AM Laurenz Albe wrote: > Thomas Güttler wrote: > > Now I realized: Nobody talked about Blobs. > > > > I guess most people do not store Blobs in PostgresSQL. > > > > Where do you store Blobs? > > > > (In my case Blobs are PDF/image files with size up to 20 MByte. > > I do not talk about very big blobs which are several hundret MByte) > > Ideally outside the database, if they are many. > Large databases are harder to backup than large file systems. > > If you keep 20MB binaries in the database, you'd use the "bytea" data type. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com > > >
Query not producing expected result
I need help figuring out why a query is not returning the records I expect it to. I'm searching on a DateTime column (timestamp without time zone - not nullable). The query includes: AND event.Primaryresp_fkey = 511 AND event.DateTime <= 'May-1-2019' AND event.EventDone < 1 This does not return a record that has a DateTime value "May 1, 2019 9:52 AM". If I modify the query to: AND event.Primaryresp_fkey = 511 AND event.DateTime <= 'May-2-2019' AND event.EventDone < 1 it does return the record in question. I assume I am not doing the data comparison correctly, but don't know why. Chuck Martin Avondale Software
Re: Query not producing expected result
Thanks, guys. It should have been obvious to me, but wasn't. I found the correct result was returned with either AND event.DateTime <= 'May-1-2019 24:00' or AND event.DateTime::date <= 'May-1-2019' The latter seems best. Chuck Martin Avondale Software On Wed, May 1, 2019 at 12:41 PM Tom Lane wrote: > Chuck Martin writes: > > I need help figuring out why a query is not returning the records I > expect > > it to. I'm searching on a DateTime column (timestamp without time zone - > > not nullable). The query includes: > > > AND event.Primaryresp_fkey = 511 AND event.DateTime <= 'May-1-2019' > > AND event.EventDone < 1 > > > This does not return a record that has a DateTime value "May 1, 2019 9:52 > > AM". > > Well, no, since the implied value of the constant is 'May-1-2019 00:00'. > > If you only want 1-day precision of the comparison, maybe you should cast > or truncate the timestamp down to date. > > regards, tom lane > > >
Re: Query not producing expected result
Thanks for the extra information. It is indeed an indexed column. I'll have to think some more about how to address this in a general way, as this issue can come up all over. I suppose using AND datetime <= 'May 1, 2019 24:00' would produce the same as AND datetime < 'May 2, 2019' wouldn't it? I'm not sure one is easier to implement than the other. Chuck Martin Avondale Software On Wed, May 1, 2019 at 1:16 PM Francisco Olarte wrote: > Chuck: > > On Wed, May 1, 2019 at 6:56 PM Chuck Martin > wrote: > > Thanks, guys. It should have been obvious to me, but wasn't. > > I found the correct result was returned with either > > AND event.DateTime <= 'May-1-2019 24:00' > > or > > AND event.DateTime::date <= 'May-1-2019' > > The latter seems best. > > The latter may prevent index usage, if you've got one. > > One think I've said before. Dates are integer-like ( they are > countable ), but timestamps are real-like ( they may be countable due > to finite precision, like float or doubles are, but you should not > count on it ). For real-like stuff it is normally better to work with > half-open ranges, which in your case would translate to to query for > >event.DateTime < 'May-2-2019' ( the minus-ininity <= DateTime > would for the other end ). > > The reason is you can cover the whole DateTime domain with > non-intersecting half-open ranges, but not with open or closed ones > and, as a side effect, the starting point of a range is the same as > the next one ( also, this does not need cast, better for the optimizer > ) ( If your input is an end date I normally pass this to timestamp > using ts < '$inclusive_end_date'::date + 1 ) ( I do lots of reports of > this kind, and using this helps a lot once you get the hang of it > after a couple tests ). > > ( I use half-open for dates to, for uniformity, and for being able to > use FEBRUARY= dateColumn >= "YEAR-02-01' and dateColumn < > "YEAR-03-01", no need to worry about leap years or remembering how > many days each month has. Generally they are easier, the only con I've > found is inability to use between ). > > Francisco Olarte. > > >
Re: Query not producing expected result
Something like daterange would be a solution in some circumstances, but
this query is a user-generated one, and they don't have that much control
over the query. It has to be modified as needed behind the scenes so that
it produces the results they expect. In this instance, I'm now (given the
advice received here) inclined to check the value entered when searching
for a date, and if no time is entered, add '24:00' to the date.
Chuck Martin
Avondale Software
On Wed, May 1, 2019 at 1:32 PM Adrian Klaver
wrote:
> On 5/1/19 10:15 AM, Francisco Olarte wrote:
> > Chuck:
> >
> > On Wed, May 1, 2019 at 6:56 PM Chuck Martin
> wrote:
> >> Thanks, guys. It should have been obvious to me, but wasn't.
> >> I found the correct result was returned with either
> >> AND event.DateTime <= 'May-1-2019 24:00'
> >> or
> >> AND event.DateTime::date <= 'May-1-2019'
> >> The latter seems best.
> >
> > The latter may prevent index usage, if you've got one.
> >
> > One think I've said before. Dates are integer-like ( they are
> > countable ), but timestamps are real-like ( they may be countable due
> > to finite precision, like float or doubles are, but you should not
> > count on it ). For real-like stuff it is normally better to work with
> > half-open ranges, which in your case would translate to to query for
> >
> > event.DateTime < 'May-2-2019' ( the minus-ininity <= DateTime
> > would for the other end ).
> >
> > The reason is you can cover the whole DateTime domain with
> > non-intersecting half-open ranges, but not with open or closed ones
> > and, as a side effect, the starting point of a range is the same as
> > the next one ( also, this does not need cast, better for the optimizer
> > ) ( If your input is an end date I normally pass this to timestamp
> > using ts < '$inclusive_end_date'::date + 1 ) ( I do lots of reports of
> > this kind, and using this helps a lot once you get the hang of it
> > after a couple tests ).
> >
> > ( I use half-open for dates to, for uniformity, and for being able to
> > use FEBRUARY= dateColumn >= "YEAR-02-01' and dateColumn <
> > "YEAR-03-01", no need to worry about leap years or remembering how
> > many days each month has. Generally they are easier, the only con I've
> > found is inability to use between ).
>
> Would daterange help/simplify?:
>
> create table dt_test(id integer, dt_fld date);
>
> insert into dt_test values (1, '2019-02-03'), (2, '2019-02-26'), (3,
> '2019-03-01');
>
> select dt_fld from dt_test where dt_fld <@ daterange('2019-02-01',
> '2019-03-01');
> dt_fld
>
> 2019-02-03
> 2019-02-26
>
> >
> > Francisco Olarte.
> >
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
>
Re: Query not producing expected result
Ok, I see that my assumptions were incorrect. In this instance, the use of < date+1 will return what is expected, where my solution might not have. For other circumstances, I want to explore tsrange. And, no, I'm not in Avondale, CA, but Decatur, GA (a few miles from Avondale Estates, where I once lived). Chuck Martin Avondale Software On Wed, May 1, 2019 at 1:52 PM Francisco Olarte wrote: > On Wed, May 1, 2019 at 7:37 PM Chuck Martin > wrote: > > > > Something like daterange would be a solution in some circumstances, but > this query is a user-generated one, and they don't have that much control > over the query. It has to be modified as needed behind the scenes so that > it produces the results they expect. In this instance, I'm now (given the > advice received here) inclined to check the value entered when searching > for a date, and if no time is entered, add '24:00' to the date. > > What I normally do for that is, if the user must enter a date, use > $user_input::date+1 and always go to less than. > > But anyway your solution with <= is incorrect. And you have made the > mistake probably because the 24:00 lead you to think postgres will > split the timestamp, compare > the date with may 1 and the time with 24:00, and that is not true. The > less-than option plus one-day add will not lead you to that error. > > You can use <= with 23:59:59.999, will be good for > some years if leap-seconds do not bite you. > > The problem is when the user enters a date, he wants a date search, so > cast(dateTime as date) <= limit. When he enters a time he does not > usually know what he is asking for ( normally when my users ask for > 'May 1 23:15 they want to include up to 23:15:59, users think in > "truncate to my precision, then search inclusively" ). But they begin > to understand it when I ask "ok, twice a month bills, go 1..15 and > 16..what ?", much easier to say [-MM-01 , -MM-16) and [ > -MM-16, (-MM-01)+1month) > > Francisco Olarte. > > >
Column type changed "spontanously"?
I have several columns that were created as "timestamp without time zone", but I changed them in 2014 to "timestamp with time zone". Recently, when I got notified that times had suddenly changed, I checked and found the columns had reverted to "timestamp without time zone." This seems impossible, yet it seems to have happened. Any ideas on what could cause this? My application has the privileges to do this, as it changed the data type to support time zones. But there is no code that could change it back not to support time zones. The database in on "PostgreSQL 9.3.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit" What other information would help solve this? Chuck Martin Avondale Software
INSERT where not exists with foreign key
My Google foo isn't working on this question, probably because I don't
understand the question well enough. I'm using Postgres 11.3 on Centos 7.
I'm trying to insert a record in table A with a foreign key to table B, but
only where there is not already a foreign key in A to B. So assume this
simple structure:
Table A
A.keyInteger
A.something text
A.Bkey Integer [foreign key to table B, column B.key
Table B (or View C)
B.keyInteger
[more columns]
Thinking that it might simplify matters, I created a view to table B that
only includes records with no foreign key in Table A. But still, after
reading the documentation and Googling, I can't work out what seems like it
should be simple. Among the many things that didn't work is:
INSERT INTO A(something,A.Bkey)
VALUES ('text',
(SELECT C.key FROM C)
But this didn't work because the subquery returned more than one value. Of
course I want it to return all values, but just one per insert.
I can do this outside of Postgres, but would like to learn how to do this
with SQL.
Chuck Martin
Avondale Software
