Re: psql '\copy to' and unicode escapes

2018-02-26 Thread David G. Johnston
On Mon, Feb 26, 2018 at 9:53 AM, Steven Hirsch wrote: > I fear that I'm missing something very obvious, but I cannot find a syntax > that permits me to use an escaped hexadecimal representation in a CSV file > and have that representation interpreted as the equivalent unicode > character when ins

Re: system catalog permissions

2018-02-26 Thread David G. Johnston
On Mon, Feb 26, 2018 at 4:55 PM, Paul Jungwirth wrote: > On 02/26/2018 03:47 PM, Tom Lane wrote: > >> PropAAS DBA writes: >> >>> We have a client which is segmenting their multi-tenant cluster >>> (PostgreSQL 9.6) by schema, however if one of their clients connects via >>> pgadmin they see ALL s

Re: selecting timestamp

2018-02-27 Thread David G. Johnston
On Tue, Feb 27, 2018 at 1:16 PM, chris wrote: > > What is the best way of selecting current timestamp in UTC? > ​You ​ haven't​ define ​d​ criteria upon which to judge - and the list below is not exhaustive ​ (but sufficiently so)​ ​ > > SELECT > CURRENT_TIMESTAMP as ct1 > standard conformi

Re: index-only-scan when there is an index on all columns

2018-02-27 Thread David G. Johnston
On Tue, Feb 27, 2018 at 2:08 PM, Tom Lane wrote: > Hadi Moshayedi writes: > > I am wondering why is it not using index-only-scan (which would use the > > cache better) and instead it does a bitmap scan? > > Never experiment on an empty table and assume that the resulting plan > is the same as yo

Re: index-only-scan when there is an index on all columns

2018-02-27 Thread David G. Johnston
On Tue, Feb 27, 2018 at 2:58 PM, Tom Lane wrote: > "David G. Johnston" writes: > > > Also, I recall discussion that select statements will touch the > visibility > > map (hence causing write I/O even in a read-only query) but [1] indicates > > that onl

Re: Enforce primary key on every table during dev?

2018-02-28 Thread David G. Johnston
On Wed, Feb 28, 2018 at 6:34 AM, Jeremy Finzel wrote: > We want to enforce a policy, partly just to protect those who might > forget, for every table in a particular schema to have a primary key. This > can't be done with event triggers as far as I can see, because it is quite > legitimate to do

Re: Posgresql Log: lots of parse statements

2018-03-01 Thread David G. Johnston
On Thu, Mar 1, 2018 at 8:23 AM, Vikas Sharma wrote: > Hi All, > > I need help to understand this please. I was looking to do performance > tuning on slow queries so have stated logging queries taking more than 15 > secs. In the postgresql log I can see a query which appears only as > "parse" whi

Re: Posgresql Log: lots of parse statements

2018-03-01 Thread David G. Johnston
On Thursday, March 1, 2018, Vikas Sharma wrote: > Thanks David, > > But why are there so many parse statement occurances for one query? Does > postgres parse the statement everytime before execution or parse the query > only first time it is loaded in memory and reuse the same parsed plan until

Re: Enforce primary key on every table during dev?

2018-03-01 Thread David G. Johnston
On Thu, Mar 1, 2018 at 11:32 AM, Daevor The Devoted wrote: > Could you perhaps elaborate on how a surrogate key allows one to insert > garbage into the table? I'm afraid I don't quite get what you're saying. > ​A bit contrived but it makes the point:​ *Company:* C1 (id c1) C2 (id c2) *Departme

Re: Enforce primary key on every table during dev?

2018-03-01 Thread David G. Johnston
On Thu, Mar 1, 2018 at 1:06 PM, Daevor The Devoted wrote: > >> This seems like hierarchical data > ​Hence the "this is contrived" disclaimer - but if one allows for employee-department to be many-to-many, and thus requiring a joining table, this still applies even if the specific choice to nouns

Re: Enforce primary key on every table during dev?

2018-03-01 Thread David G. Johnston
g with the same name and now I want to add the new person as, say David A. Johnston AND rename my existing record to David G. Johnston. I keep the needed uniqueness ​and don't need to cobble together other data elements. Or, if I were to use email address as the key the same physical entity

Re: Enforce primary key on every table during dev?

2018-03-01 Thread David G. Johnston
On Thu, Mar 1, 2018 at 1:32 PM, marcelo wrote: > What´s the question? > > ​Whether the OP, who hasn't come back, knew they were starting a flame war by asking this question... There is no context-less "right place" to place validation logic, nor are the various options mutually exclusive. Davi

Re: Enforce primary key on every table during dev?

