Re: where clauses including timstamptz and intervals
Niels:
On Fri, Apr 9, 2021 at 7:39 PM Niels Jespersen wrote:
> The answer to my original problem behind my original question which is how to
> query using utc timing in order to hit exactly the (utc created) partition,
> without changing the session timezone, would be like this:
You would have had much success had you stated your problem instead of
the seemingly academical question of queery equivalence, anyway:
> select f.* from f
> where f.r_time at time zone 'utc' >= '2020-10-01 00:00:00+00'::timestamptz at
> time zone 'utc'
> and f.r_time at time zone 'utc' < ('2020-10-01 00:00:00+00'::timestamptz at
> time zone 'utc' + interval '1 month');
Although it is equivalent I would suggest to use:
f.r_time< ('2020-10-01 00:00:00+00'::timestamptz at time zone 'utc' +
interval '1 month') at time zone 'utc' ;
optimizer should treat both the same, but I'm no sure it can recognize
it, and it may not see your condition is of the type "field <
constant" to select index scans and similar things. Also, you can
refactor your calculations into an stable function for greater
clarity.
> I think maybe it's better to just change the session timezone when this need
> arises.
Maybe. Also, I do not see the need to query exact partitions by tstz
range, but assuming you do it may be much easier to query the catalog
for the partition and then query the partition. ( In many years of
working with time-partitioned tables I have never got the need to
build a query to hit exactly X partitions from times ).
Francisco Olarte.
The Amazon CloudFront distribution is configured to block access from your country.
I am trying to download the installer and I get this error. The same happens with binary files. --- 403 ERROR The request could not be satisfied. The Amazon CloudFront distribution is configured to block access from your country. We can't connect to the server for this app or website at this time. There might be too much traffic or a configuration error. Try again later, or contact the app or website owner. If you provide content to customers through CloudFront, you can find steps to troubleshoot and help prevent this error by reviewing the CloudFront documentation. Generated by cloudfront (CloudFront) Request ID: asdbQyjKvpRkboABJVWMOVtNnqMx8XVv74Ho_GBQF-lx6qWcRIhLaw== --- Greetings Thank you very much in advance
Re: The Amazon CloudFront distribution is configured to block access from your country.
On 4/10/21 2:00 PM, felix.quin...@yahoo.com wrote: I am trying to download the installer and I get this error. The same happens with binary files. --- 403 ERROR The request could not be satisfied. The Amazon CloudFront distribution is configured to block access from your country. We can't connect to the server for this app or website at this time. There might be too much traffic or a configuration error. Try again later, or contact the app or website owner. If you provide content to customers through CloudFront, you can find steps to troubleshoot and help prevent this error by reviewing the CloudFront documentation. Generated by cloudfront (CloudFront) Request ID: asdbQyjKvpRkboABJVWMOVtNnqMx8XVv74Ho_GBQF-lx6qWcRIhLaw== Why is this directly relevant to Postgresql? -- Angular momentum makes the world go 'round.
Re: The Amazon CloudFront distribution is configured to block access from your country.
Because it is the Postgresql installer. https://www.enterprisedb.com/downloads/postgres-postgresql-downloads On Saturday, April 10, 2021, 07:30:29 PM GMT-4, Ron wrote: On 4/10/21 2:00 PM, felix.quin...@yahoo.com wrote: > I am trying to download the installer and I get this error. The same happens > with binary files. > > --- > 403 ERROR > The request could not be satisfied. > The Amazon CloudFront distribution is configured to block access from your > country. We can't connect to the server for this app or website at this time. > There might be too much traffic or a configuration error. Try again later, or > contact the app or website owner. > If you provide content to customers through CloudFront, you can find steps to > troubleshoot and help prevent this error by reviewing the CloudFront > documentation. > > Generated by cloudfront (CloudFront) > Request ID: asdbQyjKvpRkboABJVWMOVtNnqMx8XVv74Ho_GBQF-lx6qWcRIhLaw== Why is this directly relevant to Postgresql? -- Angular momentum makes the world go 'round.
Re: The Amazon CloudFront distribution is configured to block access from your country.
Then you should ask EDB, or Amazon, or use a VPN. On 4/10/21 7:03 PM, felix.quin...@yahoo.com wrote: Because it is the Postgresql installer. https://www.enterprisedb.com/downloads/postgres-postgresql-downloads On Saturday, April 10, 2021, 07:30:29 PM GMT-4, Ron wrote: On 4/10/21 2:00 PM, felix.quin...@yahoo.com wrote: I am trying to download the installer and I get this error. The same happens with binary files. --- 403 ERROR The request could not be satisfied. The Amazon CloudFront distribution is configured to block access from your country. We can't connect to the server for this app or website at this time. There might be too much traffic or a configuration error. Try again later, or contact the app or website owner. If you provide content to customers through CloudFront, you can find steps to troubleshoot and help prevent this error by reviewing the CloudFront documentation. Generated by cloudfront (CloudFront) Request ID: asdbQyjKvpRkboABJVWMOVtNnqMx8XVv74Ho_GBQF-lx6qWcRIhLaw== Why is this directly relevant to Postgresql? -- Angular momentum makes the world go 'round.
Re: The Amazon CloudFront distribution is configured to block access from your country.
I see no reason why ask elsewhere first, the link for the installer download is on the Postgresql website, not elsewhere. The most logical thing to do is ask here first. On Saturday, April 10, 2021, 08:07:15 PM GMT-4, Ron wrote: Then you should ask EDB, or Amazon, or use a VPN. On 4/10/21 7:03 PM, felix.quin...@yahoo.com wrote: > Because it is the Postgresql installer. > https://www.enterprisedb.com/downloads/postgres-postgresql-downloads > > On Saturday, April 10, 2021, 07:30:29 PM GMT-4, Ron > wrote: > > On 4/10/21 2:00 PM, felix.quin...@yahoo.com wrote: >> I am trying to download the installer and I get this error. The same happens >> with binary files. >> >> --- >> 403 ERROR >> The request could not be satisfied. >> The Amazon CloudFront distribution is configured to block access from your >> country. We can't connect to the server for this app or website at this >> time. There might be too much traffic or a configuration error. Try again >> later, or contact the app or website owner. >> If you provide content to customers through CloudFront, you can find steps >> to troubleshoot and help prevent this error by reviewing the CloudFront >> documentation. >> >> Generated by cloudfront (CloudFront) >> Request ID: asdbQyjKvpRkboABJVWMOVtNnqMx8XVv74Ho_GBQF-lx6qWcRIhLaw== > Why is this directly relevant to Postgresql? -- Angular momentum makes the world go 'round.
Ways to "serialize" result set for later use?
Hey there everyone, I am going through the process of writing my first pgtap tests for my database, and I wanted to get some feedback on if my solution seems fine, is just dumb, or could be acomplished much easier another way. So my main problem I was trying to work around, was my tests are written in functions and called using runtests(), so using psql and \copy to save test data to the filesystem isn't really part of the workflow, but I still needed a way to have my "expected" query resultset passed into results_eq [ https://pgtap.org/documentation.html#results_eq] easily within a function body. I originally manually dumped some "known good" data from a query to csv, and built some SELECT ... FROM VALUES (...) statements by hand to do this. That obviously sucks. Then I started looking to see if there was a way to get Postgres to "serialize" a query resultset to a values list similar to how pg_dump is able to be configured to dump data as inserts for the plain format. I couldn't find anything at all though. No mention of anything similar on the mailing list, stackoverflow, etc... I feel like I must be searching incorrectly. Anyways, since I didn't find anything, or any discussion online for something like I wanted, I just tried building it, see attached for source. It's a function that takes in the text of a query that produces a resultset, and returns a SELECT ... FROM VALUES (...) statement that will produce the exact same output. It does so by running that query and creating a temporary table with the results, then we query the system catalogs to get the data types, and column names of the temporary table produced by the query, and then uses that information to build a VALUES from clause that contains each row by scanning the temp table, and also dynamically builds the SELECT ... columns list to cast everything to the correct data type, we then put all of that together and return the query text to the caller. Not fully tested or anything, and not guaranteed to work well. Please, if you see any issues let me know. I got this together in an hour of hacking... but it did solve my immediate problem and I have been able to generate easy "expected" result sets for my tests. So I really just wanted to see if there is a better way to go about what i'm trying to do, does Postgres already support something similar I can harness instead of this hack? Or is this really an alright way to go? query_to_values.sql Description: Binary data
