[GENERAL] A possible use case for: "INSERT .. ON CONFLICT DO SELECT [FOR ..]"

erial_test as t (uniq_text) values('t2') on conflict (uniq_text) do update set uniq_text = t.uniq_text returning *; id | uniq_text +--- 4 | t2 (1 row) That works. But it is a bit inconvenient to write the pseudo update clause. Regards, Marc-Olaf Jaschke -

Re: [GENERAL] Keycloak and Postgres

day, April 1, 2017 12:57 PM An: Marc Tempelmeier Cc: pgsql-general@postgresql.org Betreff: Re: [GENERAL] Keycloak and Postgres On Thu, 30 Mar 2017 13:58:36 + Marc Tempelmeier wrote: > Hi, > > I have a replication question, we have some big Cisco UCS VM thingy, where > VMs are s

[GENERAL] Keycloak and Postgres

do it because of other failures? Best regards Marc

Re: [GENERAL] pg_dump and quoted identifiers

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

Re: [GENERAL] is this a known bug in 9.6?

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

Re: [GENERAL] Migrating data from DB2 zOS to PostgreSQL

Please, file these as issues on github (i'm the author). It will be much easier to solve your problems. Regards On 06/12/2016 11:42, Sameer Kumar wrote: > > > On Tue, 6 Dec 2016, 9:27 p.m. Swapnil Vaze, > wrote: > > > Hello Julien, > > We created DDLs fr

[GENERAL] row => text => row

rows as text,but in their (default) csv representation: select to_csv((c.*)) from foo c; regards, Marc Mamin

Re: [GENERAL] Uber migrated from Postgres to MySQL

ging the binaries … downgrading an older ‘major release’ requires a dump/reload … Unless I’m missing something, whether on PostgreSQL or MySQL, if you want to go back a major release, you would need to dump./ reload that 1TB database … -- Marc G Fournier

Re: [GENERAL] Uber migrated from Postgres to MySQL

also mean removing the requirement for ‘double the disk space’ to do the upgrade … I don’t have a 1TB database to try it on, mind you, so your ‘wait couple of days’ might be *with* the —link option? -- Marc G Fournier http://www.2ndQuadrant.com <http://www.2ndquadrant.com/> PostgreSQL Development, 24x7 Support, RemoteDBA, Training & Services

Re: [GENERAL] Corrupted Dabatabase

index" command p Pau Marc Muñoz Torres skype: pau_marc http://www.linkedin.com/in/paumarc http://www.researchgate.net/profile/Pau_Marc_Torres3/info/ 2016-06-27 16:21 GMT+02:00 Sterpu Victor : > You probably have a HDD problem. > Try a "cat /proc/mounts" and see if part

Re: [GENERAL] Corrupted Dabatabase

was indexing the database. the problem came when i tried to cancel a \di command as far as i know nothing changed since the last time i was working last week. p Pau Marc Muñoz Torres skype: pau_marc http://www.linkedin.com/in/paumarc http://www.researchgate.net/profile/Pau_Marc_Torres3/info

[GENERAL] Corrupted Dabatabase

uld like to remove all databases in postgresql and create everything again from scratch. Unfortunatlly i can't see the list of databases, althought i am able to create databse can you suggest what to do? is it a good idea to reinstall postgresql? Pau Marc Muñoz Torres skype: pau_m

Re: [GENERAL] postgres_fdw and Kerberos authentication

an, which is considered a security breach by auditors. Storing a password in plain text even for a short period of time is unfortunately not authorized. Thanks! Jean-Marc Lessard Administrateur de base de données / Database Administrator Ultra Electronics Forensic Technology Inc.

[GENERAL] postgres_fdw and Kerberos authentication

t to server "dcx1-006-jml" DETAIL: FATAL: SSPI authentication failed for user "jml" Am I doing something wrong or postgres_fdw does not support Kerberos authentication? Is there any plan to support Kerberos authentication? Jean-Marc Lessard Administrateur de base de donn?es /

Re: [GENERAL] revert function for to_json ?

>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

[GENERAL] revert function for to_json ?

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

Re: [GENERAL] Thoughts on "Love Your Database"

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

Re: [GENERAL] Do parallel queries work with only dblink not with fdw?

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

Re: [GENERAL] 9.5 - Is there any way to disable automatic rollback?

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

Re: [GENERAL] execute same query only one time?

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

Re: [GENERAL] How do I implement a .XSD in Postgres?

run using psql. Unfortunately, I have not gotten around to documenting the xml schema for skit, but if you run the regression tests you should be able to get enough examples to figure it out. If you want to give this a try, you can email me off list and I'll do what I can to help. __ Marc

Re: [GENERAL] Unique index problem

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

Re: [GENERAL] Fetching from psql procedures

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

[GENERAL] Fetching from psql procedures

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

Re: [GENERAL] regexp_replace to remove sql comments

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', '\/\

Re: [GENERAL] Duplicate rows during pg_dump

> -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

Re: [GENERAL] Duplicate rows during pg_dump

>>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) >>