2018-03-01 Thread David G. Johnston
On Thu, Mar 1, 2018 at 2:06 PM, Tim Cross wrote: > +1. And a good test of your underlying data model is whether you can > identify a natural primary key. If you can't, chances are your model is > immature/flawed and needs more analysis. > ​https://en.wikipedia.org/wiki/All_models_are_wrong Unfo

Re: Release upgarde failure

2018-03-02 Thread David G. Johnston
On Fri, Mar 2, 2018 at 7:55 AM, Pawan Sharma wrote: > Hi All, > > Please let me know the reasons of release upgrade failure in PostgreSQL > which you guys faced while doing it. > > Example: 9.5.1 to 9.5.2 > > ​It is highly unlikely that you will encounter a failure when upgrading to a new minor r

Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

2018-03-02 Thread David G. Johnston
On Friday, March 2, 2018, Adrian Klaver wrote: > On 03/02/2018 06:42 AM, Alexander Farber wrote: > >> >> > 2018-03-02 15:30:33.646 CET [16693] LOG: duration: 0.058 ms bind > : SELECT out_uid AS uid, out_fcm AS fcm, out_apns AS apns, > out_adm AS adm, out_body AS body FROM words_play_game

Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

2018-03-02 Thread David G. Johnston
On Fri, Mar 2, 2018 at 11:48 AM, Alexander Farber < alexander.far...@gmail.com> wrote: > Oops, I've got strings there too - when swapping instead of playing tiles: > > ​You should probably add: jsonb_array_length(tiles) > 0 as a check constraint on column ​ David J.

Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

2018-03-02 Thread David G. Johnston
On Fri, Mar 2, 2018 at 11:58 AM, Alexander Farber < alexander.far...@gmail.com> wrote: > I see, thank you for your comments, David and Adrian. > > In the "tiles" column actually save either the JSON array of tiles - when > the user plays them > > Or a string (which is jsonb too) concatenated of le

Re: Requiring pass and database psql shell command

2018-03-05 Thread David G. Johnston
On Mon, Mar 5, 2018 at 5:55 AM, Łukasz Jarych wrote: > Hi Guys, > > do you have also problem that every time you have to log into your > database using shell and psql? > > I have to write over and over localhost, username, password...how to force > shell to remember this? > > ​Use a service file.

Re: What is wrong with my pgadmin?

2018-03-05 Thread David G. Johnston
On Monday, March 5, 2018, Łukasz Jarych wrote: > Anyone pleasE? > Don't know but you might get better results by emailing the support list for the pgAdmin application. This list is for general questions about the server. David J.

Re: What is wrong with my pgadmin?

2018-03-06 Thread David G. Johnston
On Tue, Mar 6, 2018 at 3:15 AM, Laurenz Albe wrote: > David G. Johnston wrote: > > On Monday, March 5, 2018, Łukasz Jarych wrote: > > > Anyone pleasE? > > > > Don't know but you might get better results by emailing the support list > for the pgAdmin

Re: Can I grant permissions to specific set of uids (linux) ?

2018-03-06 Thread David G. Johnston
On Tue, Mar 6, 2018 at 1:26 PM, David Gauthier wrote: > Hi: > > I'd like to grant select, insert, update, delete to a table for a specific > set of uids (linux). All others get select only. Can the DB authenticate > the current linux user and grant access based on the fact that they are > logge

Re: Authentication?

2018-03-07 Thread David G. Johnston
On Wed, Mar 7, 2018 at 6:13 AM, Bjørn T Johansen wrote: > Hi. > > Is it possible to use one authentication method as default, like LDAP, and > if the user is not found, then try to authenticate using > md5/scram-sha-256 ? > ​In the "Client Authentication" Chapter:​ ​https://www.postgresql.org/d

Re: Authentication?

2018-03-07 Thread David G. Johnston
On Wed, Mar 7, 2018 at 8:14 AM, Bjørn T Johansen wrote: > On Wed, 7 Mar 2018 07:14:55 -0700 > "David G. Johnston" wrote: > > > On Wed, Mar 7, 2018 at 6:13 AM, Bjørn T Johansen wrote: > > > > > Hi. > > > > > > Is it possible to use one a

Re: a very primitive question about division

2018-03-07 Thread David G. Johnston
On Wed, Mar 7, 2018 at 10:21 AM, Martin Mueller < martinmuel...@northwestern.edu> wrote: > Given two values defined as integers, how do I divide one by the other and > get an answer with two decimals, e.g 3 /4 = 0.75. > ​Case one of them to numeric. ​select 3/4::numeric ​ ​David J.​

Re: a very primitive question about division

2018-03-07 Thread David G. Johnston
On Wed, Mar 7, 2018 at 10:30 AM, Martin Mueller < martinmuel...@northwestern.edu> wrote: > Thanks. So round(before1550/colfreq::numeric, 2) produces the desired > result. > > > > The explanations and examples of string functions in the Postgres > documentation are a model of clarity. The explan

