Thread: Convert INT to INTERVAL?
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
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.
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 ?
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/
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
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
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 | +------------------------------+---------------------------------------------+
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
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
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
>
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
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;