Re: [GENERAL] select md5 result set

On Wed, Aug 2, 2017 at 4:25 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > 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] 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 (select x,y from test1) vals(

Re: [GENERAL] select md5 result set

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 (select x,y from test1) vals(x,y); ? Peter On Thu, 3 Aug 2017 at 00:25 David G. Johnston wrote

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 > > > 9FDA7FA725B783172CA371DA04AD5754 > > > Can I do so

[GENERAL] select md5 result set

Hi I'm attempting to emulate feature available in Oracle, namely dbs_sqlhash. For example, given the following table data values: SQL> select x,y from test1; X Y -- 5 White 1 YYY 2 Goodbye 6 Black I can create a single hash value over the entire result set, specific

Re: [GENERAL] SELECT statement with sub-queries

On 05/28/2017 11:54 AM, Michelle Konzack wrote: On 2017-05-28 11:23:47 Adrian Klaver hacked into the keyboard: On 05/28/2017 10:53 AM, Michelle Konzack wrote: SELECT * FROM products WHERE category IN (SELECT categories.cat FROM categories WHERE categories.serial = products.category);

Re: [GENERAL] SELECT statement with sub-queries

On Sun, May 28, 2017 at 11:54 AM, Michelle Konzack wrote: > > I want to replace in the output the numerical ID from "products.category" > with the value of "categories.cat", where the "products.category" match > the "categories.serial" > ​Then go and read the first response in this thread, ​Amit

Re: [GENERAL] SELECT statement with sub-queries

On 2017-05-28 20:19:59 m...@ft-c.de hacked into the keyboard: > Hallo, > > SELECT * > FROM products > WHERE exists > (SELECT categories.cat FROM categories WHERE > categories.serial==products.category); This does not give an error but does nothing > or > SELECT * FROM products > WHERE

Re: [GENERAL] SELECT statement with sub-queries

On 2017-05-28 11:23:47 Adrian Klaver hacked into the keyboard: > On 05/28/2017 10:53 AM, Michelle Konzack wrote: > >> > >>SELECT * FROM products WHERE category IN > >> (SELECT categories.cat FROM categories WHERE > >> categories.serial = products.category); > Because you are comparing cate

Re: [GENERAL] SELECT statement with sub-queries

On 28.05.2017 20:19, m...@ft-c.de wrote: Hallo, SELECT * FROM products WHERE exists (SELECT categories.cat FROM categories WHERE categories.serial==products.category); categories.serial = products.category); -- one equal sign or SELECT * FROM products WHERE category IN

Re: [GENERAL] SELECT statement with sub-queries

On 05/28/2017 10:53 AM, Michelle Konzack wrote: On 2017-05-29 03:24:54 rob stone hacked into the keyboard: You only need a single equals sign in SQL. SELECT * FROM products WHERE category IN (SELECT categories.cat FROM categories WHERE categories.serial = products.category); I have

Re: [GENERAL] SELECT statement with sub-queries

Hallo, SELECT * FROM products WHERE exists (SELECT categories.cat FROM categories WHERE categories.serial==products.category); or SELECT * FROM products WHERE category IN (SELECT categories.cat FROM categories); On 28.05.2017 19:03, Michelle Konzack wrote: Hello *, I try to get

Re: [GENERAL] SELECT statement with sub-queries

On 2017-05-29 03:24:54 rob stone hacked into the keyboard: > You only need a single equals sign in SQL. > > SELECT * FROM products WHERE category IN  >     (SELECT categories.cat FROM categories WHERE  >     categories.serial = products.category); I have tried this too, but then I get: ERROR:  o

Re: [GENERAL] SELECT statement with sub-queries

Hello, On Sun, 2017-05-28 at 20:03 +0300, Michelle Konzack wrote: > Hello *, > > I try to get columns from my database with a singel SELECT, but I > stuck. > > I have 3 tables like: > > > 1)  categories (serial,cat) > > 2)  manufacturers (serial,m_name) > > 3)  products (serial,category,manu

Re: [GENERAL] SELECT statement with sub-queries

On Sun, May 28, 2017 at 10:33 PM, Michelle Konzack wrote: > Hello *, > > I try to get columns from my database with a singel SELECT, but I stuck. > > I have 3 tables like: > > > 1) categories (serial,cat) > > 2) manufacturers (serial,m_name) > > 3) products (serial,category,manufacturer,p_name

[GENERAL] SELECT statement with sub-queries

Hello *, I try to get columns from my database with a singel SELECT, but I stuck. I have 3 tables like: 1) categories (serial,cat) 2) manufacturers (serial,m_name) 3) products (serial,category,manufacturer,p_name) where the "category" and "manufacturer" are numerical IDs from the two tab