Re: [GENERAL] Best practices for aggregate table design

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

Re: [GENERAL] clone_schema function

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(),

Re: [GENERAL] clone_schema function

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

Re: [GENERAL] Any thoughts on a better approach to this query?

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

[GENERAL] PostgreSQL Developer Best Practices

in the standard, someone will eventually create tables with names that don't gel with everything else. __ Marc -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [pgsql-general] Daily digest v1.13732 (15 messages)

ersonal preference and works for me, your mileage may vary. __ Marc -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Drop down in connect time between 9.3.6 and 9.3.9 ?

$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

Re: [GENERAL] Creating table with data from a join

> 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 lar

Re: [GENERAL] Index Only Scan vs Cache

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. > > >--

Re: [GENERAL] Creating table with data from a join

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

Re: [GENERAL] Problem with ALTER TYPE, Indexes and cast

> -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

[GENERAL] Problem with ALTER TYPE, Indexes and cast

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

[GENERAL] next postgres version on Amazon RDS ?

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

Re: [GENERAL] Slow index performance

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

Re: [GENERAL] Systemd vs logging collector

combinations of # stderr, csvlog, syslog, and eventlog, # depending on platform. csvlog # requires logging_collector to be on. regards, Marc Mamin > > -- > Sent via pg

Re: [GENERAL] how to extract the page "address" from the ctid

> -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

[GENERAL] how to extract the page "address" from the ctid

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

Re: [GENERAL] Counting the occurences of a substring within a very large text

>>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

Re: [GENERAL] Counting the occurences of a substring within a very large text

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

Re: [GENERAL] Counting the occurences of a substring within a very large text

> -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, > >

Re: [GENERAL] Counting the occurences of a substring within a very large text

> -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: > >

[GENERAL] Counting the occurences of a substring within a very large text

, '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

Re: [GENERAL] select count(*);

> -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

Re: [GENERAL] select count(*);

>>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

Re: [GENERAL] select count(*);

t: > > I think that if we accept that > > chris=> select 'foo'; > ?column? > -- > foo > (1 row) > > returns 1 row, then naturally > > chris=> select count('foo'); > count > --- > 1 > (1 row) > > sh

[GENERAL] select count(*);

, 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

Re: [GENERAL] Row visibility issue with consecutive triggers, one being DEFERRED

> -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

[GENERAL] Re: Row visibility issue with consecutive triggers, one being DEFERRED

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

Re: [GENERAL] pg_relation_size performance issue

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&#

[GENERAL] Row visibility issue with consecutive triggers, one being DEFERRED

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

Re: [GENERAL] Restarting DB after moving to another drive

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

Re: [GENERAL] PGFoundry Sample databases (particularly world)?

Fixed .. .let me know if there are any other issues … > On May 5, 2015, at 09:55, William Dunn wrote: > > PgFoundry.org went down some months ago, I contacted webmaster Marc Fournier > and he was able to get it back up but a lot of it no longer works and I don't > thin

