Hi,
Is it intended that indexes are not pushed down to union all
subqueries if even a single select contains a where clause? Is this
just not implemented, is it impossible to implement or am I doing
something wrong?
The following query does a SeqScan for "bikes" and "cars" tables even
though Inde
Have you considered writing a stored procedure to process records that have
been written to temporary tables?
0. Create temporary tables tmp_farms, tmp_crops and tmp_deliveries, which
don't have id columns.
1. Truncate the three temporary tables
2. Insert into the temp tables a "set" of prod d
I see. That would definitely work, but part of this for me is to get a
better understanding of PostgreSQL's capabilities. I'm going to keep
working on a minimal solution that deletes no records from the dev
database, and only inserts the required records.
On Wed, Oct 4, 2023 at 6:58 PM Ron wrot
Ah. We'd truncate all of the dev tables, then load a "slice" (for example,
accounts 1 to 1, and all associated records from downstream tables;
lots and lots of views!!) from the prod database.
On 10/4/23 20:50, Dow Drake wrote:
Thanks for the reply, Ron!
I'm not sure I see how to make
Thanks for the reply, Ron!
I'm not sure I see how to make your suggestion work, though. Suppose I
dump the three tables to CSV as you suggest (and write a script to extract
the relevant records from those CSV dumps in the correct order). It might
be that in the dev database, the next generated ke
Frame challenge: why can't you just "\copy to" the dev database tables in
the correct order, to satisfy foreign key requirements?
On 10/4/23 18:59, Dow Drake wrote:
Hi,
I'm trying to write a postgresql script to replicate a hierarchical
structure in a live database into my development databas
Hi,
I'm trying to write a postgresql script to replicate a hierarchical
structure in a live database into my development database, where I can
debug and test more easily. I can extract the data from the live database
that needs to be inserted, but I'm having trouble writing the insertion
script
> On 4 Oct 2023, at 21:30, Lincoln Swaine-Moore wrote:
>
> > What I do in such cases is to add an extra column with the UTC timestamp to
> > serve as a linear scale to the local timestamps. That also helps with
> > ordering buckets in reports and such during DST changes (especially the
> > o
Slightly off topic, but has anyone tried TimescaleDB for timeseries databases?
The issues discussed here are still there as they apply to the underlying
Postgres ORDBMS.
We solve the problem (around 4 billion records of instrument sensor readings)
by using UTC for the "native" timestamp, and wo
> What I do in such cases is to add an extra column with the UTC timestamp
to serve as a linear scale to the local timestamps. That also helps with
ordering buckets in reports and such during DST changes (especially the
ones where an hour repeats).
> For hours and quarter hours I found it to be fa
> Really UTC (not that it matters for calculation purposes)
Sorry, yes--I just meant that literally when I run `show timezone` on the
server in question, I get `GMT`.
> As to "other places" in your (or others) code, provided that you are
storing and manipulating your data as timestamp WITH time z
> On 4 Oct 2023, at 17:58, Lincoln Swaine-Moore wrote:
>
> > SELECT
> > sub.gs AS ts_in_utc
> > ,sub.gs AT TIME ZONE 'America/New_York' AS ts_in_local_tz
> > ,date_bin('1 days', sub.gs AT TIME ZONE 'America/New_York',
> > '2023-01-01')
> > FROM (SELECT generate_series('2023-11-03 0
...
> Yeah, I'm definitely interested in keeping as much of the DST stuff
> outside my code as possible. I guess my concern is that I think there are
> other places where this database is being used in my codebase that may rely
> on the database time setting being UTC (or really, GMT, though I don
> If you mean that your input will always include an explicit zone
> specification, then this doesn't affect you. What I was thinking
> about was that
>
> select generate_series('2023-10-04 13:30', ...)
> is going to mean different things depending on the zone setting
> that prevails when
Lincoln Swaine-Moore writes:
>> Yeah. One thing to keep in mind is that that might have different
>> behavior in terms of the evaluation of the arguments to the function,
>> ie which timezone setting is your input parsed according to.
> I see. You mean, in the event that it doesn't conform to an
> Yeah. One thing to keep in mind is that that might have different
> behavior in terms of the evaluation of the arguments to the function,
> ie which timezone setting is your input parsed according to.
I see. You mean, in the event that it doesn't conform to an entry in
`pg_timezone_names`? I do
Lincoln Swaine-Moore writes:
> Excellent, thank you. So just to be explicit here, I could either run this
> function, or set/run my query/set back, with the same behavior/safety
> guarantees as if I was using the generate_series function with timezone
> from v16?
Yeah. One thing to keep in mind
> No. The function call mechanism will ensure that timezone goes back
> to its previous state at function exit.
> An actual rollback would undo the effects of set_config, yes. You
> only need this function wrapper to ensure that subsequent operations
> in the same transaction don't see the setti
Lincoln Swaine-Moore writes:
>>> create function generate_series(timestamptz, timestamptz, interval, text)
>>> returns setof timestamptz
>>> strict immutable language plpgsql as
>>> $$
>>> begin
>>> perform set_config('timezone', $4, true);
>>> return query select generate_series($1, $2, $3);
>>>
jinser writes:
> This is what confuses me, I've tried many versions of pgsql, at least
> including 11-16, and the behavior is consistent with the above; but I
> feel that maybe the order by in the window function should not affect
> the final result.
Since you have not specified an ORDER BY for t
Hello Friends,
I encountered a behavior that confused me when using window function
and group aggregate at the same time.
simple table:
CREATE TABLE t (a int, b int);
add some data to represent the behavior:
insert into t values
(1, 39),
(1, 95),
(2, 48),
(3, 87),
(4, 19),
On Oct 4, 2023, at 8:38 AM, Adrian Klaver wrote:
If you search the source file directory for "reserver_visible_restriction" is
there another file that contains it?
Sigh…that was it…Thank you.
There are two ‘views’ directories, one under ’sources/views’ and the other
under /schema/views.
That’s not it. export_schema.sh writes the scripts, and then emits a ora2pg
command to pull in the data (I've managed to do this a dozen times so far
working out how to do things. :-)
The dates on the files in the sources directory are consistent with that, and
don’t change when I run the scrip
Thank you to all who have weighed in! Very much appreciated.
A few thoughts based on what I've read:
> As far as Lincoln describes it, the series is not the real problem here,
but is just intended to be a simplified example of his actual data.
This both is and isn't the case. I was using gneerat
On 10/4/23 06:24, Johnson, Bruce E - (bjohnson) wrote:
That’s not it. export_schema.sh writes the scripts, and then emits a
ora2pg command to pull in the data (I've managed to do this a dozen
times so far working out how to do things. :-)
The dates on the files in the sources directory are con
Am 04.10.2023 um 16:11 schrieb Tom Lane:
Steve Crawford writes:
On Tue, Oct 3, 2023 at 7:33 PM Lincoln Swaine-Moore
wrote:
5) Ideally, the solution would not involve messing with the
server/connection's value of timezone. (Though I would be interested if
there was a solution that relaxed this
Steve Crawford writes:
> On Tue, Oct 3, 2023 at 7:33 PM Lincoln Swaine-Moore
> wrote:
>> 5) Ideally, the solution would not involve messing with the
>> server/connection's value of timezone. (Though I would be interested if
>> there was a solution that relaxed this constraint and was relatively
>
Am 04.10.2023 um 05:36 schrieb Steve Crawford:
On Tue, Oct 3, 2023 at 7:33 PM Lincoln Swaine-Moore
wrote:
Hi all,
I'm attempting to generate some reports using user-driven
timezones for UTC data, and I'm having trouble writing a query
that meets the following criteria:
Yes!
Thanks so much it worked!
On 10/3/23 9:37 a.m., Peter J. Holzer wrote:
On 2023-10-03 05:55:51 -0400, Amn Ojee Uw wrote:
psql -U my_group_worker -d my_group_db
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432"
failed: FATAL: Peer authentication failed for use
29 matches
Mail list logo
