[GENERAL] Unique - first

2013-10-27 Thread Robert James
I have a table (x,y,z) - I'd like to take the rows with unique x values - but, when more than one row have the same x value, I want the one with the minimal z value. How can I do that? I can imagine doing it with window functions, but also that regular SQL should be able to do it too. -- Sent v

Re: [GENERAL] Unique - first

2013-10-27 Thread Thomas Kellerer
Robert James wrote on 27.10.2013 14:04: I have a table (x,y,z) - I'd like to take the rows with unique x values - but, when more than one row have the same x value, I want the one with the minimal z value. How can I do that? I can imagine doing it with window functions, but also that regular SQL

Re: [GENERAL] Segmentation fault: pg_upgrade 9.1 to 9.3: pg_dump: row number 0 is out of range 0..-1

2013-10-27 Thread Tom Lane
Alan Nilsson writes: > What I am saying is that something changed in 90300 that causes libpq to spew > to stdout where it had not in libpq 90102 & 90203. Well, that's interesting, but the issue is not in libpq, and you've still provided no information that would help anyone diagnose where it is

Re: [GENERAL] Unique - first

2013-10-27 Thread Tom Lane
Thomas Kellerer writes: > Robert James wrote on 27.10.2013 14:04: >> I have a table (x,y,z) - I'd like to take the rows with unique x >> values - but, when more than one row have the same x value, I want the >> one with the minimal z value. >> >> How can I do that? I can imagine doing it with win

Re: [GENERAL] Unique - first

2013-10-27 Thread Rowan Collins
On 27/10/2013 13:04, Robert James wrote: I have a table (x,y,z) - I'd like to take the rows with unique x values - but, when more than one row have the same x value, I want the one with the minimal z value. How can I do that? I can imagine doing it with window functions, but also that regular SQ

[GENERAL] search_path and current_schema

2013-10-27 Thread Jayadevan M
Hi, I was trying out schema settings and related functions. PostgreSQL version is 9.3 [postgres@MyCentOS 9.3]$ psql psql (9.3.0) Type "help" for help. postgres=# show search_path; search_path "$user",public (1 row) postgres=# select current_schemas(true); current_schemas

Re: [GENERAL] search_path and current_schema

2013-10-27 Thread Tom Lane
Jayadevan M writes: > I thought current_schemas and search_path will return the same set of > schemas (except that current_schema will show pg_catalog also, if we use > true). Shouldn't mynewschema appear in the output of select > current_schemas(true)? Only if it actually exists (and you have us

[GENERAL] Work table

2013-10-27 Thread Robert James
I'm using Postgres for data analysis (interactive and batch). I need to focus the analysis on a subset of one table, and, for both performance and simplicity, have a function which loads that subset into another table (DELETE FROM another_table; INSERT INTO another_table SELECT ...). Oddly enough

Re: [GENERAL] Work table

2013-10-27 Thread Thomas Kellerer
Robert James wrote on 27.10.2013 20:47: I'm using Postgres for data analysis (interactive and batch). I need to focus the analysis on a subset of one table, and, for both performance and simplicity, have a function which loads that subset into another table (DELETE FROM another_table; INSERT INT

Re: [GENERAL] Work table

2013-10-27 Thread Robert James
On 10/27/13, Thomas Kellerer wrote: > Robert James wrote on 27.10.2013 20:47: >> I'm using Postgres for data analysis (interactive and batch). I need >> to focus the analysis on a subset of one table, and, for both >> performance and simplicity, have a function which loads that subset >> into ano

Re: [GENERAL] Work table

2013-10-27 Thread Adrian Klaver
On 10/27/2013 02:23 PM, Robert James wrote: On 10/27/13, Thomas Kellerer wrote: Robert James wrote on 27.10.2013 20:47: I'm using Postgres for data analysis (interactive and batch). I need to focus the analysis on a subset of one table, and, for both performance and simplicity, have a functio

Re: [GENERAL] Work table

2013-10-27 Thread Robert James
On 10/27/13, Adrian Klaver wrote: > On 10/27/2013 02:23 PM, Robert James wrote: >> On 10/27/13, Thomas Kellerer wrote: >>> Robert James wrote on 27.10.2013 20:47: I'm using Postgres for data analysis (interactive and batch). I need to focus the analysis on a subset of one table, and, f

Re: [GENERAL] Work table

2013-10-27 Thread Adrian Klaver
On 10/27/2013 02:48 PM, Robert James wrote: On 10/27/13, Adrian Klaver wrote: Is there another problem here? Perhaps something to do with triggerring autovacuum? Is there a FK relationship involved? Could we see the schema for another_table? 1. No FK 2. I removed the indexes from the t

Re: [GENERAL] search_path and current_schema

2013-10-27 Thread Jayadevan M
OK. When I logged in as a user who had access to the schema, the output from current_schemas and search_path were matching. Thanks. On Sun, Oct 27, 2013 at 9:55 PM, Tom Lane wrote: > Jayadevan M writes: > > I thought current_schemas and search_path will return the same set of > > schemas (exce

Re: [GENERAL] Unique - first

2013-10-27 Thread Marcin Mańk
On Sun, Oct 27, 2013 at 2:04 PM, Robert James wrote: > I have a table (x,y,z) - I'd like to take the rows with unique x > values - but, when more than one row have the same x value, I want the > one with the minimal z value. > You can use distinct on (which is a Postgresql extension to the SQL

Re: [GENERAL] PGAdmin and user privileges - what I do wrong?

2013-10-27 Thread anatoly techtonik
This saved my day. Thanks! In pgAdmin III the option it is: File -> Options... -> Browser > UI Miscellaneous This stuff is confusing for a beginner. I wish pgadmin III had some tooltip or Help link to relevant chapter about "Why creating users is discouraged?". -- View this message in context: