[SQL] ON INSERT view rule
Hello, pgsql-sql! What I've got here are a couple of ON INSERT rules for a view. The second rule is what I'm concerned about. I wrote it with PostgreSQL's ACID compliance in mind, but can I trust it? From what I gather, if I were to simply use NEW.address_line_id rather than address_lines_id_seq.last_value, it would be replaced by nextval(address_line_id_seq), so I'm trying to work around that. If there is there a better way to do this, I'm all ears. Would lastval() work for me in this case? Thanks! -- Anthony Chavez http://anthonychavez.org/ mailto:[EMAIL PROTECTED] jabber:[EMAIL PROTECTED] CREATE OR REPLACE VIEW addresses_address_lines AS SELECT a.id AS address_id, al.id AS address_line_id, line, ordering FROM addresses a INNER JOIN address_lines al ON al.address_id = a.id LEFT OUTER JOIN junctions ON parent_table = 'address_lines' AND parent_id = al.id AND child_table = 'display_orderings' LEFT OUTER JOIN display_orderings o ON o.id = child_id; CREATE OR REPLACE RULE insert_address_lines AS ON INSERT TO addresses_address_lines DO INSTEAD INSERT INTO address_lines (address_id, line) VALUES (NEW.address_id, NEW.line); CREATE OR REPLACE RULE insert_display_orderings AS ON INSERT TO addresses_address_lines DO UPDATE display_orderings SET ordering = NEW.ordering FROM address_lines_id_seq INNER JOIN junctions ON parent_table = 'address_lines' AND parent_id = last_value AND child_table = 'display_orderings' WHERE ordering <> NEW.ordering AND display_orderings.id = child_id; pgpSI6vATjFFf.pgp Description: PGP signature
[SQL] above the date functionssssssss
Hi good evening to every one……… i want to get month and year in the current date..see this is date 2005-02-12... here date is 12 , month is 02 and year is 2005..but i want format like 02-2005... can u tell me how to do this Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records. Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
Re: [SQL] above the date functionssssssss
am 11.07.2006, um 17:04:06 +0530 mailte Penchalaiah P. folgendes: > > Hi good evening to every one. > > i want to get month and year in the current date.. > see this is date 2005-02-12... here date is 12 , month is 02 and year is > 2005.. > but i want format like 02-2005... Simple, to_char is your friend: test=*# select to_char(current_date, 'MM-'); to_char - 07-2006 (1 row) HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(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] above the date functionssssssss
Penchal reddy,
You should be able to use extract or date_trunc functions like in the
documentation:
example:
SELECT EXTRACT(MONTH FROM DATE '2005-02-12') | "-" | EXTRACT(YEAR FROM DATE
'2005-02-12');
or
SELECT date_trunc('month', DATE '2005-02-12') | "-" | date_trunc('year', DATE
'2005-02-12');
Stuart
-Original Message-
>From: "Penchalaiah P." <[EMAIL PROTECTED]>
>Sent: Jul 11, 2006 7:34 AM
>To: pgsql-sql@postgresql.org
>Subject: [SQL] above the date function
>
>
>Hi good evening to every one.
>
>
>i want to get month and year in the current date..
>see this is date 2005-02-12... here date is 12 , month is 02 and year is
>2005..
>but i want format like 02-2005...
>
>can u tell me how to do this
>
>
>
>
>Thanks & Regards
>
>Penchal reddy | Software Engineer
>
>
>Infinite Computer Solutions | Exciting Times...Infinite Possibilities...
>
>
>SEI-CMMI level 5 | ISO 9001:2000
>
>IT SERVICES | BPO
>
>
>Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities |
>Retail & Distribution | Government
>
>
>Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No
>+91-9980012376|www.infics.com
>
>
>Information transmitted by this e-mail is proprietary to Infinite
>Computer Solutions and/ or its Customers and is intended for use only by
>the individual or entity to which it is addressed, and may contain
>information that is privileged, confidential or exempt from disclosure
>under applicable law. If you are not the intended recipient or it
>appears that this mail has been forwarded to you without proper
>authority, you are notified that any use or dissemination of this
>information in any manner is strictly prohibited. In such cases, please
>notify us immediately at [EMAIL PROTECTED] and delete this mail from
>your records.
>
>
>
>
>
>
>Information transmitted by this e-mail is proprietary to Infinite Computer
>Solutions and / or its Customers and is intended for use only by the
>individual or the entity to which it is addressed, and may contain information
>that is privileged, confidential or exempt from disclosure under applicable
>law. If you are not the intended recipient or it appears that this mail has
>been forwarded to you without proper authority, you are notified that any use
>or dissemination of this information in any manner is strictly prohibited. In
>such cases, please notify us immediately at [EMAIL PROTECTED] and delete this
>email from your records.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] above the date functionssssssss
On Tue, Jul 11, 2006 at 05:04:06PM +0530, Penchalaiah P. wrote: > i want to get month and year in the current date.. > see this is date 2005-02-12... here date is 12 , month is 02 and year is > 2005.. > but i want format like 02-2005... See "Data Type Formatting Functions" and "Date/Time Functions and Operators" in the "Functions and Operators" chapter of the documentation: http://www.postgresql.org/docs/8.1/interactive/functions-formatting.html http://www.postgresql.org/docs/8.1/interactive/functions-datetime.html -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] ON INSERT view rule
Anthony Chavez <[EMAIL PROTECTED]> writes: > What I've got here are a couple of ON INSERT rules for a view. The > second rule is what I'm concerned about. I wrote it with PostgreSQL's > ACID compliance in mind, but can I trust it? Oops, forgot to mention two things: 1. The addresses_address_lines view assumes that a row already exists in the addresses relation because that relation has some NOT NULL attributes that lack defaults. Hence, there is no insert_addresses rule. I suppose I should create one, but choosing a default value for some of the foreign keys in that relation would be difficult. 2. I have an AFTER INSERT trigger function on the addresses relation that inserts a default display_orderings tuple (with ordering = 0) and sets up the association in the junctions table. Hence the use of UPDATE in the insert_display_orderings rule. Cheers! -- Anthony Chavez http://anthonychavez.org/ mailto:[EMAIL PROTECTED] jabber:[EMAIL PROTECTED] pgp83LIixWmPl.pgp Description: PGP signature
Re: [SQL] MS-SQL<->Postgres sync
On Mon, Jul 10, 2006 at 01:30:51PM -0500, Scott Marlowe wrote: > > (which means you have to deal with failures in one database and not > > another). Is the idea that this is multi-master? > > I wonder if it would be possible to write a set of triggers for MSSQL > that would allow you to run slony daemons that connected to it and a > postgresql server and did replication. Just mad scientisting for a > second. Well, yes, but that's why I asked whether it's multimaster. Because if so, it'll be at least tricky to do under Slony I. A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] How to find entries missing in 2nd table?
Hi, I realize I probably lost my marbles but I've been having a god awful time with a single query: control: controller_id pk; datapack: controller_id fk; I need to get all entries from the table control that are not listed in datapack. Thanks, Alex ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] How to find entries missing in 2nd table?
> Hi, > I realize I probably lost my marbles but I've been having a god > awful time with a single query: > > control: > > > controller_id pk; > > > datapack: > > controller_id fk; > > > > > I need to get all entries from the table control that are not listed in > datapack. SELECT C.CONTROLLER_ID FROM CONTROL AS C LEFT JOIN DATAPACK AS D ON ( C.CONTROLLER_ID = D.CONTROLLER_ID) WHERE D.CONTROLLER_ID IS NULL; Regards, Richard Broersma Jr. ---(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] How to find entries missing in 2nd table?
On Tue, 2006-07-11 at 09:19, [EMAIL PROTECTED] wrote: > Hi, > I realize I probably lost my marbles but I've been having a god > awful time with a single query: > > control: > > > controller_id pk; > > > datapack: > > controller_id fk; > > > > > I need to get all entries from the table control that are not listed in > datapack. OK, this is a pretty common problem. Easy solution is left join / null. select t1.id from table1 t1 left join table table2 t2 on (t1.id=t2.id) where t2.id is null since a left join gives us all rows from the left table, and nulls where the right table doesn't match up, and we're asking for all the rows where t2.id is null, we'll get all the rows in t1 that don't have a match in t2. cool, huh? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] How to find entries missing in 2nd table?
Richard Broersma Jr написа: >> Hi, >> I realize I probably lost my marbles but I've been having a god >> awful time with a single query: >> >> control: >> >> >> controller_id pk; >> >> >> datapack: >> >> controller_id fk; >> >> >> >> >> I need to get all entries from the table control that are not listed in >> datapack. > > SELECT C.CONTROLLER_ID > > FROM CONTROL AS C > LEFT JOIN DATAPACK AS D ON ( C.CONTROLLER_ID = D.CONTROLLER_ID) > > WHERE D.CONTROLLER_ID IS NULL; > Or (SELECT controller_id FROM control) EXCEPT (SELECT controller_id FROM datapack) ? -- Milen A. Radev ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Can function results be used in WHERE?
Tom Lane wrote: > But as far as the underlying misconception goes, you seem to think that > "4" in the WHERE clause might somehow be taken as referring to the > fourth SELECT result column (why you don't think that the "1" would > likewise refer to the first result column isn't clear). This is not so. > "4" means the numeric value four. There is a special case in ORDER BY > and GROUP BY that an argument consisting of a simple integer literal > constant will be taken as a reference to an output column. This is an > ugly kluge IMHO... Yeah, it was a longshot. I only tried it because the column label did NOT work, and I had some gut reaction to repeating the same function twice: stage=# SELECT pod_code, lat, lon, calculate_distance(lat,lon,37.789629,-122.422082) as dist FROM eg_pod WHERE dist < 1 ORDER BY dist desc limit 10; ERROR: column "dist" does not exist stage=# SELECT pod_code, lat, lon, calculate_distance(lat,lon,37.789629,-122.422082) as dist FROM eg_pod WHERE calculate_distance(lat,lon, 37.789629,-122.422082) < 1 ORDER BY dist desc limit 5; pod_code |lat| lon | dist --+---+-+--- 5 | 37.792022 | -122.404247 | 0.988808031847045 62 | 37.780166 | -122.409615 | 0.944907273102541 4 | 37.798528 | -122.409582 | 0.919592583879426 86 | 37.777529 | -122.417982 | 0.866416010967029 68 | 37.789915 | -122.406926 | 0.82867104307647 (5 rows) stage=# select * from version(); version --- PostgreSQL 7.4.12 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-20) (1 row) The asymetry between HAVING/WHILE and ORDER BY seems odd. Is there more to that story? -- Visit http://www.obviously.com/ ---(end of broadcast)--- TIP 1: 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
Re: [SQL] How to find entries missing in 2nd table?
At 10:19 AM 7/11/06, [EMAIL PROTECTED] wrote: control: controller_id pk; datapack: controller_id fk; I need to get all entries from the table control that are not listed in datapack. select controller.controller_id from controller left join datapack on controller.controller_id = datapack.controller_id where datapack.controller_id is null; ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] How to find entries missing in 2nd table?
--- Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > > >> I need to get all entries from the table control that are not listed in > > >> datapack. > > > > > > SELECT C.CONTROLLER_ID > > > > > > FROM CONTROL AS C > > > LEFT JOIN DATAPACK AS D ON ( C.CONTROLLER_ID = D.CONTROLLER_ID) > > > > > > WHERE D.CONTROLLER_ID IS NULL; > > > > > > > > > Or > > (SELECT controller_id FROM control) > > EXCEPT > > Good point! But don't forget to include the list. :-) > > Regards, > > Richard Broersma Jr. > > (SELECT controller_id FROM datapack) > > ? > > > > -- > > Milen A. Radev > > > > ---(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
[SQL] Invalid memory alloc request size
Hi, I'm using PostgreSQL 8.1.4 on a Linux 2.6.8-2-686-smp machine, 2 Go memory. I have a strange error "invalid memory alloc request size", when I perform the following query: => select * from player where lower(username) = 'wario'; ERROR: invalid memory alloc request size 1918988375 where player contains a bit more than 1 million records, username is a character varying(255) not null. I have strictly no idea about what goes wrong here. Whatever operation I try to apply on the column username's value (lower, upper, length) raises such an error. I searched for some similar cases in the pgsql-sql archive but nothing really similar. Any idea? Regards, P.S.: I don't have this problem on other tables containing less data. -- Daniel CAUNE Ubisoft Online Technology (514) 490 2040 ext. 3613 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Unexpected SQL error for UPDATE
Originally I have a table like thiscreate table users ( userid integer not null, email_address varchar (255) unique not null,PRIMARY KEY (userid) ); Later I find that varchar(255) is not enough. I designed to change the type totext. There is no simple SQL to alter the type. So I use a series of SQLs tocreate a new column, copy the data over and then replace the old column with the new. ALTER TABLE users ADD email_address_text text UNIQUE; UPDATE users set email_address_text=email_address;ALTER TABLE users DROP email_address;ALTER TABLE users RENAME email_address_text TO email_address;ALTER TABLE users ALTER email_address SET not null; This works mostly OK. Until I have one database that has over 1 million recordsin table user. It fails with an inexplicable error:mydb=# UPDATE users set email_address_text=email_address;ERROR: invalid page header in block 6776 of relation "users_email_address_text_key" Anyone can shred some light what has went wrong?wy
