I am new to Postgresql and am enjoying it so far
I have a table that I imported into postgresql from MS Access
One of the fields is a time stamp that was imported in as a text field which was incorrect. I got around this by using the code
ALTER TABLE public."Table_1"
ALTER COLUMN Col_1 TYPE timestamp without time zone USING (trim(Col_1)::timestamp without time zone);
This had the desired effect and all the text was successfully changed to a time stamp for that column
The next point that I wanted to tackle was to create/select a column which would just contain the hour from the time stamp.
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
I believe you're running into a case folding issue. Having upper/mixed case object names in postgresql isn't the best idea, as AL1 does not equal al1, and unless quoted, postgresql folds object names to lower case...
As a work around, try wrapping AL1 with quotes.
SELECT EXTRACT(HOUR FROM TIMESTAMP "AL1"."Col_1")
FROM public."Table_1" "AL1";
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert
I saw the following descriptions in "PostgreSQL 8.2.0 Documentation".
So, I thought that a word TIMESTAMP in your code might be for timestamp literal input and it might be unnecessary for timestamp column.
8.5.1. Date/Time Input
Remember that any date or time literal input needs to be enclosed in single quotes, like text strings. Refer
to Section 184.108.40.206 for more information. SQL requires the following syntax
type [ (p) ] value
where p in the optional precision specification is an integer corresponding to the number of fractional
digits in the seconds field. Precision can be specified for time, timestamp, and interval types. The
allowed values are mentioned above. If no precision is specified in a constant specification, it defaults to
the precision of the literal value.