Hi Y'all. (English REALLY needs a plural "you"!)

I have a situation where a commercial software package goes bananas with datetime data types.  But it is happy to work with a time stamp if it is character data type.

I have been tasked with adding a few columns to a table. I am trying to set some defaults on the columns. It uses separate date and time columns so the date partis easy: creation_time date default today,

Now the time stamp part, most easily expressed as datetime hour to second, is the problem: I need it inserted as a character string.  Of course the following will not work but it is effectively what I'm trying to do:

create_time  char(8)  default current hour to second,

The default value is not compatible with the data type.  So I tried to cast it to character:

create_time  char(8)  default (current hour to second)::char(8),
#                             ^
#  201: A syntax error has occurred.

A not-unreasonable complaint.  BTW, if I take off the parentheses the syntax error moved to the :: of the cast operator.

I may also need to do something similar in a trigger, inserting a datetime hour to second value as a char(8) in another column.  The documentation on casting does not seem to cover this type of conversion.

I would LOVE some ideas here!

Thanks much!

Jacob:

I would just bite the bullet and go with an INSERT TRIGGER on that column:

CREATE TABLE "art".for_jake (
        one SERIAL(3) NOT NULL,
        two CHAR(8)
);

CREATE TRIGGER "art".time_for_jake 
  INSERT ON "art".for_jake 
    REFERENCING NEW AS neu 
    FOR EACH ROW 
  WHEN ((neu.two IS NULL ) ) 
  ( 
  EXECUTE function "art".get_time_str() INTO "art".for_jake.two); 

Hi Y'all. (English REALLY needs a plural "you"!)

I have a situation where a commercial software package goes bananas with datetime data types.  But it is happy to work with a time stamp if it is character data type.

I have been tasked with adding a few columns to a table. I am trying to set some defaults on the columns. It uses separate date and time columns so the date partis easy: creation_time date default today,

Now the time stamp part, most easily expressed as datetime hour to second, is the problem: I need it inserted as a character string.  Of course the following will not work but it is effectively what I'm trying to do:

create_time  char(8)  default current hour to second,

The default value is not compatible with the data type.  So I tried to cast it to character:

create_time  char(8)  default (current hour to second)::char(8),
#                             ^
#  201: A syntax error has occurred.

A not-unreasonable complaint.  BTW, if I take off the parentheses the syntax error moved to the :: of the cast operator.

I may also need to do something similar in a trigger, inserting a datetime hour to second value as a char(8) in another column.  The documentation on casting does not seem to cover this type of conversion.

I would LOVE some ideas here!

Thanks much!

Thanks much, Art.

