Re: Convert date and time colums to datetime
On Sun, 19 Oct 2025, Rob Sargent wrote: I think you have to ask why those values were separated in the first place. For instance if they are thought of as a pair in most queries then an alteration might be in order. There can be a large one time cost if these tables occur in a lot of separate sql calls in the business logic. Rob, Good point. They're in the contacts table and I use them to determine when to make another contact and if prior contacts were more productive in the morning or afternoon. Thanks, Rich
Re: Arrays vs separate tables
On 19/10/2025 21:43, Rich Shepard wrote: In the former book I read that sometimes it's better to have multiple values for an atribute in a single row by creating a separate table for that attribute rather than using the postgres array capability. The people table in my database (1706 rows) has two attributes using the array type (direct_phone and email), and only a few indivuals have multiple landline phone numbers (cell_phone is a separate column) or email addresses (office and personal?). Would it make sense for me to create new landline and email address tables and replace the array contents? My experience of doing something similar was that arrays work very well for the use-case you describe, as long as you don't have to search inside the arrays... I found that, if you have to search for a specific value inside an array, then performance really goes out the window. Mind you, clever use of an index would probably help here. Ray. -- Ray O'Donnell // Galway // Ireland [email protected]
Arrays vs separate tables
I started developing business and science databases in the mid-1980s using
dBASE III, then Paradox on DOS. I defenestrated in 1997 and have used only
linux for both business and personal needs ever since and using only
PostgreSQL for my databaes (primarily for my own use.) I read Joe Celko's
monthly column in 'Database Advisor' and all his SQL books (in multiple
editions.)
Now I'm slowly cleaning up my business tracking database using features not
available way back when I developed it. That's why I ask questions that must
seem obvious to all of you who work with postgres everyday and have for
years. I'm reading 'SQL Queries for Mere Mortals' and just ordered the 4th
edition of 'Database Design for Mere Mortals' to update my SQL knowledge.
In the former book I read that sometimes it's better to have multiple values
for an atribute in a single row by creating a separate table for that
attribute rather than using the postgres array capability. The people table
in my database (1706 rows) has two attributes using the array type
(direct_phone and email), and only a few indivuals have multiple landline
phone numbers (cell_phone is a separate column) or email addresses (office
and personal?). Would it make sense for me to create new landline and email
address tables and replace the array contents?
Other than remembering to use curly braces {} when entering data into an
array column I don't think there'd be any performance benefit for making the
change. I'm curious to learn about arrays vs separate tables.
TIA,
Rich
Re: Arrays vs separate tables
On 10/19/25 13:43, Rich Shepard wrote:
In the former book I read that sometimes it's better to have multiple
values
for an atribute in a single row by creating a separate table for that
attribute rather than using the postgres array capability. The people table
in my database (1706 rows) has two attributes using the array type
(direct_phone and email), and only a few indivuals have multiple landline
phone numbers (cell_phone is a separate column) or email addresses (office
and personal?). Would it make sense for me to create new landline and email
address tables and replace the array contents?
For direct_phone and email entries that have more then one value, how do
you know what the values are pointing at e.g home vs office vs second
office location, etc?
Other than remembering to use curly braces {} when entering data into an
array column I don't think there'd be any performance benefit for making
the
change. I'm curious to learn about arrays vs separate tables.
TIA,
Rich
--
Adrian Klaver
[email protected]
Re: Arrays vs separate tables
On Sun, 19 Oct 2025, Adrian Klaver wrote: For direct_phone and email entries that have more then one value, how do you know what the values are pointing at e.g home vs office vs second office location, etc? Adrian, At this point I don't know. If there's no answer on one number I try another. If there were separate tables the three colums would be in interger PK, a description, and the number/address. Rich
Re: Arrays vs separate tables
On Sun, 19 Oct 2025, Ray O'Donnell wrote: My experience of doing something similar was that arrays work very well for the use-case you describe, as long as you don't have to search inside the arrays... I found that, if you have to search for a specific value inside an array, then performance really goes out the window. Mind you, clever use of an index would probably help here. Ray, So far searching has not been an issue so I'll keep the status quo. Thanks, Rich
Convert date and time colums to datetime
The database has a table with separate date and time columns. 1. Are there benefits to merging the two into a single timestamp column? 2. If so, how would I do this? (Reading date/time operators and functions doc page hasn't shown me one.) TIA, Rich
Re: Convert date and time colums to datetime
On 10/19/25 07:43, Rich Shepard wrote:
The database has a table with separate date and time columns.
1. Are there benefits to merging the two into a single timestamp column?
1) One less column to fetch from.
2) If you really need a timestamp the work is already done, instead of
building on the fly.
2. If so, how would I do this? (Reading date/time operators and functions
doc page hasn't shown me one.)
select ('10/19/2025'::date + '07:50'::time)::timestamptz;
timestamptz
2025-10-19 07:50:00-07
TIA,
Rich
--
Adrian Klaver
[email protected]
Re: Convert date and time colums to datetime
On 10/19/25 07:53, Adrian Klaver wrote:
On 10/19/25 07:43, Rich Shepard wrote:
The database has a table with separate date and time columns.
1. Are there benefits to merging the two into a single timestamp column?
1) One less column to fetch from.
2) If you really need a timestamp the work is already done, instead of
building on the fly.
2. If so, how would I do this? (Reading date/time operators and functions
doc page hasn't shown me one.)
select ('10/19/2025'::date + '07:50'::time)::timestamptz;
timestamptz
2025-10-19 07:50:00-07
Should have added in previous post, the above assumes your current date
and time values are from the server timezone.
TIA,
Rich
--
Adrian Klaver
[email protected]
Re: Convert date and time colums to datetime
On Sun, 19 Oct 2025, Isaac Morland wrote: If you're talking about actually changing the table, replacing the two columns with a single column, you would need ALTER TABLE. Something like (not tested, just to give you the basic idea): Issac, I thought it could be that simple, while I was looking in the date/time conditions/functions. Thanks, Rich
Re: Convert date and time colums to datetime
On Sun, 2025-10-19 at 07:43 -0700, Rich Shepard wrote: > The database has a table with separate date and time columns. > > 1. Are there benefits to merging the two into a single timestamp column? That depends on what you do with the table. Are your SQL statements simple and natural with the current design? Then stick with what you have now. Do you need date arithmetic that is awkward with the current design? Then a timestamp column is better. If you design the table from scratch, a timestamp column is almost always the right thing. But if the current design works good enough, you may save yourself the pain of restructuring the table. For convenience with queries, you could use a view or virtual generated column, as shown below. An example for an awkward statement that would strongly indicate that a timestamp is a better choice: UPDATE tab SET datecol = CAST ((datecol + timecol) + INTERVAL '12 hours' AS date), timecol = CAST ((datecol + timecol) + INTERVAL '12 hours' AS time) WHERE id = 42; > 2. If so, how would I do this? (Reading date/time operators and functions > doc page hasn't shown me one.) - If you want to modify the table: ALTER tab ADD timestampcol timestamp; /* will take a long time if the table is big */ UPDATE tab SET timestampcol = datecol + timecol; /* will take a long time if the table is big */ ALTER TABLE tab ALTER timestampcol SET NOT NULL; ALTER TABLE tab DROP datecol, DROP timecol; /* to get rid of the bloat from the UPDATE */ VACUUM (FULL) tab; - If you just want to use the timestamp in queries, use a view: CREATE VIEW v AS SELECT *, datecol + timecol AS timestampcol FROM tab; - As an alternative to the previous, you can create a virtual generated column in the base table from v18 on: ALTER TABLE tab ADD timestampcol timestamp GENERATED ALWAYS AS (datecol + timecol); Yours, Laurenz Albe
Re: Convert date and time colums to datetime
> On Oct 19, 2025, at 1:08 PM, Rich Shepard wrote: > > On Sun, 19 Oct 2025, Laurenz Albe wrote: > >> That depends on what you do with the table. > > Laurenz, > > That makes sense. > >> Are your SQL statements simple and natural with the current design? >> Then stick with what you have now. > > That's what I'm going to do. I was curious when a timestamp column was more > efficient, or otherwise preferred, since only a couple of my databases have > a table with both date and time. And neither has many rows, but one could be > quite large some time in the future. > > Thanks very much, > > Rich > I think you have to ask why those values were separated in the first place. For instance if they are thought of as a pair in most queries then an alteration might be in order. There can be a large one time cost if these tables occur in a lot of separate sql calls in the business logic. >
Re: Convert date and time colums to datetime
On Sun, 19 Oct 2025, Adrian Klaver wrote:
2) If you really need a timestamp the work is already done, instead of
building on the fly.
Adrian,
As each row in the table already has both a date column and a time column I
don't know if I 'really' need a timestamp. When would a timestamp be really
needed?
select ('10/19/2025'::date + '07:50'::time)::timestamptz;
Yes, I saw that on the doc page. This requires manually changing each row in
the table rather than using a date/time condition/function to to create the
single timestamp column. So, apparently there's not a way to modify the
table other than by hand.
Thanks,
Rich
Re: Convert date and time colums to datetime
On Sun, 19 Oct 2025 at 12:35, Rich Shepard wrote:
> On Sun, 19 Oct 2025, Adrian Klaver wrote:
>
> > 2) If you really need a timestamp the work is already done, instead of
> > building on the fly.
>
> Adrian,
>
> As each row in the table already has both a date column and a time column I
> don't know if I 'really' need a timestamp. When would a timestamp be really
> needed?
>
> > select ('10/19/2025'::date + '07:50'::time)::timestamptz;
>
> Yes, I saw that on the doc page. This requires manually changing each row
> in
> the table rather than using a date/time condition/function to to create the
> single timestamp column. So, apparently there's not a way to modify the
> table other than by hand.
If you're talking about actually changing the table, replacing the two
columns with a single column, you would need ALTER TABLE. Something like
(not tested, just to give you the basic idea):
ALTER TABLE [table]
ADD [new_column] timestamp;
UPDATE [table]
SET [new_column] = [date_column] + [time_column];
ALTER TABLE [table]
DROP date_column,
DROP time_column;
The answer already given essentially tells you what to put in the UPDATE
statement, which is an important element. The following page may help with
details:
https://www.postgresql.org/docs/current/sql-altertable.html
Of course, all queries that touch the table need to be updated. There are
some new features that might help with migration; for example, if you made
a new timestamp column that is a generated column, you could have both
co-existing in the table at the same time while you update the users of the
table to use the new column. You also might be able to do something with
defaults to allow the column adding to also populate the new column
appropriately, which would allow you to just do a single ALTER TABLE and no
UPDATE.
I personally would almost always combine date+time into a single timestamp.
It's easier for computations, and whenever you need just one all you have
to do is cast to date or time as appropriate.
Re: Convert date and time colums to datetime
On 10/19/25 09:35, Rich Shepard wrote:
On Sun, 19 Oct 2025, Adrian Klaver wrote:
2) If you really need a timestamp the work is already done, instead of
building on the fly.
Adrian,
As each row in the table already has both a date column and a time column I
don't know if I 'really' need a timestamp. When would a timestamp be really
needed?
I would say the fact you are asking is an indication you might need it.
select ('10/19/2025'::date + '07:50'::time)::timestamptz;
Yes, I saw that on the doc page. This requires manually changing each
row in
the table rather than using a date/time condition/function to to create the
single timestamp column. So, apparently there's not a way to modify the
table other than by hand.
1) Again you seem to be establishing a need for combined value.
2) Not sure how you do "...date/time condition/function to to create
the single timestamp column" without creating a column?
If you meant creating a timestamp value then I point you at my original
post and:
"2) If you really need a timestamp the work is already done, instead of
building on the fly."
Doing the one time change to add a timestamp field to the table and then
do the UPDATE of date + time to populate it versus calculating it every
time you need the value. Of course moving forward you would need to
change the code that INSERTs new values to use a timestamp instead of a
date and a time value.
Thanks,
Rich
--
Adrian Klaver
[email protected]
Re: Convert date and time colums to datetime
On Sun, 19 Oct 2025, Laurenz Albe wrote: That depends on what you do with the table. Laurenz, That makes sense. Are your SQL statements simple and natural with the current design? Then stick with what you have now. That's what I'm going to do. I was curious when a timestamp column was more efficient, or otherwise preferred, since only a couple of my databases have a table with both date and time. And neither has many rows, but one could be quite large some time in the future. Thanks very much, Rich
Re: Convert date and time colums to datetime
> On Oct 19, 2025, at 2:38 PM, Rich Shepard wrote: > > On Sun, 19 Oct 2025, Rob Sargent wrote: > >> I think you have to ask why those values were separated in the first >> place. For instance if they are thought of as a pair in most queries then >> an alteration might be in order. There can be a large one time cost if >> these tables occur in a lot of separate sql calls in the business logic. > > Rob, > > Good point. They're in the contacts table and I use them to determine when > to make another contact and if prior contacts were more productive in the > morning or afternoon. > > Thanks, > > Rich > > Definitely a datetime (single value) problem, imho
