Re: [GENERAL] SQL help - multiple aggregates

On Thu, Aug 18, 2016 at 4:56 AM, wrote:​ > select custid, count(vendid) as c415 from cv where vendid = 415 group by > custid > ​[...] > > Is there a better way (by creating an aggregate function, perhaps) > ​You may find crosstab in the tablefuncs extension to be of use. ​ https://www.postgres

Re: [GENERAL] Unique constraint on field inside composite type.

On Thu, Aug 18, 2016 at 2:02 AM, Silk Parrot wrote: > > However, the above create table query reports syntax error: > > ERROR: syntax error at or near "." > LINE 10: UNIQUE (google_user.email) > > Is there a way to create unique constraint on a field inside composite > type? > ​Not tested h

Re: [GENERAL] Forward declaration of table

On Tue, Aug 23, 2016 at 1:10 PM, Alexander Farber < alexander.far...@gmail.com> wrote: > > So my question is if I can somehow "forward declare" the words_moves table? > > ​A better way to phrase this is: Is it possible to create circular foreign key dependencies between tables? The answer is no.

Re: [GENERAL] Forward declaration of table

On Tue, Aug 23, 2016 at 1:24 PM, Adrian Klaver wrote: > > use ALTER TABLE ADD table_constraint : > > https://www.postgresql.org/docs/9.5/static/sql-altertable.html > > to add the FK references to word_games. > > ​Hadn't considered "ALTER TABLE" but I'd be afraid of dump-restore hazards here. May

Re: [GENERAL] Forward declaration of table

On Wed, Aug 24, 2016 at 4:27 PM, Alexander Farber < alexander.far...@gmail.com> wrote: > > Why aren't m.tiles and m.score returned please? > > ​How about you output g.mid1 and g.mid2 in the first query and confirm that the rows being returned from words_games actually have a value in the set {2,3,

Re: [GENERAL] Forward declaration of table

On Wed, Aug 24, 2016 at 4:38 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, Aug 24, 2016 at 4:27 PM, Alexander Farber < > alexander.far...@gmail.com> wrote: > >> >> Why aren't m.tiles and m.score returned please? >> >> >

Re: [GENERAL] Do function calls the cached?

