I'm querying an external API and want to test out data inserts on a test server before moving to production.
Currently, I'm trying to execute the following statement in Postgres's shell:
CREATE TABLE referrals (client_id VARCHAR(36), device_id VARCHAR(100), conversion_time DATETIME, acquisition_cost MONEY);
but it keeps failing on DATETIME
.
Since this doesn't work, would timestamp? If so, how do I modify timestamp's default behavior to take in preformatted dates and times?
CREATE TABLE referrals (
client_id text
, device_id text
, conversion_time timestamp
, acquisition_cost money
);
You don't have to add without time zone
, that is the default.
I use the type text
in the example instead of varchar
with a length modifier, because that is usually the better choice. You can use varchar(n)
of course, no problem.
locale and DateStyle
settings influence how text input for date
/ timestamp
values is interpreted. Use the functions to_date()
or to_timestamp()
to be largely independent of local settings:
SELECT to_timestamp('05 Dec 2000', 'DD Mon YYYY');
More in the chapter Data Type Formatting Functions of the fine manual.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With