Your tributaries and fish master tables make sense. If I read your code
right, you're grouping by too many columns. I flattened the data into a
survey table for this simple example:
select tributary,
common_name,
scientific_name,
sum(count_value) as fish_seen,
count(cou
On Fri, Aug 30, 2019 at 12:59 AM Achilleas Mantzios <
ach...@matrix.gatewaynet.com> wrote:
> On 30/8/19 10:20 π.μ., Achilleas Mantzios wrote:
>
> Maybe take a look at
> https://dba.stackexchange.com/questions/185174/error-data-type-text-has-no-default-operator-class-for-access-method-gist
>
>
> So
On Fri, Aug 30, 2019 at 06:22:14PM -0400, stan wrote:
> OK, I am doing enough of this to start using some debug error messages.
>
> I put the following in a function declaration:
>
> RAISE notice 'Called with %', $1 ;
>
> But, when I call the function I do not see anything. I edited postgresql.c
OK, I am doing enough of this to start using some debug error messages.
I put the following in a function declaration:
RAISE notice 'Called with %', $1 ;
But, when I call the function I do not see anything. I edited postgresql.conf
Like this:
client_min_messages = notice
#client_min_messages =
On Fri, Aug 30, 2019 at 04:03:15PM -0400, stan wrote:
> I need to encapsulate, what are basically 2 related function calls into a
> single
> function. The result of each of th calls is a date type.
>
> y current thinking is to return a 2 row table with the 2 dates in it. But, I
> seem to
> be ha
Rob Sargent writes:
>> On Aug 30, 2019, at 2:09 PM, Guyren Howe wrote:
>>
>> On Aug 30, 2019, at 13:03 , stan mailto:st...@panix.com>>
>> wrote:
>>> Is it possible for a function to return a table with results from multiple
>>> queries?
>> You could just return a tuple VALUES(a, b). Or you cou
> On Aug 30, 2019, at 2:03 PM, stan wrote:
>
> I need to encapsulate, what are basically 2 related function calls into a
> single
> function. The result of each of th calls is a date type.
>
> y current thinking is to return a 2 row table with the 2 dates in it. But, I
> seem to
> be having
> On Aug 30, 2019, at 2:09 PM, Guyren Howe wrote:
>
> On Aug 30, 2019, at 13:03 , stan mailto:st...@panix.com>>
> wrote:
>
>> I need to encapsulate, what are basically 2 related function calls into a
>> single
>> function. The result of each of th calls is a date type.
>>
>> y current think
On Aug 30, 2019, at 13:03 , stan wrote:
> I need to encapsulate, what are basically 2 related function calls into a
> single
> function. The result of each of th calls is a date type.
>
> y current thinking is to return a 2 row table with the 2 dates in it. But, I
> seem to
> be having issues
I need to encapsulate, what are basically 2 related function calls into a single
function. The result of each of th calls is a date type.
y current thinking is to return a 2 row table with the 2 dates in it. But, I
seem to
be having issues getting this to work.
Is it possible for a function to r
On Fri, 30 Aug 2019, John W Higgins wrote:
You are grouping by count_value which means that you are asking the system
to return a row for each different count_value.
John,
I didn't realize this.
So if you remove the f.count_value from the select statement (not the
sum(f.count_value)) - and
Tables hold data on fish counts by stream name, species, and (unreported)
collection dates. I'm trying to write a query that returns the total number
of each species in each stream.
The latest attempt is (lines wrapped by alpine; submitted as one line):
\copy (select f.stream_tribs, f.count_valu
On Fri, 30 Aug 2019, Tom Lane wrote:
FWIW, if you're running a current release then there's a reasonable
alternative for writing multi-line COPY-from-query commands; see commit
log below.
Thanks, Tom. I'm running 11.1 and plan to upgrade to 11.5 this weekend.
Regards,
Rich
Rich Shepard writes:
> On Fri, 30 Aug 2019, Tom Lane wrote:
>> I think your problem is the line break, not the parentheses. psql knows
>> how to count parens, but it has no concept of letting backslash commands
>> continue across lines.
> Interesting. I've adopted separating select statements by
On Fri, 30 Aug 2019, Tom Lane wrote:
I think your problem is the line break, not the parentheses. psql knows
how to count parens, but it has no concept of letting backslash commands
continue across lines.
Tom,
Interesting. I've adopted separating select statements by their expressions
as it m
Rich Shepard writes:
> I want to copy query results to a text file and there's an aggregate
> function in the SELECT expression. One of the aggregate function's
> parentheses seems to end the \copy() function and I don't know how best to
> write the statement. A minimal example:
> \copy(select co
I want to copy query results to a text file and there's an aggregate
function in the SELECT expression. One of the aggregate function's
parentheses seems to end the \copy() function and I don't know how best to
write the statement. A minimal example:
\copy(select count_value, sum(count_value)
fro
Ok, yes. It’s s server for analytics running R. So users speecify their oen connection string. My initial thought was to leave out username in the connection string, but I think now to
specify username in lowercase.
Thank you, and Magnus also.
Niels
Fra: Stephen Frost
Dato: 3
stan schrieb am 30.08.2019 um 15:48:
I thought this would be common. But a quick Google only revealed what look to be
workarounds.
I am defining a bunch of functions, and I would prefer to store them in a
separate file, which then gets "source" by the main DB init file.
Is there a standard way
Moving this old thread to -hackers
https://www.postgresql.org/message-id/flat/20180519142603.GA30060%40telsasoft.com
I wanted to mention that this seems to still be an issue, now running pg11.5.
log_time | 2019-08-30 23:20:00.118+10
user_name | postgres
database
On Fri, Aug 30, 2019 at 3:00 PM Niels Jespersen wrote:
> Hello Magnus
>
> Thank you for your prompt reply.
>
> I’m not sure I understand your last statement. I want to achieve that
> regardless of the case of the entered username is logged into the same
> Postgres user (whose name is created in a
Greetings,
* Niels Jespersen (n...@dst.dk) wrote:
>Hello Magnus
>Thank you for your prompt reply.
>I’m not sure I understand your last statement. I want to achieve that
>regardless of the case of the entered username is logged into the same
>Postgres user (whose name is create
pá 30. 8. 2019 v 15:49 odesílatel stan napsal:
>
> I thought this would be common. But a quick Google only revealed what look
> to be
> workarounds.
>
> I am defining a bunch of functions, and I would prefer to store them in a
> separate file, which then gets "source" by the main DB init file.
>
I thought this would be common. But a quick Google only revealed what look to be
workarounds.
I am defining a bunch of functions, and I would prefer to store them in a
separate file, which then gets "source" by the main DB init file.
Is there a standard way to do this?
--
"They that would gi
Ken Tanzer writes:
> Hi. Using 9.6.14, I was setting up a table with this:
> EXCLUDE using gist (EXCLUDE using gist (grant_number_codes with &&)
> Where grant_numbers is a varchar[]. I get this error:
> ERROR: data type character varying[] has no default operator class for
> access method "gist
> > Any thoughts as to the best way to approach this?
>
> Use generate_series:
>
> https://www.postgresql.org/docs/11/functions-srf.html
>
> to generate all the days in the month.
>
> Loop over the days and use EXTRACT:
>
> https://www.postgresql.org/docs/11/functions-datetime.html#FUNCTIO
Hello Magnus
Thank you for your prompt reply.
I’m not sure I understand your last statement. I want to achieve that regardless of the case of the entered username is logged into the same Postgres user (whose name is created in all
lowercase).
In other words, Windows usernames one day
pá 30. 8. 2019 v 12:48 odesílatel stan napsal:
> I have created a function (PLSQL) that does a complex select with joins on
> various
> tables and views, and returns a table.
>
> In the resultant table, I have raw data, and adjusted data. The adjusted
> data i
> all adjusted by a common factor, w
On Fri, Aug 30, 2019 at 12:48 PM stan wrote:
> In the resultant table, I have raw data, and adjusted data. The adjusted data
> i
> all adjusted by a common factor, which is calculated in the select.
> Presently, I
> calculate this same adjustment factor several times in the select.
Is it possib
On Fri, Aug 30, 2019 at 1:27 PM Niels Jespersen wrote:
> Hello
>
> Postgresql 11.2 on Windows.
>
> I have a user mapping i pg_hba.conf
>
> sspi map=domain
>
> In pg_ident.conf, I have the following:
>
> domain/^(([A-Z|a-z]{3}[A|a]?)|([Xx]\d{2}))@DOMAIN$\1
>
> This maps windows logonna
Hello
Postgresql 11.2 on Windows.
I have a user mapping i pg_hba.conf
sspi map=domain
In pg_ident.conf, I have the following:
domain/^(([A-Z|a-z]{3}[A|a]?)|([Xx]\d{2}))@DOMAIN$\1
This maps windows logonname til a postgres username. Hower, for reasons I
cannot explain, sometime
I have created a function (PLSQL) that does a complex select with joins on
various
tables and views, and returns a table.
In the resultant table, I have raw data, and adjusted data. The adjusted data i
all adjusted by a common factor, which is calculated in the select. Presently, I
calculate this
On Fri, Aug 30, 2019 at 11:51 AM Durgamahesh Manne
wrote:
> Logical dump of that table is taking more than 7 hours to be completed
>
> I need to reduce to dump time of that table that has 88GB in size
Good luck!
I would see two possible solutions to the problem:
1) use physical backup and switc
Hi
To respected international postgresql team
I am using postgresql 11.4 version
I have scheduled logical dump job which runs daily one time at db level
There was one table that has write intensive activity for every 40 seconds
in db
The size of the table is about 88GB
Logical dump of that table
On Fri, Aug 30, 2019 at 8:29 AM Luca Ferrari wrote:
> testdb=># \d+ respi.y2018
> ...
> Partition of: respi.root FOR VALUES IN ('2018')
> Partition constraint: ((date_part('year'::text, mis_ora) IS NOT NULL)
> AND (date_part('year'::text, mis_ora) = '2018'::double precision))
> Partition key: LIST
On 30/8/19 10:20 π.μ., Achilleas Mantzios wrote:
On 30/8/19 3:42 π.μ., Ken Tanzer wrote:
Hi. Using 9.6.14, I was setting up a table with this:
EXCLUDE using gist (EXCLUDE using gist (grant_number_codes with &&)
Where grant_numbers is a varchar[]. I get this error:
ERROR: data type characte
Hello,
I've noticed that we our queries have very bad estimates, which leads to
the planner using slow nested loops, here is a subset of the query without
tenant separation (correct estimates):
explain (ANALYZE, COSTS, BUFFERS, FORMAT text)
select *
from "Reservation"."Reservation" r
On 30/8/19 3:42 π.μ., Ken Tanzer wrote:
Hi. Using 9.6.14, I was setting up a table with this:
EXCLUDE using gist (EXCLUDE using gist (grant_number_codes with &&)
Where grant_numbers is a varchar[]. I get this error:
ERROR: data type character varying[] has no default operator class for acce
38 matches
Mail list logo