Re: [GENERAL] Select from tableA - if not exists then tableB

On Tue, May 9, 2017 at 6:00 PM, Patrick B wrote: > SELECT > split_part(n1.path::text, '/'::text, 18)::integer AS id, > split_part(n1.path::text, '/'::text, 14)::integer AS clientid, > lower(n1.md5::text)::character(32) AS md5, 0 AS cont, > '-1000-1000-3000-6000'::uuid AS guid, > n1

Re: [GENERAL] Select from tableA - if not exists then tableB

2017-05-09 10:19 GMT+12:00 Brian Dunavant : > From what you're saying about migrating, I'm assuming the new table > has additional columns or something. If you can map the difference, > then you could use CTE's to select from the first table, and if > nothing is there, then pull from the second t

Re: [GENERAL] Select from tableA - if not exists then tableB

>From what you're saying about migrating, I'm assuming the new table has additional columns or something. If you can map the difference, then you could use CTE's to select from the first table, and if nothing is there, then pull from the second table and pad it with nulls so they "match". This sh

Re: [GENERAL] Select from tableA - if not exists then tableB

On 9 May 2017 at 09:56, Patrick B wrote: > Hi guys, > > I have two tables that supports the same data, but different table DDL (We > are migrating all the data from one to another). > > What I need is basically: > > 1. Query looks for the data on table A, > 2. if it doesn't find it on table A, go

Re: [GENERAL] Select from tableA - if not exists then tableB

On 05/08/2017 02:56 PM, Patrick B wrote: Hi guys, I have two tables that supports the same data, but different table DDL (We are migrating all the data from one to another). What I need is basically: 1. Query looks for the data on table A, 2. if it doesn't find it on table A, go look for it on

[GENERAL] Select from tableA - if not exists then tableB

Hi guys, I have two tables that supports the same data, but different table DDL (We are migrating all the data from one to another). What I need is basically: 1. Query looks for the data on table A, 2. if it doesn't find it on table A, go look for it on table B Now, how could I do that in a Sel

Re: [GENERAL] SELECT FOR UPDATE violates READ COMMITTED isolation?

On Wed, Apr 12, 2017 at 3:14 PM, Gavin Wahl wrote: > I think this paragraph explains why it happens: > https://www.postgresql.org/docs/9.6/static/transaction- > iso.html#XACT-READ-COMMITTED. > > > If the first updater commits, the second updater will ignore the row if > the > > first updater dele

[GENERAL] SELECT FOR UPDATE violates READ COMMITTED isolation?

I have this table: CREATE TABLE test (id INT PRIMARY KEY); INSERT INTO test VALUES (1); Then I run these two transactions simultaneously: one| two ---+--- BEGIN; |

Re: [GENERAL] select on view shows different plan than select on table

On Wednesday, April 12, 2017, Bernd Lehmkuhl wrote: > > Why do I get different execution plans when querying the view like this: > SELECT > * > FROM > kkm_ergebnisse.v_protokoll_details_mit_dauer > WHERE > id_rechenlauf = 123 > ORDER BY > schritt ; > > opposed to querying against the defin

[GENERAL] select on view shows different plan than select on table

Hi list, I have a view defined as: CREATE VIEW kkm_ergebnisse.v_protokoll_details_mit_dauer AS SELECT id, schritt, objekt_typ, objekt_id, zeit, rechenweg_thema, rechenweg_variante, rechenweg_stoffgruppe, formel_inhalt, formel_stoff, formel_variablen, ergebnis_variable, e

Re: [GENERAL] SELECT and RowExclusiveLock

Tim Nelson writes: > New to Postgres and I have never seen this condition. We are getting test > applications hanging on SELECT statements with a RowExclusiveLock. How can > a SELECT cause a RowExclusiveLock? > relname | pid | mode | granted > --+