I did make a couple of changes from your prescription:  (If I hadn't, you would wonder if it was really me. :-)

1. WHEN ((neu.two IS NULL ) ) 
   Why the double parentheses? I took off one pair and it still created the trigger just fine.  Haven't had the chance to test it out yet, however.
2. You created the procedure but executed function. While experiencing some errors trying to emulate your code I changed to to execute procedure.  Since it compiled fine I'll leave it as is.  I do note that in your demo code it worked as you coded it.

Question: Had you created it as a function, would I have been able to say let neu.creat_time = get_time_str()?

I must also credit Paul Watson for his article on certain errors that my imperfect emulation was generating. He pointed out exactly how I was deviating to cause error:
732: Incorrect use of old or new values correlation name inside trigger.

When I get up to the update trigger I'll likely have more to ask. But I'll jump off that bridge when I come to it. (Overconfident fellow, ain't he? ;-)

Jacob:

I would just bite the bullet and go with an INSERT TRIGGER on that column:

CREATE TABLE "art".for_jake (
        one SERIAL(3) NOT NULL,
        two CHAR(8)
);

CREATE TRIGGER "art".time_for_jake 
  INSERT ON "art".for_jake 
    REFERENCING NEW AS neu 
    FOR EACH ROW 
  WHEN ((neu.two IS NULL ) ) 
  ( 
  EXECUTE function "art".get_time_str() INTO "art".for_jake.two); 

Hi Y'all. (English REALLY needs a plural "you"!)

I have a situation where a commercial software package goes bananas with datetime data types.  But it is happy to work with a time stamp if it is character data type.

I have been tasked with adding a few columns to a table. I am trying to set some defaults on the columns. It uses separate date and time columns so the date partis easy: creation_time date default today,

Now the time stamp part, most easily expressed as datetime hour to second, is the problem: I need it inserted as a character string.  Of course the following will not work but it is effectively what I'm trying to do:

create_time  char(8)  default current hour to second,

The default value is not compatible with the data type.  So I tried to cast it to character:

create_time  char(8)  default (current hour to second)::char(8),
#                             ^
#  201: A syntax error has occurred.

A not-unreasonable complaint.  BTW, if I take off the parentheses the syntax error moved to the :: of the cast operator.

I may also need to do something similar in a trigger, inserting a datetime hour to second value as a char(8) in another column.  The documentation on casting does not seem to cover this type of conversion.

I would LOVE some ideas here!

Thanks much!

Hi Y'all. (English REALLY needs a plural "you"!)

I have a situation where a commercial software package goes bananas with datetime data types.  But it is happy to work with a time stamp if it is character data type.

I have been tasked with adding a few columns to a table. I am trying to set some defaults on the columns. It uses separate date and time columns so the date partis easy: creation_time date default today,

Now the time stamp part, most easily expressed as datetime hour to second, is the problem: I need it inserted as a character string.  Of course the following will not work but it is effectively what I'm trying to do:

create_time  char(8)  default current hour to second,

The default value is not compatible with the data type.  So I tried to cast it to character:

create_time  char(8)  default (current hour to second)::char(8),
#                             ^
#  201: A syntax error has occurred.

A not-unreasonable complaint.  BTW, if I take off the parentheses the syntax error moved to the :: of the cast operator.

I may also need to do something similar in a trigger, inserting a datetime hour to second value as a char(8) in another column.  The documentation on casting does not seem to cover this type of conversion.

I would LOVE some ideas here!

Thanks much!

Hello Jacob,

What about creating the SQL table with a real DATETIME HOUR TO SECOND column, and create a view on top of it, casting the column to CHAR(8) ?

create table tab1 (
   pkey int not null primary key,
   tm datetime hour to second default current hour to second
 );
insert into tab1 (pkey) values ( 101 );
insert into tab1 (pkey) values ( 102 );
insert into tab1 (pkey) values ( 103 );

create view view1 (pkey,tm) as select pkey, cast(tm as char(8)) from tab1;
select * from view1;

drop view view1;
drop table tab1;

Seb

Hi Y'all. (English REALLY needs a plural "you"!)

I have a situation where a commercial software package goes bananas with datetime data types.  But it is happy to work with a time stamp if it is character data type.

I have been tasked with adding a few columns to a table. I am trying to set some defaults on the columns. It uses separate date and time columns so the date partis easy: creation_time date default today,

Now the time stamp part, most easily expressed as datetime hour to second, is the problem: I need it inserted as a character string.  Of course the following will not work but it is effectively what I'm trying to do:

create_time  char(8)  default current hour to second,

The default value is not compatible with the data type.  So I tried to cast it to character:

create_time  char(8)  default (current hour to second)::char(8),
#                             ^
#  201: A syntax error has occurred.

A not-unreasonable complaint.  BTW, if I take off the parentheses the syntax error moved to the :: of the cast operator.

I may also need to do something similar in a trigger, inserting a datetime hour to second value as a char(8) in another column.  The documentation on casting does not seem to cover this type of conversion.

I would LOVE some ideas here!

Thanks much!

Hello Jacob,

What about creating the SQL table with a real DATETIME HOUR TO SECOND column, and create a view on top of it, casting the column to CHAR(8) ?

create table tab1 (
   pkey int not null primary key,
   tm datetime hour to second default current hour to second
 );
insert into tab1 (pkey) values ( 101 );
insert into tab1 (pkey) values ( 102 );
insert into tab1 (pkey) values ( 103 );

create view view1 (pkey,tm) as select pkey, cast(tm as char(8)) from tab1;
select * from view1;

drop view view1;
drop table tab1;

Seb

Hi Y'all. (English REALLY needs a plural "you"!)

I have a situation where a commercial software package goes bananas with datetime data types.  But it is happy to work with a time stamp if it is character data type.

I have been tasked with adding a few columns to a table. I am trying to set some defaults on the columns. It uses separate date and time columns so the date partis easy: creation_time date default today,

Now the time stamp part, most easily expressed as datetime hour to second, is the problem: I need it inserted as a character string.  Of course the following will not work but it is effectively what I'm trying to do:

create_time  char(8)  default current hour to second,

The default value is not compatible with the data type.  So I tried to cast it to character:

create_time  char(8)  default (current hour to second)::char(8),
#                             ^
#  201: A syntax error has occurred.

A not-unreasonable complaint.  BTW, if I take off the parentheses the syntax error moved to the :: of the cast operator.

I may also need to do something similar in a trigger, inserting a datetime hour to second value as a char(8) in another column.  The documentation on casting does not seem to cover this type of conversion.

I would LOVE some ideas here!

Thanks much!

Hello Jacob,

What about creating the SQL table with a real DATETIME HOUR TO SECOND column, and create a view on top of it, casting the column to CHAR(8) ?

create table tab1 (
   pkey int not null primary key,
   tm datetime hour to second default current hour to second
 );
insert into tab1 (pkey) values ( 101 );
insert into tab1 (pkey) values ( 102 );
insert into tab1 (pkey) values ( 103 );

create view view1 (pkey,tm) as select pkey, cast(tm as char(8)) from tab1;
select * from view1;

drop view view1;
drop table tab1;

Seb

Hi Y'all. (English REALLY needs a plural "you"!)

I have a situation where a commercial software package goes bananas with datetime data types.  But it is happy to work with a time stamp if it is character data type.

I have been tasked with adding a few columns to a table. I am trying to set some defaults on the columns. It uses separate date and time columns so the date partis easy: creation_time date default today,

Now the time stamp part, most easily expressed as datetime hour to second, is the problem: I need it inserted as a character string.  Of course the following will not work but it is effectively what I'm trying to do:

create_time  char(8)  default current hour to second,

The default value is not compatible with the data type.  So I tried to cast it to character:

create_time  char(8)  default (current hour to second)::char(8),
#                             ^
#  201: A syntax error has occurred.

A not-unreasonable complaint.  BTW, if I take off the parentheses the syntax error moved to the :: of the cast operator.

I may also need to do something similar in a trigger, inserting a datetime hour to second value as a char(8) in another column.  The documentation on casting does not seem to cover this type of conversion.

I would LOVE some ideas here!

Thanks much!

Jacob:

I like the VIEW idea. I like it a lot! As for the INSERTS from that software, you can use an INSTEAD OF trigger to map the char input to a DATETIME HOUR TO SECOND in the underlying table!

Art

Hello Jacob,

What about creating the SQL table with a real DATETIME HOUR TO SECOND column, and create a view on top of it, casting the column to CHAR(8) ?

create table tab1 (
   pkey int not null primary key,
   tm datetime hour to second default current hour to second
 );
insert into tab1 (pkey) values ( 101 );
insert into tab1 (pkey) values ( 102 );
insert into tab1 (pkey) values ( 103 );

create view view1 (pkey,tm) as select pkey, cast(tm as char(8)) from tab1;
select * from view1;

drop view view1;
drop table tab1;

Seb

Hi Y'all. (English REALLY needs a plural "you"!)

I have a situation where a commercial software package goes bananas with datetime data types.  But it is happy to work with a time stamp if it is character data type.

I have been tasked with adding a few columns to a table. I am trying to set some defaults on the columns. It uses separate date and time columns so the date partis easy: creation_time date default today,

Now the time stamp part, most easily expressed as datetime hour to second, is the problem: I need it inserted as a character string.  Of course the following will not work but it is effectively what I'm trying to do:

create_time  char(8)  default current hour to second,

The default value is not compatible with the data type.  So I tried to cast it to character:

create_time  char(8)  default (current hour to second)::char(8),
#                             ^
#  201: A syntax error has occurred.

A not-unreasonable complaint.  BTW, if I take off the parentheses the syntax error moved to the :: of the cast operator.

I may also need to do something similar in a trigger, inserting a datetime hour to second value as a char(8) in another column.  The documentation on casting does not seem to cover this type of conversion.

I would LOVE some ideas here!

Thanks much!