How do I alter the date format in Postgres?

by 
SHOW datestyle;

 DateStyle 
-----------
 ISO, MDY
(1 row)

INSERT INTO container VALUES ('13/01/2010');
ERROR:  date/time field value out of range: "13/01/2010"
HINT:  Perhaps you need a different "datestyle" setting.

SET datestyle = "ISO, DMY";
SET

INSERT INTO container VALUES ('13/01/2010');
INSERT 0 1

SET datestyle = default;
SET

http://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-DATESTYLE

DateStyle – Sets the display format
for date and time values, as well as
the rules for interpreting ambiguous
date input values.
For historical reasons, this variable
contains two independent components:
the output format specification (ISO,
Postgres, SQL, or German) and the
input/output specification for
year/month/day ordering (DMY, MDY, or
YMD).

Of course it’s best to use unambiguous input format (ISO 8601), but there is no problem to adjust it as you need.

More Related Contents:

  1. What to do with null values when modeling and normalizing?
  2. Postgres: INSERT if does not exist already
  3. postgres default timezone
  4. How to change PostgreSQL user password?
  5. How to get the count of current month Sunday’s in psql?
  6. Restore a postgres backup file using the command line?
  7. How to close idle connections in PostgreSQL automatically?
  8. Refresh a materialized view automatically using a rule or notify
  9. postgresql – replace all instances of a string within text field
  10. Is it possible to have autoincrement number without it being an ID?
  11. PostgreSQL: Modify OWNER on all tables simultaneously in PostgreSQL
  12. pgadmin4 : postgresql application server could not be contacted.
  13. PostgreSQL error ‘Could not connect to server: No such file or directory’
  14. Insert if not exists, else return id in postgresql
  15. function returns multiple columns as a single column instead of multiple columns
  16. Sequences not affected by transactions?
  17. Postgres: check if array field contains value?
  18. Dynamic pivot query using PostgreSQL 9.3
  19. PostgreSQL ERROR: canceling statement due to conflict with recovery
  20. Change type of varchar field to integer: “cannot be cast automatically to type integer”
  21. eliminate duplicate array values in postgres
  22. Find Parent Recursively using Query
  23. How to work with PGpoint for Geolocation using PostgreSQL?
  24. Heroku database restore issue
  25. user postgres launches process that takes all CPUs 100% usage
  26. PostgreSQL disable more output
  27. PostgreSQL array_agg order
  28. Postgres table column name restrictions?
  29. How do I get min, median and max from my query in postgresql?
  30. Syntax error at end of input in PostgreSQL

Leave a Comment