Re: [GENERAL] Invalid memory alloc

Can I change the segment size to allow more memory? Is it a good idea? The concerned function work only on the entire table then I can't process a part of it. Should I split the table in multiple table and merge them after the process? Thanks Marc -Message d'origine- De :

Re: [GENERAL] Invalid memory alloc

| 1GB shared_buffers | 10GB work_mem| 256MB I use Postgis and PGrouting extension. The error come when I use a pgrouting function pgr_createtopology() Marc Marc-André Goderre Analyste en informatique Courriel: magode

Re: [GENERAL] Invalid memory alloc

Postgresql is running on a 30 Gb of memory server. I should have access to the server in few minutes and I'll give you more information about the other spec. Thanks Marc -Message d'origine- De : pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org]

[GENERAL] Invalid memory alloc

142645362  pgr_createtopology   FAIL (1 row) The server has a 10Gb of shared_buffer. Do you thing this quantity of memory allowed should normaly be enough to process the data? Thanks Marc -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your su

Re: [GENERAL] function returning a merge of the same query executed X time

cm_get_loop_route_4(2, 10, -73.597070, 45.544083))r)q CROSS JOIN generate_series(1, 3) the quey is executed only 1 time. Thanks Marc De : gwinkl...@gmail.com [mailto:gwinkl...@gmail.com] De la part de Geoff Winkless Envoyé : 22 avril 2015 11:22 À : Marc-André Goderre Cc : 'pgsql-general@postgresql.org&#x

[GENERAL] function returning a merge of the same query executed X time

ber() over() as id, sum(cost) as total_cost,sum(length) as total_length,json_agg(row_to_json(r)) as data from (select * from cm_get_loop_route_4(2, 10, -73.597070, 45.544083))r)q return next jsona end loop; return jsona; Marc -- Sent via pgsql-general mailing l

[GENERAL] Using array_agg in pgr_kdisjkstrpath() error

::integer[]  as id from n2) AND '{28411,25582}' Thanks Marc -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Using array_agg in pgr_kdisjkstrpath()

::integer[] as id from n2) AND '{28411,25582}' Thanks Marc-André Marc-André Goderre Analyste en informatique [LOGO-FINAL-1_Mini.png] Courriel: magode...@cgq.qc.ca<mailto:magode...@cgq.qc.ca> Tel.: (418) 698-5995 poste 1628 Téléc.: (418) 698-4108 Cégep de Chicoutimi, 534 rue Jacques-C

Re: [GENERAL] :Posgres - performance problem

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

Re: [GENERAL] Group by range in hour of day

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

Re: [GENERAL] Group by range in hour of day

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

Re: [GENERAL] Slow query with join

Update : My query SELECT * FROM v_actor JOIN f_intervenant_ref ON (actor_id = ir_actor_id) WHERE ir_dos_id = '5226' took 7 secs. If I substitute the _RETURN rule for the view and add the above join, it takes 31 ms. Mark Watson Service au client - R&D Tél. 418 659-7272 ou 1 888 692-1050 www.j

Re: [GENERAL] Slow query with join

>-Message d'origine- >De : Tom Lane [mailto:t...@sss.pgh.pa.us] >Envoyé : March-16-15 5:07 PM >À : Tomas Vondra >Cc : pgsql-general@postgresql.org; Marc Watson >Objet : Re: [GENERAL] Slow query with join > >Tomas Vondra writes: >> On 16.3.2015 19:50, Ma

[GENERAL] Slow query with join

Hello all, I am using PostgreSQL 9.4.1, compiled by Visual C++ build 1800, 64-bit, as downloaded from EnterpriseDB, and is running on my dev system under Win 7 64-bit. I hope someone can help me with a problem I'm having when joining a view with a table. The view is somewhat involved, but I can

Re: [GENERAL] range type expression syntax

> 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 __

Re: [GENERAL] Postgres architecture for multiple instances

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