On Tue, Aug 30, 2016 at 4:15 PM, Daniel Caldeweyher wrote: > > select x,y,z, (extract_keywords(l.*)).* > > ​[...]​ > Does this mean the function gets called three time? > ​Yes. ​ (​function_call(...)).*​ ​syntax is problematic. You should avoid it via one of two options. LATERAL (new way,

Re: [GENERAL] Dynamic query return

On Wed, Sep 14, 2016 at 2:00 AM, Mat Arye wrote: > I am leaning towards solution 3 for now. But it's kind of a hack. Is there > any better solution that I am missing? > ​You probably will want to look into using/returning a cursor - though I'm not that familiar with them myself. David J. ​

Re: [GENERAL] [HACKERS] temporary table vs array performance

Its considered bad form to post to multiple lists. Please pick the most relevant one - in this case I'd suggest -general. On Mon, Sep 26, 2016 at 8:39 AM, dby...@163.com wrote: > > Array is not convenient to use in function, whether > there are other methods can be replaced temp table in functi

Re: [GENERAL] [HACKERS] temporary table vs array performance

On Mon, Sep 26, 2016 at 9:18 AM, 邓彪 wrote: > we have to do dml in temp table,the CTE is not fit > > ​Moving this to -general only...​ ​Please direct all replies to the list. You are asking for help but not providing any context for what your requirements are. You are not likely to get good hel

Re: [GENERAL] bitwise storage and operations

On Mon, Sep 26, 2016 at 1:44 PM, Jonathan Vanasco wrote: > The documentation doesn't have any examples for SELECT for the bitwise > operators, Um... ​https://www.postgresql.org/docs/9.5/static/functions-bitstring.html​ SELECT B'111'::varbit & B'101'::varbit = B'101'::varbit ​SELECT 10::bit(8

Re: [GENERAL] bitwise storage and operations

​ ​Please include the list in all replies. On Mon, Sep 26, 2016 at 4:14 PM, Jonathan Vanasco wrote: > > On Sep 26, 2016, at 5:04 PM, David G. Johnston wrote: > > On Mon, Sep 26, 2016 at 1:44 PM, Jonathan Vanasco > wrote: > >> The documentation doesn't have an

Re: [GENERAL] Update two tables returning id from insert CTE Query

On Mon, Sep 26, 2016 at 9:06 PM, Patrick B wrote: > > I'm doing this now: > > > sel AS ( >> SELECT i.id AS c_id >> FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1) i >> JOIN rows s USING (rn) >> ) >> UPDATE table_2 SET c_id = >> ( >> SELECT c_id >> FROM sel >> ORDER

Re: [GENERAL] Update two tables returning id from insert CTE Query

On Tue, Sep 27, 2016 at 1:10 PM, Igor Neyman wrote: > > > Patrick, > > > > You need to explain your problems in more “coherent” way, David suggested > one. > > If you aren’t willing, people will stop responding to your request, they > are not obligated to read your mind. > > > ​I'll put it this

Re: [GENERAL] Update two tables returning id from insert CTE Query

On Tue, Sep 27, 2016 at 2:31 PM, Patrick B wrote: > 2016-09-28 10:25 GMT+13:00 Patrick B : > >> >> Actually I can't use name_last or name_first because some of the rows > have name_last/name_first = null > > I'm inserting more columns that I shown: > > CREATE TABLE >> public.not_monthly >>

Re: [GENERAL] executing os commands from a function

On Thu, Sep 29, 2016 at 2:41 PM, Armand Pirvu (home) wrote: > I used plsh extension but I had to use two functions and a trigger, see > code below > ​I don't see any way to not use a trigger given your requirement. And as written I don't see that you need the second function​ - just write the t

Re: [GENERAL] isnull() function in pgAdmin3

On Mon, Oct 3, 2016 at 6:39 AM, dudedoe01 wrote: > What is the most feasible way to emulate the below MySQL function into > postgreSQL. Since the isnull() function is no longer supported in 9.6 > version. I have tried every trick in the hat to get the desired results. > Still 'RPG INV' doesn't sh

[GENERAL] Make psql print number values right-aligned with locale group separator?

Take this as either a request for a pointer to how to accomplish $subject or a feature request. psql nicely prints number values right-aligned - but otherwise applies only a single decimal separator. Using to_char() to get the proper formatting is annoying and has downsides as well (mixing presen

Re: [GENERAL] functions - triggers cross schemas

On Thu, Oct 13, 2016 at 3:18 PM, Armand Pirvu (home) wrote: > 2 schemas , schema1 and schema2, with same tables , a base table and a > tracking one. > > > Search path is set to schema1 by default. > insert into schema2.test1 (col2 , col3) values ('foo1','foo2') > I get an entry in schema1.test1_h

Re: [GENERAL] Selecting records with highest timestamp - for a join

On Wed, Oct 19, 2016 at 11:35 AM, Alexander Farber < alexander.far...@gmail.com> wrote: > I.e. a user can have several records in the above table, but I always use > the most recent one (the one with the highest "stamp") to display that user > in my game. > ​And if the second most recent has a pi

Re: [GENERAL] Unnest an array in postgresql

On Thu, Oct 20, 2016 at 2:40 AM, Jaisingkar, Piyush < piyush.jaising...@nttdata.com> wrote: > Hello, > > > > > > I am trying to run following query in a function: > > > > > > CREATE TEMP TABLE temptable on commit drop as (Select * from > unnest(string_to_array(temp1,',')) as (rep_id int,install_up

Re: [GENERAL] Showing matrix with single query

On Thu, Oct 20, 2016 at 6:07 AM, Арсений Нестюк wrote: > Hello everyone. > > I'm trying to solve the following problem: > There's a table (row int, col int, val int) that represents some matrix. > The number of columns in the matrix is arbitrary. I need to show the matrix > in the usual form, wit

Re: [GENERAL] Limiting to sub-id in a query ?

On Wed, Oct 26, 2016 at 9:22 AM, Tim Smith wrote: > Hi, > > I've been trying various GROUP BY but these all end up erroring out, > so maybe I'm using the wrong tool for the job (or, more likely, the > required query is beyond my level of SQL-fu !). > > CREATE TABLE IF NOT EXISTS names ( > main_id

Re: [GENERAL] How to hint 2 coulms IS NOT DISTINCT FROM each other

On Fri, Oct 28, 2016 at 12:54 PM, Kim Rose Carlsen wrote: > ​​ > > CREATE VIEW view_circuit_with_status AS ( >SELECT r.*, > s.circuit_status, > s.customer_id AS s_customer_id, > p.line_speed, > p.customer_id AS p_customer_id > FROM view_circuit r >

Re: [GENERAL] SELECT DISTINCT ON removes results

On Fri, Oct 28, 2016 at 1:39 PM, Guyren Howe wrote: > Using 9.5, this query: > > SELECT o.id, >a.number AS awb > FROM pt.orders o > LEFT JOIN ( > SELECT DISTINCT ON ((string_agg(air_way_bills.number::text, > ','::text))) > string_agg(air_way_bills.numb

Re: [GENERAL] SELECT DISTINCT ON removes results

On Fri, Oct 28, 2016 at 2:17 PM, Guyren Howe wrote: > On Oct 28, 2016, at 14:15 , Guyren Howe wrote: > > > On Oct 28, 2016, at 13:50 , David G. Johnston > wrote: > > > ​On its face the statement "DISTINCT ON removes results" is not at all > surprisin

Re: [GENERAL] How to optimize SELECT query with multiple CASE statements?

On Mon, Oct 31, 2016 at 5:53 AM, Alexander Farber < alexander.far...@gmail.com> wrote: > Good afternoon, > > is it please posible to optimize the following SQL query with numerous > CASE statements (on same condition!) without switching to PL/pgSQL? > > SELECT > g.gid, > EXTRAC

Re: [GENERAL] Turning slave into a master - PostgreSQL 9.2

On Mon, Oct 31, 2016 at 1:46 PM, Patrick B wrote: > Hi guys, > > I got a test server, let's call it test01. > > The test01 has a basebackup from the master. > I want to turn test01 into a master. It doesn't need to catch up with the > wal_files, because I don't need it to be up-to-date. > > So wh

Re: [GENERAL] Must I create an index for child table if master table have one?

On Tue, Nov 8, 2016 at 11:08 AM, Edmundo Robles wrote: > Hi! > > i have a simple question, if the master table have an index, must i > have create the same index in a child table? > what is the best practice for indexes in table partition ? > Indexes are self-contained within the physi

Re: [GENERAL] Running on Docker, AWS with Data Stored on EBS

On Tue, Nov 8, 2016 at 12:48 PM, Ryan Mahoney wrote: > Hi All, > > TL;TR: Can a new PostgreSQL process, running on a different server > instance effectively resume operations by reading the same data directory > location as another PostgreSQL process that is no longer running? > In short - yes.

Re: [GENERAL] resolution order for foreign key actions?

On Tue, Nov 8, 2016 at 1:20 PM, Adrian Klaver wrote: > On 11/08/2016 12:08 PM, Karl Czajkowski wrote: > >> Hi, >> >> Is there a formal definition for the order in which constraint actions >> (i.e. the ON DELETE or ON UPDATE rules) are applied when there are >> multiple overlapping/relevant constr

Re: [GENERAL] Running on Docker, AWS with Data Stored on EBS

On Tue, Nov 8, 2016 at 1:41 PM, Ryan Mahoney wrote: > I'm so glad the use-case will work -- and sounds somewhat normative. > ​The program and the data are distinct things - which is why you can upgrade from say 9.5.1 to 9.5.3 by simply updating the program. Heck, a simple reboot of a typical ser

Re: [GENERAL] which work memory parameter is used for what?

On Wed, Nov 9, 2016 at 7:54 AM, Adrian Klaver wrote: > On 11/08/2016 06:45 PM, Andreas Joseph Krogh wrote: > > På onsdag 09. november 2016 kl. 03:02:54, skrev John R Pierce > > > > > > https://www.postgresql.org/docs/current/static/runtime- > config-resource.html > > > > > > maintenance_w

Re: [GENERAL] PLPGSQL returning number of rows

On Thu, Nov 10, 2016 at 5:44 PM, Patrick B wrote: > Hi guys, > > I'm writing a simple Plpgsql function to delete some data from different > tables. > > The function starts with a select, and then 2 deletes after that. > > How can I return the number of rows that each delete performed? > > ​The pl

Re: [GENERAL] Change column type from int to bigint - quickest way

On Wed, Nov 16, 2016 at 7:49 AM, Merlin Moncure wrote: > On Fri, Nov 11, 2016 at 9:30 AM, Andreas Brandl > wrote: > > You just posted the same question a few days ago -- were the answers > there unsatisfactory? > ​This seems to be a mail system provoked duplicate since Gmail is telling me this

Re: [GENERAL] pg_class (system) table increasing size.

On Wed, Nov 16, 2016 at 7:30 PM, dhaval jaiswal wrote: > PostgreSQL 9.4.0 > ​Are generalizing here or are you really running ​2+ year old patch version? > Why pg_class table is getting bigger in size. > ​Because you are creating (specific) objects.​ See: https://www.postgresql.org/docs/9.6/stat

Re: [GENERAL] help with moving tablespace

On Thu, Nov 17, 2016 at 9:16 AM, wrote: > First, the above works only *most* of the time in our testing on multiple > servers. When it fails, it's because not everything was moved out of the > old tablespace and I don't understand why. An "ls $PGDATA/ourdb/PG*/" shows > files are still present. A

Re: [GENERAL] Avoiding double-counting in aggregates with more than one join?

On Fri, Nov 18, 2016 at 10:16 AM, Paul Jungwirth < p...@illuminatedcomputing.com> wrote: > But is there a better way? ​Nothing that would be more readable nor likely more performant. When performing aggregation it is necessary to limit the scope of the query to only whatever it is you are calcu

Re: [GENERAL] pg_class (system) table increasing size.

On Mon, Nov 21, 2016 at 10:34 AM, dhaval jaiswal wrote: > Due to business impact auto vacuum is off. > ​You won't get much sympathy if you turn this feature off. The question you should be pondering is how to properly configure ​it for your environment. ​pg_class gets bloated for the same reas

Re: [GENERAL] variable value in array_to_string

On Mon, Nov 21, 2016 at 3:09 PM, Armand Pirvu (home) wrote: > Hi > > Is there anyway I can pass a variable in the array_to_string function ? > > ​Yes, just like you can pass variables to any other function.​.. > CREATE OR REPLACE FUNCTION test1 () RETURNS void AS $$ > ​​ > DECLARE > foo text; >

Re: [GENERAL] variable value in array_to_string

Please don't top-post - it makes following the thread a lot harder. On Mon, Nov 21, 2016 at 4:15 PM, Armand Pirvu (home) wrote: > Played with unnest but not much luck > If you want help you will need to show your work - ideally with examples that can execute with meaningful data on an empty da

Re: [GENERAL] variable value in array_to_string

For what its worth: DO $$ DECLARE vresult text; begin EXECUTE $qry$ WITH cols (c) AS ( VALUES ('col1'),('col2') ) SELECT string_agg('a.' || cols.c, ',') FROM cols WHERE 'foo' = $1 $qry$ USING 'foo' INTO vresult; RAISE NOTICE '%', vresult; END; $$; I still haven't actually figured out what

Re: [GENERAL] Postgresql 9.5 and Shell scripts/variables vs. C programming/defining a value to be used

On Wed, Nov 23, 2016 at 3:46 AM, Poul Kristensen wrote: > In the postgresql.log I get "select datname from $1". > ​That is expected when you parameterize a query - the query string includes parameter​s instead of values. What you are seeing is the "Prepare" component of a "prepare-execute". Wh

Re: [GENERAL] Wal files - Question | Postgres 9.2

On Mon, Nov 28, 2016 at 8:22 PM, Patrick B wrote: > > Ho > ​[w] > is that even possible?? I don't understand! > > ​https://www.postgresql.org/docs/9.2/static/warm-standby.html """​ If you use streaming replication without file-based continuous archiving, you have to set wal_keep_segments in the

Re: [GENERAL] About the MONEY type

On Wed, Nov 30, 2016 at 6:43 AM, Raymond O'Donnell wrote: > > I seem to remember that it was actually deprecated at some point - this is > going back quite a few years. This was later reversed, though I don't know > why. > > ​Because its pointless to deprecate something that you haven't replaced

Re: [GENERAL] select function alias

On Wed, Nov 30, 2016 at 8:15 AM, bto...@computer.org wrote: > 5. Use a CTE: > > with shortnames as ( > select to_char(impressions_create_date,'-mm-dd') as ymd from > impressionsdb > ) select ymd from shortnames where ymd like '2016-11%'; > > ​Except that WHERE clause won't get pus

Re: [GENERAL] Logging for 2 instances of PostgreSQL

On Wed, Nov 30, 2016 at 4:01 PM, George Weaver wrote: > Good afternoon, > > I have set up 2 instances of PostgreSQL 9.6 on the same Windows server. I > noticed when I ran initdb for the second instance that it did not create a > pg_log folder in the new cluster, and that all logging is going to

Re: [GENERAL] Wal files - Question | Postgres 9.2

On Wed, Nov 30, 2016 at 6:05 PM, Patrick B wrote: > https://www.postgresql.org/docs/9.2/static/runtime-config-replication.html > > wal_keep_segments is the parameter responsible for streaming replication > be able to recover itself without using wal_files, is that right? > [...] ​without using w

Re: [GENERAL] Overwrite pg_catalog?

On Thu, Dec 1, 2016 at 4:16 AM, Juliano wrote: > Hi everyone, > > I tried to restore pg_catalog to my new database, but the existing > pg_catalog can't be overwritten or dropped, and postgres auto creates > pg_catalog when I create a new DB. > > So, there is a way to restore the pg_catalog to a n

Re: [GENERAL] Moving pg_xlog

On Thu, Dec 1, 2016 at 7:59 PM, Jeff Janes wrote: > On Thu, Dec 1, 2016 at 5:55 AM, Robert Inder > wrote: > >> >> I'd really like to read an explicit discussion of this in the official >> documentation, rather than just glean what I can from answers to >> questions. >> > > The official documenta

Re: [GENERAL] Select works only when connected from login postgres

On Saturday, December 3, 2016, Joseph Brenner wrote: > > doom=# select 'hello' as world; > doom=# > > Nothing else gives me any output either: \l, \du, etc. > > For kicks, how about \echo or \! Something that doesn't need a server to work. David J,

Re: [GENERAL] Select works only when connected from login postgres

On Mon, Dec 5, 2016 at 6:13 PM, Joseph Brenner wrote: > Another oddity I noticed is that I expected that the .psqlrc > file would not be read at all when using the --command feature, > but instead I would see messaging that indicated the commands > in there were being executed ​New behavior in

Re: [GENERAL] Select works only when connected from login postgres

On Mon, Dec 5, 2016 at 9:53 PM, Joseph Brenner wrote: > And I guess I did that intentionally, my .bashrc has > > # I use emacs shells, I got a "pager" already: > export PAGER='' > > ​PAGER= psql --pset=pager=always -c 'select 1;'​ Remove PAGER= and I'm good. I guess that psql could be a bi

Re: [GENERAL] Select works only when connected from login postgres

On Tue, Dec 6, 2016 at 1:13 PM, Tom Lane wrote: > So what I'm thinking now is that if PAGER is empty or all white space > then we should not try to use it as a shell command; we can either > treat the case as "pager off" or as "use default pager". Everything > else we can leave to the invoked sh

Re: [GENERAL] Problems Importing table to pgadmin

On Wed, Dec 7, 2016 at 4:13 PM, metaresolve wrote: > However, when I look at the table it's got the OID fields in there. From > what I read, the default is set to off, so I don't understand why they're > creating them. > > >

Re: [GENERAL] huge table occupation after updates

On Saturday, December 10, 2016, Tom DalPozzo wrote: > > ​I have one direct DB client (let's name it MIDAPP) only. This client of > the DB is a server for up to 1 final clients. > Any time MIDAPP is going to reply to a client, it must save a "status > record with some data" related to that cli

Re: [GENERAL] pg_dump and quoted identifiers

On Tue, Dec 13, 2016 at 9:43 AM, Pavel Stehule wrote: > >> pg_dump -t '"Statuses"' postgres > > ​To elaborate - your shell was removing the double-quotes. You need make it so after shell processing the double-quotes remain. Wrapping the double-quote string in single-quotes should do it. David

Re: [GENERAL] Install doesnt set SuperUser PW

On Tue, Dec 13, 2016 at 1:46 PM, Jovi Federici wrote: > Hi Postgres, > > My installation of Postgress 9.6 did not include any setting of a > SuperUser password so I'm at a loss. > > Do I have a bad installation? > > I got it here: > > http://www.enterprisedb.com/postgresql-961-installers- > win64

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

On Wed, Dec 14, 2016 at 2:17 PM, Patrick B wrote: > > As you can see, I select a date. So in December, the date will be: *BETWEEN > '201612015' AND '201601015'*, for example. > > ​That is an unusual timestamp value...what's the 5 for?​ (I've figured this out...but its still unusual) > > > 1. Why

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

On Wed, Dec 14, 2016 at 4:49 PM, Patrick B wrote: > ERROR: function logextract(integer, integer) does not exist > > LINE 1: select logextract(20160901,20161001); > So change the constants you are passing into your function to text (i.e., surrounding them with single quotes) so it matches the new

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

On Wed, Dec 14, 2016 at 5:12 PM, rob stone wrote: > > On Wed, 2016-12-14 at 17:00 -0700, David G. Johnston wrote: > > On Wed, Dec 14, 2016 at 4:49 PM, Patrick B > > wrote: > > > ERROR: function logextract(integer, integer) does not exist > > > LINE 1:

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

On Wednesday, December 14, 2016, Patrick B wrote: > > ' || date_start || ' > > AND > > ' || date_end || ' > > Results in this > BETWEEN > > 2016-12-15 > > AND > > 20160901 > > Compared to this > '2016-12-15' > > AND > >

Re: [GENERAL] Allow login on slave only

On Fri, Dec 16, 2016 at 9:17 AM, Andomar wrote: > We run a master server and a hot standby server. Reporting users login to > the standby server to run long queries. However, their login is also valid > on the master server. Is it possible to prevent a user from logging in to > the master server?

Re: [GENERAL] [OT] Postgresql and PHP

On Fri, Dec 23, 2016 at 8:44 AM, Alessandro Baggi < alessandro.ba...@gmail.com> wrote: > $query = pg_query_params($dbcon, "SELECT count(*) from bs_ipsource where > srcaddr = $1", array($ipsrc)); > if(!$query) { print error...} > > I don't understand why this query fails without error. I have searc

Re: [GENERAL] Error in column constraint syntax

On Tue, Dec 27, 2016 at 9:39 AM, Rich Shepard wrote: > I'm encountering DDL syntax errors using postgres-9.6.1 that I've not > before seen, and I'm having a difficult time finding the cause when reading > the appropriate sections of the manual; Section 5.3.1 in this case: > ​5.3.1 is instructi

Re: [GENERAL] Special index for "like"-based query

On Thu, Dec 29, 2016 at 4:21 PM, Job wrote: > Hello, > > in Postgresql 9.6 we have a query running on a very large table based, in > some cases, on a like statement: > > ... WHERE FIELD='CODE' OR FIELD LIKE 'CODE_%' > > Which type of index can i create to speed to the search when the "like" > cas

Re: [GENERAL] Special index for "like"-based query

On Thu, Dec 29, 2016 at 4:38 PM, Tomas Vondra wrote: > On 12/30/2016 12:33 AM, David G. Johnston wrote: > >> On Thu, Dec 29, 2016 at 4:21 PM, Job > <mailto:j...@colliniconsulting.it>>wrote: >> >> Hello, >> >> in Postgresql 9.6 we have a

Re: [GENERAL] Special index for "like"-based query

On Thu, Dec 29, 2016 at 4:51 PM, Tomas Vondra wrote: > On 12/30/2016 12:46 AM, David G. Johnston wrote: > >> On Thu, Dec 29, 2016 at 4:38 PM, Tomas Vondra >> mailto:tomas.von...@2ndquadrant.com >> >>wrote: >> >> On 12/30/2016 12:33 AM, David G. Jo

Re: [GENERAL] Default column value

On Fri, Dec 30, 2016 at 9:19 AM, Rich Shepard wrote: > On Fri, 30 Dec 2016, Adrian Klaver wrote: > > DEFAULT is what is the column is set to if the user does not specify a >> value. As shown above a user can supply a NULL value. To guard against >> that the NOT NULL constraint is required. >> > >

Re: [GENERAL] What's the benefit (or usage scenario) of a "typed table"?

On Saturday, December 31, 2016, Thomas Kellerer wrote: > I recently stumbled over "typed tables" in Postgres > (there were several questions containing this on stackoverflow recently) > > create type some_type as (id integer, data text); > create table some_table of some_type; > > I wonde

Re: [GENERAL] Difficulty modelling sales taxes

On Mon, Jan 2, 2017 at 3:33 AM, Frank Millman wrote: > > CONSTRAINT _prod_tax_code_2 FOREIGN KEY (category_id, tax_code) > REFERENCES tax_codes (category_id, code)); > ​This is ​typical solution to this modelling problem. If you need to do other stuff (introduce redundancies) to make it pla

Re: [GENERAL] Difficulty modelling sales taxes

On Mon, Jan 2, 2017 at 7:38 AM, Melvin Davidson wrote: > > > *Next, DO NOT begin object names with underscores.* > ​This is only a bit arbitrary (though I do subscribe to it)...naming custom data types or tables with underscores is likely to introduce problems since an implementation detail of P

Re: [GENERAL] Matching indexe for timestamp

On Mon, Jan 9, 2017 at 2:58 PM, Job wrote: > > But in the query planner, at that point, Postgresql 9.6.1 seems not to use > any index (single on dalle / alle field and combindex index on dalle+alle) > but it use seqscan: > > Seq Scan on gruorari_tmp (cost=0.00..5.90 rows=290 width=68) (actual >

Re: [GENERAL] Slow index scan - Pgsql 9.2

On Mon, Jan 9, 2017 at 6:06 PM, Patrick B wrote: > *Explain Analyze:* > > CTE Scan on "query_p" "qp" (cost=0.01..1060.57 rows=1 width=8) (actual > time=4065.244..4065.246 rows=1 loops=1) > > CTE query_p > > -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.003 > rows=1 loo

Re: [GENERAL] Slow index scan - Pgsql 9.2

On Mon, Jan 9, 2017 at 8:05 PM, Patrick B wrote: > ​3,581​ individual pokes into the heap to confirm tuple visibility and >> apply the deleted filter - that could indeed take a while. >> David J. > > > I see.. The deleted column is: > > deleted boolean > > Should I create an index for that? How c

Re: [GENERAL] Materialized view vs. view

On Tue, Jan 10, 2017 at 11:27 AM, Job wrote: > Hi guys, > > i am making some tests with a massive number of "select" queries (only > for reading datas) on a view and a materialized view. > We use Postgresql 9.6.1 on a 64bit server. > > Only for "select" queries, which one is faster and less expen

Re: [GENERAL] Materialized view vs. view

On Tue, Jan 10, 2017 at 12:36 PM, Melvin Davidson wrote: > >> > >I disagree with the notion that defining a relation in terms of a > >query (like a view) and materializing the results (like a table) > >makes "materialized view" a misleading name. > > > *IMHO, I disagree. I feel a better name woul

Re: [GENERAL] Materialized view vs. view

On Tue, Jan 10, 2017 at 1:01 PM, Melvin Davidson wrote: > > Can we all agree that the "Materialized View" should be faster > ​ > > If you add in the condition that the same answer has to be returned whether you query the view or the mat-view then no, I wouldn't agree; and the original question is

Re: [GENERAL] Table Design for Many Updates

On Tue, Jan 10, 2017 at 2:33 PM, Craig Boucher wrote: > I have a multi-tenant database that I'm migrating from SQL Server to > PostgreSQL 9.6.1. I read the recent articles about the potential write > amplification issue in Postgres. I have one particular table that has 14 > columns, a primary k

Re: [GENERAL] Means to emulate global temporary table

On Wed, Jan 11, 2017 at 5:38 PM, Adrian Klaver wrote: > Because the tables are known, many different functions can access the >> same tables during a session to manipulate the result set. And, because >> the tables are global the client can see the results easily based on the >> then-current tabl

Re: [GENERAL] temporarily disable autovacuum on a database or server ?

On Wed, Jan 11, 2017 at 5:50 PM, Melvin Davidson wrote: > On Wed, Jan 11, 2017 at 7:26 PM, Jonathan Vanasco > wrote: > >> >> > *I can't confirm this, but have you tried :* > > *SELECT set_config('autovacuum', 'off'', false);* > > *SELECT pg_reload_conf(); * > > *note: you must be a superuser for

Re: [GENERAL] temporarily disable autovacuum on a database or server ?

On Wed, Jan 11, 2017 at 6:19 PM, Melvin Davidson wrote: > >> > *Yes, you're right about ALTER SYSTER. Unfortunately, the op provided > neither PostgreSQL version or O/S, so we can't even be sure that is * > > *an option. That is why I stated "I cannot confirm".* > > ​Thought it was just a non-de

Re: [GENERAL] Means to emulate global temporary table

On Wed, Jan 11, 2017 at 7:39 PM, Ian Lewis wrote: > On Wed, Jan 11, 2017 at 4:54 PM, David G. Johnston < >> david.g.johns...@gmail.com> wrote: >> >>> > ​The way I read this is that the OP wants to be able to write >>> functions that target temporar

Re: [GENERAL] Means to emulate global temporary table

On Wed, Jan 11, 2017 at 7:51 PM, John R Pierce wrote: > On 1/11/2017 6:39 PM, Ian Lewis wrote: > >> Does this mean that a local temporary table created in one function in a >> database is visible globally throughout the database for the duration of >> the session? >> > > postgres temporary tables

Re: [GENERAL] efficiently migrating 'old' data from one table to another

On Thu, Jan 12, 2017 at 2:45 PM, Adrian Klaver wrote: > > > so our migration is then based on that `is_migrate` column: > > > > BEGIN; > > UPDATE table_a__live SET is_migrate = TRUE WHERE record_timestamp > < transaction_timestamp() AT TIME ZONE 'UTC' - INTERVAL '1 month'; > > I

Re: [GENERAL] Custom type column index - Postgres 9.1

On Mon, Jan 16, 2017 at 2:48 PM, Lucas Possamai wrote: > 2017-01-17 10:15 GMT+13:00 Patrick B : > >> Hi guys, >> >> I've got a custom data type column... The query I'm using its looking >> over 9 million rows. >> >> I've created a BTREE index but it didn't help on the speed. Is there any >> speci

Re: [GENERAL] 9.6.1: INSERT with PK as serial

On Monday, January 16, 2017, Rich Shepard wrote: > On Mon, 16 Jan 2017, Tom Lane wrote: > > It looks like something deleted the quote marks. How are you entering >> this SQL command, exactly? >> > > Tom, > > I noticed that, too. Here's the first line: > > INSERT INTO companies > ('comp_id','co

Re: [GENERAL] 9.6.1: INSERT with PK as serial

On Monday, January 16, 2017, Rich Shepard wrote: > I have a table with the primary key type of serial. The initial data load > has 40 lines, and each has a value for every column including sequential > integers from 1 through 40 for the data. > Without any special effort on your part the first

Re: [GENERAL] COPY to question

On Tue, Jan 17, 2017 at 11:23 AM, Rich Shepard wrote: > Running -9.6.1. I have a database created and owned by me, but cannot > copy > a table to my home directory. Postgres tells me it cannot write to that > directory. The only way to copy tables to files is by doing so as the > superuser (pos

Re: [GENERAL] recovery dump on database with different timezone

On Mon, Jan 23, 2017 at 4:45 PM, Edmundo Robles wrote: > * This is the message: COPY failed for table "stlm_201012ad": ERROR: new > row for relation "stlm_201012ad" violates check constraint > "time_stamp_201012ad" > DETAIL: Failing row contains (..., 2011-01-01 00:00:03.925+00, .., 0). > > *

Re: [GENERAL] recovery dump on database with different timezone

On Mon, Jan 23, 2017 at 5:57 PM, Adrian Klaver wrote: > > * this the constraint: "time_stamp_201012ad" CHECK (time_stamp >= >> '2010-12-01'::date AND time_stamp < '2011-01-01'::date) >> >> if change 2011-01-01 00:00:03.925+00 to 2011-01-01 >> 00:00:03.925-06 wor

Re: [GENERAL] recovery dump on database with different timezone

On Mon, Jan 23, 2017 at 6:08 PM, Adrian Klaver wrote: > > production=# show timezone; > TimeZone > -- > UTC > (1 row) > > production=# select ' 2011-01-01 00:00:03.925-06'::timestamptz < > '2011-01-01'::date; > ?column? > -- > f > ​What Tom said - though I missed the part ab

Re: [GENERAL] using hstore to store documents

On Thu, Jan 26, 2017 at 5:37 AM, Rita wrote: > of course, sorry for being vague. > > I have an external process generating a XML file (every 30 secs) which is > about 10MB. I would like to store the file as XML type for me to query > using xpath. I plan to query it every few seconds by few hundre

Re: [GENERAL] recursive query too big to complete. are there any strategies to limit/partition?

On Thu, Jan 26, 2017 at 4:37 PM, Jonathan Vanasco wrote: > There are over 20 million records in a self-referential database table, > where one record may point to another record as a descendant. > > Because of a bug in application code, there was no limit on recursion. > The max was supposed to b

[GENERAL] using hstore to store documents

On Saturday, January 28, 2017, Rita wrote: > After xmltest has been populated, I can run xpath and unest to get my data > into a row but I would like to store that result in another table, I am > guessing I should look into triggers for something like that? > I suspect that using xpath in the da

Re: [GENERAL] Average - Pg 9.2

On Thursday, February 2, 2017, Patrick B wrote: > Hi guys, > > I've got a table which has id and created date columns. > > I want to get the average of inserted rows monthly. > How can I get this data? > > This query is not working as it is showing me same data in both columns. > > select created

Re: [GENERAL] Poor performance on 9.4.4 when matching multiple columns from left side of LATERAL join

On Fri, Nov 13, 2015 at 11:25 PM, Steven Grimm wrote: > We want to find all the rows from multi_id where any of the IDs (including > its primary key) have a certain setting with a certain value. > > LATERAL seemed like the tool for the job, so we tried the following: > -

Re: [GENERAL] referencing other INSERT VALUES columns inside the insert

On Mon, Nov 16, 2015 at 4:06 AM, Geoff Winkless wrote: > On 16 November 2015 at 10:55, Albe Laurenz > wrote: > >> What about something along these lines: >> >> INSERT INTO test (c1, c2, c3) >>(WITH fixed(x1, x2) AS (VALUES (3, 7)) >> SELECT x1, x2, x1 * x2 FROM fixed); >> > > ​Genius! >

Re: [GENERAL] postgres zeroization of dead tuples ? i.e scrubbing dead tuples with sensitive data.

On Wed, Nov 18, 2015 at 12:45 PM, Day, David wrote: > Hi, > > > > One of my co-workers came out of a NIST cyber-security type meeting today > and asked me to delve into postgres and zeroization. > > > > I am casually aware of mvcc issues and vacuuming > > > > I believe the concern, based on my

Re: [GENERAL] Selecting newly added column returns empty but only when selecting with other columns in table

On Thursday, November 26, 2015, mrtruji wrote: > Ok this is strange. Shouldn't it always be one or the other? Total row > count for the table is 279,096. > > doggies=# select count(*) from data where features_bin is null; > count > > 279096 > (1 row) > > doggies=# select count(*) from

<    1   2   3   4   5   6   7   8   9   10   >