Re: [GENERAL] PG 10 - Trouble with overlap of range partition of two dimensions

On Nov 1, 2017 02:41, "Stephen Froehlich" wrote: Hi Michael, So if I'm reading this correctly, the proper way to do my use case is to use partitions of partitions, right? Or maybe reverse the order of the columns: PARTITION BY RANGE (source_no, start_time)

Re: [GENERAL] regexp_matches where clause - PG 9.1

On Apr 7, 2017 4:16 AM, "Patrick B" wrote: 2017-04-07 14:08 GMT+12:00 David G. Johnston : > On Thu, Apr 6, 2017 at 6:33 PM, Patrick B > wrote: > >> When actually I just want the 'main'' >> > > ​SELECT * FROM tbl

Re: [GENERAL] regexp_matches where clause - PG 9.1

On Apr 7, 2017 4:08 AM, "David G. Johnston" wrote: On Thu, Apr 6, 2017 at 6:33 PM, Patrick B wrote: > When actually I just want the 'main'' > ​SELECT * FROM tbl WHERE path_name ~ '/main$' ? David J. ​ Or just: SELECT 'main';

Re: [GENERAL] regexp_matches where clause - PG 9.1

On Apr 6, 2017 05:57, "Patrick B" wrote: Hi guys, i've got this column: path_name character varying(255) > I store full S3 bucket path for the attachments of my application on it; example: /{s3bucket}/filesuser/client/27801123/attachment/4510/main >

Re: [GENERAL] Foreign key references a unique index instead of a primary key

On Feb 23, 2017 12:42 PM, "Ivan Voras" wrote: Hello, I've inherited a situation where: - a table has both a primary key and a unique index on the same field. - at some time, a foreign key was added which references this table (actually, I'm not sure about the

Re: [GENERAL] Fwd: Query parameter types not recognized

On Feb 10, 2017 8:11 PM, "Roberto Balarezo" wrote: Hi, I would like to know why this is happening and some advice if there is a way to solve this problem: I have a query like this: select COALESCE(duedate, ? + 1) from invoices order by duedate desc limit 10; where ? is a

Re: [GENERAL] Converting a TimestampTz into a C# DateTime

