Re: [SQL] Last day of month
Greg Sabino Mullane wrote:
How to find the last sunday/mon/sat of any given month.
There is probably a smoother way to do it, but here is a
quick little function to do what you ask. Feed it a date
and a number, where 0 is Sunday, 1 is Monday, etc.
How about this:
regression=# select date_trunc('month', current_date + '1 month'::interval);
date_trunc
-
2004-03-01 00:00:00
(1 row)
Joe
---(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] Last day of month
At 11:30 PM 2/25/04, Joe Conway wrote:
Greg Sabino Mullane wrote:
How to find the last sunday/mon/sat of any given month.
There is probably a smoother way to do it, but here is a
quick little function to do what you ask. Feed it a date
and a number, where 0 is Sunday, 1 is Monday, etc.
oops...forget my last reply...I was a bit too quick on the draw. Try this
instead:
regression=# select date_trunc('month', current_date + '1
month'::interval) - '1 day'::interval;
?column?
-
2004-02-29 00:00:00
(1 row)
Joe
But the original request was for a specific day-of-week. So use Joe's
answer above to get last day of month, and use 'dow' to determine the
day-of-week of that day. Let's call that dow1. If the day-of-week being
asked for is dow2 then:
if dow1 < dow2
return (last-day-of-month - dow1 - 7 + dow2)
else
return (last-day-of-month - dow1 + dow2)
I'm no good at coding pgsql functions, so I'm not going to attempt proper
syntax.
Frank
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Getting FK relationships from information_schema
Tom Lane wrote: Kyle <[EMAIL PROTECTED]> writes: The problem is, the constraint names ($1, $2, etc.) are not unique so I don't know how to join the third query into the fourth. Hmm, this is messy :-(. The SQL spec requires constraint names to be unique within a schema. Postgres doesn't require them to be unique even within a table. I think this is only an issue when the user relies on postgres to choose a constraint name automatically. Seems like a reasonable approach would be to have postgres choose a name for the constraint that happens to be unique in the schema (like tablename_fkey_$1). Then if the user wants to make named constraints that all have the same name, he can (and information_schema will be less useful) or he can rely on the automatically generated names to be a bit more descriptive in information_schema. Can we get away with adding implementation-specific columns to information_schema tables? If not, what other alternatives are there? Another possible approach: Does the constraint name showing up in information_schema really have to be the _real_ name? Or can we just make a name consisting of the table name appended to the internal postgres constraint name. I think I like this less than the first idea. Kyle
Re: [SQL] Query becoming slower on adding a primary key
Hi,
Is there any solution to this issue ? I am facing it every week.
Warm Regds
Mallah.
Rajesh Kumar Mallah wrote:
Tom Lane wrote:
[EMAIL PROTECTED] writes:
tradein_clients=# explain analyze select email_id ,email ,contact from
t_a a join email_source f using(email_id) join email_subscriptions h
using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ;
Runs for Ever.
So what does plain explain say about it?
Oops sorry that was a valuable info i left. (sorry for delay too)
tradein_clients=# explain select email_id ,email ,contact from t_a a
join email_source f using(email_id) join email_subscriptions h
using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ;
+-+
| QUERY
PLAN|
+-+
| Hash Join (cost=133741.48..224746.39 rows=328814
width=40) |
| Hash Cond: ("outer".email_id =
"inner".email_id) |
| -> Seq Scan on email_subscriptions h (cost=0.00..70329.54
rows=749735 width=4) |
| Filter: (sub_id =
3)|
| -> Hash (cost=130230.99..130230.99 rows=324994
width=44)|
| -> Hash Join (cost=26878.00..130230.99 rows=324994
width=44) |
| Hash Cond: ("outer".email_id =
"inner".email_id) |
| -> Seq Scan on email_source f (cost=0.00..26159.21
rows=324994 width=4) |
| Filter: (source_id =
1) |
| -> Hash (cost=18626.80..18626.80 rows=800080
width=40) |
| -> Seq Scan on t_a a (cost=0.00..18626.80
rows=800080 width=40) |
+-+
(11 rows)
Time: 452.417 ms
tradein_clients=# ALTER TABLE t_a add primary key(email_id);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
"t_a_pkey" for table "t_a"
ALTER TABLE
Time: 7923.230 ms
tradein_clients=# explain select email_id ,email ,contact from t_a a
join email_source f using(email_id) join email_subscriptions
h using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ;
+---+
|QUERY
PLAN |
+---+
| Hash Join (cost=106819.76..197824.68 rows=328814
width=40) |
| Hash Cond: ("outer".email_id =
"inner".email_id)
|
| -> Seq Scan on email_subscriptions h (cost=0.00..70329.54
rows=749735 width=4)|
| Filter: (sub_id =
3)
|
| -> Hash (cost=103309.28..103309.28 rows=324994
width=44) |
| -> Merge Join (cost=0.00..103309.28 rows=324994
width=44) |
| Merge Cond: ("outer".email_id =
"inner".email_id) |
| -> Index Scan using t_a_pkey on t_a a
(cost=0.00..44689.59 rows=800080 width=40) |
| -> Index Scan using email_source_pkey on email_source
f (cost=0.00..52602.59 rows=324994 width=4) |
| Filter: (source_id =
1) |
+---+
(10 rows)
Time: 2436.551 ms
tradein_clients=#
Regds
Mallah.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] simple addition of constraints problem
Hi All,
I'm trying to do something which seems really simple to me. (Postgres 7.3.4)
I've got this table:
charter_dev2=# \d rcourseinfo
Table "public.rcourseinfo"
Column| Type | Modifiers
-+---+---
courseid| integer | not null default
nextval('rcourseinfo_seq'::text)
coursename | character varying(50) |
dept| character varying(30) |
number | character varying(4) |
section | character varying(2) |
trimester | character varying(5) |
schoolyear | character varying(8) |
facultyid | integer |
description | text |
credits | real |
Indexes: rcourseinfo_pkey primary key btree (courseid),
rcourseinfo_number_index btree (number)
With a primary key called 'courseid'.
I've got a second table:
charter_dev2=# \d coursesevaluations
Table "public.coursesevaluations"
Column | Type |Modifiers
---+-+--
courseid | integer |
evalid| integer |
coursesevaluations_id | integer | not null default
nextval('coursesevaluations_seq'::text)
Indexes: coursesevaluations_pkey primary key btree (coursesevaluations_id),
coursesevaluations_evalid_index btree (evalid)
I'd like to make 'courseid' in this second table a foreign key,
referencing 'courseid' in the first table. So I did this command:
charter_dev2=# ALTER TABLE coursesevaluations ADD CONSTRAINT rc_ce
FOREIGN KEY (courseid) REFERENCES rcourseinfo (courseid);
And I get this result:
NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
ERROR: rc_ce referential integrity violation - key referenced from
coursesevaluations not found in rcourseinfo
This doesn't make any sense to me - the fields are named the same,
and are the same data type. I've pored over the docs, to no avail.
I'm sure I'm missing something really elementary, but it's escaping
me.
I did try the following (because the error said "key referenced"):
ALTER TABLE coursesevaluations ADD CONSTRAINT rc_ce FOREIGN KEY
(courseid) REFERENCES rcourseinfo (rcourseinfo_pkey);
I got the error :
ERROR: ALTER TABLE: column "rcourseinfo_pkey" referenced in foreign
key constraint does not exist
!!
Thanks for any advice.
--
.Michelle
--
Michelle Murrain
mmurrain at dbdes dot com
413-222-6350 ph
617-889-0929 ph
952-674-7253 fax <--- new
Page: [EMAIL PROTECTED]
AIM:pearlbear0 ICQ:129250575
Y!: michelle_murrain Jabber: [EMAIL PROTECTED]
"Work like you don't need the money. Love like you've never been
hurt. Dance like nobody's watching." - Satchel Paige
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] a query question
hi all,
i was trying to migrate some data into a new structure i created. so i
execute this exact query:
insert into rec_t_ordenpago("ent_codigo", "sec_codigo", "ani_codigo",
"orp_numero",
"orp_tipopersona", "orp_persona", "cic_codigo",
"esp_codigo", "cur_codigo", "cur_paralelo",
"rub_codigo", "orp_valor", "orp_fechaemision",
"orp_fechavencimiento", "ban_codigo",
"cta_numero",
"est_codigo")
select cua.ent_codigo, cua.sec_codigo, cua.ani_codigo,
(select (nvl(max(orp_numero), 0) + 1) from rec_t_ordenpago),
'AL', alcu.alu_codigo, cua.cic_codigo, cua.esp_codigo,
cua.cur_codigo,
cua.cur_paralelo, cua.rub_codigo, cua.cpa_valor, current_date,
cua.cpa_fechavencimiento, null, null, 'PE'
from rec_m_cuadropagos cua, aca_t_alumnocurso alcu
where cua.ent_codigo= 1
and alcu.ent_codigo = cua.ent_codigo
and alcu.sec_codigo = cua.sec_codigo
and alcu.ani_codigo = cua.ani_codigo
and alcu.cic_codigo = cua.cic_codigo
and alcu.esp_codigo = cua.esp_codigo
and alcu.cur_codigo = cua.cur_codigo
and alcu.cur_paralelo = cua.cur_paralelo
but i ended up with duplicate key error then i replace the(select
(nvl(max(orp_numero), 0) + 1) from rec_t_ordenpago) part with
nextval('seq1') and that work.
So my question is, is this a postgreSQL limitation or is the way it have to
happen? why?
thanx in advance,
Jaime Casanova
_
The new MSN 8: smart spam protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] simple addition of constraints problem
On Tue, 8 Jun 2004, Michelle Murrain wrote:
> I'm trying to do something which seems really simple to me. (Postgres 7.3.4)
>
> I've got this table:
>
> charter_dev2=# \d rcourseinfo
> Table "public.rcourseinfo"
> Column| Type | Modifiers
> -+---+---
> courseid| integer | not null default
> nextval('rcourseinfo_seq'::text)
> coursename | character varying(50) |
> dept| character varying(30) |
> number | character varying(4) |
> section | character varying(2) |
> trimester | character varying(5) |
> schoolyear | character varying(8) |
> facultyid | integer |
> description | text |
> credits | real |
> Indexes: rcourseinfo_pkey primary key btree (courseid),
> rcourseinfo_number_index btree (number)
>
> With a primary key called 'courseid'.
>
> I've got a second table:
>
> charter_dev2=# \d coursesevaluations
> Table "public.coursesevaluations"
> Column | Type |Modifiers
> ---+-+--
> courseid | integer |
> evalid| integer |
> coursesevaluations_id | integer | not null default
> nextval('coursesevaluations_seq'::text)
> Indexes: coursesevaluations_pkey primary key btree (coursesevaluations_id),
> coursesevaluations_evalid_index btree (evalid)
>
>
> I'd like to make 'courseid' in this second table a foreign key,
> referencing 'courseid' in the first table. So I did this command:
>
> charter_dev2=# ALTER TABLE coursesevaluations ADD CONSTRAINT rc_ce
> FOREIGN KEY (courseid) REFERENCES rcourseinfo (courseid);
>
> And I get this result:
>
> NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
> ERROR: rc_ce referential integrity violation - key referenced from
> coursesevaluations not found in rcourseinfo
>
> This doesn't make any sense to me - the fields are named the same,
> and are the same data type. I've pored over the docs, to no avail.
> I'm sure I'm missing something really elementary, but it's escaping
> me.
That's the message for the constraint violation. It looks like perhaps
the data doesn't meet the constraint (in 7.4 it would have shown you
values, for 7.3 you'll have to look yourself).
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Convert INT to INTERVAL?
On Fri, Feb 27, 2004 at 11:54:04AM -0500, Brian Knox wrote: > Is it possible within pl/pgsql, to convert an integer to an interval in > months? IE, if num_months is an INT set to 48, can it be converted to an > interval? select (1||' months')::interval; You should be able to replace the "1" with your integer variable. Michael -- Michael Darrin Chaney [EMAIL PROTECTED] http://www.michaelchaney.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Input Arguments
One more query: Cant I do something like this: $2 := $2 + interval ''86399 seconds''; Where $2 is one of my input variables to the function. It gives me an error: ERROR: $2 is declared CONSTANT ---(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
Re: [SQL] Postgres DB
Hi Tom, I have read that template1 is the default DB that gets installed with Postgres. But when I type the command "psql" and don't specify a DB name, it says that: psql: FATAL: Database "postgres" does not exist in the system catalog. This is the reason why I guessed that it expects the postgres DB be installed by default. Also earlier I have seen this DB get installed by itself, although we have not created it. Any answers to the above questions. Regards, Sumita -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Saturday, February 21, 2004 9:41 PM To: Sumita Biswas Cc: [EMAIL PROTECTED] Subject: Re: [SQL] Function "Sumita Biswas" <[EMAIL PROTECTED]> writes: > We installed postgres, but it did not install the default DB > "postgres". The only DB that exists in a fresh installation is "template1". If you want to create a DB named "postgres", use the createdb script. BTW, it's bad manners to ask a new question by following up an existing unrelated thread. Start a new thread with an appropriate subject line. Otherwise you're wasting the time of the other people on the mailing list, who are exactly the people who might answer your question. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Getting FK relationships from information_schema
Kyle <[EMAIL PROTECTED]> writes: > I think this is only an issue when the user relies on postgres to choose > a constraint name automatically. Seems like a reasonable approach would > be to have postgres choose a name for the constraint that happens to be > unique in the schema (like tablename_fkey_$1). We have discussed changing the default names of FK constraints before. I have no problem with doing something like the above --- any objection out there? (Of course, this is only a long-term fix for your original problem, as it'll take a good long while for any such naming change to propagate to Joe's-Corner-Bar's database.) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Last insert id
I have three tables which are related a serial field, table1_id, in on of the tables. Updating the tables is done through a transaction. My problem is, once I have insert a row in the first tables with table1_id, I need for the other two tables. How can I get this? Is it safe to use "select max(table1_id) from table1" after the insert? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Getting FK relationships from information_schema
Tom Lane wrote: > Kyle <[EMAIL PROTECTED]> writes: > > I think this is only an issue when the user relies on postgres to > > choose a constraint name automatically. Seems like a reasonable > > approach would be to have postgres choose a name for the constraint > > that happens to be unique in the schema (like tablename_fkey_$1). > > We have discussed changing the default names of FK constraints > before. I have no problem with doing something like the above --- any > objection out there? I think it's a good idea. It will also make the error messages of the kind "foreign key $1 violated" a bit clearer by default. There will, however, be complaints that the constraint names are not automatically renamed with the table; but we are used to those by now. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
