Re: [SQL] Re: Problem with Dates
On Fri, 26 Jan 2001 12:46, Glen and Rosanne Eustace wrote:
> template1=# select '31/12/2000'::date + '365 days'::timespan;
> ?column?
>
> 2002-01-01 00:00:00+13<<< Wrong
> (1 row)
This appears to be fixed in the current sources by CVSup :-
23:16:03 chris@berty:~ $ psql --version
psql (PostgreSQL) 7.1beta3
contains readline, history support
Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
Portions Copyright (c) 1996 Regents of the University of California
Read the file COPYRIGHT or use the command \copyright to see the
usage and distribution terms.
Winter Time:
template1=# select '30/06/2000:10:30'::timestamp as date;
date
2000-06-30 10:30:00+12
(1 row)
template1=# select '30/06/2000:10:30'::timestamp + '1 year'::interval as
date;
date
2001-06-30 10:30:00+12
(1 row)
Correct!!
Summer Time:
template1=# select '30/12/2000:10:30'::timestamp as date;
date
2000-12-30 10:30:00+13
(1 row)
template1=# select '30/12/2000:10:30'::timestamp + '1 year'::interval as
date;
date
2001-12-30 10:30:00+13
(1 row)
Correct!!
Now lets try with '365 days' instead of '1 year'
template1=# select '30/12/2000:10:30'::timestamp + '365 days'::interval as
date;
date
2001-12-30 10:30:00+13
(1 row)
Correct!!
Now lets try across a leap year:
template1=# select '30/01/2004:10:30'::timestamp as date;
date
2004-01-30 10:30:00+13
(1 row)
template1=# select '30/01/2004:10:30'::timestamp + '1 year'::interval as date;
date
2005-01-30 10:30:00+13
(1 row)
template1=# select '30/01/2004:10:30'::timestamp + '365 days'::interval as
date;
date
2005-01-29 10:30:00+13
(1 row)
Correct!!
school=# select * from day;
number | name
+---
0 | Sunday
1 | Monday
2 | Tuesday
3 | Wednesday
4 | Thursday
5 | Friday
6 | Saturday
(7 rows)
school=# select name from day where number= \
(select date_part('dow','now'::datetime) as day);
name
Sunday
(1 row)
Correct!!
The PostgreSQL Team is to be congratulated.
--
Sincerely etc.,
NAME Christopher Sawtell
CELL PHONE 021 257 4451
ICQ UIN45863470
EMAIL csawtell @ xtra . co . nz
CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz
-->> Please refrain from using HTML or WORD attachments in e-mails to me <<--
[SQL] Multicolumn primary keys and multicolumn foreign keys
Hi,
I've found a trouble and I've tried to avoid it without success:
---
create table companies (
id serial not null primary key,
firm_name text not null,
activity text
);
create table customers (
id int not null references companies,
seller_id int not null references companies,
note text,
primary key (id,seller_id)
);
create table invoices (
seller_id int4 not null references companies, /* who send invoice
*/
customer_id int4 not null, /* who receive the invoice and pay
for it */
invoice_no int4 not null unique,
invoice_date date,
primary key (seller_id,invoice_no,invoice_date),
foreign key (seller_id, customer_id) references customers
);
INSERT INTO "companies" ("firm_name","activity") VALUES
('NonSoLoSoft','ASP');
INSERT INTO "companies" ("firm_name","activity") VALUES
('MyFavouriteCustomer','Buy and pay');
INSERT INTO "customers" ("id","seller_id","note") VALUES (2,1,'customer
since 1966');
INSERT INTO "invoices" (seller_id,customer_id,invoice_no,invoice_date)
values (1,2,1,'now');
ERROR: referential integrity violation - key referenced from
invoices not found in customers
select * from customers;
id | seller_id |note
+---+-
2 | 1 | customer since 1816
(1 row)
---
Why have I found this ERROR about referential integrity violation, if
the record to reference is
in the customer table?
Thank you in advance,\fer
Re: [SQL] Multicolumn primary keys and multicolumn foreign keys
On Sun, 28 Jan 2001, Ferruccio Zamuner wrote:
> Hi,
>
> I've found a trouble and I've tried to avoid it without success:
>
> ---
> create table companies (
>id serial not null primary key,
>firm_name text not null,
>activity text
> );
>
> create table customers (
>id int not null references companies,
>seller_id int not null references companies,
>note text,
>primary key (id,seller_id)
> );
>
> create table invoices (
>seller_id int4 not null references companies, /* who send invoice
> */
>customer_id int4 not null, /* who receive the invoice and pay
> for it */
>invoice_no int4 not null unique,
>invoice_date date,
>
>primary key (seller_id,invoice_no,invoice_date),
>foreign key (seller_id, customer_id) references customers
> );
>
> INSERT INTO "companies" ("firm_name","activity") VALUES
> ('NonSoLoSoft','ASP');
> INSERT INTO "companies" ("firm_name","activity") VALUES
> ('MyFavouriteCustomer','Buy and pay');
> INSERT INTO "customers" ("id","seller_id","note") VALUES (2,1,'customer
> since 1966');
>
> INSERT INTO "invoices" (seller_id,customer_id,invoice_no,invoice_date)
> values (1,2,1,'now');
>
> ERROR: referential integrity violation - key referenced from
> invoices not found in customers
>
> select * from customers;
> id | seller_id |note
> +---+-
> 2 | 1 | customer since 1816
> (1 row)
>
> ---
>
> Why have I found this ERROR about referential integrity violation, if
> the record to reference is
> in the customer table?
>
> Thank you in advance,\fer
>
>
there's something wrong with ur foreign key statement, take note ur table
customers has primarykey (id,sellers_id)<< take note the order of the
fields, so ur foreignkey must be foreignkey(customer_id,seller_id) and not
foreignkey(seller_id,customer_id)
regards
richard
[SQL] FastPath Protocol Error
Helooo.. I have problem while retrieving image from postgres 7.0.0 through some front-end. I am getting Exception saying: FastPath protocol error : Z any idea? -- som