Re: [GENERAL] SELECT and RowExclusiveLock

On Fri, Apr 7, 2017 at 1:25 PM, Tim Nelson wrote: > New to Postgres and I have never seen this condition. We are getting test > applications hanging on SELECT statements with a RowExclusiveLock. How can > a SELECT cause a RowExclusiveLock? > Two common ways: SELECT ... FOR UPDATE; SELECT fun

Re: [GENERAL] SELECT and RowExclusiveLock

On 04/07/2017 01:25 PM, Tim Nelson wrote: New to Postgres and I have never seen this condition. We are getting test applications hanging on SELECT statements with a RowExclusiveLock. How can a SELECT cause a RowExclusiveLock? relname | pid | mode | granted --

[GENERAL] SELECT and RowExclusiveLock

New to Postgres and I have never seen this condition. We are getting test applications hanging on SELECT statements with a RowExclusiveLock. How can a SELECT cause a RowExclusiveLock? relname | pid | mode | granted --+---+--+---

Re: [GENERAL] SELECT x'00000000F'::int leading zeros causes "integer out of range"

On 2/25/17 at 6:56 AM, Gavin Flower wrote: On 25/02/17 08:39, John McKown wrote: On Fri, Feb 24, 2017 at 1:25 PM, David G. Johnston On Friday, February 24, 2017, Tom Lane wrote: Justin Pryzby writes: Is this expected behavior ? ts=# SELECT x'F'::int; ERROR: 22003: integer out of rang

Re: [GENERAL] SELECT x'00000000F'::int leading zeros causes "integer out of range"

On 25/02/17 08:39, John McKown wrote: On Fri, Feb 24, 2017 at 1:25 PM, David G. Johnston mailto:david.g.johns...@gmail.com>>wrote: On Friday, February 24, 2017, Tom Lane mailto:t...@sss.pgh.pa.us>> wrote: Justin Pryzby writes: > Is this expected behavior ? >

Re: [GENERAL] SELECT x'00000000F'::int leading zeros causes "integer out of range"

On Fri, Feb 24, 2017 at 1:25 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Friday, February 24, 2017, Tom Lane wrote: > >> Justin Pryzby writes: >> > Is this expected behavior ? >> > ts=# SELECT x'F'::int; >> > ERROR: 22003: integer out of range >> >

Re: [GENERAL] SELECT x'00000000F'::int leading zeros causes "integer out of range"

On Friday, February 24, 2017, Tom Lane wrote: > Justin Pryzby > writes: > > Is this expected behavior ? > > ts=# SELECT x'F'::int; > > ERROR: 22003: integer out of range > > LOCATION: bittoint4, varbit.c:1575 > > Yes. The provided operation is "convert a bitstring of

Re: [GENERAL] SELECT x'00000000F'::int leading zeros causes "integer out of range"

On Fri, Feb 24, 2017 at 2:13 PM, Tom Lane wrote: > Justin Pryzby writes: > > Is this expected behavior ? > > ts=# SELECT x'F'::int; > > ERROR: 22003: integer out of range > > LOCATION: bittoint4, varbit.c:1575 > > Yes. The provided operation is "convert a bitstring o

Re: [GENERAL] SELECT x'00000000F'::int leading zeros causes "integer out of range"

Justin Pryzby writes: > Is this expected behavior ? > ts=# SELECT x'F'::int; > ERROR: 22003: integer out of range > LOCATION: bittoint4, varbit.c:1575 Yes. The provided operation is "convert a bitstring of up to 32 bits to an integer". It's not "guess whether it's ok

[GENERAL] SELECT x'00000000F'::int leading zeros causes "integer out of range"

Is this expected behavior ? This works: ts=# SELECT x'000F'::int; int4|15 .. but an additional leading zero causes it to fail: ts=# SELECT x'F'::int; ERROR: 22003: integer out of range LOCATION: bittoint4, varbit.c:1575 |/* Check that the

Re: [GENERAL] SELECT slow immediately after many update or delete+insert, except using WHERE .. IN