Re: a very primitive question about division

2018-03-07 Thread David G. Johnston
On Wed, Mar 7, 2018 at 10:56 AM, Martin Mueller < martinmuel...@northwestern.edu> wrote: > But there is no example of 4/9 and the different ways of formatting it as > a decimal fraction with different options for rounding or a percentage. > ​That would be the responsibility of the "Data Type Form

Re: Prefixing schema name

2018-03-07 Thread David G. Johnston
On Wed, Mar 7, 2018 at 4:05 PM, Tiffany Thang wrote: > ​ > The search_path configuration works only for queries. > ​Um ​https://www.postgresql.org/docs/10/static/sql-createschema.html "​A CREATE command specifying an unqualified object name creates the object in the current schema (the one

Re: cached plan must not change result type

2018-03-07 Thread David G. Johnston
On Wed, Mar 7, 2018 at 4:06 PM, Jan Bilek wrote: > I would like to ask, would you see this solution in general as fine, or is > there any better way to achieve this? I particularly dislike the part when > we are trying matching return string on "cached plan must not change result > type" as error

Re: Command to execute SQL string

2018-03-08 Thread David G. Johnston
On Thursday, March 8, 2018, Francisco Olarte wrote: > > On Thu, Mar 8, 2018 at 1:26 PM, Łukasz Jarych wrote: > > i have a table when i have string in each row, > > for example "DELETE FROM t_test WHERE ID = 3". > > > > I want to execute for each row this command. > > It is possible? > > I supose

Re: JDBC connectivity issue

2018-03-08 Thread David G. Johnston
On Thu, Mar 8, 2018 at 2:30 PM, chris wrote: > Given that the syntax looks correct for the url, how would we go about > debugging that it's not seeing the comma? ​First thing I'd do is ensure the version of the driver I'm using supports the feature I'm trying to use. David J.

Re: Postgresql upgrade to 9.5.12/10.3 changes pg_dump format for cloning schemas

2018-03-09 Thread David G. Johnston
On Fri, Mar 9, 2018 at 6:26 AM, Aldrin Martoq Ahumada < aldrin.mar...@gmail.com> wrote: > Thinking in the long term, how could be the best way to clone a schema > into another? > Depends on why you are cloning schemas. Generally not cloning is the best bet - instead place the reference schema in

Re: pgpass hostname and IP address

2018-03-09 Thread David G. Johnston
On Friday, March 9, 2018, Ron Johnson wrote: > (8.4 and 9.2, but soon to 9.6) > > If we sometimes access a db server by IP address, and sometimes by > hostname, must we have two entries for each server+user (one with the > hostname and the other with IP address), or is there a way to put them bot

Re: Logical decoding on standby

2018-03-12 Thread David G. Johnston
On Mon, Mar 12, 2018 at 5:08 PM, Andreas Kretschmer wrote: > Let me explain my question. One of the key aspects of logical replication > is, that you can define what to replicate. That wouldn't work in this way, > that's why i'm asking. One of the key aspects of "standby" is that it is ready to

Re: Programmatically duplicating a schema

2018-03-13 Thread David G. Johnston
On Mon, Mar 12, 2018 at 11:05 PM, wrote: > The reason we'd want to infer the create statements via pg_dump is, so we > don't need to keep database migration files in sync with a 'create new > schema' SQL script. It adds risk that they get out of sync, causing > inconsistencies in new schemas cre

Re: UPSERT on a view

2018-03-13 Thread David G. Johnston
On Tue, Mar 13, 2018 at 6:47 AM, Melvin Davidson wrote: > On Mon, Mar 12, 2018 at 2:26 PM, Steven Roth > wrote: > >> This code raises the error 'foo', even though the insert says DO NOTHING >> and the error type is unique_violation. Why? >> >> More generally: how can one write trigger function

Re: SELECT .. FOR UPDATE: find out who locked a row

2018-03-15 Thread David G. Johnston
On Thu, Mar 15, 2018 at 1:30 PM, Enrico Thierbach wrote: > Now I wonder if it is possible, given the id of one of the locked rows in > the queue table, to find out which connection/which transaction owns the > lock > ​I'd start here:​ https://www.postgresql.org/docs/10/static/view-pg-locks.html

Re: SELECT .. FOR UPDATE: find out who locked a row

2018-03-16 Thread David G. Johnston
On Fri, Mar 16, 2018 at 8:00 AM, Enrico Thierbach wrote: > Hi Melvin, Stephen, hi list, > > *FWIW, I really don't understand your need to identify the actual rows that > are locked. Once you have identified the query that is causing a block > (which is usually due to "Idle in Transaction"), AFAIK

Re: STRING_AGG and GROUP BY

