From: Josh Berkus <josh@agliodbs.com>
To: Clint Stotesbery <cstotes@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: assorted Postgres SQL/ORDBMS questions
Date: Mon, 20 Oct 2003 19:47:45 -0700
Message-ID: <200310201947.45530.josh@agliodbs.com> (raw)
In-Reply-To: <BAY9-F15IHRyeH4bgct0002545f@hotmail.com>
References: <BAY9-F15IHRyeH4bgct0002545f@hotmail.com>
Clint,
> 1. You can raise exceptions but you can't catch exceptions in pgsql right?
right. We'd like to do exception-trapping, but nobody has offered to program
it.
> 2. Does Postgres support ORDBMS operations?
> Specifically I am wondering about the ability to define your own objects
> and create functions/procedures for the objects (e.g. object.method()). In
> Oracle I would use CREATE TYPE and CREATE TYPE BODY. Postgres' create type
> seems quite different than Oracle's version and they don't seem equivalent
> to each other. The Postgres version seems like it is for creating your own
> datatypes but not your own objets. I couldn't find any docs on this except
> on the SQL commands page.
Not with that syntax, no. Our ORDBMS functionality is more aimed at creating
your own datatypes, domains, operators, aggregates, and similar.
> 3. Does it support nested tables?
> Again I couldn't find any info in the docs for this.
No. Nor will it if I have any clout on the Hackers list; Nested tables are a
Bad Idea and they Violate The Relational Standard. Arrays are as far as I am
willing to go, and only in special cases.
> 4. Can dates only be storied in YYYY-MM-DD format?
Dates are stored in an internal format in order to ensure compliance with the
SQL date standard. The DATE type is stored as an integer; the TIMESTAMP is
(I believe) binary. Depending on your locale, the default *representation*
of dates may be yyyy-mm-dd, or something else.
> I've looked over the documentation at
> http://www.postgresql.org/docs/7.3/static/functions-formatting.html and it
> seems that doing to_date(t_date,''DD-MON-YYYY'') should return 20-OCT-2003
> but it returns 2003-10-20 no matter what I do.
You want to re-format the date; see the docs on to_char().
> This works fine but it is a varchar. I really want it to be stored like
> that but in a date type instead.
No, you don't need it to be stored that way.
If you want dates to display a particular way, use to_char() when you query
them. Dates are stored as dates, not as strings.
BTW, MS SQL Server's implementation of DATETIME sucks rocks and violates the
SQL standard besides. So don't go comparing them on me.
--
Josh Berkus
Aglio Database Solutions
San Francisco
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, cstotes@hotmail.com
Subject: Re: assorted Postgres SQL/ORDBMS questions
In-Reply-To: <200310201947.45530.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox