Re: [SQL] INHERITS and Foreign keys
Foreign keys, unique and primary key constraints do not meaningfully inherit currently. At some point in the future, that's likely to change, but for now you're pretty much stuck with workarounds (for example, using a separate table to store the ids and triggers/rules on each of the tables in the hierarchy in order to keep the id table in date.) hi same problem here on 7.4 can we vote for this bug somewhere ?! thanks for your time Pedro ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] SQL QUERY
I have the following function: CREATE FUNCTION public.auto_incremento() RETURNS trigger AS 'beginnew.id = (select (max(id) + 1) from teste);return new;end;' LANGUAGE 'plpgsql' I created a trigger that uses this function, but i want to the function be usefull for all tables and not only to tbale teste. Someone know how ??? Pedro Igor
[SQL] Case-insensitive
Someone knows how config the postgresql for case-insensitive mode ? Pedro Igor
[SQL] Accent-insensitive
Does have PostgreSQL some option to allow me execute selects accent-insensitive ? I can´t find any reference, including the manual Pedro Igor
[SQL] PostgreSQL X Resin EE
Have someone used Resin EE with PostgreSQL or actually use both for building applications ? About PostgreSQL i know that is one of the best options for back-end, but what about Resin EE ? Thanks ... Pedro Igor
[SQL] Query
Regards . I hope someone can help me in this query. Have a field in a table that needs to check if another table has the value that is being inserted. Ex: table A - id int constraint pkey_id primary key, table B - id int constraint fkey_A_B references A, Here comes my doubt: table C - id int constraint fkey_A_C references A check (if exists B.id = C.id) How can i build this expression so, when I insert a tupple in table C the field will check in the table A(ok, because is a foreign key) and also in table B I have tried : check (select count(b.id) from B b where b.id = id) <> 0) but doesn´t work .. I can use trigger here, but i don´t know if is the best solution . Thanks, Pedro Igor
[SQL] Data between different databases
I would like to know in how can i reference a table in a database A from a database B. In resume, i want to separate the data in my database in two others databases and make references for them. Thanks, Pedro Igor ---Outgoing mail is certified Virus Free.Checked by AVG anti-virus system (http://www.grisoft.com).Version: 6.0.443 / Virus Database: 248 - Release Date: 1/10/2003
[SQL] Function unkown
How is this function ? plpgsql_call_handler() RETURNS language_handler AS 'plpgsql_call_handler' LANGUAGE 'c' VOLATILE; ---Outgoing mail is certified Virus Free.Checked by AVG anti-virus system (http://www.grisoft.com).Version: 6.0.443 / Virus Database: 248 - Release Date: 1/10/2003
[SQL] Newbie (to postgres) question
Hello all, First of all, accept my apologies for what is surely a dumb question, and yes i have been reading extensively through all the documents, but i really need to ask this.. :) I have recently started the migration of a large ex-MySql database to postgresql, and im still "adapting" to the new tweaks of this new (to me) environment. My question is basically... how does postgresql deal with the equivalent of "permanent connections of mysql"? Alongside with the database, i have an extensive amount of .c code that used to just reuse sockets if they were already in an open state (and only if needed new one(s) would be open). It's an application that will run as a standalone, but many times per minute, so the reusage is indeed a must for me. I have substituted the "mysql_ping"s with PQconnectPolls just to see if the behaviour would be alike, and right now that seems to work, but i'm in a standstill regarding the sockets and permanent connection usage. Any help/directions someone might give me will be deeply appreciated. Regards, \\pb ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Datetime
Hello, I'm using a TIMESTAMP column with a now() default which (correctly i assume) uses a '-mm-dd hh:mm:ss' format. Is it possible to make it something like '-mm-dd hh:mm:ss:cc' ? (basically, a DATE and a TIME, but with 2 decimal cases on the :cc and not .c as the TIME format. Thanks, \\pb ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] PQexec and SPI_exec
Hello everyone. I'm experiencing some doubts regarding a procedure i have (.c compiled as .so) running as an 'after insert for each row' trigger. This trigger is supposed to do a simple query, something like SELECT * FROM table order by id where processed=0 limit 1 It's not the perfect way to get the vars of the insert itself, but the result is a set of 45 columns, and the operations of the trigger are somehow complex, so .c is really a necessity on this one, and as long as this select actually returns the proper values, i can deal with it later. But my problem is not one of a structure nature: my problem is the fact that using SPI_exec, and the SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, columnX), and DatumGetInt32(DirectFunctionCall1(int4in, CStringGetDatum(SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, columnX, etc, it all works fine. the SPI-running-from-the-triggered-.so can detect the correct values - from the insert that triggered it. I would prefer to use the more friendly PQexec and the simpler PQgetvalue(res,0,X), but this last approach does not return the values of the insert that triggered it. It returns the values from the "the last insert before this one". What is the proper way to make this method work? I'm sorry if this might be a basic question, but i have tried so many things, that i'm probably too puzzled right now to make any sense. Any help is deeply appreciated. Thanks, \\Pedro ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Given 02-01-2006 to 02-28-2006, output all days.
Hello.
I'm having difficulties on my first incursion through generate_series.
The details:
SELECT DATE_TRUNC('month', timestamp '2006-02-01' )::DATE + s.d AS date,
COUNT (o."04-sms") as totalcause98
FROM generate_series(11,19) AS s(d)
LEFT JOIN netopia o ON (substr(o."26-insertTime",1,10) = (DATE_TRUNC('month',
timestamp'2006-02-01' )::DATE + s.d) and o.cause01=98)
GROUP BY s.d ORDER BY 1;
This query (although quite messed up on the date parameters), does exactly
what i want:
"sum column 'cause01=98' for a specified date range, including 0's"
date| totalcause98
+--
2006-02-12 |0
2006-02-13 |0
2006-02-14 |0
2006-02-15 |0
2006-02-16 | 68
2006-02-17 | 256
2006-02-18 | 104
2006-02-19 | 34
2006-02-20 | 20
I'm using a left join because i really need the =0 sums.
The use of substr() is due to the fact the "26-insertTime" on the 'netopia'
table has a default of 'default (now())::timestamp(2) without time zone'.
So, i can make generate_series work with the left join using the substr.
I was getting ready to optimize this query, when i remembered i also have the
need for another column, 'totalcause99', almost the same as this query, but
with 'cause01=99' as condition.
The maximum i was able to do without syntax errors was:
SELECT DATE_TRUNC('month', timestamp '2006-02-01' )::DATE + s.d AS date,
COUNT (o."04-sms") as totalcause98,
COUNT (p."04-sms") as totalcause99
FROM generate_series(11,19) AS s(d)
LEFT JOIN netopia o ON (substr(o."26-insertTime",1,10) = (DATE_TRUNC('month',
timestamp'2006-02-01' )::DATE + s.d) and o.cause01=98)
LEFT JOIN netopia p ON (substr(p."26-insertTime",1,10) = (DATE_TRUNC('month',
timestamp'2006-02-01' )::DATE + s.d) and p.cause01=99)
GROUP BY s.d ORDER BY 1;
Reading this one aloud, i feel the "logic" of what i'm trying to do, but the
values of its output are.. scary to say the least, and the sums are exactly
the same on the 2 columns, and that should never happen with the data i have
on the table.
I'm starting to wonder if this is actually possible to be done on one single
query...
Ideas, anyone?
Sorry for the long email.
Any and all help is deeply appreciated.
Regards,
--
\\pb
---(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
Re: [SQL] ... more than one count with left join
On Monday 20 February 2006 20:39, Mark R. Dingee Pedro wrote:
|> Pedro,
|>
|> Would something such as this suffice?
Hello Mark,
It's far superior to what i was doing, serialization wise. Thank you.
However, it still leaves me with the big headache of the left joins with the
"count ... where..."...
Thanks,
\\pb
|>
|> Mark
|>
|> create function get_date_range(date, date) returns setof date as '
|> DECLARE
|> cur date;
|> BEGIN
|> cur := $1;
|>
|> while cur <= $2 LOOP
|> return next cur;
|> cur := cur + interval ''1 day'';
|> end LOOP;
|> return;
|> END;' language 'plpgsql';
|>
|> dev=# select * from get_date_range('2/1/2006'::date, '2/28/2006'::date);
|> get_date_range
|>
|> 2006-02-01
|> 2006-02-02
|> 2006-02-03
|> 2006-02-04
|> 2006-02-05
|> 2006-02-06
|> 2006-02-07
|> 2006-02-08
|> 2006-02-09
|> 2006-02-10
|> 2006-02-11
|> 2006-02-12
|> 2006-02-13
|> 2006-02-14
|> 2006-02-15
|> 2006-02-16
|> 2006-02-17
|> 2006-02-18
|> 2006-02-19
|> 2006-02-20
|> 2006-02-21
|> 2006-02-22
|> 2006-02-23
|> 2006-02-24
|> 2006-02-25
|> 2006-02-26
|> 2006-02-27
|> 2006-02-28
|> (28 rows)
|>
|> On Monday 20 February 2006 15:30, Pedro B. wrote:
|> > Hello.
|> > I'm having difficulties on my first incursion through generate_series.
|> >
|> > The details:
|> >
|> > SELECT DATE_TRUNC('month', timestamp '2006-02-01' )::DATE + s.d AS
|> > date, COUNT (o."04-sms") as totalcause98
|> > FROM generate_series(11,19) AS s(d)
|> > LEFT JOIN netopia o ON (substr(o."26-insertTime",1,10) =
|> > (DATE_TRUNC('month', timestamp'2006-02-01' )::DATE + s.d) andcreate
|>
|> function get_date_range(date, date) returns setof date as '
|> DECLARE
|> cur date;
|> BEGIN
|> cur := $1;
|>
|> while cur <= $2 LOOP
|> return next cur;
|> cur := cur + interval ''1 day'';
|> end LOOP;
|> return;
|> END;' language 'plpgsql';
|>
|> dev=# select * from get_date_range('2/1/2006'::date, '2/28/2006'::date);
|> get_date_range
|>
|> 2006-02-01
|> 2006-02-02
|> 2006-02-03
|> 2006-02-04
|> 2006-02-05
|> 2006-02-06
|> 2006-02-07
|> 2006-02-08
|> 2006-02-09
|> 2006-02-10
|> 2006-02-11
|> 2006-02-12
|> 2006-02-13
|> 2006-02-14
|> 2006-02-15
|> 2006-02-16
|> 2006-02-17
|> 2006-02-18
|> 2006-02-19
|> 2006-02-20
|> 2006-02-21
|> 2006-02-22
|> 2006-02-23
|> 2006-02-24
|> 2006-02-25
|> 2006-02-26
|> 2006-02-27
|> 2006-02-28
|> (28 rows)
|>
|> > o.cause01=98)
|> > GROUP BY s.d ORDER BY 1;
|> >
|> >
|> > This query (although quite messed up on the date parameters), does
|> > exactly what i want:
|> > "sum column 'cause01=98' for a specified date range, including 0's"
|> >
|> > date | totalcause98
|> > +--
|> > 2006-02-12 | 0
|> > 2006-02-13 | 0
|> > 2006-02-14 | 0
|> > 2006-02-15 | 0
|> > 2006-02-16 | 68
|> > 2006-02-17 | 256
|> > 2006-02-18 | 104
|> > 2006-02-19 | 34
|> > 2006-02-20 | 20
|> >
|> > I'm using a left join because i really need the =0 sums.
|> > The use of substr() is due to the fact the "26-insertTime" on the
|> > 'netopia' table has a default of 'default (now())::timestamp(2) without
|> > time zone'. So, i can make generate_series work with the left join
|> > using the substr. I was getting ready to optimize this query, when i
|> > remembered i also have the need for another column, 'totalcause99',
|> > almost the same as this query, but with 'cause01=99' as condition.
|> >
|> > The maximum i was able to do without syntax errors was:
|> >
|> > SELECT DATE_TRUNC('month', timestamp '2006-02-01' )::DATE + s.d AS
|> > date, COUNT (o."04-sms") as totalcause98,
|> > COUNT (p."04-sms") as totalcause99
|> > FROM generate_series(11,19) AS s(d)
|> > LEFT JOIN netopia o ON (substr(o."26-insertTime",1,10) =
|> > (
[SQL] generate_series with left join
Greetings. I'm having some difficulties with my first use of the generate_series function. Situation: cause| integer date | timestamp(2) without time zone cause | date --++---+ 1 | 2006-03-23 15:07:53.63 | 2 | 2006-02-02 12:13:23.11 | 2 | 2006-11-12 16:43:11.45 | 1 | 2005-03-13 18:34:44.13 | 3 | 2006-01-23 11:24:41.31 | (etc) What i need to do, is to count the 'cause' column for the values '1' and '2', and group them by year, using left joins in order to also have the serialized years with empty values in the output. My needed output for a series of (2005,2007) would be: year | one | two --+--+-- 2005 |1 |0 2006 |1 |2 2007 |0 |0 I have tried something like #select s, (select count(cause) from mytable where cause=1 ) as one, COUNT (cause) as two from generate_series(2006,2009) AS s(d) left JOIN mytable o ON (substr(o.date,1,4) = s.d and cause=2) GROUP BY s.d ORDER BY 1; which obviously is wrong, because of the results: s | one | two --+--+-- 2006 | 3769 | 1658 2007 | 3769 |0 2008 | 3769 |0 2009 | 3769 |0 As far as the 'two', the left join was successful, however i can not find a way to join the 'one'. The output value is correct, but the result shown should be only for the year 2006, not for all the values of the series. Maybe i've looked at it TOO much or maybe i'm completely failing to find a working logic. Any suggestions? Any and all help is humbly appreciated. \\pb -- This message has been scanned for viruses and dangerous content at MsgLab.com and is believed to be clean. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] generate_series with left join
On Wed, 2006-06-28 at 13:34 -0500, Aaron Bono wrote:
> select
> year_list.year,
> count(one.*),
> count(two.*)
> from (
> select years
> from generate_series(2006,2009) as years
> ) year_list
> left outer join mytable as one on (
> date_part('year', one.date) = year_list.years
> and one.cause = 1
> )
> left outer join mytable as two on (
> date_part('year', two.date) = year_list.years
> and two.cause = 2
> )
> group by
> year_list.year
> ;
>
>
> select
> year_list.year,
> mytable.cause,
> count(mytable.*)
> from (
> select years
> from generate_series(2006,2009) as years
> ) year_list
> left outer join mytable on (
> date_part('year', mytable.date) = year_list.years
> )
> group by
> year_list.year,
> mytable.cause
> ;
>
Aaron,
Thank you so much for your reply.
However, the 2 examples you provided have "weird" outputs:
The first:
years | count | count
---+-+-
2009 | 0 | 0
2008 | 0 | 0
2007 | 0 | 0
2006 | 7802080 | 7802080
(4 rows)
Time: 87110.753 ms << yay.
The second:
years | cause | count
---+-+---
2009 | | 0
2008 | | 0
2007 | | 0
2006 | 6 | 1
2006 | 1 | 4030
2006 | 2 | 1936
2006 | 3 | 4078
2006 | 100 | 3159
2006 | 98 | 2659
2006 | 99 | 2549
My need is really to only group the counts of where cause=1 and cause=2
for each year, none of the others.
> I think one of the problems many people have is the writing of their
> SQL in paragraph form. It makes the SQL really hard to read and even
> harder to understand and debug. Formatting your SQL like I did above
> may make it easier to see what is wrong.
Indeed. Note taken, i'll improve my formatting.
\\pb
--
This message has been scanned for viruses and
dangerous content at MsgLab.com and is
believed to be clean.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] generate_series with left join
On Wed, 2006-06-28 at 15:16 -0500, Aaron Bono wrote:
> This should work too:
>
> select
> year_list.year,
> one_list.one_count,
> two_list.two_count
> FROM (
> select years
> from generate_series(2006,2009) as years
> ) year_list
> left outer join (
> select
> date_part('year', one.date) as one_year,
> count(one.*) as one_count
> from mytable as one
> where one.cause = 1
> group by
> date_part('year', one.date)
> ) one_list on (year_list.years = one_year)
> left outer join (
> select
> date_part('year', two.date) as two_year,
> count(two.*) as two_count
> from mytable as two
> where two.cause = 2
> group by
> date_part('year', two.date)
> ) two_list on (year_list.years = two_year)
> ;
Aaron,
I confess i will take some time to digest the amazing code you just
sent, but in the meantime, let me tell you right away that both work
just as i needed.
I will stop pulling my hairs now.
Thank you so much.
\\pb
--
This message has been scanned for viruses and
dangerous content at MsgLab.com and is
believed to be clean.
---(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
Re: [SQL] random rows
Hi! > How I can return random N rows from my select stmt? > like: > e.g. what my selectee is a set of 1000 rows, I want randomly > pickup 100 of 1000. use the LIMIT clause example SELECT * FROM test_table LIMIT 100; you can also use the OFFSET clause to skip to n row and the fetch the n desired rows example SELECT * FROM test_table LIMIT 100 OFFSET 100; this will skip to row number 100 and the fetch the next 100 rows []´s ----- Joao Pedro M. F. Monoo Infortrade Information Systems #183816 Linux Registered User Slackware 7.1 running 2.4.2 Linux Kernel ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] System´s database table
I´m looking for the name of the table that contains all databases in my system. I already see this in the postgre manual, but i´m forgot where Tanks ... Pedro Igor
[SQL] PostgreSQL + SSL
I´m trying to config PG with SSL, but i got a error. I create the key and the certificate and put both in $PGDATA directory. I also enabled the ssl option in postgresql.conf. But when i run postmaster i got a error saying that server.key has wrong permissions. Thanks, Pedro Igor
[SQL] Cross-database references
Title: AIP - Assessoria Informática e Proteção LTDA Someone knows how is going the implementation of cross database references in pgsql ? Would have some future release with this great functionality ? Thanks, Pedro Igor