2016-12-10 15:41 GMT+01:00 Adrian Klaver : > On 12/10/2016 04:21 AM, Tom DalPozzo wrote: > >> Hi, >> my release is 9.5.4. >> a took a look over it. I guessed that counting could be slow because it >> needs to read everything and also that it can take advantage from an >> index. But I don't underst

Re: [GENERAL] SELECT slow immediately after many update or delete+insert, except using WHERE .. IN

On 12/10/2016 04:21 AM, Tom DalPozzo wrote: Hi, my release is 9.5.4. a took a look over it. I guessed that counting could be slow because it needs to read everything and also that it can take advantage from an index. But I don't understand why the delay is after the updates for a Best guess, a

Re: [GENERAL] SELECT slow immediately after many update or delete+insert, except using WHERE .. IN

Hi, my release is 9.5.4. a took a look over it. I guessed that counting could be slow because it needs to read everything and also that it can take advantage from an index. But I don't understand why the delay is after the updates for a certain time and why WHERE..IN is much faster (ok, it's an in

Re: [GENERAL] SELECT slow immediately after many update or delete+insert, except using WHERE .. IN

On 12/09/2016 08:03 AM, Tom DalPozzo wrote: > Hi, > I did two tests: > TEST 1 > 1 I created a table ("Table") with two fields, one ("Id") is a bigint > and the other ("Data") is a bytea. Also created an index on Id. > 2 Populated the table with 1 rows, in which the bigint is > incremental and

[GENERAL] SELECT slow immediately after many update or delete+insert, except using WHERE .. IN

Hi, I did two tests: TEST 1 1 I created a table ("Table") with two fields, one ("Id") is a bigint and the other ("Data") is a bytea. Also created an index on Id. 2 Populated the table with 1 rows, in which the bigint is incremental and bytea is 1000 bytes long. 3 Executed SELECT COUNT(*) FROM

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

Joseph Brenner writes: > I thought I'd reproduced the behavior in an xterm, but I was just > trying again and I don't see it. It does seem that the dumbness of my > dumb terminal is a factor. Evidently. > If I understand the way this works, it could be an even more baffling > behavior if I were

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

Yes, I have a tendency to use emacs sub-shells (and occasionally M-x sql-postgres)-- I thought I'd reproduced the behavior in an xterm, but I was just trying again and I don't see it. It does seem that the dumbness of my dumb terminal is a factor. If I understand the way this works, it could be

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

Tom Lane wrote: > BTW, I realized while testing this that there's still one gap in our > understanding of what went wrong for you: cases like "SELECT 'hello'" > should not have tried to use the pager, because that would've produced > less than a screenful of data At some point emacs was m

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

Joseph Brenner writes: > Well, my take would be that if you've taken the trouble to set an > empty string as the PAGER that means something, and it probably means > you don't want any pager to be used. Yeah, on reflection that argument seems pretty persuasive. So I propose the attached patch. B

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

But on the other hand, if you've got a blank PAGER envar and a "\pset pager something", the pset should win (just as it does now). On Tue, Dec 6, 2016 at 1:53 PM, Joseph Brenner wrote: > Well, my take would be that if you've taken the trouble to set an > empty string as the PAGER that means some

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

Well, my take would be that if you've taken the trouble to set an empty string as the PAGER that means something, and it probably means you don't want any pager to be used. But then, I would say that. On Tue, Dec 6, 2016 at 12:13 PM, Tom Lane wrote: > Joseph Brenner writes: >>> Agreed. One th

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] Select works only when connected from login postgres

Joseph Brenner writes: >> Agreed. One thing that would be very simple is to treat an empty PAGER >> value the same as "unset". > Sounds excellent. Actually, after thinking about it a bit longer, should PAGER-set-but- empty be treated as equivalent to "pager off", rather than as a request to use

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

> Agreed. One thing that would be very simple is to treat an empty PAGER > value the same as "unset". Sounds excellent. > Detecting whether a nonempty value is behaving > sanely seems a great deal harder ... I was thinking a check for existence and executability, but I guess that's covered alre

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

Joseph Brenner writes: > Looking back on the order of events, I think it went like this: > [ careful postmortem ] Thanks for following up! > So yeah, some better messaging when PAGER is mangled wouldn't hurt, if > that's possible. Falling back to "pager off" would make sense to me. Agreed. On

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

