On Wed, Oct 3, 2012 at 9:31 AM, Robert Buck wrote:
> Samuel,
>
> You asked:
>
> Some questions before I provide crosstab samples - your example query has
> a hardcoded set of keys that it is looking for, but maybe that was
> code-generated. There are multiple forms of the crosstab function, some
e same row.
On Tue, Oct 2, 2012 at 12:57 PM, Samuel Gendler
wrote:
>
>
> On Tue, Oct 2, 2012 at 2:45 AM, Robert Buck wrote:
>
>> Hi Samuel
>>
>> Thank you. This may be a bit of a stretch for you, but would it be
>> possible for me to peek at a sanitized version
On Tue, Oct 2, 2012 at 2:45 AM, Robert Buck wrote:
> Hi Samuel
>
> Thank you. This may be a bit of a stretch for you, but would it be
> possible for me to peek at a sanitized version of your cross tab query, for
> a good example on how to do this for this noob?
>
> This will be pretty common in m
On Mon, Oct 1, 2012 at 11:46 PM, Thomas Kellerer wrote:
>
> That combined with the tablefunc module (which let's you do pivot queries)
> might
> make your queries substantially more readable (and maybe faster as well).
>
>
I woud think that using the crosstab functions in tablefunc would solve th
On Sat, Sep 29, 2012 at 9:02 AM, Andreas wrote:
> Hi,
>
> asume I've got 2 tables
>
> objects ( id int, name text )
> attributes ( object_id int, value int )
>
> attributes has a default entry with object_id = 0 and some other where
> another value should be used.
>
> e.g.
> objects
> ( 1,
you put a conditional clause in the order by statement, either by
referencing a column that is populated conditionally, like this
select A, when B < C Then B else C end as condColumn, B, C, D
from ...
where ...
order by 1,2, 5
or
select A, when B < C Then B else C end as condColumn, B, C, D
from
I'll admit I don't see any reason why you should get duplicate rows based
on the data you've provided, but I am wondering why you are using the
subquery instead of just 'where r.r_id = 5695'
select p.p_id, r.pr_ind
from pieces p
join pieces_requests r on p.p_id = r.p_id
where r.r_id = 5695
Though
I have the following crosstab query, which needs to be parameterized in the
2 inner queries:
SELECT * FROM crosstab(
$$
SELECT t.local_key,
s.sensor_pk,
CASE WHEN t.local_day_abbreviation IN (?,?,?,?,?,?,?) THEN
q.dpoint_value
ELSE NULL
END as dpoint
I need to tag entities in my db with arbitrary strings. That part is
simple enough. The difficulty is that I need to select entities based on
whether they match an expression built via boolean combinations of tags:
(tag1 || tag2 || tag3) && tag4
I can roll my own tagging mechanism, in which case
On Wed, Jun 27, 2012 at 7:26 PM, David Johnston wrote:
> On Jun 27, 2012, at 21:07, Andreas wrote:
>
>
> You should look and see whether the hstore contrib module will meet your
> needs.
>
> http://www.postgresql.org/docs/9.1/interactive/hstore.html
>
>
hstore is certainly an option, as are the
On Fri, Jun 15, 2012 at 2:28 AM, Samuel Gendler
wrote:
>
>
> On Fri, Jun 15, 2012 at 1:24 AM, Achilleas Mantzios <
> ach...@matrix.gatewaynet.com> wrote:
>
>>
>> And i forgot to mention, minicom term emulation quality sucks, even
>> giving simple
>>
On Fri, Jun 15, 2012 at 1:24 AM, Achilleas Mantzios <
ach...@matrix.gatewaynet.com> wrote:
>
> And i forgot to mention, minicom term emulation quality sucks, even giving
> simple
> shell commands is a PITA, upgrading the whole fleet would mean bast case
> scenario
> minimum 21K USD for the whole f
On Wed, May 23, 2012 at 12:07 PM, Lee Hachadoorian <
lee.hachadooria...@gmail.com> wrote:
> On Wed, May 23, 2012 at 5:24 AM, Ferruccio Zamuner
> wrote:
> > Hi,
> >
> > I like PostgreSQL for many reasons, one of them is the possibility to use
> > sub query everywhere. Now I've found where it doesn
On Wed, May 16, 2012 at 3:46 PM, Wes James wrote:
>
>
> On Mon, May 14, 2012 at 5:00 PM, Tom Lane wrote:
>
>> Wes James writes:
>> > Why is there a different order on the different platforms.
>>
>> This is not exactly unusual. You should first check to see if
>> lc_collate is set differently i
On Sun, May 13, 2012 at 8:11 PM, Tom Lane wrote:
> It strikes me that "cannot be cast" is a poor choice of words here,
> since the types *can* be cast if you try. Would it be better if the
> message said "cannot be cast implicitly to type foo"? We could also
> consider a HINT mentioning use of
On Tue, Apr 24, 2012 at 3:37 PM, Andreas wrote:
> Am 25.04.2012 00:04, schrieb Joe Conway:
>
> On 04/24/2012 02:42 PM, David Johnston wrote:
>>
>>> You must specify the output record structure:
>>>
>>> SELECT crosstab(text, text) AS ( col1_name col1_type [, colN_name
>>> colN_type]* )
>>>
>>> Wh
On Tue, Apr 24, 2012 at 1:01 PM, Andreas wrote:
> Hi,
>
> is there a generic solution to dump the result of a query as a crosstab,
> when I can't know how many columns I will need?
>
> E.g. I get something like this:
>
> id, x
> 1, a
> 1, b
> 1, c
> 2, l
> 2, m
>
>
>
Yes. You can provide a
On Fri, Mar 2, 2012 at 3:49 AM, Philip Couling wrote:
> Hi Rehan
>
> I suggest attempting to drop the table before you create the temp table:
> DROP TABLE IF EXISTS table1;
>
> See:
> http://www.postgresql.org/docs/current/static/sql-droptable.html
>
>
> Also if you're using an actual TEMP table,
t; ** **
>
> *From:* pgsql-sql-ow...@postgresql.org [mailto:
> pgsql-sql-ow...@postgresql.org] *On Behalf Of *Edward W. Rouse
> *Sent:* Tuesday, January 31, 2012 4:27 PM
> *To:* 'Samuel Gendler'
>
> *Cc:* pgsql-sql@postgresql.org
> *Subject:* Re: [SQL] must appear
On Tue, Jan 31, 2012 at 12:27 PM, Edward W. Rouse wrote:
> And in most cases this works fine. The problem arises when invoices get
> added to the exception table due to their not being an invoice number. Even
> though we join on the tracking id, the group by on invoicenum lumps the
> different bla
On Thu, Jan 19, 2012 at 8:20 AM, Adrian Klaver wrote:
> On Thursday, January 19, 2012 7:59:27 am Gary Stainburn wrote:
> > The following code works in 8.4 but not 8.3.
> > Anyone know why, or what I need to do to change it?
> >
> > SELECT aid, asid,
> >date_range (asdate, afdate)::date AS
On Thu, Jan 19, 2012 at 1:57 AM, Rehan Saleem wrote:
> hi ,
>
> how can i return the whole user table from this function not just the id .
> thanks
>
> Chapter 39, specifically 39.3, of the postgresql documentation provides
all of the information necessary to answer this question. If, after
rea
On Tue, Jan 17, 2012 at 12:49 AM, Thomas Kellerer wrote:
> Gera Mel Handumon, 17.01.2012 07:31:
>
> What version of postgresql that the update compatibility below will be
>> implemented?
>>
>> UPDATE COMPATIBILITY
>>
>>
>> UPDATE accounts SET (contact_last_name, contact_first_name) =
>> (SELE
would someone with the appropriate authority please unsubscribe this
person's email address from this list so we don't all get a bounce message
after every email we send to the list? Thanks.
--sam
-- Forwarded message --
From: Jan Verheyden
Date: 2012/1/2
Subject: Re: [SQL] i w
On Wed, Jan 4, 2012 at 1:57 AM, Zdravko Balorda wrote:
> Andreas Kretschmer wrote:
>
>> rverghese wrote:
>>
>> I want to insert a bunch of records and not do anything if the record
>>> already
>>> exists. So the 2 options I considered are 1) check if row exists or
>>> insert
>>> and 2) ignore th
On Sun, Jan 1, 2012 at 8:21 PM, vinodh chowdary wrote:
> Hi sir,
>
> i am using postgres as database for my project.
> i want to convert postgres data into excel sheet.
> is there any way to do it?
> please reply me.
>
>
You can export data in CSV format, which excel can easily read, or you can
s
On Fri, Dec 30, 2011 at 4:03 PM, Scott Marlowe wrote:
> On Fri, Dec 30, 2011 at 4:19 PM, Tom Lane wrote:
> > Jan Bakuwel writes:
> >> Why-o-why have the PostgreSQL developers decided to do it this way...?
> >
> > Because starting and cleaning up a subtransaction is an expensive thing.
> > If we
On Mon, Dec 19, 2011 at 12:16 AM, Gabriel Filipiak <
gabriel.filip...@gmail.com> wrote:
> Hi,
>
> so I am working on PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled
> by gcc (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 64-bit.
>
> It seems that i can't give a table name for example testTable it ha
On Tue, Nov 15, 2011 at 4:28 PM, John Fabiani wrote:
> On Tuesday, November 15, 2011 08:33:54 am Richard Broersma wrote:
> > On Tue, Nov 15, 2011 at 8:06 AM, John Fabiani
> wrote:
> > > alter sequence somename restart with (select max(pk) from sometable).
> > >
> > > I need this for automating a
On Sun, Nov 6, 2011 at 9:06 AM, Gabriel Filipiak wrote:
> I have problem with creating a pivot table in postgreSQL using crosstab
> function. It works well but it produces many records for the same
> client_id, how can I avoid it?
>
> Here is the SQL:
>
> SELECT * FROM crosstab('SELECT client_id,
On Tue, Oct 18, 2011 at 2:47 PM, Scott Swank wrote:
> I have a postgres 9.1 database up & running, no problem. Purely in
> terms of writing sql (ddl, dml & pg/plsql), what tools are
> recommended?
>
> Coming from an Oracle world, I'm thinking of toad, sql developer, etc.
>
> 1. psql & text editor
On Wed, Sep 21, 2011 at 4:49 PM, David Johnston wrote:
> ** **
>
> *
> *
>
> Not tested but I think all local variables are implicitly scoped to the
> function name so you should be able to do the following:
>
> ** **
>
> WHERE reporting_mgmt.aggregate_timescales_impl.tbl_schema = e.tbl_schem
I've got a stored proc (which worked fine in 8.3 and 8.4) that is declared
as such:
CREATE OR REPLACE FUNCTION reporting_mgmt.aggregate_timescales_impl (
div_start TIMESTAMP WITHOUT TIME ZONE,
* tbl_schema VARCHAR, *
tbl_root VARCHAR,
fine_timescale VARCHAR,
coars
On Mon, Sep 12, 2011 at 3:36 AM, Boris wrote:
>
> SELECT (
> UPDATE tbl SET val = 1
> WHERE KEY = any('{0,1,2,3,4,5}'::int[])
> returning key
> );
>
> cause syntax error. Is any query of such type (update warped into
> select) is possible?
>
>
Just lose the select ();
On Wed, Aug 31, 2011 at 10:15 PM, bhavesh1385 wrote:
> Hello All,
>
> I Want some basic compare of data type on PostgreSQL and MySQL.
>
> I am Try to using create the database on PostgreSQL with the same query on
> MySQL then it will create a problem...
>
> I have make changes according to the Dat
On Thu, Aug 25, 2011 at 3:56 PM, wrote:
> This is my first shot at using postgresql's geometric functions
> I have a passel of lines defined by x1 y1 x2 y2
> I created a table with type lseg and loaded it
> I start by finding the line that has an end closest to 0,0
> Now I want to find the next l
I don't have time to experiment with actual queries, but you can use the
rank() window function to rank rows with prio sorted ascending, and do the
same thing to rank rows with prio sorted descending, and update rows with
the value from the second where the rank matches the rank from the first.
I'
On Tue, Aug 23, 2011 at 1:27 AM, Herouth Maoz wrote:
> My thanks to everyone who replied.
>
> I have decided not to implement that constraint at this time. Using a
> compound type will make the system more complicated and less readable, plus
> requires installing the package which is beyond vanil
On Thu, Aug 11, 2011 at 8:39 AM, Don Parris wrote:
> Hi all,
>
> Note: I'm happy to read howtos, tutorials, archived messages - I just
> haven't found anything that addresses this yet. I found a related topic on
> the novice list, but my post got no response there. I've been struggling
> with t
On Thu, Aug 4, 2011 at 4:42 AM, Gnanakumar wrote:
> Hi Amitabh,
>
> ** **
>
> Yes, I already took a glance of the Date/Time functions. But what I’m
> expecting is something more meaningful/user-friendly value to be returned
> than from the actual return value of these functions available he
On Tue, Jul 5, 2011 at 10:42 AM, M. D. wrote:
> This is a little hard to explain, and I'm not sure if it's possible, but
> here goes.
>
> This is my query:
> select year, month,
> (select number from account where account.account_id =
> view_account_change.account_**id) as number,
> (select name
On Tue, Jul 5, 2011 at 2:42 PM, John Fabiani wrote:
> On Tuesday, July 05, 2011 01:11:11 pm Kevin Crain wrote:
> > You don't need a loop there. Assuming your order id field is of type
> > varchar you can just build the first part of your string and then do a
> > count to get the last part using
On Tue, Jul 5, 2011 at 6:52 AM, John Fabiani wrote:
> Hi,
>
> I have a special need to create a sequence like function.
>
> "O-20110704 -2" which is
> "O" for order (there are other types)
> "20110704" is for July 4, 2011
> '2' the second order of the day for July 4, 2011
>
> I of course can get
On Wed, Jun 22, 2011 at 8:57 PM, Pavel Stehule wrote:
> 2011/6/22 Peter Eisentraut :
> > On ons, 2011-06-22 at 02:39 -0700, Samuel Gendler wrote:
> >> Pavel suggested using a collation of ucs_basic, but I get an error
> >> when I
> >> try that on linux:
>
On Wed, Jun 22, 2011 at 3:39 AM, Samuel Gendler
wrote:
>
>
> On Wed, Jun 22, 2011 at 3:01 AM, Pavel Stehule wrote:
>
>> Hello Peter
>>
>>
>> > Pavel suggested using a collation of ucs_basic, but I get an error when
>> I
>> > try that on linux:
On Wed, Jun 22, 2011 at 3:01 AM, Pavel Stehule wrote:
> Hello Peter
>
>
> > Pavel suggested using a collation of ucs_basic, but I get an error when I
> > try that on linux:
> > $ createdb -U u1 --lc-collate=ucs_basic -E UTF-8 test
> > createdb: database creation failed: ERROR: invalid locale name
On Wed, Jun 22, 2011 at 2:39 AM, Samuel Gendler
wrote:
>
> I was able to create the db with --lc_collate=C and get case-sensitive
> sorting that treats spaces 'correctly,' but I have no idea how reliable that
> is with multibyte characters and it almost certainly d
Interesting. The original thread to which I was referring has a subject of
"Sorting Issue" and the original request showed a list of vehicle model
names which were sorting as though there were no spaces. The user had
collation set to en_US.UTF-8. However, my database (on OS X) sorts both his
exam
On Tue, Jun 21, 2011 at 3:37 AM, Eyal Wilde wrote:
> the database collation is: en_US.UTF-8
>
> drop table t1;
> create table t1 (recid int ,f1 varchar(20));
> insert into t1 values (1,'a');
> insert into t1 values (2,' ');
> insert into t1 values (3,'aa');
> insert into t1 values (4,' a');
> sel
Actually, you need the array_agg() function to aggregate multiple rows into
a single array, and that is discussed on the aggregate functions page,
here: http://www.postgresql.org/docs/9.0/static/functions-aggregate.html
You could then use one of the other array functions to manipulate that
array.
Look at the documentation for the tablefunc contrib module. It provides a
crosstab() function which will allow you to pivot multiple rows into multiple
columns in a single row, with all rows that share a particular key in one or
more columns pivoting up into the same row, but different keys endi
On Wed, Jun 15, 2011 at 10:23 AM, Samuel Gendler
wrote:
> I have a fact table that looks like this:
>
> dim1_fk bigint,
> time_fk bigint,
> tstamp timestamp without timezone
> value numeric(16,2)
>
> The tstamp column is completely redundant to a colume in the time_ut
I have a fact table that looks like this:
dim1_fk bigint,
time_fk bigint,
tstamp timestamp without timezone
value numeric(16,2)
The tstamp column is completely redundant to a colume in the time_utc table,
but I like to leave it there for convenience when writing ad-hoc queries in
psql - it allows
On Fri, Jun 10, 2011 at 9:28 PM, C. Bensend wrote:
> SELECT next_bill_date( '2011-01-01', '1 month', '4 months' );
>
>
> .. I expect the following result set:
>
>
> next_bill_date
>
>
> 2011-07-01
> 2011-08-01
> 2011-09-01
> 2011-10-01
>
>
>
http://www.postgresql.org/docs/8.4/i
On Fri, May 27, 2011 at 9:15 AM, Emi Lu wrote:
>
> Solution:
>
> (1) Save pdfs to file system, only point file name in psql8.3
>
> (2) Save oids of pdfs into table
>
> (3) Save pdf files as bytea column in psql8.3
>
>
> Pros and cons for (1), (2), (3), which is the most
On Mon, May 23, 2011 at 9:53 AM, Dean le Roux wrote:
> postgres 8.3 to 8.4 Open office base 3.2 Ubuntu 9.04 to 10.04 (new
> install)
>
> I recently upgraded from Ubuntu 9.04 to Ubuntu 10.04. Security and other
> updates have been effected. I used webmin to restore the databases.
>
> Since migrat
On Thu, May 12, 2011 at 1:45 PM, Thomas Kellerer wrote:
> Nguyen,Diep T wrote on 12.05.2011 03:59:
>
>>
>> Any help would be appreciated.
>>
>
> SELECT id,
> date,
> score_count,
> row_number() over (partition by id order by date desc) as order_value
> FROM your_table
>
>
>
Or t
On Tue, May 10, 2011 at 9:47 AM, Tom Lane wrote:
> "Ozer, Pam" writes:
> > Isn't this the English standard for collation? Or is this a non-c
> > locale as mentioned below? Is there anyway around this?
>
> >LC_COLLATE = 'en_US.utf8'
>
> en_US is probably using somebody's idea of "dictio
On Mon, May 9, 2011 at 1:38 PM, Emi Lu wrote:
> Hi Pam,
>
>
> >> Select VehicleTrimAbbreviated, VehicleTrimAbbreviatedId
> >> from VehicleTrimAbbreviated
> >> Where vehicleTrimAbbreviated like 'CX%'
> >> order by
> >>
> >> split_part(VehicleTrimAbbreviated, ' ', 1) asc,
> >> split_part(VehicleTri
On Thu, Mar 10, 2011 at 2:53 AM, Uwe Bartels wrote:
> same same.
> all errors including syntax_error match to others, but I checked it again.
> and the exception remains.
>
I'm just guessing here, but is it throwing a new exception in the exception
handler? I realize that the exception that is
On Thu, Mar 10, 2011 at 12:45 AM, Uwe Bartels wrote:
> Hi,
> Yes, of course is this sql producing an error.
> The point is, I want to trap the error and handle it. Here in this case I
> set the variable l_state and l_message.
>
Doh! Of course. Sorry about that. What happens when you explicit
when I run 'select 1count(*) from table' in my postgres 8.4 installation, I
get the exact same error message. Assuming the '1count()' function does
exist, perhaps you need to full qualify it with a schema name?
It looks to me like the query you are passing to the procedure is invalid
and is gener
On Fri, Feb 11, 2011 at 12:47 PM, Aaron Burnett wrote:
>
>
> Thank you all very much for your help.
>
> The suggestion from Osvaldo below was the best for my situation (not having
> any soret of xref table to join)...
>
>
It may work well for now, but if that foo_activity table has the potential
Assuming you have a table which lists all possible activities, with one
activity per row and no duplicates, you need to do a left outer join between
activities and your query result. That will generate a resultset that has
at least one row for every row in activities, with nulls in all the columns
On Thu, Feb 10, 2011 at 9:57 AM, Josh wrote:
> Hi
>
> I'm trying to do a DELETE FROM on my large table (about 800 million
> rows) based on the contents of another, moderately large table (about
> 110 million rows). The command I'm using is:
>
> DELETE FROM records WHERE id NOT IN (SELECT id FROM
You need to modify the search_path for that user. Read the first section of
this page very carefully:
http://www.postgresql.org/docs/8.4/static/runtime-config-client.html
The schema that is used for new objects that don't have an explicit schema
is always the first schema in the search_path.
You
I'd think you need to indicate multiple alphabetic matches. Your first
regex actually matches only b followed by end of string and the second is
really only matching start of string followed by a. The third is looking
for a single character string.
Try this: select 'ab' ~ '^[a-z]+$'
or this: sel
On Wed, Jan 5, 2011 at 11:39 AM, Good, Thomas wrote:
> This dubious query worked well previously:
> select * from db_log where log_date LIKE '2011-01-%';
> (currently works on bluehost.com where they run 8.1.22)
>
> Can someone offer a (preferably ANSI compliant) way to do this on 8.4.5?
>
> I re
On Wed, Dec 15, 2010 at 11:38 PM, Scott Marlowe wrote:
> On Wed, Dec 15, 2010 at 8:12 AM, Alberto wrote:
>
> >
> > Is there any way to make the 3 operations be one transaction for the
> > database, so that it keeps them all consistent in case a power shortage
> > occurs in the middle?
>
> Yes, pu
On Fri, Dec 10, 2010 at 7:28 PM, Gary Chambers wrote:
> psql - not as advanced, doesn't have all the features SQL*Plus has.
>>>
>> On the other hand, it is at least capable of command history and readline
>> support.
>>
>
> Yes, but rlwrap will eliminate that limitation.
>
>
Damn! Why didn't any
On Fri, Dec 10, 2010 at 5:44 PM, Igor Neyman wrote:
>
>
>
> -Original Message-
> From: Tony Capobianco
> [mailto:tcapobia...@prospectiv.com
> ]
> Sent: Thu 12/9/2010 4:47 PM
> To: pgsql-sql@postgresql.org
> Subject: sqlplus reporting equivalent in postgres?
>
> We're in the process of po
On Tue, Nov 30, 2010 at 7:47 PM, Samuel Gendler
wrote:
> I have a fact table with a number of foreign keys to dimension data and
> some measure columns. Let's assume I've got dimension columns a,b,c,d,e,
> and f and measure columns x, and y.
>
> I need to be able to find
I have a fact table with a number of foreign keys to dimension data and some
measure columns. Let's assume I've got dimension columns a,b,c,d,e, and f
and measure columns x, and y.
I need to be able to find the value of f, x, and y for the row with min(x/y)
when e in (1,2) and max(x/y) when e not
73 matches
Mail list logo