On Nov 14, 2016 12:53, "valeriof" wrote: > > Hi, > I'm handling a TimestampTz value inside a plugin to stream WAL changes to a > .NET client application. What I'm trying to do is to return all possible > column changes as binary (don't like to have Postgres handle

Re: [GENERAL] xml-file as foreign table?

On Thu, May 5, 2016 at 2:13 PM, Johann Spies <johann.sp...@gmail.com> wrote: > Dankie Arjen, > > On 29 April 2016 at 07:01, Arjen Nienhuis <a.g.nienh...@gmail.com> wrote: > >> >> > The options I am considering is : >> > >> > 1. Unpack th

Re: [GENERAL] xml-file as foreign table?

On Apr 28, 2016 14:33, "Johann Spies" wrote: > > I have several large (7GB+) xml files to get into an SQL database. > > The xml-files can contain up to 500 000 subrecords which I want to be able to query in the database. > > They are too large to do something like this: >

Re: [GENERAL] substring on bit(n) and bytea types is slow

On Feb 29, 2016 22:26, "Evgeny Morozov" < evgeny.morozov+list+pg...@shift-technology.com> wrote > SELECT substring(bitarray from (32 * (n - 1) + 1) for 32) -- bitarray is a column of type bit(6400) > FROM array_test_bit > JOIN generate_series(1, 1) n ON true; Substring on a bit string is

Re: [GENERAL] Asp.net 5 and EF6

On Thu, Feb 4, 2016 at 5:46 PM, FarjadFarid(ChkNet) wrote: > > > Hi I am trying to use certain features of Asp.net 5 using postgresql using > EF6. > I think you can use EF6, but not with .NET Core. It's possible to use Aap.net 5 without .NET Core. > > > I have

Re: [GENERAL] Large Binary Columns - Slow Query

On Jan 10, 2016 21:50, "Saulo Merlo" wrote: > > The binary columns are large so I think that's why the query referencing them is slow. > > PostgreSQL 9.2 > > Is there a way to speed it up, maybe compression on transfer? Some indexes has millions of rows... > > Maybe create

Re: [GENERAL] grep -f keyword data query

On Jan 4, 2016 09:45, "Hiroyuki Sato" <hiroys...@gmail.com> wrote: > > Hello Arjen > > Thank you for replying. > > 2016年1月4日(月) 16:49 Arjen Nienhuis <a.g.nienh...@gmail.com>: >> >> >> On Dec 28, 2015 00:55, "Hiroyuki Sat

Re: [GENERAL] grep -f keyword data query

On Dec 28, 2015 00:55, "Hiroyuki Sato" wrote: > > Hello Andreas and Tom > > Thank you for replying. > > Sorry, I re-created my questions. I was mis-pasted query log on previous question. > (@~ operator is PGroonga extension (http://pgroonga.github.io)) > Please ignore it. > >

Re: [GENERAL] Allowing postgresql to accept 0xff syntax for data types that it makes sense for?

On Fri, May 22, 2015 at 6:57 PM, Bill Moran wmo...@potentialtech.com wrote: On Fri, 22 May 2015 12:44:40 -0400 Tom Lane t...@sss.pgh.pa.us wrote: Bill Moran wmo...@potentialtech.com writes: Tom Lane t...@sss.pgh.pa.us wrote: Other questions you'd have to think about: what is the data type

Re: [GENERAL] Some indexing advice for a Postgres newbie, please?

Hi, On 19 Feb 2015 17:12, brian br...@meadows.pair.com wrote: Hi folks, I have a single-user application which is growing beyond the fixed-format data files in which it currently holds its data, I need a proper database as the backend. The front end is written using Lazarus and FreePascal

Re: [GENERAL] ENUM drop label workaround

On Sep 26, 2013 9:20 PM, Sergey Konoplev gray...@gmail.com wrote: Hi, AIU we have no ability to drop a label from ENUM currently, and there are no plans to add this feature in the nearest future. I came to a workaround via DOMAIN, here it is: CREATE TYPE ref AS ENUM ('aaa', 'bbb');

Re: [GENERAL] How to switch file systems with least downtime?

On Sat, Sep 14, 2013 at 2:32 PM, Moshe Jacobson mo...@neadwerx.com wrote: How do I migrate my 9.1 directory to a new file system with the least downtime possible? Create a new tablespace on a the new filesystem and move everything over. -- Sent via pgsql-general mailing list

Re: [GENERAL] numeric to ipv6 inet

On Aug 23, 2013 11:58 AM, Mikhail Puzanov misha.puza...@gmail.com wrote: Hello, I need to convert the set of ipv6 addresses stored as numerics (by historical reasons mostly) to inet type. Something like '0:0:0:0:0:0:0:0'::inet + 55831599345971591062080247067748335616::bigint apparently

Re: [GENERAL] PostgresQL 9.2 table query - underscores

On Wed, Jul 17, 2013 at 12:04 AM, Victoria S. 1...@victoriasjourney.com wrote: Hello: My first post; a Postgres newbie ... I am teaching myself PostgresQL using a trial database, and I am having trouble with underscores: IN the following example, development=# SELECT created_at, username

Re: [GENERAL] Postgres case insensitive searches

On Jun 30, 2013 7:07 PM, bhanu udaya udayabhanu1...@hotmail.com wrote: I almost used every option ; upper, posix, gist, gin, citext, etc. feature of the postgres to get the query most optimal.. If a particular query is taking 1 + second for one user/thread, then for many users accessing it

Re: [GENERAL] intagg

On Thu, Jun 20, 2013 at 12:22 AM, Andrew Bartley ambart...@gmail.com wrote: Sorry that should be aggregate int_array_aggregate not function On 20 June 2013 08:16, Andrew Bartley ambart...@gmail.com wrote: Hi All, I am trying to use the intagg extension. in 9.1.9 I have created the

Re: [GENERAL] create a script which imports csv data

On Thu, Jun 28, 2012 at 2:12 PM, Berend Tober bto...@broadstripe.net wrote: Robert Buckley wrote: I have to create a script which imports csv data into postgresql The csv data is automatically created from an external database so I have no influence over which columns etc are downloaded.

Re: [GENERAL] 7.4 quoting

On Thu, Mar 31, 2011 at 18:56, Steve Clark scl...@netwolves.com wrote: Hi List, I am having a problem trying to do the following: ssh postgres@192.168.198.93 'psql -Atc select a.interface, a.source_ip,a.dest_ip from kernel_gre a, ospfd_interface b where a.interface = b.interface and config

Re: [GENERAL] 7.4 quoting

On Thu, Mar 31, 2011 at 20:05, Arjen Nienhuis a.g.nienh...@gmail.com wrote: On Thu, Mar 31, 2011 at 18:56, Steve Clark scl...@netwolves.com wrote: Hi List, I am having a problem trying to do the following: ssh postgres@192.168.198.93 'psql -Atc select a.interface, a.source_ip,a.dest_ip from

Re: [GENERAL] Question regarding custom parser

You can create an index on to_tsvector(replace(foo, '-', ' ')) and then search using ...match..(replace(foo, ...), ...) On Mon, Oct 4, 2010 at 11:41 AM, Arthur van der Wal arthurvander...@gmail.com wrote: Hi, I want to change the way PostgreSQL splits text into tokens, for example:

Re: [GENERAL] Query to get the next available unique suffix for a name

4) Do a SELECT on each row that starts with MikeChristensen and then trying to append the row count to the end, this might not be exact but it's somewhat intelligent as a starting point.  However, this might require some special indexes on this table to quickly scan rows that start with a

Re: [GENERAL] varchar lengths

On Tue, Sep 21, 2010 at 1:23 PM, Massa, Harald Armin c...@ghum.de wrote: I recommend to use TEXT as type for that kind of columns. 99 out of 100 theories about this value will never be longer then xx characters fail in the long run. And text, limited only by PostgreSQLs limits, performs as

Re: [GENERAL] unintuitive subquery record wrapping

On Sat, Sep 18, 2010 at 9:31 AM, Rikard Pavelic rikard.pave...@zg.htnet.hr wrote: On 18.9.2010 4:31, Tom Lane wrote: Rikard Pavelic rikard.pave...@zg.htnet.hr writes: For basic query: select t from t result is of type t. yeah ... If I query: select sq from (select t from t) sq; result

Re: [GENERAL] unintuitive subquery record wrapping

On Sat, Sep 18, 2010 at 12:40 PM, Rikard Pavelic rikard.pave...@zg.htnet.hr wrote: On 18.9.2010 11:36, Arjen Nienhuis wrote: I'm not sure what you want but maybe it's this: = select * from (select t from t) sq;    t ---  (1,x) (1 row) = select (sq.t).* from (select t from t) sq

Re: [GENERAL] Using libpq, floats and binary data

Hi, Inserting many of rows is almost always IO bound. Converting ints and floats to text is CPU bound and really fast anyway. To speed things up first look at things like indexes, how often you need to COMMIT or using COPY. Only then look at prepared statements and binary transfer modes. Else

Re: [GENERAL] Using libpq, floats and binary data

On Thu, Sep 16, 2010 at 12:37 AM, Merlin Moncure mmonc...@gmail.com wrote: On Wed, Sep 15, 2010 at 6:18 PM, Arjen Nienhuis a.g.nienh...@gmail.com wrote: Hi, Inserting many of rows is almost always IO bound. Converting ints and floats to text is CPU bound and really fast anyway. To speed

Re: [GENERAL] Search then Delete Performance

. Met vriendelijke groet, Arjen Nienhuis On Wed, Sep 15, 2010 at 2:55 AM, Michael Hull mikehul...@googlemail.com wrote: Hi Everyone, I am fairly new to practical databases, but I am trying out the c interface to postgres and am wondering how to improve performance. I am a researcher, and I am

Re: [GENERAL] Forcing the right queryplan

Maybe you can extract stuff like IP addresses and words like 'error' and put it in a separate column in the table. Full text search is not a solution for data that is in a wrong format. On Fri, Sep 10, 2010 at 10:27 AM, Henk van Lingen h.g.k.vanlin...@uu.nl wrote: On Thu, Sep 09, 2010 at

Re: [GENERAL] Post Install / Secure PostgreSQL

   su - postgres    psql -l If you didn't set a password for the postgres user it's actually: sudo su - postgres -- 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] postgres patch for autovacuum error in postgres

Maybe you can download the latest version of postgres 8.1 and change the version number in the source to 8.1.2 and then compile. Would that solve your problem? On Thu, Sep 9, 2010 at 10:38 PM, tamanna madaan tamanna.ma...@globallogic.com wrote: Hi All I am using postgres-8.1.2 . I am getting

Re: [GENERAL] Strange/random overload of the server

issues again. I found that file system have errors. Can this lead to this kind of behavior ? Yes. Your disk might be broken. Check the SMART status. Groeten, Arjen Nienhuis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

[GENERAL] How to defer ON DELETE CASCADE

Hi, If I create a DEFERRED ON DELETE CASCADE constraint, it doesn't really work as I expected. I expected it to defer the deletion to the end of the transaction, but it dosn't. Is there a way to replace the contents of a table which has foreign keys? There's no MERGE/UPSERT/whatever either.

Re: [GENERAL] How to convert a binary filed to an integer field?

an error message about unable to convert boolean to integer. I have a look at the case statement for the conversion, but don't understand how it works. - Original Message - From: Arjen Nienhuis a.g.nienh...@gmail.com To: wei...@lycos.com Cc: pgsql-general@postgresql.org Sent: Thursday

Re: [GENERAL] Using FULLTEXT search with different weights for various fields

The simple answer is this: SELECT R.RecipeId, R.Title, R.Description, R.ImageUrl, R.Rating, setweight(to_tsvector(title), 'A') || setweight(to_tsvector(coalesce(description, '')), 'B') || setweight(to_tsvector(coalesce(steps, '')), 'C') as vector, ts_rank_cd(

Re: [GENERAL] How to convert a binary filed to an integer field?

On Wed, Aug 25, 2010 at 9:29 PM, wei...@lycos.com wrote: I have an application in the product. Now, one status field needs to have three statuses instead of two. How to make a such change in PostgreSQL? ALTER TABLE product ALTER status TYPE int USING status::integer;

Re: [GENERAL] Tool to compare data between tables

http://phpsqldiff.sourceforge.net/ On Mon, Aug 23, 2010 at 4:32 PM, akp geek akpg...@gmail.com wrote: Hi all - Is there any open source tool available for postgres to compare data between 2 tables. Appreciate your help Regards

Re: [GENERAL] select from pipe-delimited field

On Tue, Aug 24, 2010 at 12:44 AM, san man neelakas...@gmail.com wrote: Hello all, I am trying to do a SELECT operation with a WHERE condition. However, the column with which I am trying to do the comparison has several values which are pipe-delimited. I want to return a match(true) if the

Re: [GENERAL] Donwload location for PostgreSQL version 8.2.7

On Mon, Nov 9, 2009 at 12:22 PM, Anand 1008an...@gmail.com wrote: Hi, I tried to download the PostgreSQL version 8.2.7 from the following location. http://www.postgresql.org/ftp/binary/v8.2.7/linux/rpms/redhat/rhel-5-i386/ But i am not able to download any files. Every link is showing the

Re: [GENERAL] Translate function and strange results ...

On Sun, Nov 1, 2009 at 4:56 PM, Hervé Piedvache bill.foot...@gmail.comwrote: base=# select translate('Hervé', 'é', 'e'); translate --- Herve (1 row) base=# select translate('Hervé', 'âàäéèêëïöôùüû', 'aaaioouuu'); translate --- Hervai (1 row) You are actually

Re: [GENERAL] Translate function and strange results ...

On Mon, Nov 2, 2009 at 12:07 AM, Hervé Piedvache bill.foot...@gmail.com wrote: Hi Tom, Thar's mean I need to convert my database in other enconding ? No you don't. The problem is with the encoding of the query: test=# \encoding SQL_ASCII test=# SELECT convert('Hervé', 'UTF-8', 'LATIN1');

Re: [GENERAL] Column Type Suggestions

On Thu, Oct 29, 2009 at 9:34 PM, Blake Starkenburg bl...@oldride.com wrote: I am in the midst of planning for a new database which will contain specific keywords relating to different products. For example a Microwave may have the keywords HOUSEHOLD - PANASONIC - CAROUSEL - CONVECTION - 2200

Re: [GENERAL] Add Space symbols for TSvector

preprocess text, for example, using replace(), regexp_replace() functions Oke, you mean at the time the insert in de db is done, i suppose. However, I'm using the new syslog-ng 3 feature which does the insert directly. So I have to change the syslogd for that. I understand the ts_search