Looking back on the order of events, I think it went like this: Back in around May, I was annoyed at pager behavior and wanted to get rid of them-- I tried a blank PAGER setting in my .bashrc (and forgot about it). I also noticed the psql option "\pset pager off". For not particular reason, it d

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

Well yeah, trying to run a PAGER that's not there might throw an error. Or you know, nothing in PAGER might imply "pager off". > I find it a bit odd that all of your queries were using the pager...did I > miss where you reported that setting? I didn't report it because I wasn't looking in that d

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

And I guess I did that intentionally, my .bashrc has # I use emacs shells, I got a "pager" already: export PAGER='' On Mon, Dec 5, 2016 at 8:52 PM, Joseph Brenner wrote: > Wait, that's not quite right. The user 'postgres' has no PAGER envar, > but user 'doom' has an empty value: > > PAGER=

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

Wait, that's not quite right. The user 'postgres' has no PAGER envar, but user 'doom' has an empty value: PAGER= On Mon, Dec 5, 2016 at 8:50 PM, Joseph Brenner wrote: >> So what does: >> >> env | grep PAGER >> >> show? > > Nothing. I have no PAGER settting (I don't normally use one). > > > On

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

> So what does: > > env | grep PAGER > > show? Nothing. I have no PAGER settting (I don't normally use one). On Mon, Dec 5, 2016 at 5:33 PM, Adrian Klaver wrote: > On 12/05/2016 05:13 PM, Joseph Brenner wrote: > > >> I just went around temporarily undoing things I did while >> straigtening up,

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

Joseph Brenner writes: > Okay: I think I'm closing in on the trouble. I didn't used to > have a ~/.psqlrc file, but recently I experimented with > creating one. When I have a .psqlrc file containing the magic > incantaion >\pset pager off > *Then* everything works. Ah! So, most likely, t

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

On 12/05/2016 05:13 PM, Joseph Brenner wrote: I just went around temporarily undoing things I did while straigtening up, and I find there's one thing I can do that consistently breaks things: removing my new ~/.psqlrc file. In fact, it appears that I need to have a file that exists and contains

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

Okay: I think I'm closing in on the trouble. I didn't used to have a ~/.psqlrc file, but recently I experimented with creating one. When I have a .psqlrc file containing the magic incantaion \pset pager off *Then* everything works. All three of my extant postgresql installations work correc

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

Joseph Brenner writes: >> So what happens when you specify the port in your psql connection, eg: >> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5432 >> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5433 >> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5434

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

Adrian Klaver writes: > My suspicion is that when you did the source build you got some cross > contamination of libraries. That's the best theory I can come up with either, although libpq's APIs haven't really changed in any non-backwards-compatible fashion in years. I could imagine a newer ps

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

Yeah, I get the sense I need to simplify the situation, if only to eliminate some distractions from the story. This morning I was thinking I want to play around with pg_hba.conf settings some more, but after that I'll do some pg_dumps and uninstalls and see how things behave. Oh, trying: /usr/

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

On 12/03/2016 09:38 PM, Joseph Brenner wrote: So is the 9.4 instance the production/live database? Essentially, but it's not heavily used: this is me messing around on a dev box. So what happens when you specify the port in your psql connection, eg: /usr/local/pgsql/bin/psql --dbname=doom --u

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

> So is the 9.4 instance the production/live database? Essentially, but it's not heavily used: this is me messing around on a dev box. > So what happens when you specify the port in your psql connection, eg: > /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5432 > /usr/local/pgsql/bin/

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

On 12/03/2016 07:38 PM, Joseph Brenner wrote: Our story thus far: I've now got three different pg installations, with three servers running simultaneously: ps ax | egrep postgres | egrep '\-D' 748 ?S 0:04 /usr/lib/postgresql/9.4/bin/postgres -D /var/lib/postgresql/9.4/main -c conf

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

>For kicks, how about \echo or \! Something that doesn't need a server to work. Sure: those do work. doom@tango:~$ /usr/local/pgsql/bin/psql --dbname=doom --username=doom psql (9.6.1) Type "help" for help. doom=# select 'hello' as world; doom=# \echo 'yo' yo doom=# \! ls -lad p* drwxr-xr-x 1 do

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

Our story thus far: I've now got three different pg installations, with three servers running simultaneously: ps ax | egrep postgres | egrep '\-D' 748 ?S 0:04 /usr/lib/postgresql/9.4/bin/postgres -D /var/lib/postgresql/9.4/main -c config_file=/etc/postgresql/9.4/main/postgresql.co 2

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

Joseph Brenner writes: > I'm trying to get a new build of 9.6.1 working on Debian > stable and I'm seeing some odd behavior where things work > correctly if I run psql when logged in as user 'postgres', > but if I'm logged-in as user 'doom' (my usual login), I don't > seem to have any select privi

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

Yes, and sorry about the re-post. I thought my original message was hung-up in moderation, so I was doing an unsub/resub fandango to get email addresses to match. On Sat, Dec 3, 2016 at 12:13 PM, Adrian Klaver wrote: > On 12/03/2016 12:08 PM, Joseph Brenner wrote: >> >> I'm trying to get a new

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

On 12/03/2016 12:08 PM, Joseph Brenner wrote: I'm trying to get a new build of 9.6.1 working on Debian stable and I'm seeing some odd behavior where things work correctly if I run psql when logged in as user 'postgres', but if I'm logged-in as user 'doom' (my usual login), I don't seem to have an

[GENERAL] Select works only when connected from login postgres

I'm trying to get a new build of 9.6.1 working on Debian stable and I'm seeing some odd behavior where things work correctly if I run psql when logged in as user 'postgres', but if I'm logged-in as user 'doom' (my usual login), I don't seem to have any select privileges. Even this fails silently:

Re: [GENERAL] select function alias

On Wed, Nov 30, 2016 at 12:15 PM, David G. Johnston wrote: > 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 shortname

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] select function alias

