Thread: Convert INT to INTERVAL?

Convert INT to INTERVAL?

From
Brian Knox
Date:
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?

Thanks,
Brian


Re: Convert INT to INTERVAL?

From
Stephan Szabo
Date:
On Fri, 27 Feb 2004, 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?

You should be able to say something likenum_months * INTERVAL '1 month'
I believe.


Re: Convert INT to INTERVAL?

From
Bruno Wolff III
Date:
On Fri, Feb 27, 2004 at 11:54:04 -0500, Brian Knox <laotse@aol.net> 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?

Can you use num_months * '1 month'::interval ?


Re: Convert INT to INTERVAL?

From
Michael Chaney
Date:
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
mdchaney@michaelchaney.com
http://www.michaelchaney.com/


Re: Convert INT to INTERVAL?

From
Tom Lane
Date:
Brian Knox <laotse@aol.net> writes:
> 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?

Sure, just multiply by the appropriate interval value:

regression=# select 48 * '1 month'::interval;?column?
----------4 years
(1 row)
        regards, tom lane


changing constraints

From
"David"
Date:
I tried adding a constraint thus:
de4=> ALTER TABLE genus ADD CHECK(gender = 'masculine' || 'feminine');
But get the msg:
ERROR:  AlterTableAddConstraint: rejected due to CHECK constraint $2

de4=> \d genus               Table "public.genus"    Column     |         Type          | Modifiers
----------------+-----------------------+-----------genus_name     | character varying(20) | not nullgender         |
charactervarying(10) |cas_gen_number | integer               |family_name    | character(7)          |
 
Indexes: genus_pkey primary key btree (genus_name)
Foreign Key constraints: $1 FOREIGN KEY (family_name) REFERENCES
family(family_name) ON UPDATE NO ACTION ON DELETE NO ACTION

I cant see a $2 constraint so why am i getting the error msg?

Many thanks Dave



Re: changing constraints

From
Paul Thomas
Date:
On 10/03/2004 12:27 David wrote:
> I tried adding a constraint thus:
> de4=> ALTER TABLE genus ADD CHECK(gender = 'masculine' || 'feminine');
> But get the msg:
> ERROR:  AlterTableAddConstraint: rejected due to CHECK constraint $2
> 
> de4=> \d genus
>                 Table "public.genus"
>      Column     |         Type          | Modifiers
> ----------------+-----------------------+-----------
>  genus_name     | character varying(20) | not null
>  gender         | character varying(10) |
>  cas_gen_number | integer               |
>  family_name    | character(7)          |
> Indexes: genus_pkey primary key btree (genus_name)
> Foreign Key constraints: $1 FOREIGN KEY (family_name) REFERENCES
> family(family_name) ON UPDATE NO ACTION ON DELETE NO ACTION
> 
> I cant see a $2 constraint so why am i getting the error msg?
> 
> Many thanks Dave


Just guessing but maybe it's because gender is nullable?

-- 
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for 
Business             |
| Computer Consultants         | 
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+


Re: changing constraints

From
Achilleus Mantzios
Date:
O kyrios David egrapse stis Mar 10, 2004 :

> I tried adding a constraint thus:
> de4=> ALTER TABLE genus ADD CHECK(gender = 'masculine' || 'feminine');
> But get the msg:
> ERROR:  AlterTableAddConstraint: rejected due to CHECK constraint $2

Which pgsql version are you using??

In any case what you wrote means CHECK (gender='masculinefeminine')
you should write it as gender = 'masculine' OR gender = 'feminine'.

> 
> de4=> \d genus
>                 Table "public.genus"
>      Column     |         Type          | Modifiers
> ----------------+-----------------------+-----------
>  genus_name     | character varying(20) | not null
>  gender         | character varying(10) |
>  cas_gen_number | integer               |
>  family_name    | character(7)          |
> Indexes: genus_pkey primary key btree (genus_name)
> Foreign Key constraints: $1 FOREIGN KEY (family_name) REFERENCES
> family(family_name) ON UPDATE NO ACTION ON DELETE NO ACTION
> 
> I cant see a $2 constraint so why am i getting the error msg?
> 
> Many thanks Dave
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
> 

-- 
-Achilleus



Re: changing constraints

From
Richard Huxton
Date:
On Wednesday 10 March 2004 12:27, David wrote:
> I tried adding a constraint thus:
> de4=> ALTER TABLE genus ADD CHECK(gender = 'masculine' || 'feminine');
> But get the msg:
> ERROR:  AlterTableAddConstraint: rejected due to CHECK constraint $2

> I cant see a $2 constraint so why am i getting the error msg?

Firstly, $2 will be the automatically created name for this new constraint.
Secondly || doesn't mean OR - it joins strings.

Try something like (untested): ALTER TABLE genus ADD CONSTRAINT valid_gender CHECK (gender IN 
('masculine','feminine'));


--  Richard Huxton Archonet Ltd


Re: changing constraints

From
"David"
Date:
Cheers that worked fine, i guess its obvious im new to postgres (SQL in
general!), oh well you have to learn somehow

Dave

>
> Try something like (untested):
>   ALTER TABLE genus ADD CONSTRAINT valid_gender CHECK (gender IN
> ('masculine','feminine'));
>
>
> -- 
>   Richard Huxton
>   Archonet Ltd
>



Alter table

From
"David"
Date:
Ok another very newbie question. How can i change the data type a column can
accept? at the moment it will only take character(7) i want to change it to
varchar(30), but i cant figure how, ideas?

Many thanks Dave



Re: Alter table

From
"scott.marlowe"
Date:
On Wed, 10 Mar 2004, David wrote:

> Ok another very newbie question. How can i change the data type a column can
> accept? at the moment it will only take character(7) i want to change it to
> varchar(30), but i cant figure how, ideas?

While there are ways to tinker with the system catalogs to change between 
different text types / lengths, none of these are "officially supported" 
and may well screw up your database if you do something wrong.  I believe 
the archives likely have this question over and over in them.

The proper way to do this is to make a new column, put the old column in 
it, and then drop the old column:

create table test (a char(7));
insert a few thousand lines to test...;
begin;
alter table test add column b varchar(30);
update test set b=a;
alter table test drop column a;
commit; (or rollback; should things go horribly wrong...)
vacuum full test;