Re: [GENERAL] Migrating money column from MS SQL Server to Postgres

On Wednesday, November 8, 2017, Igal @ Lucee.org wrote: > > Kettle throws an error though: column "discount" is of type money but > expression is of type double precision. > > The value in the offending insert is: 0.0 > > Why does Postgres decide that 0.0 is "double precision"

Re: [GENERAL] Naming conventions for column names

On Mon, Nov 6, 2017 at 10:30 PM, Sachin Kotwal wrote: > > Please committers give their final view on this. > > ​They, and others, have - its a "don't want".​ IOW, don't expend any effort since that effort will have been wasted - not that it would take zero effort to

Re: [GENERAL] idle in transaction, why

On Mon, Nov 6, 2017 at 12:32 PM, Rob Sargent wrote: > Using postgres 10-beta3 (hopefully 10.0 this week) on virtual CentOS7 and > this JDBC driver postgresql:42.1.4 > > > The postgresql.conf file has > > #idle_in_transaction_session_timeout = 0# in milliseconds, 0

Re: [GENERAL] ERROR: invalid input syntax for integer: "INSERT"

On Saturday, November 4, 2017, Robert Lakes wrote: > > Here's the error I am receiving - when I am attempting to insert a record > into a table: > > ERROR: invalid input syntax for integer: "INSERT" > LINE 1: ...T INTO listings_cdc SELECT statement_timestamp(), 'INSERT', ...

Re: [GENERAL] explain analyze output: 0 rows, 1M loops

On Wed, Nov 1, 2017 at 12:25 PM, Justin Pryzby <pry...@telsasoft.com> wrote: > On Wed, Nov 01, 2017 at 12:19:21PM -0700, David G. Johnston wrote: > > On Wed, Nov 1, 2017 at 11:59 AM, Scott Marlowe <scott.marl...@gmail.com> > > wrote: > > > > > So some of

Re: [GENERAL] explain analyze output: 0 rows, 1M loopa