- Original Message - > From: "Howard News" > Sent: Wednesday, November 30, 2016 9:03:28 AM > Subject: Re: [GENERAL] select function alias > On 30/11/2016 13:42, Timoteo Blanco wrote: > > I've a series of timestamp columns I'd like to alias in se

Re: [GENERAL] select function alias

On 30/11/2016 13:42, Timoteo Blanco wrote: Howdy, I've a series of timestamp columns I'd like to alias in select statements. psql indicates my alias doesnt exist after I define it. Example -> select to_char(impressions_create_date,'-mm-dd') as ymd from impressionsdb where ymd like '201

[GENERAL] select function alias

Howdy, I've a series of timestamp columns I'd like to alias in select statements. psql indicates my alias doesnt exist after I define it. Example -> select to_char(impressions_create_date,'-mm-dd') as ymd from impressionsdb where ymd like '2016-11%' ; psql always complains column ymd does no

Re: [GENERAL] SELECT DISTINCT ON removes results

On 28 October 2016 at 21:39, 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.number::

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 > surprising given its definition. > > > What is surpris

Re: [GENERAL] SELECT DISTINCT ON removes results

> On Oct 28, 2016, at 13:50 , David G. Johnston > wrote: > > On Fri, Oct 28, 2016 at 1:39 PM, Guyren Howe > wrote: > Using 9.5, this query: Unless I'm missing something, this ought to be impossible. Two queries differing only in having a DISTINCT clause produce the s

Re: [GENERAL] SELECT DISTINCT ON removes results

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 >> surprising given its definition. > > What is surprising is that it remov

Re: [GENERAL] SELECT DISTINCT ON removes results

On Oct 28, 2016, at 13:50 , David G. Johnston wrote: > > ​On its face the statement "DISTINCT ON removes results" is not at all > surprising given its definition. What is surprising is that it removes *all* results…

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

[GENERAL] SELECT DISTINCT ON removes results

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.number::text, ','::text) AS number, air_way_bills.order_id

Re: [GENERAL] select distinct postgres 9.2

2016-09-19 9:18 GMT+12:00 Patrick B : > Hi guys, > > I've got the following query: > > WITH >> accounts AS ( >> SELECT >> c.id AS company_id, >> c.name_first AS c_first_name, >> c.name_last AS c_last_name, >> c.company AS c_name, >> FROM public.clients c >>

[GENERAL] select distinct postgres 9.2

Hi guys, I've got the following query: WITH > accounts AS ( > SELECT > c.id AS company_id, > c.name_first AS c_first_name, > c.name_last AS c_last_name, > c.company AS c_name, > FROM public.clients c > WHERE id = 33412393 > ORDER BY 1 LIMIT 100 >

Re: [GENERAL] select date between - PostgreSQL 9.5

On Wed, Sep 14, 2016 at 4:49 AM, Patrick B wrote: > > > 2016-09-14 13:17 GMT+12:00 David Rowley : > >> On 14 September 2016 at 12:20, Patrick B >> wrote: >> > I want to select all rows that have been modified from now to 4 months >> ago. >> > >> > I've used these queries: >> > >> >> select >> >>

Re: [GENERAL] select date between - PostgreSQL 9.5

2016-09-14 13:17 GMT+12:00 David Rowley : > On 14 September 2016 at 12:20, Patrick B wrote: > > I want to select all rows that have been modified from now to 4 months > ago. > > > > I've used these queries: > > > >> select > >> modified_date, > >> from > >> clients > >> WHERE > >>

Re: [GENERAL] select date between - PostgreSQL 9.5

On 14 September 2016 at 12:20, Patrick B wrote: > I want to select all rows that have been modified from now to 4 months ago. > > I've used these queries: > >> select >> modified_date, >> from >> clients >> WHERE >> modified_date BETWEEN '2016-06-13' AND '2016-09-13' > Going by my

Re: [GENERAL] select date between - PostgreSQL 9.5

On 9/13/16, Patrick B wrote: > Hi guys, > > I got the following column: > > modified_date TIMESTAMP(6) WITHOUT TIME ZONE DEFAULT >> "statement_timestamp"(), > > > I want to select all rows that have been modified from now to 4 months ago. > > I've used these queries: > > select >> modified

Re: [GENERAL] select date between - PostgreSQL 9.5

On 09/13/2016 05:20 PM, Patrick B wrote: Hi guys, I got the following column: modified_date TIMESTAMP(6) WITHOUT TIME ZONE DEFAULT "statement_timestamp"(), I want to select all rows that have been modified from now to 4 months ago. I've used these queries: select mod

[GENERAL] select date between - PostgreSQL 9.5

Hi guys, I got the following column: modified_date TIMESTAMP(6) WITHOUT TIME ZONE DEFAULT > "statement_timestamp"(), I want to select all rows that have been modified from now to 4 months ago. I've used these queries: select > modified_date, > from > clients > WHERE > modified

Re: [GENERAL] SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

Thank you Craig, this has worked in my custom function too: BEGIN PERFORM check_positions(in_uid, in_gid, in_tiles); CREATE TEMP TABLE _words ON COMMIT DROP AS SELECT out_word AS word, max(out_score) AS score FROM check_words(in_uid,

Re: [GENERAL] SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

On 12 August 2016 at 18:43, Alexander Farber wrote: > Thank you, I have rewritten it into: > > BEGIN > PERFORM check_positions(in_uid, in_gid, in_tiles); > > CREATE TEMP TABLE _words(word varchar, score integer) ON COMMIT > DROP; > > INSERT INTO _words > SELECT >

Re: [GENERAL] SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

Thank you, I have rewritten it into: BEGIN PERFORM check_positions(in_uid, in_gid, in_tiles); CREATE TEMP TABLE _words(word varchar, score integer) ON COMMIT DROP; INSERT INTO _words SELECT out_word AS word, max(out_score) AS score

Re: [GENERAL] SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

2016-08-12 11:00 GMT+02:00 Alexander Farber : > Francisco, thanks, but - > > On Fri, Aug 12, 2016 at 10:47 AM, Francisco Olarte > wrote: > >> >> https://www.postgresql.org/docs/9.5/static/plpgsql-statement >> s.html#PLPGSQL-STATEMENTS-SQL-ONEROW >> >> > but the custom function I am trying to call

Re: [GENERAL] SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

Alexander: On Fri, Aug 12, 2016 at 11:00 AM, Alexander Farber wrote: > but the custom function I am trying to call (from another function) does not > return one row, but several rows, which I'd like to store into a temp table: This I know, I wasn't trying to solve the problem. I was just trying

  1   2   3   4   5   6   7   8   9   10   >