2018-03-16 Thread David G. Johnston
On Fri, Mar 16, 2018 at 7:17 AM, Alexander Farber < alexander.far...@gmail.com> wrote: > And I can not change the query to: STRING_AGG(DISTINCT x->>'letter', '') > AS tiles, > > Because for example in the last move with mid=6 the player Bob had played > 2 tiles, both with letter-value "P" and has

Re: STRING_AGG and GROUP BY

2018-03-16 Thread David G. Johnston
On Fri, Mar 16, 2018 at 9:10 AM, Alexander Farber < alexander.far...@gmail.com> wrote: > > But you say that "tiles" and "word (score)" are unrelated and this does > not seem true to me: > > For each move id aka "mid" there is a JSON value, describing how the > player played the letter tiles. > An

Re: STRING_AGG and GROUP BY

2018-03-19 Thread David G. Johnston
On Mon, Mar 19, 2018 at 1:54 PM, Alexander Farber < alexander.far...@gmail.com> wrote: > > I've come up with the following query, wonder if you meant something > similar - > > http://sqlfiddle.com/#!17/4ef8b/48 > > WITH cte1 AS ( > SELECT > mid, > STRING_AGG(x->>'letter', '') AS tiles > FR

Re: Foreign Key Validation after Reference Table Ownership Change

2018-03-21 Thread David G. Johnston
On Wednesday, March 21, 2018, Battuello, Louis wrote: > What permission is being violated at the schema level? > USAGE https://www.postgresql.org/docs/10/static/sql-grant.html David J.

Re: Re: Foreign Key Validation after Reference Table Ownership Change

2018-03-21 Thread David G. Johnston
On Wed, Mar 21, 2018 at 8:30 AM, Battuello, Louis < louis.battue...@etasseo.com> wrote: > So, user_2 needs usage on the schema containing its newly owned reference > table even though user_1 is performing the insert on a table in the other > schema? Interesting. I though the validation was only de

Foreign Key Validation after Reference Table Ownership Change

2018-03-21 Thread David G. Johnston
On Wednesday, March 21, 2018, Tom Lane wrote: > Louis Battuello writes: > >> The point is you can't resolve a name like "schema_1.something" unless > >> you have USAGE on schema_1. So the RI-checking query, which is run as > >> the owner of the table, fails at parse time. > > > That certainly m

Re: PostgreSQL version on VMware vSphere ESXI 6.5 and harware version 13

2018-03-26 Thread David G. Johnston
On Mon, Mar 26, 2018 at 1:28 PM, nmmulla wrote: > Thanks JD. > > Like i said we are not changing OS at all. WE are just upgrading VMWare > from > ESXi 5.5 to 6.5 and hardware from version 10 to 13. > > We are using Linux 6, 7. > > By just upgrading VMWare will it affects the performane of postgre

Re: [EXTERNAL]Re: pg_dump -Fd -j2 on standby in 9.6.6

2018-03-28 Thread David G. Johnston
On Wed, Mar 28, 2018 at 10:13 AM, Ron wrote: > On 03/28/2018 03:05 AM, Andreas Kretschmer wrote: > [snip] > >> > This e-mail message, including any attachments, >> >> this is a public mailing list ... >> > > The intended recipient is the public mailing list, no? ​A mailing list doesn't really "

Using Lateral

2018-03-28 Thread David G. Johnston
On Tuesday, March 27, 2018, Johann Spies wrote: > In the past I could use this in a query: > > SELECT > DISTINCT ut, > CASE > WHEN xpath_exists ('//t:address_spec/t:country/text()', > q.address_spec, > p.ns) > THEN unnest

Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade

2018-03-28 Thread David G. Johnston
On Wed, Mar 28, 2018 at 7:29 PM, Ken Beck wrote: > Is it possible a big problem is that the password for user postgres not > longer works for some reason, not sure why. It appears the password can > not be rest or changed without knowing the original, and what I thought > was the original no long

Re: Concurrent CTE

2018-04-04 Thread David G. Johnston
On Tuesday, April 3, 2018, Artur Formella wrote: > > And the question: is it possible to achieve more concurrent execution plan > to reduce the response time? For example: > Thread1: aa | dd | ff | primary > Thread2: bb | ee | gg > Thread3: cc | -- | hh > If and how depends greatly on your versi

Re: Concurrent CTE

2018-04-04 Thread David G. Johnston
On Wed, Apr 4, 2018 at 10:12 PM, Thomas Munro wrote: > Parallel query can't be used for CTE queries currently. ​A pointer to the location in the docs covering this limitation would be appreciated. It isn't covered here: https://www.postgresql.org/docs/10/static/when-can-parallel-query-be-used

Re: decompose big queries

2018-04-05 Thread David G. Johnston
On Thu, Apr 5, 2018 at 3:39 PM, hmidi slim wrote: > I want to know what are the best practice to use in order to decompose a > big query which contains so many joins.Is it recommended to use stored > procedures ? or is there any other solution? > Views are another solution. https://www.postgres