On Wed, Nov 1, 2017 at 11:59 AM, Scott Marlowe wrote: > So some of my output from an explain analyze here has a line that says > this: > > ex Scan using warranty_order_item_warranty_order_id_idx on > warranty_order_item woi_1 (cost=0.57..277.53 rows=6 width=137) (actual

[GENERAL] From the "SQL is verbose" department, WINDOW RANGE specifications

The default range specification is: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW It seems like a common second choice is to want: RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING Why did they have to make something so common take 49 characters that, for seldom-using users, is

[GENERAL] Make "(composite).function_name" syntax work without search_path changes?

CREATE SCHEMA altschema; CREATE TYPE altschema.alttype AS ( altid text, altlabel text ); CREATE FUNCTION altschema.label(item altschema.alttype) RETURNS text LANGUAGE sql AS $$ SELECT (item).altlabel; $$; WITH vals (v) AS ( SELECT ('1', 'One')::altschema.alttype ) SELECT (v).label FROM vals; --

Re: [GENERAL] Roles inherited from a role which is the owner of a database can drop it?

On Mon, Oct 30, 2017 at 12:25 PM, Ivan Voras wrote: > > 3. But they do log in with "developer" roles which are inherited from the > owner role. > > ​[...]​ > I've tried it on a dummy database and it apparently works as described > here. Is this by design? > > ​Not quite

Re: [GENERAL] starting PG command line options vs postgresql.con

On Mon, Oct 30, 2017 at 6:48 AM, rakeshkumar464 wrote: > I would prefer using postgresql.conf. what is the consensus in this forum > regarding command line vs postgresql.conf. ​I suspect that most people administering a PostgreSQL database would expect that the

Re: [GENERAL] Old pg_clog files

On Sunday, October 29, 2017, Ron Johnson wrote: > Hi, > > v8.4.17 > > http://www.postgresql-archive.org/pg-clog-questions-td2080911.html > > According to this old thread, doing a VACUUM on every table in the > postgres, template1 and TAPd databases should remove old

Re: [GENERAL] How do I insert and update into a table of arrays of composite types via a select command?

On Wed, Oct 25, 2017 at 2:16 PM, Celia McInnis wrote: > Got it, finally... > > insert into t_array select array[row((data_comp).*)::mytype[] from > t_composite; > > I'm not sure why I need (data_comp).* rather than some of the other things > that I tried and failed

Re: [GENERAL] multiple sql results to shell

On Mon, Oct 23, 2017 at 7:08 AM, Mark Lybarger wrote: > I have this bash/sql script which outputs some curl commands. the > backticks causes it to get interpreted by the shell. This works fine if > there is one result, but when there are many rows returned, it looks like

Re: [GENERAL] How to find out extension directory

On Fri, Oct 20, 2017 at 1:12 PM, rakeshkumar464 wrote: > I am documenting on automating installation of pgaudit extension for > containers. On my laptop I see that the directory where the files > pgaudit.control and pgaudit--1.2.sql needs to be present is > >

Re: [GENERAL] Is it OK to create a directory in PGDATA dir

On Thu, Oct 19, 2017 at 5:32 PM, John R Pierce wrote: > On 10/19/2017 1:25 PM, Tomas Vondra wrote: > > Is it fine to create a subdir inside PGDATA and store our stuff > there, or will PG freak out seeing a foreign object. > > > PostgreSQL certainly does not check if there

Re: [GENERAL] Using Variables in Queries

On Thu, Oct 19, 2017 at 12:14 PM, Tom Lane wrote: > FROM products, > (values ('red widget'::text)) consts(target) > WHERE similarity(target, item_name) > 0.25 > ORDER BY target <<-> item_name > > PG 9.5 and up will flatten out cases like this to be exactly what you >

Re: [GENERAL] Using Variables in Queries

On Thu, Oct 19, 2017 at 8:21 AM, Igal @ Lucee.org wrote: > Is it still true (the posts I see on this subject are quite old) that I > can not do so in Postgres outside of a stored procedure/function? And if > so, what's the reason of not adding this feature? Seems very useful to

Re: [GENERAL] Finally upgrading to 9.6!

On Wed, Oct 18, 2017 at 2:34 PM, Don Seiler wrote: > On Wed, Oct 18, 2017 at 4:17 PM, Vik Fearing > wrote: > >> On 10/18/2017 08:17 PM, Don Seiler wrote: >> >> > I disagree with this. It isn't my company's business to test the >> > Postgres software

Re: [GENERAL] Finally upgrading to 9.6!

On Wednesday, October 18, 2017, Joshua D. Drake wrote: > > I am not sure why this is even a question. There are plenty of businesses > that can risk the deployment of a .0 release but there are also *MANY THAT > CAN NOT*. The proper way to do this is to have a staging

Re: [GENERAL] Finally upgrading to 9.6!

On Wed, Oct 18, 2017 at 8:16 AM, Igal @ Lucee.org wrote: > On 10/18/2017 7:45 AM, Ron Johnson wrote: > > On 10/18/2017 09:34 AM, Igal @ Lucee.org wrote: > > A bit off-topic here, but why upgrade to 9.6 when you can upgrade to > 10.0? > > > There's no way we're going to put an

Re: [GENERAL] Problems with the time in data type timestamp without time zone

On Wed, Oct 18, 2017 at 8:21 AM, américo bravo astroña < americobr...@gmail.com> wrote: > Hi, > > I have a program that saves information in a DB Postgresql need to extract > data from date and time of that DB but when I retrieve the date and time > information is always ahead 3 hours, the type

Re: [GENERAL] Simple query fail

On Tue, Oct 17, 2017 at 2:29 PM, Glenn Pierce wrote: > and I have a simple query that fails > ​This is not failure, this is a query that found zero matching records. > > Ie > > SELECT sensor_id, MAX(ts), date_trunc('day', ts), COALESCE(MAX(value), > 'NaN')::float FROM

Re: [GENERAL] Using Substitution Variables In PostgreSQL

On Mon, Oct 16, 2017 at 7:08 AM, Osahon Oduware wrote: > Hi All, > > I wanted to find out how to use a substitution variable in an SQL > statement that would cause the user to be prompted for a value. Something > similar to the ampersand (&&) in ORACLE. > > For example,

Re: [GENERAL] REASSIGN OWNED simply doesn't work

On Fri, Oct 13, 2017 at 6:04 AM, Alvaro Herrera wrote: > Sam Gendler wrote: > > psql 9.6.3 on OS X. > > > > I'm dealing with a production database in which all db access has been > made > > by the same user - the db owner, which isn't actually a superuser because > > the

Re: [GENERAL] Permissions for Web App

On Fri, Oct 13, 2017 at 11:03 AM, Igal @ Lucee.org wrote: > You mean that if I execute the ALTER DEFAULT command above as user > `postgres` then only tables created by user `postgres` will give default > privileges to role `webapp`? > ​Yes. "​You can change default privileges

Re: [GENERAL] "Shared strings"-style table

s" like this, reducing size on disk > > > at the cost of lookup overhead for all queries? > > > (I guess maybe it's like TOAST, but content-hashed and de-duped and not > > > only for large objects?) > > On Fri, Oct 13, 2017, at 01:12 PM, David G. Johnston wrote: > &g

Re: [GENERAL] "Shared strings"-style table

On Fri, Oct 13, 2017 at 8:49 AM, Seamus Abshere wrote: > Theoretically / blue sky, could there be a table or column type that > transparently handles "shared strings" like this, reducing size on disk > at the cost of lookup overhead for all queries? > > (I guess maybe it's

Re: [GENERAL] [asking about how to upgrade docker postgresql without losing the data]

On Tue, Oct 10, 2017 at 4:25 AM, Olivani Prisila wrote: > Hi, > > I am beginner both of docker and postgresql. > > How do i upgrade docker postgresql 9.5 into 9.6 without losing my > current database? > fyi: im using ubuntu verison 14 and docker 17.09 > ​More of a Docker

Re: [GENERAL] Permissions for Web App

On Mon, Oct 9, 2017 at 9:44 AM, Igal @ Lucee.org wrote: > But I want to give that role permissions on future tables since I add new > tables and drop/recreate current ones. > ​ALTER DEFAULT PRIVILEGES​ ​https://www.postgresql.org/docs/9.6/static/sql-alterdefaultprivileges.html

Re: [GENERAL] pg_start/stop_backup naming conventions

On Mon, Oct 9, 2017 at 9:33 AM, mj0nes wrote: > Hi, > > I'm just starting out on a rolling backup strategy and the naming > convention > has thrown me slightly for the WAL and "backup_label" files. > ​[...]​ > Thanks for any pointers. > ​I'll give out the standard

Re: [GENERAL] Functions and Parentheses

On Fri, Oct 6, 2017 at 2:18 PM, Igal @ Lucee.org wrote: > Hi, > > Is current_date a function? It's a bit puzzling to me since there are no > parentheses after it, i.e. > > SELECT current_date; > > And not > > SELECT current_date(); -- syntax error > ​ It, and the others

Re: [GENERAL] Setting search_path ignored

On Mon, Oct 2, 2017 at 7:09 AM, Guyren Howe wrote: > I logged out and back and did SET ROLE and got the same resullt. > ​ Are you logging in as "thing_accessor" or some role that is a member of "thing_accessor"? David J. ​

Re: [GENERAL] Setting search_path ignored

On Mon, Oct 2, 2017 at 7:00 AM, Guyren Howe wrote: > CREATE ROLE thing_accessor; > > CREATE ROLE > > CREATE SCHEMA thing_accessor; > > CREATE SCHEMA > > covermything=> ALTER ROLE thing_accessor SET search_path=thing_accessor; > > ALTER ROLE > > covermything=# SET ROLE

Re: [GENERAL] Plan changes from index scan to seq scan after 5 executions

On Sat, Sep 30, 2017 at 10:57 AM, Alexander Kukushkin wrote: > Hi, > > Recently I've been investigating a strange behavior of one stored > procedure. > Please provide the output of: SELECT version(); David J. ​

Re: [GENERAL] COPY vs \COPY FROM PROGRAM $$ quoting difference?

On Fri, Sep 29, 2017 at 9:27 AM, Alexander Stoddard < alexander.stodd...@gmail.com> wrote: > I found what seems to be an odd difference between COPY and \copy parsing. > ​[...] ​ > COPY dest_table FROM PROGRAM $$ sed 's/x/y/' | etc... $$ > > To my surprise this worked with COPY but not \COPY

Re: [GENERAL] Catching errors inside a LOOP is causing performance issues

On Thu, Sep 28, 2017 at 1:08 AM, Denisa Cirstescu < denisa.cirste...@tangoe.com> wrote: > Hi Tom, > > You said that trapping an *arbitrary* exception is a “fairly expensive > mechanism”. > ​I suppose a better (though maybe not perfectly accurate) wording is that setting up the pl/pgsql execution

Re: [GENERAL] pg_upgrade?: Upgrade method from/to any version on random OS?

On Wed, Sep 27, 2017 at 12:48 PM, Hans Schou wrote: > I have looked through > https://www.postgresql.org/docs/9.6/static/pgupgrade.html > but it seems more complicated than necessary. > ​[perform dump/restore]​ It went very good but took 100 minutes - where we had

Re: [GENERAL] Catching errors inside a LOOP is causing performance issues

On Mon, Sep 25, 2017 at 9:13 AM, Denisa Cirstescu < denisa.cirste...@tangoe.com> wrote: > > Can someone please explain to me why this worked? > > What happened behind the scenes? > > I suspect that when you catch exceptions inside of a LOOP and the code > ends up generating an exception, Postgres

Re: [GENERAL] hard parse?

On Thu, Sep 21, 2017 at 5:48 AM, Peter Koukoulis wrote: > Hi > > I have a query where a filter would always be negative, how many steps, > out these: > >- parsing and syntax check >- semantic analysis >- transformation process (query rewrite based on system or >

Re: [GENERAL] Insert large number of records

On Tuesday, September 19, 2017, Job wrote: > and would not care about table partitioning (COPY command fire > partitioned-table triggers). You might want to write a script that inserts directly into the partitions and bypass routing altogether. Insert into ...

Re: [GENERAL] reload postgresql with invalid pg_hba.conf

On Mon, Sep 18, 2017 at 12:36 PM, Tom Lane wrote: > jotpe writes: > > A system administration applied an invalid line into the pg_hba.conf > > file and called "service postgresql reload". Since that command doesn't > > return any error and leaves with exit

Re: [GENERAL] Selecting a daily puzzle record - which type of column to add?

On Sun, Sep 17, 2017 at 1:13 PM, Alexander Farber < alexander.far...@gmail.com> wrote: > > I need to add a useful column, so that it would be easy to me to create a > web script which would display today's and all past "daily puzzle" records > - and wouldn't change the already published

Re: [GENERAL] looking for a globally unique row ID

On Thu, Sep 14, 2017 at 12:45 AM, Rafal Pietrak wrote: > Hello everybody, > > Can anybody help me find a way to implement an ID which: > > 1. guarantees being unique across multiple tables. > > 2. guarantees its uniqueness not only during INSERT, but also during the > lifetime

Re: [GENERAL] Numeric numbers

On Saturday, September 2, 2017, Олег Самойлов wrote: > > There is only 20 "3" after ".". Well, may be this is not a problem, but > why are they infinite number of "0" after the point? I can write even > > => select (1::numeric/3-0.)*1e10; > ?column?

Re: [GENERAL] Fields re-ordered on JOIN with * and USING

On Fri, Sep 1, 2017 at 2:25 PM, Ken Tanzer wrote: > Hi. I recently noticed that when doing a SELECT * with USING, that the > join field(s) appear first in the output. I'd never noticed that before, > and was just curious if that is expected behavior or not. Thanks. > ​I

Re: [GENERAL] pgadmin - import a CSV with nulls?

On Thu, Aug 31, 2017 at 1:04 PM, George Neuner wrote: > Does anyone know a way to do this reliably? > ​The psql "\copy" meta-command should be capable of doing what you desire. David J.​

Re: [GENERAL] Table create time

On Wednesday, August 30, 2017, wrote: > > Hi, > > is there a way to add a table create (and perhaps schema modify) timestamp > to the system? > > There is not. You may wish to search the archives for discussions as to why previous requests for this feature have not

Re: [GENERAL] Create Action for psql when NOTIFY Recieved

On Mon, Aug 28, 2017 at 6:42 PM, Jerry Regan < jerry.re...@concertoglobalresources.com> wrote: > Let’s suppose for a moment that I piped the output of a psql instance to > awk or some similar program, configured to detect the NOTIFY. That program > would then spawn a process to actually perform

Re: [GENERAL] Create Action for psql when NOTIFY Recieved

On Mon, Aug 28, 2017 at 1:28 PM, Jerry Regan < jerry.re...@concertoglobalresources.com> wrote: > My concern is how, after LISTENing in psql, I can tell it what to do when > the NOTItFY is received. > ​As far as I am aware you cannot. The docs for psql, and its feature set, with respect to

Re: [GENERAL] DROP [TEMP] TABLE syntax, as reason why not?

On Wed, Aug 23, 2017 at 6:08 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > "David G. Johnston" <david.g.johns...@gmail.com> writes: > > I'm wondering if there is anything technical preventing someone from > making: > > > DROP TEMP TABLE tablename; > >

Re: [GENERAL] 'value too long' and before insert/update trigger

On Wednesday, August 23, 2017, Kevin Golding wrote: > Presumably the length validation is being done before the trigger is run. > Is there some way this could be changed so the trigger happens first? > The input tuple passed into the trigger is a valid record of the

Re: [GENERAL] jdbc driver vis Release 10

On Wed, Aug 23, 2017 at 4:33 PM, Rob Sargent wrote: > I see no mention of a new jdbc driver on the release notes for Beta 1. > Does that mean there isn't one? ​Whose release notes? PostgreSQL Server? I don't believe the server release notes ever talk about external

[GENERAL] DROP [TEMP] TABLE syntax, as reason why not?

Hey all, I'm wondering if there is anything technical preventing someone from making: DROP TEMP TABLE tablename; work. Implementation wise the command would fail if a temporary table of the given name doesn't exist. Today, if a temporary table exists it will be dropped, but if tablename is

Re: [GENERAL] Function not inserting rows

On Wed, Aug 23, 2017 at 8:23 AM, Frank Foerster wrote: > > sql = "select * from api_dev.add_texts_to_item( %s, %s ); x x" > i get the following python-error: > psycopg2.ProgrammingError: FEHLER: Syntaxfehler bei »s« > LINE 1: ...dd_texts_to_item( 1234,

Re: [GENERAL] What is the proper query

On Mon, Aug 21, 2017 at 9:08 PM, Igor Korot wrote: > Hi, ALL, > draft=# SELECT * FROM information_schema.key_column_usage > ​[...]​ > There are 3 foreign keys in that table. > > Is there a way to get values of 0, 1, 1, and 2 for the ordinal position? > Not using the

Re: [GENERAL] What is the proper query

On Tue, Aug 22, 2017 at 8:43 AM, Igor Korot wrote: > Or this is the bug in 9.1? > Since it looks like there are 2 columns with the same info in 1 > table/view > ​This old email thread sounds similar to what you are describing here.

Re: [GENERAL] Count column with name 'count' returns multiple rows. Why?

On Fri, Aug 18, 2017 at 1:47 PM, Peter J. Holzer wrote: > So apparently > ​ ​ > columnname open-parenthesis tablename closed-parenthesis is a specific > syntactic construct, but I can't find it documented anywhere. ​The documentation linked to speaks mainly in terms of

Re: [GENERAL] Where is pg_hba.conf

On Sunday, August 13, 2017, Igor Korot wrote: > Also, I presume that the address in this file is the address of the > machine where the server is located, not the address from where the > connection is initiated. > Not according to the docs.

Re: [GENERAL] sequence used on null value or get the max value for a column whith concurrency

Thinking aloud... On Thu, Aug 10, 2017 at 3:05 PM, marcelo wrote: > In some table, I have a bigint column which at the app level can be null. > Call it "DocumentNumber", and of course is not the PK. > In most cases, the applications give some value to the column. > >

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

On Wed, Aug 9, 2017 at 12:26 PM, Melvin Davidson wrote: > *>I'm am wondering whether "REASSIGNED OWNED" **needs fixing as well* > > *Possibly, but as the op is on 9.3, it is not available to him.* > ​You should check the docs again...​ > *I would also argue that since*

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

On Wed, Aug 9, 2017 at 11:30 AM, Melvin Davidson <melvin6...@gmail.com> wrote: > > > On Wed, Aug 9, 2017 at 1:56 PM, David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Wed, Aug 9, 2017 at 10:37 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: >&g

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

On Wed, Aug 9, 2017 at 10:37 AM, Tom Lane wrote: > Scott Marlowe writes: > > Seems like something that should be handled by alter doesn't it? > > I have some vague memory that we intentionally didn't implement > ALTER EXTENSION OWNER because we were

Re: [GENERAL] Any thoughts on making a psql meta-command "hide (or show alt text) if no results"?

On Tue, Aug 8, 2017 at 6:25 PM, Melvin Davidson wrote: > > *​H​ave you looked at the TUPLES ONLY option?* > > *-t* *--tuples-only* > > *Turn off printing of column names and result row count footers, etc. This > is equivalent to the \t command.* >

[GENERAL] Any thoughts on making a psql meta-command "hide (or show alt text) if no results"?

Hey all, looking for thoughts on a feature request: I run quite a few queries, using psql, that are intended for exceptional situations. When there are no results, which is expected, I still get the table header and basic frame showing up in the output. The option I'd like is to be able to

[GENERAL] PostgreSQL with PowerBuilder, and Identity keys (serials)

On Saturday, August 5, 2017, Dan Cooperstock at Software4Nonprofits < i...@software4nonprofits.com > wrote: > > As I have mentioned in several replies, I have tested all of this code > directly in SQL statements and they work perfectly.

Re: [GENERAL] select md5 result set

On Wed, Aug 2, 2017 at 4:50 PM, Peter Koukoulis wrote: > david, thanks for the help. > > Would this be the equivalent, for the statement in your email, for table > TEST1 (x integer, y varchar(20)): > > ft_node=# SELECT md5(string_agg(vals::text, '')) > ft_node-# from

Re: [GENERAL] select md5 result set

On Wed, Aug 2, 2017 at 3:42 PM, Peter Koukoulis wrote: > > SQL> select dbms_sqlhash.gethash('select x,y from test1',2) as md5_value > from dual; > > MD5_VALUE > > >

Re: [GENERAL] Would you add a --dry-run to pg_restore?

On Wed, Aug 2, 2017 at 10:10 AM, Edmundo Robles wrote: > I imagine pg_restore can execute the instructions on dump but don't > write on disk. just like David said: "tell me what is going to happen > but don't actually do it" > You may wish to respond to the actual

Re: [GENERAL] Do not INSERT if UPDATE fails

On Wed, Aug 2, 2017 at 8:58 AM, Alexander Farber wrote: > However if the user record is not found or the user already has vip_until > >= CURRENT_TIMESTAMP (i.e. the user has already purchased "vip status") I > would like to cancel the INSERT. > > ​You can "join" two

Re: [GENERAL] Would you add a --dry-run to pg_restore?

On Wed, Aug 2, 2017 at 9:02 AM, Edmundo Robles wrote: > Will be great to have a dry run option, because the time to verify > reduces a lot and will save space on disk, because just execute with no > write to disk. > "Dry run", the way I understand it, can be

Re: [GENERAL] RETURNS SETOF RECORD with 1 column

On Thursday, July 27, 2017, David G. Johnston <david.g.johns...@gmail.com> wrote: > On Thursday, July 27, 2017, Vincenzo Romano <vincenzo.rom...@notorand.it > <javascript:_e(%7B%7D,'cvml','vincenzo.rom...@notorand.it');>> wrote: >> >> The main difference is th

Re: [GENERAL] RETURNS SETOF RECORD with 1 column

On Thursday, July 27, 2017, Vincenzo Romano wrote: > > The main difference is that with RETURNS SETOF RECORD I still get the > "usual"(tm) function argument list in the usual place: between two > parentheses. > It's a matter of style. And a consistent one. > But I

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On Mon, Jul 24, 2017 at 8:11 PM, Tom Lane wrote: > ​[*docs] > If the data were perfectly distributed, with the same > * number of tuples going into each available bucket, then the bucketsize > * fraction would be 1/nbuckets. But this happy state of affairs will > occur >

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On Mon, Jul 24, 2017 at 7:58 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Jul 24, 2017 at 3:46 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > >> >> The cost to form the inner hash is basically negligible whether it's >> de-duped or n

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On Mon, Jul 24, 2017 at 3:46 PM, Tom Lane wrote: > > The cost to form the inner hash is basically negligible whether it's > de-duped or not, but if it's not (known) de-duped then the cost > estimate for the semijoin is going to rise some, and that discourages > selecting it.

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On Mon, Jul 24, 2017 at 3:22 PM, Dmitry Lazurkin wrote: > ALTER TABLE ids ALTER COLUMN id SET NOT NULL; > EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM ids WHERE id IN > :values_clause; > > Aggregate (cost=245006.46..245006.47 rows=1 width=8) (actual >

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On Mon, Jul 24, 2017 at 3:12 PM, Dmitry Lazurkin wrote: > And I have one question. I don't understand why IN-VALUES doesn't use > Semi-Join? PostgreSQL has Hash Semi-Join... For which task the database > has node of this type? > ​Semi-Join is canonically written as: SELECT

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On Sun, Jul 23, 2017 at 4:35 AM, dilaz03 . wrote: > - IN-VALUES clause adds new node to plan. Has additional node big > overhead? How about filter by two or more IN-VALUES clause? > ​IN-VALUES is just another word for "TABLE" which is another word for "RELATION". Writing

Re: [GENERAL] What is the problem with this query?

On Sunday, July 23, 2017, Igor Korot <ikoro...@gmail.com> wrote: > Hi, David, > > On Sun, Jul 23, 2017 at 5:07 PM, David G. Johnston > <david.g.johns...@gmail.com <javascript:;>> wrote: > > On Sunday, July 23, 2017, Igor Korot <iko

Re: [GENERAL] What is the problem with this query?

On Sunday, July 23, 2017, Igor Korot wrote: > > Is "IF" operator not supported by PostgreSQL > IF is pl/pgsql, not SQL. David J.

Re: [GENERAL] Postgres csv logging

It is customary to indicate when you've posted the same question to other forums - in this case SO. https://dba.stackexchange.com/questions/180263/postgres-log-query-and-command-tag-to-csv As the comment there says your config and your output seem at odds. Though I think your confusion is still

Re: [GENERAL] Backward compatibility

On Fri, Jul 21, 2017 at 8:49 AM, Igor Korot wrote: > MySQL uses this: > https://dev.mysql.com/doc/refman/5.7/en/mysql-get-server-version.html. > Is it safe to assume that PostgreSQL calculates the version the same way? > ​ Yes and no. Things are changing with this next

Re: [GENERAL] Backward compatibility

On Thursday, July 20, 2017, David G. Johnston <david.g.johns...@gmail.com> wrote: > On Thursday, July 20, 2017, Igor Korot <ikoro...@gmail.com > <javascript:_e(%7B%7D,'cvml','ikoro...@gmail.com');>> wrote: > >> Hi, David, >> >> On Thu, Jul 20, 2017 at

Re: [GENERAL] Backward compatibility

On Thursday, July 20, 2017, Igor Korot <ikoro...@gmail.com> wrote: > Hi, David, > > On Thu, Jul 20, 2017 at 10:23 PM, David G. Johnston > <david.g.johns...@gmail.com <javascript:;>> wrote: > > On Thu, Jul 20, 2017 at 7:13 PM, Igor Korot <iko

Re: [GENERAL] Backward compatibility

On Thu, Jul 20, 2017 at 7:23 PM, Igor Korot wrote: > On Thu, Jul 20, 2017 at 10:19 PM, Andreas Kretschmer > wrote: > > > >>Is there a query or a libpg function which can return the version of > >>the server I'm running? > > > Select version(); > >

Re: [GENERAL] Backward compatibility

On Thu, Jul 20, 2017 at 7:13 PM, Igor Korot wrote: > Hi, ALL, > According to the documentation PostgreSQL 9.6 (latest) supports > > CREATE INDEX IF NOT EXIST > > However, the version 9.4 and below supports only > > CREATE INDEX. > > Is there a query or a libpg function which

Re: [GENERAL] ~/.psqlrc file is ignored [solved: $HOME/.psqlrc]

On Thu, Jul 20, 2017 at 8:37 AM, vstuart wrote: > ​​ > [victoria@victoria ~]$ pg > [sudo -u postgres -i] > [sudo] password for victoria: > ​Just curious, what is going on here? David J. ​

Re: [GENERAL] How to stop array_to_json from interpolating column names that weren't there

On Wed, Jul 19, 2017 at 8:53 PM, Guyren Howe wrote: > Thanks. Seeking greater understanding, why is json_agg(*) not equivalent? > ​Are you referring to the fact that ​this provokes an error? "select json_agg(*) from schemata;" The json_agg(expression) function has an arity

Re: [GENERAL] ~/.psqlrc file is ignored

On Wed, Jul 19, 2017 at 12:12 PM, vstuart wrote: > Hi David: I see what you are saying; sorry for the confusion. This is how > postgres operates on my system: > ​None of that is surprising or unique. If you ask specific questions I'd be willing to answer them but I'm

Re: [GENERAL] ~/.psqlrc file is ignored

On Wed, Jul 19, 2017 at 11:49 AM, vstuart wrote: > As a simple solution, I can sudo symlink MY ~/.psqlrc to that directory > (/var/...; changing ownership also to postgres), but there appears to be > some underlying issue, as Pg should find ~/.psqlrc, correct? > ​What

Re: [GENERAL] PG 9.1 - FK + Check constraint

On Tue, Jul 18, 2017 at 6:56 PM, Patrick B wrote: > Another solution would be to create an IMMUTABLE functiondoing the check > and use that in a CHECK constraint [1]. > Why do you need an FK constraint? Why can you not use a data modification trigger? Placing the

Re: [GENERAL] What is exactly a schema?

On Fri, Jul 14, 2017 at 9:13 AM, marcelo wrote: > I'll be using Devart's dotConnect. I have two alternatives at this moment > > a) To set the user name to the required schema. This has the (little) > drawback that forces user configuration for every schema... > ​With

Re: [GENERAL] What is exactly a schema?

On Fri, Jul 14, 2017 at 9:01 AM, Jerry Sievers wrote: > marcelo writes: > > > Thank you. > > Now I have a related question. > > Could I select a specific schema in the connection string? Say, by > > example database=mydb.schemanumbertwo ? > > >

Re: [GENERAL] Event Trigger question

On Thursday, July 13, 2017, ProPAAS DBA wrote: > > 2) where can I find a complete list of the tg_ variables? I see this > list:https://www.postgresql.org/docs/9.4/static/plpgsql-trigger.html > > which includes TG_NAME. OLD, NEW, etc but tg_tag and tg_event are not in the >

Re: [GENERAL] Does a row lock taken out in a CTE stay in place?

On Tue, Jul 11, 2017 at 8:36 AM, Seamus Abshere wrote: > Given an update that uses CTEs like this: > > WITH > lock_rows AS ( > SELECT 1 FROM tbl WHERE [...] FOR UPDATE > ) > UPDATE [...] > > Will the rows in `tbl` remain locked until the UPDATE is finished? > > ​Yes​ -

Re: [GENERAL] How to handle simultaneous FOR-IN UPDATE-RETURNING loops?

On Mon, Jul 10, 2017 at 7:32 AM, Alexander Farber < alexander.far...@gmail.com> wrote: > > However there is a problem: I can not use a "single-instance" cronjob to > run words_expire_games hourly. > ​Setup a cron job that invokes the servlet - probably via "curl" My question is if I should

Re: [GENERAL] psql doesn't pass on exported shell environment functions

On Fri, Jul 7, 2017 at 8:45 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > "David G. Johnston" <david.g.johns...@gmail.com> writes: > > ​I'm using Ubuntu 16.04 > > Hmph. Works for me on RHEL6. I'm betting that Ubuntu has put in some > weird securi

Re: [GENERAL] psql doesn't pass on exported shell environment functions

On Fri, Jul 7, 2017 at 8:19 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Fri, Jul 7, 2017 at 8:12 AM, David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> ​I'm using Ubuntu 16.04 >> >> > ​Seems to be a regression sinc

Re: [GENERAL] psql doesn't pass on exported shell environment functions

On Fri, Jul 7, 2017 at 8:12 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > ​I'm using Ubuntu 16.04 > > ​Seems to be a regression since this works on my 14.04 setup. David J. ​

Re: [GENERAL] psql doesn't pass on exported shell environment functions

On Fri, Jul 7, 2017 at 7:43 AM, Albe Laurenz <laurenz.a...@wien.gv.at> wrote: > David G. Johnston wrote: > >> It works for me on Linux with 9.6.3 psql: > > > > ​Except you haven't recreated the scenario I presented.​ > > > > ​You only are involvi

  1   2   3   4   5   6   7   8   9   10   >