[SQL] foreign key question
Hi folks, I have a table which lists facilities and another table that lists access levels for those facilities. All straight forward using a foreign key set up using a normal references clause. users=# select f_id, f_desc from facilities order by f_id; f_id | f_desc --+- 1 | Login 3 | Users 16 | Itinerary 31 rows) users=# select * from facility_levels ; fl_f_id | fl_level |fl_desc -+--+--- 16 |1 | Own itinerary 16 |2 | Dealer/Dept itinerary 16 |3 | Dept itinerary 16 |4 | Dealer/Dept On/Off 16 |5 | Dept On/Off 16 |6 | All On/Off 16 |7 | All features (7 rows) users=# Now I want to set up a new access level table specific to the itinerary, along the lines of u_id int4 not null references users(u_id) fl_level int4 not null references facility_levels(16, fl_level) Firstly, is this possible, and secondly how would I do it? -- Gary Stainburn I.T. Manager Ringways Garages http://www.ringways.co.uk -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] foreign key question
On Wednesday 05 January 2011 09:53:43 Gary Stainburn wrote: > Now I want to set up a new access level table specific to the itinerary, > along the lines of > > u_id int4 not null references users(u_id) > fl_level int4 not null references facility_levels(16, fl_level) > > Firstly, is this possible, and secondly how would I do it? I've managed a work-around by creating a column that defaults to 16 and then used ALTER TABLE ADD CONSTRAINT to add a foreign key with two pairs of keys. This feels wrong though as my table now has a column that is ultimately redundant, and worse can be changed to a wrong value. Ok, I've sorted the last bit by adding a check constraint to make sure it always contains 16, but it still feels wrong. -- Gary Stainburn I.T. Manager Ringways Garages http://www.ringways.co.uk -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] foreign key question
On 2011-01-05, Gary Stainburn wrote: > On Wednesday 05 January 2011 09:53:43 Gary Stainburn wrote: >> Now I want to set up a new access level table specific to the itinerary, >> along the lines of >> >> u_id int4 not null references users(u_id) >> fl_level int4 not null references facility_levels(16, fl_level) >> >> Firstly, is this possible, and secondly how would I do it? > > I've managed a work-around by creating a column that defaults to 16 and then > used ALTER TABLE ADD CONSTRAINT to add a foreign key with two pairs of keys. > > This feels wrong though as my table now has a column that is ultimately > redundant, and worse can be changed to a wrong value. > > Ok, I've sorted the last bit by adding a check constraint to make sure it > always contains 16, but it still feels wrong. it feels wrong that's because it's not normalised, the column with the 16's probably should not be there. or possibly it should have rows with other values too. look at how this table is useful and look for a more general way to do it. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] explicit casts
Hi there,
I installed postgresql 8.4 on my box and now i have troubles with the
following query regarding explicit casts.
select to_char(trunc(to_date(:the_date, '-mm-dd'), 'Month'),
'fmMonth') as month,
to_char(trunc(to_date(:the_date, '-mm-dd'), 'Month'), '')
as year,
to_char(trunc(to_date(:the_date, '-mm-dd'), 'Month'), 'J') as
first_julian_date_of_month,
to_char(last_day(to_date(:the_date, '-mm-dd')), 'DD') as
num_days_in_month,
to_char(trunc(to_date(:the_date, '-mm-dd'), 'Month'), 'D') as
first_day_of_month,
to_char(last_day(to_date(:the_date, '-mm-dd')), 'DD') as last_day,
trunc(add_months(to_date(:the_date, '-mm-dd'), 1),'Day') as
next_month,
trunc(add_months(to_date(:the_date, '-mm-dd'), -1),'Day') as
prev_month,
trunc(to_date(:the_date, '-mm-dd'), 'year') as beginning_of_year,
to_char(last_day(add_months(to_date(:the_date, '-mm-dd'), -1)),
'DD') as days_in_last_month,
to_char(add_months(to_date(:the_date, '-mm-dd'), 1), 'fmMonth')
as next_month_name,
to_char(add_months(to_date(:the_date, '-mm-dd'), -1),
'fmMonth') as prev_month_name
from dual
the value assigned to the variable :the_date is '2010-01-05'
The error is
Error: Ns_PgExec: result status: 7 message: ERROR: function
to_date(timestamp with time zone, unknown) does not exist
LINE 1: select to_date(date_trunc('month',add_months( $1 ,1)),'-...
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
QUERY: select to_date(date_trunc('month',add_months( $1
,1)),'-MM-DD') - 1
CONTEXT: PL/pgSQL function "last_day" line 6 at SQL statement
how would i apply the following solution
date_trunc('month', p_date_in + interval '1 month')::date - 1
to fix the query above?
cheers,
iuri
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] explicit casts
So far,
I could write the following query
select to_char(trunc(to_date('2010-01-02', '-mm-dd'), 'Month'),
'fmMonth') as month,
to_char(trunc(to_date('2010-01-02', '-mm-dd'), 'Month'), '') as
year,
to_char(trunc(to_date('2010-01-02', '-mm-dd'), 'Month'), 'J') as
first_julian_date_of_month,
to_char(last_day('2010-01-02')::date, 'DD') as num_days_in_month,
to_char(trunc(to_date('2010-01-02', '-mm-dd'), 'Month'), 'D') as
first_day_of_month,
to_char(last_day('2010-01-02')::date, 'DD') as last_day,
trunc(add_months(to_date('2010-01-02', '-mm-dd'), 1),'Day') as
next_month,
trunc(add_months(to_date('2010-01-02', '-mm-dd'), -1),'Day') as
prev_month,
trunc(to_date('2010-01-02', '-mm-dd'), 'year') as beginning_of_year,
to_char(last_day(add_months('2010-01-02', -1))::date, 'DD') as
days_in_last_month,
to_char(add_months(to_date('2010-01-02', '-mm-dd'), 1), 'fmMonth')
as next_month_name,
to_char(add_months(to_date('2010-01-02', '-mm-dd'), -1), 'fmMonth')
as prev_month_name
from dual
But i still miss some lines in order to properly explicit casts in the query
cheers,
iuri
On 01/05/2011 10:24 PM, Iuri Sampaio wrote:
Hi there,
I installed postgresql 8.4 on my box and now i have troubles with the
following query regarding explicit casts.
select to_char(trunc(to_date(:the_date, '-mm-dd'), 'Month'),
'fmMonth') as month,
to_char(trunc(to_date(:the_date, '-mm-dd'), 'Month'), '')
as year,
to_char(trunc(to_date(:the_date, '-mm-dd'), 'Month'), 'J') as
first_julian_date_of_month,
to_char(last_day(to_date(:the_date, '-mm-dd')), 'DD') as
num_days_in_month,
to_char(trunc(to_date(:the_date, '-mm-dd'), 'Month'), 'D') as
first_day_of_month,
to_char(last_day(to_date(:the_date, '-mm-dd')), 'DD') as
last_day,
trunc(add_months(to_date(:the_date, '-mm-dd'), 1),'Day') as
next_month,
trunc(add_months(to_date(:the_date, '-mm-dd'), -1),'Day') as
prev_month,
trunc(to_date(:the_date, '-mm-dd'), 'year') as beginning_of_year,
to_char(last_day(add_months(to_date(:the_date, '-mm-dd'),
-1)), 'DD') as days_in_last_month,
to_char(add_months(to_date(:the_date, '-mm-dd'), 1),
'fmMonth') as next_month_name,
to_char(add_months(to_date(:the_date, '-mm-dd'), -1),
'fmMonth') as prev_month_name
from dual
the value assigned to the variable :the_date is '2010-01-05'
The error is
Error: Ns_PgExec: result status: 7 message: ERROR: function
to_date(timestamp with time zone, unknown) does not exist
LINE 1: select to_date(date_trunc('month',add_months( $1 ,1)),'-...
^
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.
QUERY: select to_date(date_trunc('month',add_months( $1
,1)),'-MM-DD') - 1
CONTEXT: PL/pgSQL function "last_day" line 6 at SQL statement
how would i apply the following solution
date_trunc('month', p_date_in + interval '1 month')::date - 1
to fix the query above?
cheers,
iuri
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] explicit casts
On Wednesday 05 January 2011 4:24:34 pm Iuri Sampaio wrote:
> Hi there,
>
> I installed postgresql 8.4 on my box and now i have troubles with the
> following query regarding explicit casts.
>
> select to_char(trunc(to_date(:the_date, '-mm-dd'), 'Month'),
> 'fmMonth') as month,
> to_char(trunc(to_date(:the_date, '-mm-dd'), 'Month'), '')
> as year,
> to_char(trunc(to_date(:the_date, '-mm-dd'), 'Month'), 'J') as
> first_julian_date_of_month,
> to_char(last_day(to_date(:the_date, '-mm-dd')), 'DD') as
> num_days_in_month,
> to_char(trunc(to_date(:the_date, '-mm-dd'), 'Month'), 'D') as
> first_day_of_month,
> to_char(last_day(to_date(:the_date, '-mm-dd')), 'DD') as last_day,
> trunc(add_months(to_date(:the_date, '-mm-dd'), 1),'Day') as
> next_month,
> trunc(add_months(to_date(:the_date, '-mm-dd'), -1),'Day') as
> prev_month,
> trunc(to_date(:the_date, '-mm-dd'), 'year') as beginning_of_year,
> to_char(last_day(add_months(to_date(:the_date, '-mm-dd'), -1)),
> 'DD') as days_in_last_month,
> to_char(add_months(to_date(:the_date, '-mm-dd'), 1), 'fmMonth')
> as next_month_name,
> to_char(add_months(to_date(:the_date, '-mm-dd'), -1),
> 'fmMonth') as prev_month_name
> from dual
>
> the value assigned to the variable :the_date is '2010-01-05'
>
> The error is
>
> Error: Ns_PgExec: result status: 7 message: ERROR: function
> to_date(timestamp with time zone, unknown) does not exist
> LINE 1: select to_date(date_trunc('month',add_months( $1 ,1)),'-...
> ^
> HINT: No function matches the given name and argument types. You might
> need to add explicit type casts.
> QUERY: select to_date(date_trunc('month',add_months( $1
> ,1)),'-MM-DD') - 1
> CONTEXT: PL/pgSQL function "last_day" line 6 at SQL statement
>
>
> how would i apply the following solution
>
> date_trunc('month', p_date_in + interval '1 month')::date - 1
>
> to fix the query above?
>
> cheers,
> iuri
If I am following this right the problem is in the last_day function and in
particular the return value of the add_months function used in the
date_trunc(). To be sure we would need to see those functions. As a shot in the
dark:
select to_date(date_trunc('month',add_months( $1 ,1)::date)...
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] explicit casts
On Wednesday 05 January 2011 5:22:30 pm Iuri Sampaio wrote:
> So far,
> I could write the following query
>
> select to_char(trunc(to_date('2010-01-02', '-mm-dd'), 'Month'),
> 'fmMonth') as month,
> to_char(trunc(to_date('2010-01-02', '-mm-dd'), 'Month'), '') as
> year,
> to_char(trunc(to_date('2010-01-02', '-mm-dd'), 'Month'), 'J') as
> first_julian_date_of_month,
> to_char(last_day('2010-01-02')::date, 'DD') as num_days_in_month,
> to_char(trunc(to_date('2010-01-02', '-mm-dd'), 'Month'), 'D') as
> first_day_of_month,
> to_char(last_day('2010-01-02')::date, 'DD') as last_day,
> trunc(add_months(to_date('2010-01-02', '-mm-dd'), 1),'Day') as
> next_month,
> trunc(add_months(to_date('2010-01-02', '-mm-dd'), -1),'Day') as
> prev_month,
> trunc(to_date('2010-01-02', '-mm-dd'), 'year') as beginning_of_year,
> to_char(last_day(add_months('2010-01-02', -1))::date, 'DD') as
> days_in_last_month,
> to_char(add_months(to_date('2010-01-02', '-mm-dd'), 1), 'fmMonth')
> as next_month_name,
> to_char(add_months(to_date('2010-01-02', '-mm-dd'), -1), 'fmMonth')
> as prev_month_name
> from dual
>
>
> But i still miss some lines in order to properly explicit casts in the
> query
>
> cheers,
> iuri
My guess is you upgraded from a version prior to 8.3. In 8.3 many of the
implied
casts where removed, so you probably have to go over your code and make the
corrections.
Go here for more detail:
http://www.postgresql.org/docs/8.4/interactive/release-8-3.html
Section
E.21.2.1. General
A temporary solution can be found here:
http://petereisentraut.blogspot.com/2008/03/readding-implicit-casts-in-postgresql.html
An important tip from the blog-
"The gist was, only restore the casts you need, not all of them."
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
