On Sep 23, 5:43 am, AnthonyV wrote:
> Hello,
>
> I have a table like :
>
> date | value
> ---
> 2009-09-19 | 1
> 2009-09-20 | 2
> 2009-09-21 | 6
> 2009-09-22 | 9
> 2009-09-23 | 1
>
> I'd like a request which gives me the sum of each
od of overlap surrounding the time when you switch to a new
partition), you're looking at evaluating every input query twice.
In this case, the rules presumably are just simply re-directing DML,
so there'd only be one rule in play at a time. That means the only
real ov
ou could do that by querying the catalogs directly, but I think you'll
find the pg_user_foreign_keys view defined by
http://pgfoundry.org/projects/newsysviews to be most helpful.
--
Jim Nasby [EMAIL PROTECTED]
EnterpriseDB h
Use case statement and sum to get a count where status=20...
For example
Select sum(case when status=20 then 1 else 0 end) as status20,
Sum(case when status=30 then 1 else 0 end) as status30
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Br
;NULL')) in the dynamic statement.
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner
Select sno AS "SNO",
SELECT value AS "VALUE",
get_sum(value) as SUM
from temp;
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Penchalaiah P.
Sent: April 24, 2007 8:09 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] hi
Hi
I have
I am new to psql , so please be patient !
Can someone please provide a small quick example of a a function that
take 1 paramater and based on that parameter, returns a table or view ?
Etc Note this is just a abstract of the functon, not a working
function call !!!
Create function viewtest(
Hi there,
I have tried many ideas to get this working but no luck.
Can some show me or explain what is happening
EXAMPLE
==
I am trying to read to 2 text fields to combine them togther to form the
name of a
VIEW. example
SELECT * FROM ( 'april'||'may') ;
I have tried the EXECUTE i
PROTECTED]
Sent: April 3, 2007 12:45 PM
To: Wilkinson, Jim
Cc: pgsql-sql@postgresql.org
Subject: RE: [SQL] Using a variable as a view name in a select
Jim
So let's suppose you have a "master" table of incidents
incident_no (serial)
incident_date (timestamp)
other fields
My unders
I have created a view, called april_may. I need to select this view by
combineing to fields in the database to create the view name etc ...
Create view as select * from table_X;
I need to do something like this ...
Select * from (select table.start_month||_||table.end_month);
==
Below is a select statement that select incidents by month. I need a
function or a method to select differents views that will show the Month
columns is a different order. Say Apr - Mar for a fiscal year.
I need to do something like an if/then/else statement that selects the
correct view to us
Hi there,
I have a web based reporting system that I am coding. I need to be able
to view statistics by the calendar year or the fiscal year depending on
what the user selects.
My issue is how do I change y select statements to change the column
output?
Example
IncidentJan
00:00:00'::timestamptz - '2004-01-01
> 00:00:00'::timestamptz;
>?column?
> --
>24:00:00
> (1 row)
>
> ---
>
> Jim Nasby wrote:
> > On
unsubscribe
Try this query
select a.relname as base,a.relkind
from
pg_class a
join pg_depend d on (a.oid = d.refobjid)
join pg_class c on (d.classid = c.oid)
join pg_rewrite r on (objid = r.oid)
join pg_class v on (ev_class = v.oid)
where a.relkind in('r', 'v')
and a.relname <> v.relname
and v.relname='YOUR
Try
select into a now() - interval ($1 || ' day')
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Richard Ray
Sent: Tuesday, December 19, 2006 3:10 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Help with quotes in plpgsql
How should this be properly qu
Use a trigger instead, the rule is only run once per insert/update/delete
while the trigger is run for each row.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of William Scott Jordan
Sent: Wednesday, December 13, 2006 9:05 PM
To: pgsql-sql@postgresql.org
S
PostgreSQL docs? Roles aren't pgAdmin
specific, afterall.
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of broadcast)---
TIP 9: In versions
g that for
output column names, /d, etc. get shot down? I thought it would be a
useful addition...
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of broadcast)
cron job to decrement some counter every day, I
think that's not the best approach. Instead, I'd run a query once a day
that finds all students that are past-due and takes some kind of action.
--
Jim Nasby[EMAIL PROTECTED]
Enter
On Sun, Oct 22, 2006 at 12:03:38AM +0300, Devrim GUNDUZ wrote:
> On Tue, 2006-10-17 at 14:21 +0530, Sandeep Kumar Jakkaraju wrote:
> > Can we convert from Postgres to Oracle !!???
You can also run our software and get Oracle syntax for 1/25th the cost.
--
BY votes DESC
LIMIT 3
)
;
Note that this has to scan the table twice (well, the second subquery
will likely use an index on date). If you have another table that has
the dates in it already, you can use that instead of the first subquery.
If you know that every day has a
See section 9.12 of the docs.
On Thu, Oct 19, 2006 at 12:28:58PM +0530, Indira Muthuswamy wrote:
> Then how do we clear the values of a serial column(is it done only by
> dropping the column?)?
>
> Regards,
> M.Indira
>
>
>
> On 10/19/06, Jim C. Nasby <[EMAIL PR
Search the archives for hierarchical query.
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
column a incremented to the value 21.
> But I heard from my friends that the value of the serial column gets
> decremented whenever we issue the 'truncate table' command (in MS SQL
> SERVER).Is the concept same with PostgreSQL or not?Can anyone clarify me on
>
ipt that creates the function. You
don't need to populate it or anything, you just need it to exist
(prefferably with the correct definition).
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
On Mon, Oct 09, 2006 at 02:57:28PM -0500, Aaron Bono wrote:
> On 10/5/06, Jim Nasby <[EMAIL PROTECTED]> wrote:
> >
> >On Oct 5, 2006, at 11:50 AM, Tom Lane wrote:
> >> regression=# select ('2006-09-15 23:59:00'::timestamp - '2006-09-01
purposes.
I agree it should be removed, but we might need a form of backwards
compatibility for a version or two...
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of broadcas
ic, you might want to mix and match the two somehow.
Here's what you don't want to do: have each page issueing 100 queries to
the database. That's a great way to kill your performance and/or
scaleability.
--
Jim Nasby
Scott,
I use the following query with psql \o option. Change the schema name from
public to whatever. I am sure you could put
this into a plpgsql function using execute as well.
Jim
\o drops.sql
select 'drop function ' || nspname || '.' || proname || '('
check out pg_stat_user_indexes, you will need to turn on the stats collection
in your postgresql.conf file first.
Jim
-- Original Message ---
From: "George Pavlov" <[EMAIL PROTECTED]>
To:
Sent: Tue, 1 Aug 2006 09:05:34 -0700
Subject: [SQL] finding unused ind
try
now() + (? || ' day')::interval
-- Original Message ---
From: "Marc G. Fournier" <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Sent: Wed, 26 Jul 2006 18:29:32 -0300 (ADT)
Subject: [SQL] DBD::Pg ... how would I format this prepare?
> I need to do:
>
> NOW() + '2 day'::in
use plperl
-- Original Message ---
From: T E Schmitz <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Sent: Fri, 07 Jul 2006 20:23:50 +0100
Subject: Re: [SQL] SELECT substring with regex
> Rodrigo De Leon wrote:
> > On 7/7/06, T E Schmitz <[EMAIL PROTECTED]> wrote:
> >
> >> But
ct_id;
Jim
-- Original Message ---
From: "James Moliere" <[EMAIL PROTECTED]>
To:
Sent: Fri, 7 Jul 2006 06:53:45 -0700
Subject: [SQL] create aggregate function 'count_bool( column_name, boolean )'
> Hello,
> I'd like to create a function ca
try www.slony.info
-- Original Message ---
From: "Mark Adan" <[EMAIL PROTECTED]>
To:
Sent: Wed, 14 Jun 2006 08:50:23 -0700
Subject: Re: [SQL] Good examples of calling slony stored procedures
> Hi
>
> Can somebody direct me to the mailing list for slony. I couldn't find
> it
You will have to use the "CALLED ON NULL INPUT" option to "create function"
(Postgresql 8.1, I don't know about other
versions) if you expect NULL arguments.
Jim
-- Original Message ---
From: "Rommel the iCeMAn" <[EMAIL PROTECTED]>
To:
11 22 33
>
>
> I was expecting doc_num would receive twice the C1 value, just like with a
> normal sql join.
>
> Regards,
>
> --
> Philippe Lang, Ing. Dipl. EPFL
> Attik System
> rte de la Fonderie
other consideration is that the free space map doesn't care too much
for tracking space info on tons of small tables.
Perhaps the biggest issue is: what happens when you need to do DDL? If
you have 1000 schemas that should be identical, you'll need to perform
any DDL 1000 times.
But as
rently.
>
> You should be able to do this with a fairly simple self-join...
>
> select a.id, b.aid, a.field1, b.field1
> from mytable a
> join mytable b
> on (a.id=b.aid)
>
> Or something like that.
>
> ---(end of broadcast)---
> T
ftp://candle.pha.pa.us/pub/postgresql/mypatches/checkfile.*
>
> Let me know if you want details.
Is it able to also delete the cruft? Seems to be a useful extension,
especially on windows, which AFAIK doesn't have an equivalent to ``.
--
Jim C. Nasby, Sr. Engineering Consultant
chaber | Logical Tracking&Tracing International AG
> Dipl. Inf. | Software Development GIS
>
> Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
>
> ---(end of broadcast)---
> TIP 4: Have you searched ou
or
psql db <
To:
Sent: Thu, 6 Apr 2006 14:37:51 -0700
Subject: Re: [SQL] Query from shell
> Judith wrote:
>
> >Hi every body, somebody can show me hot to execute a
> > query from a shell
>
> echo QUERY HERE | psql databasename
>
> Or, if you want to run several queries, run psql and
ystem more usable if the parser tried to apply a heuristic rule
> about some occurrences being meant as variable references and other ones
> not. If the rule ever got it wrong, it'd be even more confusing.
BTW, I believe SELECT investment_products.provider_id would work here,
but I'm
Because of this I *always* prefix plpgsql variables with
something, such as p_ for parameters and v_ for general variables.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/perva
CT max(greatest(column_a, column_b) ...
SELECT min(least(column_a, column_b) ...
There may be a difference in performance between the two.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://ji
, you should link a band to a track, not an album.
> This opens another can of worms...
>
> I would use the following tables :
BTW, if you're going to be writing code to manage stuff like this, you
should absolutely check out the source for http://musicbrainz.or
select T.* from quality_control_reset T inner join
> (select qualitycontrolrange, max(date) as date from quality_control_reset
> group by qualitycontrolrange) T2
> on (T.qualitycontrolrange = T2.qualitycontrolrange and T.date=T2.date);
BTW, I believe the new row operator fixes in 8.2
id ) FROM bookmarks;
> array_accum
> ---
> {1,2,3,4,5,7}
Couldn't you just use array()?
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/
fied, hist.modified
> as
> hist_modified, coalesce(hist.etc, curr.etc) as etc FROM curr LEFT OUTER JOIN
> hist ON(curr.id = hist.curr_id) WHERE ...
>
> I'm really stuck here. It seems to me that I need a lot of
> CASE...WHEN...ELSE.. statements in the query, but
t addicted, is all.
>
> A
>
> --
> Andrew Sullivan | [EMAIL PROTECTED]
> Information security isn't a technological problem. It's an economics
> problem.
> --Bruce Schneier
>
> ---(end of broadcast)---
> TIP
ompany - Command Prompt, Inc.
>
> ---(end of broadcast)---
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>choose an index scan if your joining column's datatypes do not
>match
>
--
Jim C. N
try
select ...,sum(case when sales.itemname = 'some' then sales.count else 0 end)
as "Sales Candies"
from your_table_here
group by ...
-- Original Message ---
From: "Daniel Hernandez" <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Sent: Thu, 23 Feb 2006 12:46:44 -0500 (EST)
Try this rule instead
create rule checks_d0 as
on delete to checks
do delete from checkitems
where ckid = OLD.ckid;
-- Original Message ---
From: "Milen A. Radev" <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Sent: Sun, 05 Feb 2006 15:10:23 +0200
Subjec
Marc
Is the "assumption" that anytime there are comments the status
changes?
If I'm reading between the lines correctly, there could be a large
number of comments before the status changes. So no need to change
status until explicitly needed.
If there is a specific "comment" th
dead tuples, so if
> >>you cluster you don't need to vacuum.
> >
> >It also does a REINDEX...
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasb
ommand that can do that?
> >
> > Cluster does that. Vacuum only cleans dead tuples from the tables.
>
> Note that while reordering, CLUSTER also gets rid of dead tuples, so if
> you cluster you don't need to vacuum.
It also does a REINDEX...
--
Jim C. Nasby, Sr. Engineering C
Joost Kraaijeveld wrote:
Hi Stephan,
On Sun, 2005-12-04 at 13:33 -0800, Stephan Szabo wrote:
SELECT COUNT(customers.objectid) FROM prototype.customers,
prototype.addresses
WHERE
customers.contactaddress = addresses.objectid
AND
zipCode < '2716BN'
ORDER By zipCode, houseNumber
I
broadcast)-------
> TIP 2: Don't 'kill -9' the postmaster
>
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/perv
NULL
You might have to do the NULL check in a HAVING clause instead... try
it.
BTW, this is probably better asked on pgsql-sql.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive
worthless - it works fine for many
> people. But it does have limitations.
And you can easily have multi-master syncronous replication in
PostgreSQL using the same idea; just see pgCluster.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software
y time
> increased compare to say select a,b from tableA.
>
> any help, prettymuch appreciated.
>
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
>
--
Jim C. Nasby, Sr.
s the only examples in
> chapter 35. There is a paragraph in there suggesting you go look at
> the PL languages first, but obviously it's not getting the job done.
Chapter 35 is plpgsql.. do you mean chapter 32.4?
> Anybody have a better idea?
What about a See Also section a
an EXECUTE. If you need
help, post the schema for a couple of
your tables and I will help with the function
Jim
-- Original Message ---
From: solarsail <[EMAIL PROTECTED]>
To: Yasir Malik <[EMAIL PROTECTED]>
Cc: PostgreSQL
Sent: Tue, 4 Oct 2005 15:50:39 -0400
Subje
rry... It
I don't think either assertion is true. I'd bet most of the developers
actually do normally use an index on a serial, since it's normally used
as a PK. And while people can be a bit terse with their replies, I
wouldn't say you were blasted. :)
--
Jim C. Nasby,
e useful to toss a NOTICE
or maybe even WARNING when a serial is created without a unique
constraint of some kind?
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512
lect t1.id from t1, t2 where t1.id = t2.id and t2.id = x
>
> or more correctly, based on the OP's example:
>
> select t2.x from t1, t2 where t1.id = t2.id and t1.id =
Actually, I think you want AND t2.x , not t1.id.
BTW, I recommend not using id as a bareword field name. Very easy
do you mean UNION ALL instead of JOIN, if you mean UNION ALL , I would go with
a set returning function passing it
the necessary WHERE clause to be applied to all of your tables. You might be
able to wrap the whole thing into a view
-- Original Message ---
From: solarsail <[
Is this possible?
> >
> > Sure, given a suitable schema
> >
> > It is not clear to me, if the hours worked are
> > to be found in the same table you want to insert
> > into, or not.
> >
> > gnari
> >
> >
> >
> >
--
Jim C.
r than individual transactions.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Softwarehttp://pervasive.com512-569-9461
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your des
ti-column returns from pgsql
> 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
> > sele
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
-- Original Message ---
From: "Mark R. Dingee" <[EMAIL PROTECTED]>
To: pgsql-sq
use pg_dump with the --schema-only and --table= params
-- Original Message ---
From: "Mark Fenbers" <[EMAIL PROTECTED]>
To: Pg SQL Discussion Group
Sent: Mon, 18 Jul 2005 12:50:54 -0400
Subject: [SQL] Dumping table definitions
> I am looking for a way to reformat the informatio
I use the following function which returns a date series. You can modify it to
return an int series instead
create or replace function alldates(date,date) returns setof date
as
'
declare
s alias for $1;
e alias for $2;
d date;
begin
d := s;
while d <= e
did you restart postgresql and use the pg_stat_activity view instead (just to
save some typing).
Jim
-- Original Message ---
From: Erik Wasser <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Sent: Fri, 1 Jul 2005 15:58:46 +0200
Subject: [SQL] 'show full proc
try case
for example
select case when bool_column then 'Yes' else 'No end from your_table;
-- Original Message ---
From: Roy Souther <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Sent: Mon, 27 Jun 2005 11:16:58 -0600
Subject: [SQL] How can I simply substatue a value in a que
if you need a multi column fk don't use the "references" keyword on your create
table, instead use the "FOREIGN KEY"
keyword for the table, see the "create table" help.
so for example (untested) change
CREATE TABLE appalto (
cod_op int not null references Opere,
cod_co
works fine for me. Do you have any triggers on the tables or other rules? Can
you provide a complete SQL script that
starts from an empty database.
Jim
-- Original Message ---
From: Neil Dugan <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Sent: Thu, 16 Jun 2005 13
Stef:
Why is iuserid numeric? Are you going to do any math on the field?
If not, change it to varchar. In the long run you'll be happier.
Stef wrote:
Hello Everyone,
Currently, here at work, I am doing the whole
'advocacy' part of postgreSQL. It's not really hard to
do, as the other
Mike,
I posted this RULE also on hackers
CREATE or replace RULE crypto_view_delete_rule
AS ON DELETE
TO crypto_view
DO INSTEAD
(
select func_delFromCrypto( OLD.id,OLD.crypted_content);
);
Jim
-- Original Message ---
From: "Moran.Michael" <[EMAIL PROTECTED]&g
I would change the return type to TEXT, I believe your original example had it
as a varchar and I didn't change it.
Also, I believe that "under the hood" text does equal varchar.
Glad I could help
Jim
-- Original Message ---
From: "Moran.Michael&quo
give this a try
CREATE OR REPLACE FUNCTION selectFromCrypto(INTEGER)
RETURNS VARCHAR
AS '
DECLARE
_pid ALIAS FOR $1;
c text;
BEGIN
SELECT decrypt(crypted_content, decode(''password''::text,
''escape''::text), ''aes''::text) into c
FROM crypto
WHERE pid = _pid;
RET
tested yet with
8.0.X.
You can adjust the output to fit your needs.
Jim
-- Original Message ---
From: Stef <[EMAIL PROTECTED]>
To: Tom Lane <[EMAIL PROTECTED]>
Cc: pgsql-ADMIN@postgresql.org, pgsql-sql@postgresql.org
Sent: Mon, 7 Mar 2005 17:31:55 +0200
Subject: Re:
have you reindexes your tables. When I was running 7.1.4, I ran a vacuum and reindex
nightly. Otherwise your index
files will keep getting bigger and bigger (this has been fixed in 7.4).
Jim
-- Original Message ---
From: patrick ~ <[EMAIL PROTECTED]>
To: [EMAIL PRO
This is what I use to flatten a table, the syntax may not be postgresql
correct but you will get idea.
SELECT
a.name
,SUM (CASE
WHEN EXTRACT(month from a.date) = 1 THEN a.quantity
ELSE 0
END) AS '01'
Hello-
The 7.2 version distributed with RedHat 8.0 seems not
to work for substrings and POSIX regular expressions.
I think this should work
pds=> select substring( str1 from ',' ) from address;
ERROR: pg_atoi: error in ",": can't parse ","
Am I missing something?
TIA
J. Drummey
[EMAIL PR
to change this Postgres behavior?
Thanks,
Jim Ballard
Netezza Corp.
-- Original Message --
From: Mark <[EMAIL PROTECTED]>
Date: 29 May 2001 10:21:15 -0600
>We tried these but it didn't work. However, that's because username is
>a bpchar
I have a table that has the following columns: AUTHOR_NO, ASMT_CODE, &
STATUS. The table would look something like this:
AUTHOR_NO ASMT_CODE STATUS
12345 1 PASSED
12345 2 FAILED
12345 3 FAILED
12345 4 PASSED
12346 1 PASSED
12346 2 PASSED
alues, as in the following where
that maximum is 20 and we are looking at 3 fields:
select count(distinct (cast(field1 as char(20)) || cast(field2 as char(20))
|| field3)) from ...
Jim Ballard
- Original Message -
From: "Jeff Eckermann" <[EMAIL PROTECTED]>
To: "
88 matches
Mail list logo
