7;,'2015-03-18 11:45:00';
insert into t select '2015-03-17 20:15:00','2015-03-18 11:45:00';
insert into t select '2015-03-17 21:15:00','2015-03-18 10:10:00';
insert into t select '2015-03-18 23:30:00','2015-03-19 01:30:00';
SELECT ser, SUM(
case when e - ser < interval '1 hour' then e-ser --end interval
when s >= ser then interval '1 hour' - (s - ser) --start interval
else interval '1 hour'
end ) as time_tot
FROM
(select e,s,
generate_series(date_trunc('hour',s), date_trunc('hour',e), '1 hour')
ser
from t
)foo
group by ser
order by 1
regards,
Marc Mamin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
5-03-16 11:05:00';
>insert into t select '2015-03-16 08:00:00','2015-03-16 11:45:00';
>insert into t select '2015-03-17 15:15:00','2015-03-18 11:45:00';
>insert into t select '2015-03-17 20:15:00','2015-03-18 11:45:00';
>in
date master_items set feedback_to_de <> 'Yes' WHERE feedback_to_de IS
DISTINCT FROM 'Yes'
depending on the column nullable.
regards,
Marc Mamin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hi,
have you checked that the links in $PGDATA\pg_tblspc on the new drive are valid
?
They possibly still point to the old drive.
I guess you have to correct them per hand before starting the moved DB.
regards,
Marc Mamin
Von: pgsql-general-ow
Hello,
The test below is running fine
but if you add the trigger push_foo_tr (uncomment)
then the exception is raised.
It seems that this additional trigger to be called at the first place
changes the deferrable status of the second one.
Is this an expected behaviour ?
regards,
Marc Mamin
table.
Have you checked the statistics and vacuum state of the catalog tables (pg_*)
Maybe some of them haven't been analyzed yet as this is a freshly created DB.
(and with a lot of DLL statements we regularly have to call vacuum full on part
of the catalog to avoid bloating there.)
You
recall!
this self containing case works well if I call the correct functions in the
triggers :)
Marc
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Marc Mamin
> Sent: Donnerstag, 4. Juni 201
> -Original Message-
> From: Tom Lane [mailto:t...@sss.pgh.pa.us]
> Sent: Donnerstag, 4. Juni 2015 15:56
> To: Marc Mamin
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Row visibility issue with consecutive triggers,
> one being DEFERRED
>
> Marc
,
Marc Mamin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
> > select *;
> > --
> > ERROR: SELECT * with no tables specified is not valid
> >
> > select count(*);
> >
> > 1
> >
> > Is this a must? and why 1?
>
> Hi,
>
> regarding the "why 1" part:
>
> I think that if we accept that
>
> chris=> select 'foo';
> ?column?
> -
>>That's the point. * has no meaning without FROM
>But COUNT(*)
>
>does have meaning - it means "the number of rows".
which rows? :-)
> It's not counting the number of columns in the row, so postgres doesn't need
> to know what columns exist in the row to return a row count.
>Geoff
--
Sen
> -Original Message-
> From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
> Sent: Donnerstag, 11. Juni 2015 16:31
> To: Marc Mamin; 'Geoff Winkless'; Postgres General
> Subject: Re: [GENERAL] select count(*);
>
> On 06/11/2015 07:17 AM, Marc Mami
, 'g')
from mytable
where id =-2146999703
)foo;
ERROR: invalid memory alloc request size 1447215584
regards,
Marc Mamin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
> -Original Message-
> From: Albe Laurenz [mailto:laurenz.a...@wien.gv.at]
> Sent: Mittwoch, 24. Juni 2015 13:44
> To: Marc Mamin; Postgres General
> Subject: RE: Counting the occurences of a substring within a very large
> text
>
> Marc Mamin wrote:
> >
> -Original Message-
> From: Chris Mair [mailto:ch...@1006.org]
> Sent: Mittwoch, 24. Juni 2015 13:26
> To: Marc Mamin; Postgres General
> Subject: Re: [GENERAL] Counting the occurences of a substring within a
> very large text
>
> > Hello,
> >
message, E'\n',
> ''))
> > FROM mytable WHERE id = -2146999703;
>
>
> no, they both yeld the same error.
>
and this fails too, which is more annoying as it looks like a bug:
SELECT replace(full_message, E'\n', '') FROM stadium_rprod.aserror
>>On 24 June 2015 at 14:51, Marc Mamin wrote:
>>note that the 345MB text only contains 635 lines. This might be the issue...
>There's similar issue discussed here:
>http://www.postgresql.org/message-id/6046.1353874...@sss.pgh.pa.us
>Tom did seem to accept that the
tinct myColumn) as c
FROM myTable
GROUP BY substring(ctid::text, '^.([^,]+)')
)foo
GROUP BY c order by 1 desc;
Is there a quicker way to extract the page reference from the ctid?
regards,
Marc Mamin
--
Sent via pgsql-general mailing list (pgsql-general@postgresq
> -Original Message-
> From: Michael Paquier [mailto:michael.paqu...@gmail.com]
> Sent: Mittwoch, 1. Juli 2015 07:05
> To: Marc Mamin
> Cc: Postgres General
> Subject: Re: [GENERAL] how to extract the page "address" from the ctid
>
> On Tue, Jun 30, 201
combinations of
# stderr, csvlog, syslog, and eventlog,
# depending on platform. csvlog
# requires logging_collector to be on.
regards,
Marc Mamin
>
> --
> Sent via pg
e "extra"
> "rec_isins_current_attachment" btree (attachment), tablespace
> "extra"
Hello,
Are you sure that the column order of the PKs is the same in both tables?
(attachment, isin) or (isin, attachment).
When isin is at the s
Hello,
has anyone some insight on when we can expect a newest 9.4.x Version on RDS ?
Or knows a better forum to ask for this?
I should upgrade an application DB currently on 9.3 that might go on RDS
but I'm a bit reluctant to go only on 9.4.1 ...
regards,
Marc Mamin
7;XY' as 'month2.foo'::regtype)
Is there a way for it ?
regards,
Marc Mamin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
> -Original Message-
> From: Tom Lane [mailto:t...@sss.pgh.pa.us]
> Sent: Mittwoch, 8. Juli 2015 15:44
> To: Marc Mamin
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Problem with ALTER TYPE, Indexes and cast
>
> Marc Mamin writes:
> > Now
M a JOIN b on a.id = b.id
WHERE a.id%4 = [0,1,2,3}
I usually avoid parallel INSERTS to avoid I/O contention and random
distribution within the target tables.
Are you monitoring the I/O activity in your tests ?
Have you tried to use only 2 parallel processes?
regards,
Marc Mamin
>(accor
ver got
analyzed.
Can this fool the query planner in a negative way ?
regards,
Marc Mamin
>> The table blocks would fall out of cache if they're never touched.
>>
>>regards, tom lane
>>
>>
>
>Sweet! Thanks Tom.
>
>
>--
> Marc, I am using postgres 9.4. I didn't benchmark, but intuitively the modulo
> operator will force traversing every record in table "a" 4 times, as it can't
> use an index.
Not necessarily. seq scans can be synchronized:
"This allows sequential scans of large tables to synchronize with each
$i -lt 1000 ]; do let i++; psql -c 'select 1' ;
done; } >/dev/null
real0m11.081s
user0m0.140s
sys 0m0.208s
regards,
Marc Mamin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http
when type='A' then link end ))as ar_a,
array_not_nulls(array_agg(case when type='B' then link end ))as ar_b,
array_not_nulls(array_agg(case when type NOT IN ('A', 'B') then link end))
as ar_others
from demo
GROUP BY id
)
SELECT
id,
a_ct,
b_ct,
ar_a,
ar_b,
ar_others,
coalesce (ar_a[1], case when b_ct > 1 then ar_b[2] else ar_others[1] end ) as
link_a,
coalesce (ar_b[1], case when a_ct > 1 then ar_a[2] when a_ct = 0 then
ar_others[2] else ar_others[1] end) as link_b,
-- unused others
case when a_ct + b_ct >=2 then ar_others
else ar_others[3 - (a_ct + b_ct) : array_length(ar_others,1)]
end
||
-- unused A & B
case when has_a AND has_b then ar_a[2:a_ct] || ar_b[2:b_ct]
when a_ct > 2 then ar_a[3:a_ct]
when b_ct > 2 then ar_b[3:b_ct]
end as unused
FROM PREP
order by id
regards,
Marc Mamin
t take care of the column statistic targets
(i.e. ALTER TABLE t ALTER COLUMN c SET STATISTICS n;)
regards,
Marc Mamin
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Melvin Davidson
Sent: Donnerstag, 17. September 2015 15:48
To: David
Von: Melvin Davidson [melvin6...@gmail.com]
Gesendet: Donnerstag, 17. September 2015 17:11
An: Marc Mamin
Cc: pgsql-general@postgresql.org
Betreff: Re: [GENERAL] clone_schema function
Thanks,
>I'm not sure why you had trouble with the REPLACE(),
I would store your monthly data within a separate table.
regards,
Marc Mamin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
>>remote_db=> insert into users_copy_without_indexes select * from users;
>>INSERT 0 523342
>> remote_db=> select count(1) from users_copy_without_indexes where id
>> = 123;
>> count
>>---
>> 2
>>(1 row)
>>
> -Original Message-
> From: Jim Nasby [mailto:jim.na...@bluetreble.com]
> Sent: Montag, 26. Oktober 2015 01:55
> To: Marc Mamin; Adrian Klaver; Chaz Yoon; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Duplicate rows during pg_dump
>
> On 10/24/15 3:15
rrent_database())
>)
>
>SELECT regexp_replace(q,'/\*.*\*/','') as q /* strip off comments like
>this */ <-- cannot get a regex to do this
>FROM to_clean ORDER BY q
Hi,
Does this help ?
select regexp_replace(' aaa /*
x
y
z
*/ foo', '\/\
rows as text,but
in their (default) csv representation:
select to_csv((c.*)) from foo c;
regards,
Marc Mamin
as (select 1::int x),
j as (select 1::int x)
select
max(x) from (select x from i union all select x from j) b;
---
ERROR: could not find plan for CTE "i"
regards,
Marc Mamin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
t; in front of that
> to see what gets printed:
>
> $ echo pg_dump -d postgres -t "\"Statuses\""
> pg_dump -d postgres -t "Statuses"
Hello,
Have you checked if the problem is limited to pg_dump ?
Maybe you're facing a trivial mistake, like a space in the
good tutotial for a task like this ?
thanks and best regards,
Marc Mamin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
can try CTE Common Table Expression. It isn't possible with plpgsql :(
> Theoretically you can write C extension where SRF function can returns data
> continually.But plpgsql function using local stack and returns data as block.
> Regards
> Pavel
Thanks.
A simpler solution will be
d_lab_tests_siui,
id_lab_tests_groups, valid_from, id_lab_sample_types) in the index definition,
but I've never tried that and suspect the planner will also have trouble to
include such an index in the plan.
regards,
Marc Mamin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
cte as (select ..),
tmp as ( INSERT INTO result2 select ... from cte),
SELECT ... from cte;
query_2:
select * from result2;
regards,
Marc Mamin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hello,
if you are using pgjdbc, there is a discussion about adding an option to modify
this behavior:
https://github.com/pgjdbc/pgjdbc/issues/423
This would simplify the migration of java projects, e.g. from oracle to
postgres.
regards,
Marc Mamin
From: pgsql-general-ow
emented
that solution...
something like
db_link_send_query('conn0', 'CREATE UNLOGGED TABLE my_result...
db_link_send_query('conn1', 'INSERT INTO my_result statement based on
partitioning field');
db_link_send_query('conn2', 'INSERT INTO my_res
What counts are the data and how they are arranged within the DB.
Therefore they should put more focus on them, rather then think in OOM.
That book is a great idea. A collection of bad code example and the SQL
equivalents, strengthend with runtimes figures, may help move some minds
best regards
Hello,
I'm looking for $subject
example:
how to revert
select to_json(E'a\n''b'::text)
I can achieve it with something like this:
select
json_extract_path_text(('{"a":'||to_json(E'a\n''b'::text)||'}')::json,'a')
does anybody knows a less ugly solution ?
regards,
Marc Mamin
>From: pgsql-general-ow...@postgresql.org
>[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Marc Mamin
>Sent: Montag, 23. Mai 2016 16:03
>To: pgsql-general@postgresql.org
>Subject: [GENERAL] revert function for to_json ?
>
>Hello,
>
>I'm looking for
Hello,
if vacuuming does not help, you may also want to log all queries running
more than x milliseconds to help localize the problem.
see log_min_duration_statement= #ms in postgresql.conf
(i didn't check its avaibility in older version)
HTH,
Marc
d be the dafault behaviour for
pg_advisory_lock_shared (..) and pg_try_advisory_lock_shared (...)
--and possibly define a max number of allowed shared locks (over all
sessions)
pg_[try_]advisory_lock_shared_max(key bigint, max_count int)
regards,
Marc Mamin
Hello,
I wonder if there are some special cases where STABLE functions should
be avoided.
I think for example of functions using set_config or querying the
advisory locks.
Thanks in advance,
Marc Mamin
...
Does anyone have a handmade solution for a pg_get_procdef function.
I'm still on Postgres 8.3 ...
Thanks for any help,
Marc Mamin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ed FROM clause
rowtype
SQL state: 42804
A subsequent call returns an empty result set, as described here:
http://www.postgresql.org/docs/8.3/interactive/contrib-dblink-get-result
.html
Thanks for any help.
Marc Mamin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To ma
> (Postgres 8.3.9)
oops, this is actually 8.4.4 on Windows.
-Original Message-
From: Marc Mamin
Sent: Mittwoch, 10. November 2010 18:25
To: 'pgsql-general@postgresql.org'
Subject: dblink_get_result issue
Hello,
I'm experimenting with dblink, and have an issue with a
,
old_ratio double precision,
new_ratio double precision,
old_pagecount integer,
new_pagecount integer,
execution_count integer,
reindex_time bigint,
CONSTRAINT reindex_list_pk PRIMARY KEY (nspname_oid, index_oid)
)
regards,
Marc Mamin
From: pgsql-general-ow
For now I just ignore the first exception.
BEGIN
PERFORM * from dblink_get_result('myconn')as x (t text);
EXCEPTION WHEN datatype_mismatch THEN NULL;
END;
PERFORM * from dblink_get_result('myconn')as x (t text);
thanks,
Marc Mamin
-Original Message-
From
SSD caveats are well described here:
http://www.postgresql.org/about/news.1249
HTH,
Marc Mamin
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Allan Kamau
Sent: Donnerstag, 11. November 2010 07:42
To: Postgres General
Hello,
may schemaspy help you ?
http://schemaspy.sourceforge.net/sample/relationships.html
HTH,
Marc Mamin
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Aram Fingal
Sent: Donnerstag, 11. November 2010 17:51
To
sum(case when fair then 1 else 0 end) as fair,
=>
sum(case when fair then 1 end) as fair,
:)
regards,
Marc Mamin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ed table has a higher priority.
The best solution in my opinion, would allow to dump/restore these 2 table
types in separate processes (or threads..).
(and by the way: would it be possible to choose the compress tool as an option
for pg_dump)
pgdump -F.. -Compress pigz -f out.dmp -f_unlogged o
d the indexes
- drop the live table
- rename the shadow table to the visible one.
Is it imaginable to use unlogged tables foe the shadow one and then enable
logging after the switch ?
regards,
Marc Mamin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to
yep,
but I'll miss this as I only use WAL for crash recovery...
regards,
Marc Mamin
-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: Mittwoch, 17. November 2010 23:40
To: Marc Mamin
Cc: PostgreSQL general; Josh Berkus
Subject: Re: [GENERAL] Survey on backi
ail: ERROR: relation "my_share4" already exists (=>
DUPLICATE TABLE)
best regards,
Marc Mamin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
> What are the use-cases for replicating unlogged tables?
Hello,
I guess they could be useful in cloud infrastructures.
(see http://archives.postgresql.org/pgsql-general/2010-11/msg00865.php)
regards,
Marc Mamin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To m
Hello,
did you also try defininig partial indexes?
e.g.
CREATE INDEX xx on task_definitions (ctrlid) WHERE (name::text = 'UseWSData')
CREATE INDEX yy on ctrl_definitions (ctrlid) WHERE (name::text =
'IrrPeriodStart')
HTH,
Marc Mamin
-Original Message-
Fr
y bound to the
pg_backend_pid().
Here I will have to add the current_user within the GUC name to better
isokate them)
show search_path;
"$user",public
SET ROLE 'xxx';
show search_path;
xxx,public
RESET ROLE
show search_path;
xxx,public
best regards,
Marc Mamin
Hello,
> Obviously each "user" could use RESET ROLE and become the super user.
Yes, this is a point not to forget, but isn't an issue in our case.
best regards,
Marc Mamin
From: Derrick Rice [mailto:derrick.r...@gmail.com]
Sent: Samstag, 4. Dezember 2010 00:21
To: Ma
etting a consistent dump of all your data may be an issue thought...
regards,
Marc Mamin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
is off
# - Archiving -
#archive_mode = off # allows archiving to be done
#archive_command = '' # command to use to archive a logfile
segment
#archive_timeout = 0# force a logfile segment switch after
this
Thanks,
Marc Mamin
--
Sent
r matches the duplicate_object exception and I'll try
to catch it as well, but I wonder where this comes from.
Cheers,
Marc Mamin
CREATE OR REPLACE FUNCTION public.createtable_foo(varchar,INT)
RETURNS int4 AS
$BODY$
BEGIN
EXECUTE 'CREATE TABLE '
elem; rangesel; contjoinsel
@>; 11;10; b; f; f; 3831; 3831; 16; 3892; 0; range_contains;
rangesel; contjoinsel
regards,
Marc Mamin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
> -Original Message-
> From: Pujol Mathieu [mailto:mathieu.pu...@realfusio.com]
> Sent: Dienstag, 15. Juli 2014 08:40
> To: Marc Mamin
> Subject: Re: [GENERAL] operator is not unique: smallint[] @>
> smallint[] You might need to add explicit type casts (!)
>
nect using the IP instead of "localhost" ?
regards,
Marc Mamin
>
> Thanks for helping.
>
> Regards,
> basti
>
> Am 15.07.2014 16:46, schrieb Adrian Klaver:
> > On 07/15/2014 07:17 AM, basti wrote:
> >> Hello Adrian,
> >>
> >> Ye
cost of detoasting seems to often be
underestimated.
For the case when one of your 3 first columns has a very low cardinality, you
may consider adding some partial indexes.
e.g.:
create INDEX idx_the_geom_gist_general USING gist (the_geom) where class
='general';
create INDEX idx_
OP
EXECUTE 'select exists (select * from public.'||rec.tablename||')' into
test;
IF test THEN raise notice 'COPY public.% TO
%.dump',rec.tablename,rec.tablename;
END IF;
END LOOP;
END;
$$ language plpgsql
regards,
Marc Mamin
kes sense in your context, but something like this could do
the job:
WITH SEL AS ( Your Query)
SELECT * FROM SEL
UNION ALL
SELECT 'nothing found' WHERE NOT EXISTS ( select * from sel);
regards,
Marc Mamin
= '428842195.338828'
And as it now looks like a number, maybe go a step further depending on which
patterns these SpawnID can have.
This given value can for example be stored as 2 int4 or one int8:
('428842195.338828'::numeric * 100)::int8
On the other hand this will only addre
Hello,
When different sessions create temp tables with the same name:
How can I identify the oid of the one created within the current session ?
Thanks,
Marc Mamin
> -Original Message-
> From: Michael Paquier [mailto:michael.paqu...@gmail.com]
> On Mon, Aug 25, 2014 at 7:48 PM, Marc Mamin
> wrote:
> > Hello,
> >
> > When different sessions create temp tables with the same name:
> > How can I identify the oid o
ou may check how up to date your statistics are
and try to raise the statistic target on the column resource_2_tag.tag_id.
Also try a CTE form for your query:
WITH A as (SELECT DISTINCT id FROM resource
WHERE resource_attribute1_id = 614
)
SELECT
resource_2_tag.tag_id AS resource_2_tag_tag_id,
count(resource_2_tag.tag_id) AS counted
FROM
resource_2_tag
JOIN A ON A.id = resource_2_tag.resource_id
ORDER BY counted DESC
LIMIT 25;
regards,
Marc Mamin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ld use a shared full table scan on oldtable.
HTH
Marc Mamin
>
>Best regards,
>Daniel
>
>-Original Message-
>From: pgsql-general-ow...@postgresql.org
>[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Tom Lane
>Sent: December-08-14 21:52
>To: Scott Mar
the end I'm not sure if multiple threads will help here.
I'm using this approach in aggregations which are more cpu intensive than a
simple distinct.
I'm looking forward to see your tests results :)
Marc Mamin
>
>Daniel
>
>-Original Message-
>
HAVING COUNT (*) =2
OR COUNT(case when friend_id =USER1 then true end)=1
) q1
regards,
Marc Mamin
end)=1 as are_friend,
count(case when c1=2 then true end) as common_friends
FROM
(
SELECT count(*) as c1
FROM friends
WHERE user_id IN (USER1, USER2)
GROUP BY case when user_id = USER2 then USER1 else USER1 end,
friend_id
HAVING COUNT (*) =2
OR COUNT(case when friend_id =USER1 then true end)=1
) q1
regards,
Marc Mamin
>Von: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org]"
>im Auftrag von "David Johnston [david.g.johns...@gmail.com]
>Gesendet: Freitag, 6. Februar 2015 00:38
>An: Tim Smith
>Cc: Adrian Klaver; pgsql-general
>Betreff: Re: [GENERAL] Using row_to_json with %ROWTYPE ?
>On Thu,
rying on the local time, the planner will consider all partitions, but an
additional index or constraint on this column should be sufficient as long as
your partition count remains small.
regards,
Marc Mamin
ed
>knowledge about Postgres, I don't think we have those in pg. Can someone
>tell me how to convert it? Thanks.
Hi,
it seems that you are looking for UPDATE RETURNING:
http://www.postgresql.org/docs/9.4/static/sql-update.html
regards,
Marc Mamin
--
Sent via pgsql-general mailing list
being that users are less isolated (everybody can read the catalog).
regards,
Marc Mamin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
> postgres=# select concat('[', now()::date, ',', now()::date,
> ']')::daterange testrange;
There are range specific functions for this:
select daterange(now()::date, now()::date, '[]')
regards,
Marc Mamin
__
pends
-
(TR,_efm_sequences,_efm_sequences_tr)
(TR,_emr_reportlist,_emr_reportlist_tr)
(TR,_efm_models,_efm_models_del_tr)
regards,
Marc Mamin
pg_logs', t)).* FROM
get_modeltablelist('efeeds') t
^ ^
Marc
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Marc Mamin
Sent: Freitag, 1. März 2013 13:51
To: pgsql-general@postgresql.org
Subject: [
time
zone.
The function below works, but is slow.
Any way to build an equivalent function with better performances ?
Thanks,
Marc Mamin
CREATE FUNCTION houroffset_to_daystart (p_houroffset int, p_tz varchar) returns
int AS
$$
DECLARE daystart int;
BEGIN
EXECUTE 'select EXTRACT ('
2 as a,
3 as b
)
select * from t1 WHERE b =0
ERROR: column reference "b" is ambiguous.
It would be nice, if extra undeclared columns would not be visible outside the
CTE.
regards,
Marc Mamin
n as I don't need it...
Maybe someone can also point me to some documentation about writing my
own dummy dictionnary?
(I'm currently experimenting with 8.3.6, but we will probably implement
such functionalities only with 8.4)
Thanks,
Marc Mamin
--
Sent via pgsql-general mailing lis
Hello,
I've found a dirty hack with custom GUC variables here:
http://archives.postgresql.org/pgsql-hackers/2008-11/msg00643.php
Although dirty, it seems that it beats the windowing performances of
8.4.
So I wonder if there are any concern about this...
Cheers,
Marc Mamin
--
Sen
Hello,
it seems that I should reduce the Fill Factor of some FTS indexes, but
what is the default ?
The doc days:
> The other index methods use fillfactor in different but roughly
analogous ways;
> the default fillfactor varies between methods
Thanks,
Marc Mamin
nds, thought
I guess this is feature request...
best regards,
Marc Mamin
> How about disabling cycling and doing your own monitoring?
> It seems a bit fiddly because sequence values are created "outside"
> of any transaction and hence if you write something into a logging
> table you're going to loose it if the transaction rolls back.
The sequences are expected to
port function
>would be sufficient for me.
Hello,
Have a look at pghashlib:
https://github.com/markokr/pghashlib
regards,
Marc Mamin
hi,
in addition to the others comments, you can also remove " ELSE 0 " from your
query.
It will result in values that are discarded by SUM.
regards,
Marc Mamin
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of jane...@web.de
Sent:
Hi,
"this is not an elephant"
(to fake Magritt)
http://enculturation.gmu.edu/3_2/images/magritte1.jpg
or an elephant breast-feeding its babies to illustrate replication.
but better ask someone else for the painting ;-)
regards,
Marc Mamin
> -Original Message-
> Fro
1 - 100 of 156 matches
Mail list logo