Re: [GENERAL] how to convert "output deleted/inserted into" in My SQL to Postgres

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

Re: [GENERAL] Partioning with overlapping and non overlapping constraints

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

Re: [GENERAL] Using row_to_json with %ROWTYPE ?

>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,

Re: [GENERAL] Combining two queries

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

Re: [GENERAL] Combining two queries

HAVING COUNT (*) =2 OR COUNT(case when friend_id =USER1 then true end)=1 ) q1 regards, Marc Mamin

Re: [GENERAL] Removing duplicate records from a bulk upload (rationale behind selecting a method)

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- >

Re: [GENERAL] Removing duplicate records from a bulk upload (rationale behind selecting a method)

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

Re: [GENERAL] faster way to calculate top "tags" for a "resource" based on a column

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

[GENERAL] Postgresql out of memory during big transaction

Trying to debug an out of memory error with Postgresql. Simple: * Open Connection * begin transaction * trying to import about 20GBytes of data (40K rows + rest large image blob's) * end transaction * Close Connection What I notice is that the python app stays around 200Mbytes of memory us

Re: [GENERAL] Way to identify the current session's temp tables within pg_class ?

> -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

[GENERAL] Way to identify the current session's temp tables within pg_class ?

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

Re: [GENERAL] understanding why two nearly identical queries take two different planner routes, one 5s and one 2hr

= '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

Re: [GENERAL] How can I create null value from function call with no results?

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

Re: [GENERAL] copy/dump database to text/csv files

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

Re: [GENERAL] Index usage with slow query

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_

Re: [GENERAL] php password authentication failed for user ...

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

FW: [GENERAL] operator is not unique: smallint[] @> smallint[] You might need to add explicit type casts (!)

> -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 (!) >

[GENERAL] operator is not unique: smallint[] @> smallint[] You might need to add explicit type casts (!)

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

Re: [GENERAL] BAKUP ISSUE

De : pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] De la part de hubert depesz lubaczewski Envoyé : July-09-14 9:55 AM On Wed, Jul 9, 2014 at 3:28 PM, Ramesh T mailto:rameshparnandit...@gmail.com>> wrote: Yes,not an error it is a warning and archive is wo

Re: [GENERAL] log_collector & sysout on windows

sys.stdout = sys.stderr at the start of entry python script, and all was well after that. On Wed, May 28, 2014 at 1:30 PM, Marc Brazeau wrote: > So I'm sure I'm doing something wrong, or this is maybe a novice question, > > On My Mac, I set log_collector=on, archive_command='

[GENERAL] log_collector & sysout on windows

So I'm sure I'm doing something wrong, or this is maybe a novice question, On My Mac, I set log_collector=on, archive_command='echo hello %p %f' And awesome, my postgres log files get the output from my archive command. On Windows, this does not work (Using postgres 9.3..4) Only way to get outpu

Re: [GENERAL] Could use some advice on search architecture

s do match the search. basically a standard approach would look like: SELECT pID, sum(match) as matches FROM ( selct pID, 1 as match from Products where color ='pink' UNION ALL selct pID, 1 as match from Products where size ='XXL'

Re: [GENERAL] Could use some advice on search architecture

Hi, Seems that this blog post is worth reading in your case http://hlinnaka.iki.fi/2014/03/28/gin-as-a-substitute-for-bitmap-indexes/ regards, Marc Mamin Von: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org]" im Auftrag von &

[GENERAL] pljava on postgres 9.3, mac & windows

Is pljava totally dead? Is anyone still contributing to this? Buildling pljava is becoming increasingly difficult and impossible. We're looking into converting our Oracle base to psql, but this is a real obstacle for us as we've got a number of oracle java stored procedures, and would truly pref

[GENERAL] anyrecord/anyelement escaping question.

ion myescape(a anyelement, OUT escaped text) as $$ .. select case when typeof(a) = then when typeof(a) = then when typeof(a) = then when typeof(a) = then .. regards, Marc Mamin

  1   2   3   4   5   6   7   8   >