Re: Conflict between JSON_AGG and COPY

2018-04-09 Thread David G. Johnston
On Mon, Apr 9, 2018 at 7:14 AM, Adrian Klaver wrote: > On 04/08/2018 08:44 PM, Đỗ Ngọc Trí Cường wrote: > > Presently, COPY TO will never emit an octal or hex-digits backslash > sequence, but it does use the other sequences listed above for those > control characters. > > >> The second query is f

Re: Multiple records returned by a JOIN

2018-04-10 Thread David G. Johnston
On Tue, Apr 10, 2018 at 8:44 AM, Alexander Farber < alexander.far...@gmail.com> wrote: > Unfortunately, it returns multiple records and with wrong values too: > > # select * from words_stat_games(1, '10999844041575271'); > out_gid | out_reason | out_state1 | out_score1 | out_score2 > ---

Re: pg_basebackup restore a single table

2018-04-11 Thread David G. Johnston
On Wed, Apr 11, 2018 at 8:22 AM, Ron wrote: > On 04/11/2018 10:21 AM, Andreas Kretschmer wrote: >> >> Am 11.04.2018 um 15:53 schrieb camarillo: >> >>> Can I do a restore of a single table or single base using the archive >>> generated for the basebackup without having to delete the filesystem >>>

Re: Archiving Data to Another DB?

2018-04-11 Thread David G. Johnston
On Wed, Apr 11, 2018 at 9:15 AM, Don Seiler wrote: > Right now I'm thinking of falling back to the far-less-elegant method of > dumping the data to a flat file via COPY, running psql to connect to the > archive DB remotely and running a COPY to load the data (or maybe > transferring the flat file

Re: Regex on field value

2018-04-14 Thread David G. Johnston
On Saturday, April 14, 2018, Mike Martin wrote: > Is this possible, eg > Substring(field, regex include other field name) > Your example doesn't make sense but if you are asking whether a regular expression can be stored in a column the answer is yes - though it's nothing but a text value with r

Re: array UNNESTed to rows stable with respect to order?

2018-04-17 Thread David G. Johnston
On Tue, Apr 17, 2018 at 1:20 PM, Thiemo Kellner wrote: > This works fine but I am not sure whether the ordering remains in the > later use. ​It does not. If the array is not naturally ordered you will want to attach a "with ordinality" clause to it for performing future ordering. select * fro

Re: Old active connections?

2018-04-17 Thread David G. Johnston
On Tue, Apr 17, 2018 at 5:02 PM, Hans Sebastian wrote: > Hello group, > > We run postgresql 10.3 for a python django app with gunicorn on nginx with > django version 1.9.5.​ > > Recently, we started noticing there are many active connections from the > django app server that are more than 1 week

Re: New website

2018-04-18 Thread David G. Johnston
On Wed, Apr 18, 2018 at 3:03 PM, Adrian Klaver wrote: > I would contact the Webmaster but Contact goes to a big image of an > elephant head. That is also where Downloads, Support and Donate lands. > Might have been a good idea to roll out a demo site for testing first. Will > reserve judgment on

Re: Inconsistent compilation error

2018-04-18 Thread David G. Johnston
On Wed, Apr 18, 2018 at 7:04 PM, Melvin Davidson wrote: > > Normally, literals are inside the first quotes. IE: raise notice ' > blah_history.original_id' %', r; > ​But a compiler doesn't care about human concepts like "normally" - it just cares about "syntactically correct" and as David and Tom

Re: Inconsistent compilation error

2018-04-19 Thread David G. Johnston
On Wednesday, April 18, 2018, Adrian Klaver wrote: > > Hmm, wonder if there is an oops in the below: > > http://www.pygresql.org/contents/changelog.html > > Version 5.0 (2016-03-20) > Changes in the DB-API 2 module (pgdb): > "SQL commands are always handled as if they include parameters, i.e. > li

Re: Problem with trigger makes Detail record be invalid

2018-04-19 Thread David G. Johnston
On Thu, Apr 19, 2018 at 11:57 AM, Tom Lane wrote: > (I'm not sure that this issue is adequately documented, though. > I'd have expected to find something about it in triggers.sgml and/or > create_trigger.sgml, but in a quick look neither of them mentions foreign > keys.) > ​I'm leading toward in

Re: A couple of pg_dump questions

