Thank you very much for your response. However, I'm unclear what you want
me to substitute for sum(...)?
select '1' as "num_ads", sum(...)
from
(select a.userid from
user_event_stg2 a, user_region b
where a.userid = b.userid
and b.region_code = 1000
and a.messagetype = 'impression'
group by a.user
The following is my code and results:
select '1' "num_ads",
(case when r.region_code = 1000 then (
select count(*) from (
select userid from user_event_stg2 where userid in (
select userid from user_region where region_code = 1000)
and messagetyp
The following is my code and results:
select '1' "num_ads",
(case when r.region_code = 1000 then (
select count(*) from (
select userid from user_event_stg2 where userid in (
select userid from user_region where region_code = 1000)
and messagetyp
r all your responses.
Tony
On Wed, 2012-01-11 at 07:43 +0100, Brice André wrote:
> Just my 2 cents...
>
> Why don't you use a date column type instead of a string ? In this
> case, at insertion, you could simply do this :
>
> INERT INTO tablename (insertion_time, ...)
larger script where I want to dynamically select
tablenames older than 10 days and drop them. The tables are created in
a tmp_stagingMMDD format. I know postgres does not maintain object
create times, how can I write this to select tables from pg_tables that
are older than 10 days?
Thanks.
Tony
this result:
tablename
tmp_staging1229
However, I'm receiving:
tablename
tmp_staging0109
tmp_staging1229
tmp_staging0108
How can I write this correctly?
Thanks.
Tony
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To
Use the concat || operator.
On Thu, 2011-08-25 at 15:21 +0100, gvim wrote:
> I have to deal with a table which contains:
>
> first_name
> surname
> email1
> email2
>
> ... and I would like to create a view which combines both email columns thus:
>
> first_name
> surname
> email
>
> It looks s
Ok, I think I found it:
select translate(firstname,'"','') from members;
gives me what I want.
Thanks.
On Wed, 2011-05-11 at 16:29 -0400, Tony Capobianco wrote:
> We are converting from Oracle to Postgres. An Oracle script contains
> this line:
>
> se
We are converting from Oracle to Postgres. An Oracle script contains
this line:
select replace(firstname,'"'), memberid, emailaddress from members;
in an effort to replace the " with nothing. How can I achieve the same
result with Postgres?
Here's the Postgres error I get:
select replace(fir
Tom,
That's a frighteningly easy solution.
Thanks.
Tony
On Tue, 2011-02-15 at 16:10 -0500, Tom Lane wrote:
> Tony Capobianco writes:
> > I'm altering datatypes in several tables from numeric to integer. In
> > doing so, I get the following error:
>
> &g
Pavel,
That's perfect!
Thanks.
Tony
On Tue, 2011-02-15 at 22:04 +0100, Pavel Stehule wrote:
> Hello
>
> probably you have to use a explicit cast
>
> postgres=# select length(10::numeric::text);
> length
>
> 2
> (1 row)
>
> Regards
than 5 digits long. I'm able to perform
this query on Oracle and would like something similar on postgres 8.4:
delete from uniq_hits where sourceid in (select sourceid from uniq_hits
where length(sourceid) > 5);
I haven't had much luck with the length or char_length functions on
pos
|Type | Modifiers
-+-+---
memberid| numeric | not null
etc
How can I get this so I don't have to preface the \d with the schema
name every time?
Thanks.
Tony
--
Sent via pgsql-sql mailing list (
Here's my table:
plsql_dw=# \d tmpsv_parent_master
Table "staging.tmpsv_parent_master"
Column |Type | Modifiers
+-+---
memberid | numeric |
addeddate | timestamp witho
We're in the process of porting our Oracle 10.2 datawarehouse over to
PostGres 8.4. One thing we rely upon are daily/hourly reports that are
run and formatted in sqlplus (break on report, compute sum, etc.). Is
there an equivalent available in postgres?
Thanks.
Tony
--
Sent via pgsq
r wrote:
> I don't know what Postgres version you're using but check out the doc
> related to String Functions and Operators.
> Cheers,
>Peter
>
>
> On Tue, Dec 7, 2010 at 4:47 PM, Tony Capobianco
> wrote:
> Ok, that worked. Why did I need to cast
4005943492010-11-16 19:35:22
4005943662010-11-16 19:35:37
(5 rows)
Thanks.
On Tue, 2010-12-07 at 16:43 -0500, Peter Steinheuser wrote:
> I think the HINT is what you need to look at.
>
> Cast both columns to text.
>
> On Tue, Dec 7, 2010 at 4:37 PM, Tony Capobianco
> wrote
Here's my table:
plsql_dw=# \d tmpsv_parent_master
Table "staging.tmpsv_parent_master"
Column |Type | Modifiers
+-+---
memberid | numeric
27;m sticking with the recursive query, because it seems to me the only
way to ensure there are no cycles is to check the whole graph for
cycles, and the only way I know how to do that is the recursive
approach. Since "FOR UPDATE" isn't implemented for recursive queries,
I'll ju
On 3/16/10 4:34 PM, Tom Lane wrote:
> The same kind of problem exists for unique and foreign key constraints,
> both of which use low-level locking mechanisms to catch such cases.
> There's no way that I can see to express the "no cycle" constraint as a
> uniqueness constraint unfortunately. You c
having
the non-recursive SELECT use NEW.parent, NEW.child, etc. but that isn't
working. Is there any way to do this, or do I have to just insert the
edge, check if it cycles, and delete it if it does?
Thanks.
-Tony
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make chan
like you could get around most of these cases by making a function or
set returning function to return the data and making it "security definer"
and then grant your monitoring user access to that.
Tony
Greg Sabino Mullane wrote:
> A few things spring to mind:
>
> 1) Use a separate table, rather than storing things inside of
> dataset itself. This will reduce the activity on the dataset table.
A separate table just for that one column? Would that really help,
given that I'd have to add the fore
Hi Craig, thanks for your help.
Craig Ringer wrote:
> MVCC bloat from the constant updates to the assoc_count table, maybe?
That's what a coworker suggested might be happening. The fact that a
no-op trigger performs fine but the UPDATE trigger doesn't would seem to
confirm that it's something in
inconsistencies if another client does inserts without updating the
count. I would really prefer to use the trigger solution recommended on
the PGsql wiki, but can't do so until I solve this performance problem.
I greatly appreciate any and all help. Thanks.
-Tony
--
Sent v
On Fri, Oct 24, 2008 at 10:24 AM, Tony Wasson <[EMAIL PROTECTED]> wrote:
> On Fri, Oct 24, 2008 at 8:04 AM, Steve Midgley <[EMAIL PROTECTED]> wrote:
>> At 11:28 AM 10/23/2008, Joe wrote:
>>>
>>> Steve Midgley wrote:
>>>>>
>>>>> #
On Fri, Oct 24, 2008 at 8:04 AM, Steve Midgley <[EMAIL PROTECTED]> wrote:
> At 11:28 AM 10/23/2008, Joe wrote:
>>
>> Steve Midgley wrote:
# (invoiceid, txid)
(A, 1)
(A, 3)
(B, 1)
(B, 2)
(C, 5)
(D, 6)
(D, 7)
(E, 8)
(F, 8)
For journalli
On Tue, Sep 30, 2008 at 5:16 AM, Glenn Gillen <[EMAIL PROTECTED]> wrote:
> Hey all,
>
> I've got a table with a unique constraint across a few fields which I
> need to regularly import a batch of data into. Is there a way to do it
> with COPY without getting conflicts on the unique contraint? I hav
r codebase to look at is http://www.sql-ledger.org/. It uses postgresql.
Regards,
Tony
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
On 7/18/06, Tony Wasson <[EMAIL PROTECTED]> wrote:
On 7/18/06, Curtis Scheer <[EMAIL PROTECTED]> wrote:
> Does anyone have any examples of how I would make a stored procedure in
> plpgsql that would allow for passing a list or arrays of values to be used
> in an sql IN cla
On 7/18/06, Curtis Scheer <[EMAIL PROTECTED]> wrote:
Does anyone have any examples of how I would make a stored procedure in
plpgsql that would allow for passing a list or arrays of values to be used
in an sql IN clause? Like so: select * from table where field1 in (values).
Here's a very sim
On 5/12/06, Michael Joseph Tan <[EMAIL PROTECTED]> wrote:
hi,
im new in postgresql, generally new in databases.
im trying to make a function using PGAdminIII which returns several types,
example, my query is:
"select count(id) as requests, organization from connection_requests group
by organiz
I ended up writing a perl script to parse my SQL
and make a graphviz dot file. I then used graphviz to make a function
dependency chart. I can't promise it would catch every single case,
but I can provide you with the code if you wish to give it a whirl.
Tony Wasson
-
On 1/11/06, Jeff Boes <[EMAIL PROTECTED]> wrote:
> Stumped: is there any way to set up default values for psql variables
> within the .SQL file itself? Obviously, I can do something like:
>
> $ psql -f my_script -v MYVAR=${myvar:-mydefault}
>
> but I would prefer to have the value stored with the .
On 7/22/05, Jim Buttafuoco <[EMAIL PROTECTED]> wrote:
> Mark,
>
> Instead of RETURN NEXT rec.txt1; RETURN NEXT rec.txt2; just use RETURN NEXT
> rec;
>
> then your select statement would be
> select * from my_func() as (txt1 text,txt2 text);
>
> Jim
Besides a simple RETURN NEXT, you'll need to
TDERR for logging).
>
> I see. Can I make the ouput somehow less verbose? It spits out a lot of noise
> for each "NOTICE":
You can control the severity messages sent to your client by first
setting client_min_message.
Try SET client_min_messages = WARNING;
http://www.postgresq
sible?
>
> Thanks alot.
>
> Your Friend,
>
> John Kopanas
You can do this by using array_to_string and using a space as your
delimiter. If you need to trim the quotes use the trim function also.
You can also see the split_on_commas example below -- you'd want to
split on
h left join p on (h.r=p.r and h.pos=r.pos and p.r_order=1)
where h.tn > 20
and h.tn < 30
Filtering within the join condition is very useful when doing a left outer join.
Here's another example "from the book" doing this type of filter
within the join:
http://www.postgresql
eriodic
summarization you run. This sounds like a "top N" report so a periodic
update out to work.
http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html
Tony Wasson
---(end of broadcast)---
TIP 8: explain analyze is your friend
le row by using something like SELECT
build_keyword_table('123');
I also used this as my test data... It worked for me!
CREATE TABLE user_data (
id SERIAL,
user_id INTEGER,
keywords VARCHAR(256) NOT NULL,
add_date TIMESTAMP,
PRIMARY KEY(id)
);
INSERT
rom your subquery. This is not elegant at all, but it
should do what you are wanting.
update myTable set myColumn = (CASE
WHEN (Select altColumn from altTable where altColumn != 'XXX'
limit 1) IS NULL
THEN 'some default value'
ELSE (Select altColumn from altTable where
um_integer_array(somearr) FROM arraytest ;
sum_integer_array
---
{1,3,5}
(1 row)
Tony Wasson
---(end of broadcast)---
TIP 8: explain analyze is your friend
x_sum
example in the docs.
-
CREATE OR REPLACE FUNCTION sum_intarray(INTEGER[],INTEGER[]) RETURNS
INTEGER[] LANGUAGE 'plpgsql' AS '
/*
|| Author: Tony Wasson
||
|| Overview: Experiment with arrays and aggregates
|| 3,2,1
||+ 0,2,2
|| ---
|| 3,4,3
||
|| Revisions
Hi Gurus,
Please forgive this naive question:
Say, I have a table (containerId, itemId) where for each containerId there
are several rows (different itemId value) in that table. Now I want to give
those rows (with same containerId) a sequence, so add one colum there to
make it become (containerI
inserts.
>From the pg_dump manpage,
--attribute-inserts
Dump data as INSERT commands with explicit column names
(INSERT
INTO table (column, ...) VALUES ...). This will make
restoration
very slow, but it is necessary if you desire to
rearrange the
Any way to do this?
-Tony
---(end of broadcast)---
TIP 8: explain analyze is your friend
Thanks for the reply.
-Tony
- Original Message -
From: "Christoph Haller" <[EMAIL PROTECTED]>
To: "Tony Reina" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, April 14, 2004 5:42 PM
Subject: Re: [SQL] Help with COPY command
> >
tline(conn,"\\.\n");
PQendcopy(conn);
1. I'm assuming that I can put in as many PQputline statements as I
want to between the PQexec("COPY ... FROM ...") and the terminator
line. Is that correct? No limit?
2. Do any of these lines need to be followed by a PQclear(res)? Wha
t be broken up into separate lines? For example,
res = PQexec(conn, "COPY testtable FROM stdin WITH DELIMITER ',';");
res = PQexec(conn, "1, 2, 'a', 3, 4");
res = PQexec(conn, "5, 6, 'b', 7, 8");
res = PQexec(conn, "9, 10, 'c',
On Tue, 17 Jun 2003 00:42:54 -0400 Tom Lane <[EMAIL PROTECTED]> writes:
> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > On Mon, 16 Jun 2003, Tony G. Harris wrote:
> >> The problem is, when I call the function, I get:
> >> sql-practice=# select update_pay();
Josh,
thanks for your help.
Josh Berkus wrote:
Tony,
I've a query which needs too many time ca. 12-15 sec.
how can i get a better perfomance?
First of all, please take this to the PGSQL-PERFORMANCE list.
Second, see this web page:
http://techdocs.postgresql.org/g
Hello. I'm using PostgreSQL to learn SQL and SQL-based languages. The
text I'm using (SAMS Teach Yourself SQL in 21 Days, 3rd.ed.) references
Oracle in its examples, though I can usually get PostgreSQL to work
almost as well.
Well, I'm almost to the end of the book and I'm trying to port some of
hello,
I've a query which needs too many time ca. 12-15 sec.
how can i get a better perfomance?
my table have less than 2300 rows.
thanks in advance
tony
explain select o.id from ioobeject o,dist_vertron v where
macro_lid=1123 and (o.id=v.id) and (o.deleted<>'1&
empo" integer DEFAULT 0 NOT NULL,
"deleted" character(1) DEFAULT '0',
Constraint "pk_iobjects" Primary Key ("id")
);
GRANT ALL on "iobjects" to "jantos";
Wei
- Original Message -
From: "Tony Simbine&q
hello,
I'm trying to update a columm on a table with a trigger
but it don't work on PostgreSQL 7.2 (i686-pc-linux-gnu, compiled by GCC
2.96).
when I update a row the trigger does nothing.
what can I do?
thanks in advance
tony
here is
e page that is mentionned on the postgresql web site???
nuff said?
Tony
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
turned the to_ascii stuff off. See
www.3continents.com/base_de_donnees.htm and search for "Amnésie" if you
want the english search to work you search for "Amnesia"...
The client wants the user to check spelling...
Before it worked just the way you wanted _but_ I am using a JDBC reque
I don't get it? What's the difference between an inner-join and an
equijoin?
"Nils Zonneveld" <[EMAIL PROTECTED]> wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
>
>
> Maik wrote:
> >
> > Its clear, union concat the two results.
> >
> > But he can also use this join version, if its
Hello,
I have visited your web site and found very interesting and informative.
I would like to know:
What is a BLOB Field and can you give a example of one?
I hope to hear from you soon
Get your small business started at Lycos Small Business at
http://www.lycos.com/business/mail.html
Volker,
Thank you, I was using UTF8 because I am accessing the database via
JDBC, and Java's default encoding is UTF8. I wasn't sure about whether
it was a right thing to do or not.
Thank you very much. I really appreciate your help!
Tony Nakamura
- Original Message
I am using PHP to insert/retrieve data
without
any encoding conversions. I think my Postgresql was not specified any
special
encoding at the time of make. Do I need to convert everything EUC before
I store data, and do why would I need to do that?
Thanks in advance!!
Tony
61 matches
Mail list logo
