[SQL] funny update, say update 1, updated 1 added 2nd.
I have been having some trouble with a particular table view. An UPDATE command is not only changing the applicable record it is also creating a new record as well. wholesale=# select * from accounts_supplier; id | name | contact | addr| addr2 | town | postcode | state | phone | fax | account_type +--+-+---+---++--+---+---+-+-- 1 | ABC construction | TOM | | || | NSW | | | Cash Only 2 | test | | | || | | | | 7 Day 3 | build-4-U| boss| somewhere | | back of beyond | | | | | 7 Day (3 rows) wholesale=# update accounts_supplier set addr='nowhere' where id=3; UPDATE 1 wholesale=# select * from accounts_supplier; id | name | contact | addr | addr2 | town | postcode | state | phone | fax | account_type +--+-+-+---++--+---+---+-+-- 1 | ABC construction | TOM | | || | NSW | | | Cash Only 2 | test | | | || | | | | 7 Day 6 | build-4-U| boss| nowhere | | back of beyond | | | | | 7 Day 3 | build-4-U| boss| nowhere | | back of beyond | | | | | 7 Day (4 rows) Can anyone tell me why this is happening and how to fix it. Here are the table and view definitions. CREATE TABLE account_type ( number smallint, name character varying(20) ); CREATE TABLE address ( addr character varying(40), addr2 character varying(40), town character varying(20), postcode character varying(10), state character(4) ); CREATE TABLE supplier ( id bigserial NOT NULL, name character varying(40), phone character varying(20), fax character varying(20), contact character varying(40), account_type smallint DEFAULT 0 ) INHERITS (address); CREATE VIEW accounts_supplier AS SELECT supplier.id, supplier.name, supplier.contact, supplier.addr, supplier.addr2, supplier.town, supplier.postcode, supplier.state, supplier.phone, supplier.fax, account_type.name AS account_type FROM supplier, account_type WHERE (account_type.number = supplier.account_type); CREATE RULE accounts_supplier_update AS ON UPDATE TO accounts_supplier DO INSTEAD UPDATE supplier SET name = new.name, contact = new.contact, addr = new.addr, addr2 = new.addr2, town = new.town, postcode = new.postcode, state = upper((new.state)::text), phone = new.phone, fax = new.fax, account_type = (SELECT account_type.number FROM account_type WHERE ((account_type.name)::text = (new.account_type)::text)) WHERE (supplier.id = new.id); wholesale=# select version(); version -- PostgreSQL 7.4.8 on i386-redhat-linux-gnu, compiled by GCC i386-redhat-linux-gcc (GCC) 3.4.3 20050227 (Red Hat 3.4.3-22) (1 row) wholesale=# select * from account_type; number | name +--- 0 | Cash Only 1 | 7 Day 2 | 30 Day 3 | 60 Day 4 | 90 Day (5 rows) Thanks for any help Regards Neil. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] How to obtain the list of data table name only
On Thu, 2005-07-14 at 15:24 -0700, [EMAIL PROTECTED] wrote: > Hi, > > I am wondering if anyone can tell me how I can obtain only the list of > data table in postgresql without function and other ancillary tables. I > hope that I can add a tag that can filter only data table. > > I am using the following SQL Statement: > > "SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES" > > I would appreciate if anyone can enligten me on how this can be > achieve. > > Sunny I am not sure what you mean but one way to find out what tables there are is to use the 'pg_tables' view. select tablename from pg_tables; Regards Neil. ---(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
[SQL] joining two simular (but not identical tables)
I have two similar but not identical tables. I would like to create a view that combines the contents of both tables into a single view, where each record in each table is visible as a separate record in the view. table a Column | Type | Modifiers ---+---+--- prcode| character varying(12) | descr | character varying(55) | rrp | numeric(10,5) | sugcusmkdn| numeric(6,2) | customerpr| numeric(7,2) | costpr| numeric(11,6) | lengths | character(1) | profitpercent | numeric(6,2) | table b Column | Type | Modifiers ---+---+--- prcode| character varying(12) | descr | character varying(55) | rrp | numeric(10,5) | customerpr| numeric(7,2) | supdis| numeric(6,3) | costpr| numeric(11,6) | lengths | character(1) | profitpercent | numeric(6,2) | dnprice | numeric(7,2) | stcode| character varying(18) | dnprofit | numeric(5,2) | Thanks for any help. Regards Neil. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Extract date from week
Jaime Casanova wrote:
On 11/8/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
Hi
Looking the e-mail I remembered a question.
I saw that "select extract (week from now()::date)" will return the
week number
of current year. But, how can I convert a week to the first reference
date. Ex:
select extract(week from '20050105'::date); -- 5 Jan 2005
--Returns--
date_part |
1 |
It is the first week of year (2005), and how can I get what is the first date
references the week 1? Ex:
select week 1
--should return---
date |
20050103 | -- 3 Jan 2005
Thank you.
Lucas Vendramin
Extracted from:
http://www.postgresql.org/docs/8.0/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
--- begin extracted text ---
week
The number of the week of the year that the day is in. By definition
(ISO 8601), the first week of a year contains January 4 of that year.
(The ISO-8601 week starts on Monday.) In other words, the first
Thursday of a year is in week 1 of that year. (for timestamp values
only)
Because of this, it is possible for early January dates to be part of
the 52nd or 53rd week of the previous year. For example, 2005-01-01 is
part of the 53rd week of year 2004, and 2006-01-01 is part of the 52nd
week of year 2005.
SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 7
--- end extracted text ---
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 6: explain analyze is your friend
Hi
http://www.postgresql.org/docs/8.0/static/functions-formatting.html
for the first week of 2005 use
=> select to_timestamp('1 2005','IW ')::date as week_start;
week_start
2005-01-03
Regards Neil.
---(end of broadcast)---
TIP 6: explain analyze is your friend