2018-04-19 Thread David G. Johnston
On Thu, Apr 19, 2018 at 3:39 PM, Ron wrote: > > $ pg_dump --host=farawaysrvr -Fc $REMOTEDB > /local/disk/backups/$REMOTEDB. > dump > > Is the data compressed on the remote server (thus minimizing traffic on > the wire), or locally? (I'd test this myself, but the company has really > strict firew

Re: Dynamically filtering a CTE?

2018-04-19 Thread David G. Johnston
On Thursday, April 19, 2018, W. Trevor King wrote: > Is there > an idiomatic way to approach this problem? > > I would use pl/pgsql as the language and build a query using a combination of text literals and the format() function - invoking via pl/pgsql's EXECUTE command. David J.

Re: Problem with trigger makes Detail record be invalid

2018-04-20 Thread David G. Johnston
On 04/19/2018 06:49 PM, PegoraroF10 wrote: On Fri, Apr 20, 2018 at 6:55 AM, Adrian Klaver wrote: > I know I did that trigger incorrectly but referential integrity is >> obligatory. >> > > I would agree if the FK relationship was entirely driven by the system > trigger e.g: > > alter table Detail

Re: Dynamically filtering a CTE?

2018-04-20 Thread David G. Johnston
On Fri, Apr 20, 2018 at 9:22 AM, W. Trevor King wrote: > format(' > WITH RECURSIVE _ancestors(id, ancestors) AS ( > SELECT > item.id AS id, > ARRAY[item.ancestor_id] AS ancestors > FROM items AS item > %s > ​[...]​ > > ', condition); > > SELEC

Re: Adding AVG to a JOIN

2018-04-23 Thread David G. Johnston
On Mon, Apr 23, 2018 at 12:47 PM, Alexander Farber < alexander.far...@gmail.com> wrote: > SELECT > u.elo, > AVG(c.played - c.prev_played) AS avg_time_per_move, > (SELECT ROUND(AVG(score), 1) FROM words_moves >

Re: Questions on user defined data types

2018-04-24 Thread David G. Johnston
On Tue, Apr 24, 2018 at 4:53 AM, a <372660...@qq.com> wrote: > Hi there: > > I want to ask the if I could define an object like type in postgresql, it > may be better to illustrate with actual examples. > > Background information: > > 1, I would like to pack group of data into 1 entry of in a

Re: Long running INSERT+SELECT query

2018-04-26 Thread David G. Johnston
On Thu, Apr 26, 2018 at 1:32 PM, Vitaliy Garnashevich < vgarnashev...@gmail.com> wrote: > The aggregated table has hundreds of millions of rows, and the query runs > for many hours (which is one of the reasons why it's better not to fail) > ​[...]​ > Maybe this could be solved by calculating res

Re: Surprised by index choice for count(*)

2018-05-01 Thread David G. Johnston
On Tue, May 1, 2018 at 8:46 AM, Rob Sargent wrote: > Should I be? I would have thought the pk would have been chosen v. > function index? > Indexes: > "segment_pkey" PRIMARY KEY, btree (id) > "useg" UNIQUE, btree (probandset_id, chrom, startbase, endbase) > "fpv" btree (pv(events_les

Re: CSVQL? CSV SQL? tab-separated table I/O? RENAME COLUMN

2018-05-02 Thread David G. Johnston
On the whole this email is very confusing/hard-to-follow... On Wed, May 2, 2018 at 2:29 PM, Jim Michaels wrote: > what do you think about foreign data wrappers getting CSV file table I/O? > ​ > ​I don't understand the question...​ ​ > I had thought that CSVQL db could be implemented complet

Re: Two things bit baffling in RDS PG

2018-05-03 Thread David G. Johnston
On Thu, May 3, 2018 at 1:18 PM, Ravi Krishna wrote: > I am playing around with RDS PG and I am not able to understand the > following: > > 1. The database name I created via RDS console is in upper case with no > quotes. From the remote machine via psql, > if I try to use lower case db name

Re: [GENERAL] Full text search with plain input

2018-05-04 Thread David G. Johnston
On Friday, May 4, 2018, Havasvölgyi Ottó wrote: > so that it matches (with less rank) even if one of its words match ? > That seems to be what "ts_rank" provides. David J.

Re: [GENERAL] Full text search with plain input

2018-05-04 Thread David G. Johnston
On Friday, May 4, 2018, Havasvölgyi Ottó wrote: > > Now I am thinking about splitting the input text myself to terms, then > searching and ranking the documents for each term. > Maybe do: replace(input_text, ' ', ' | ') David J.

Known Bugs on Postgres 9.5

2018-05-05 Thread David G. Johnston
On Friday, May 4, 2018, Anudeep Gudipelli wrote: > I would like to know the known bugs for v9.5 and also v9.6, is there any > place where I can check? > I think as a whole the project does a good job of fixing known bugs shortly after they are reported. There is no official bug tracker (i.e.,

Re: psycopg2.DataError: invalid input syntax for integer: ""

2018-05-06 Thread David G. Johnston
On Sunday, May 6, 2018, tango ward wrote: > cur_p.execute(""" > > INSERT INTO a_recipient (created, mod, agreed, address, > honor,) > VALUES (%s, %s)""", (current_timestamp, current_timestamp, > current_timestamp, '', '')) > > That code doesn't even run (extra co

Re: psycopg2.DataError: invalid input syntax for integer: ""

2018-05-06 Thread David G. Johnston
On Sunday, May 6, 2018, tango ward wrote: > Yes, my apologies. > > May I also ask if there's a limitation for the number of timestamp with > timezone fields in a table? > Not one that is likely to matter in practice. There's a page discussing limitations on the website/docs somewhere if you wis

Re: psycopg2.DataError: invalid input syntax for integer: ""

2018-05-07 Thread David G. Johnston
On Mon, May 7, 2018 at 12:28 AM, tango ward wrote: > I think I've found the culprit of the problem. > > I have a field which is varchar from the source DB while on the > destination DB its integer. > > Reading the documentation: http://www.postgresqltutorial. > com/postgresql-cast/ but it gives m

Re: psycopg2.DataError: invalid input syntax for integer: ""

2018-05-07 Thread David G. Johnston
On Mon, May 7, 2018 at 6:35 AM, Adrian Klaver wrote: > >> Not sure but I'm thinking you at least need to add single >> quotes around the %s symbols. That doesn't really explain >> the integer input error though I'm not familiar with the >> exact fe

Re: void function and view select

2018-05-07 Thread David G. Johnston
On Mon, May 7, 2018 at 6:52 AM, Philipp Kraus wrote: > Hello, > > I have got a complex query with a dynamic column result e.g.: > > select builddata('_foo‘); > select * from _foo; > > The first is a plsql function which creates a temporary table, but the > function returns void. > The second call

Re: Run external command as part of an sql statement ?

2018-05-07 Thread David G. Johnston
On Mon, May 7, 2018 at 2:35 PM, David Gauthier wrote: > Hi: > > At the psql prompt, I can do something like... >"select \! id -nu" > ...to get the uid of whoever's running psql. > > I want to be able to run a shell command like this from within a stored > procedure. Is there a way to do thi

Re: KeyError: self._index[x]

2018-05-07 Thread David G. Johnston
On Monday, May 7, 2018, tango ward wrote: > > cur_t.execute(""" > SELECT TRANSLATE(snumber, ' ', '') > FROM sprofile """) > > # This will result in KeyError > for row in cur_t: > print row['snumber'] > > # This works fine > for row in cur_t: > print row[0] > So ap

Re: KeyError: self._index[x]

2018-05-07 Thread David G. Johnston
On Monday, May 7, 2018, tango ward wrote: > I didn't know it. Is it only in psycopg2 that the name of the columns > will use the name of the function? > The server assigns column names - hence the advice to use psql to investigate SQL issues more easily since there is one less moving part to de

Re: cursor empty

2018-05-08 Thread David G. Johnston
On Tue, May 8, 2018 at 6:36 AM, Adrian Klaver wrote: > >> select * from vectorize('myvec'); >> fetch all from myvec; >> >> Can you explain me, which part is wrong? >> > > I am going to say: > > perform pivottable( ... > > https://www.postgresql.org/docs/10/static/plpgsql-statements > .html#PLPGSQ

Re: Concatenate 2 Column Values For One Column

2018-05-08 Thread David G. Johnston
On Tue, May 8, 2018 at 7:17 PM, tango ward wrote: > I am trying to concatenate the value of column firstname and lastname from > source DB to name column of destination DB. > > (SELECT CONCAT(first_name, ',', last_name) AS > name FROM lib_author LIMIT 1) >

Re: Concatenate 2 Column Values For One Column

2018-05-08 Thread David G. Johnston
On Tue, May 8, 2018 at 7:44 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tue, May 8, 2018 at 7:17 PM, tango ward wrote: > >> I am trying to concatenate the value of column firstname and lastname >> from source DB to name column of destination DB

Re: Enhancement to psql command, feedback.

2018-05-09 Thread David G. Johnston
On Wed, May 9, 2018 at 6:44 AM, John McKown wrote: > Again, this is just a discussion point. And I'm quite willing to admit > defeat if most people don't think that it is worth the effort. > ​-1, at least per the example. I would not want "-U postgres" inside the file. I tend to rely on servic

Re: Enhancement to psql command, feedback.

2018-05-09 Thread David G. Johnston
On Wed, May 9, 2018 at 7:17 AM, John McKown wrote: > > However, in the second case, the "magic" first line causes psql, at > present, to report an error and abort. This is why I'd like to modify how > the file referenced via the -f argument is processed. That is, the first > line of any file refe

Re: Enhancement to psql command, feedback.

2018-05-09 Thread David G. Johnston
On Wed, May 9, 2018 at 9:04 AM, Christopher Browne wrote: > On Wed, 9 May 2018 at 04:00, John McKown > wrote: > > To me this looks similar to a UNIX shell script. Now, going sideways for > a second, if someone wanted to create a "self contained" awk script. It > would look something like: > > >

Re: New install of 9.5.12 missing default PostgreSQL DB

2018-05-09 Thread David G. Johnston
On Wed, May 9, 2018 at 8:05 PM, Chandru Aroor wrote: > Yes, the service shows as running. But I don't have a Server to connect > to! > ​I have to imagine you can add a server to pgAdmin...try host: localhost and port: 5432 David J.

Re: Domain based on TIMEZONE WITH TIME ZONE

2018-05-10 Thread David G. Johnston
On Thu, May 10, 2018 at 9:13 AM, Ben Hood wrote: > On 10 May 2018, at 15:12, Vick Khera wrote: > > On Thu, May 10, 2018 at 7:31 AM, Ben Hood wrote: > >> Or are we saying that domains are one way of achieving the timestamp >> hygiene, but equally, you can get the same result as described above?

Re: Selecting strict, immutable text for a composite type.

2018-05-10 Thread David G. Johnston
On Thu, May 10, 2018 at 2:16 PM, Steven Lembark wrote: > exclude using gist > ( > locationusing =, > effective using && > ) > Have you installed the btree-​gist extension? https://www.postgresql.org/docs/10/static/btree-gist.html Not sure about composites but

Re: Selecting strict, immutable text for a composite type.

2018-05-10 Thread David G. Johnston
On Thursday, May 10, 2018, Steven Lembark wrote: > > Q: Why does it work with enums? Guessing because enums are not composites; they are scalar and most scalar types in core seem to be covered by the extension. > e.g., If I create a type foo_t as enum (...) and install the function > foo_text

Re: 10.4 upgrade, function markings, and template0

2018-05-14 Thread David G. Johnston
On Mon, May 14, 2018 at 1:42 PM, Dominic Jones wrote: > Good afternoon, > > The PostgreSQL 10.4 upgrade involves changes to some function markings > (see release notes, E.1.2, second and third bullet points for specifics). > One way to make these changes is to use `ALTER FUNCTION` to change the >

Re: Query ID Values

2018-05-14 Thread David G. Johnston
On Monday, May 14, 2018, tango ward wrote: > > May I ask an advice on how to approach this? > I can't make heads nor tails of your description...but there isn't IF in SQL. But you may get some mileage out of simple joins. David J.

Re: Query ID Values

2018-05-14 Thread David G. Johnston
I'd bottom-post, as is the convention for these lists, but it seems pointless now... CASE *expression* WHEN *value* THEN *result* [WHEN ...] [ELSE *result*] END Try that where expression is the %s. The values and results are simple literals. And you compare the result of the express

Re: Combining \i and \copy in psql

2018-05-15 Thread David G. Johnston
On Tue, May 15, 2018 at 8:30 AM, Rhys A.D. Stewart wrote: > Greetings, > > I have a query in a .sql file and I'd like to use \i to execute it and > \copy to save it to a csv file. Is there any way to combine the two? > > Something along the lines of: > > \copy \i myquery.sql to 'output.csv' > ​M

Re: Control PhoneNumber Via SQL

2018-05-15 Thread David G. Johnston
On Tuesday, May 15, 2018, tango ward wrote: > > > I can access the index 1 of the output list to get the +639078638001. I > think this has been explained already by Sir Adrian in my previous question > about the about being shown as list. I'll review that. > Last time you had multiple rows...this

Re: Control PhoneNumber Via SQL

2018-05-15 Thread David G. Johnston
On Tuesday, May 15, 2018, tango ward wrote: > Sorry for bumping this email. > > I would just like to clarify regarding regexp_replace: > > WHEN mobilenumber ~'^9[0-9]' AND LENGTH(mobilenumber) = 10 > THEN regexp_replace(mobilenumber, '', '+63') > > If the pattern is empty '', does this mean that

Re: Control PhoneNumber Via SQL

2018-05-15 Thread David G. Johnston
On Tuesday, May 15, 2018, tango ward wrote: > Ah yeah, the concatenation. I was thinking of using "+" but I can't make > it work and the documentation says not to use it for psycopg2. > > Sorry what's bottom-post? I see you did that yesterday. Is there a format > for before making a bottom-post?

Re: sql function with empty row

2018-05-16 Thread David G. Johnston
On Wed, May 16, 2018 at 11:49 AM, Philipp Kraus < philipp.kr...@tu-clausthal.de> wrote: > I have tested it on my data and it works also, but that is a little bit > confusing, because imho setof is >= 0 rows and > without setof it is [0,1]. ​Without setof it will always return exactly 1 row, neve

  1   2   3   4   5   6   7   8   9   10   >