Re: [HACKERS] money with 4 digits after dot
I found a workaround: domain type defined as: CREATE DOMAIN currency AS numeric(16,4); Thank you!
[HACKERS] money with 4 digits after dot
I'm trying to insert data into Postgres using COPY command. The data originates from AdventureWorksDW. However, it fails with: "ERROR: invalid input syntax for type money: "2171.2942" " Is it possible to import money into Postgres with 4 digits after the dot?
Re: [HACKERS] question about meaning of character varying without length
Here you go: fromMail Delivery Subsystem topgf...@gmail.com dateMon, Jun 15, 2009 at 9:16 PMsubjectDelivery Status Notification (Failure) hide details 9:16 PM (31 minutes ago) Reply [image: Follow up message] This is an automatically generated Delivery Status Notification Delivery to the following recipient failed permanently: pgsql-gene...@postgresql.com Technical details of permanent failure: Google tried to deliver your message, but it was rejected by the recipient domain. We recommend contacting the other email provider for further information about the cause of this error. The error that the other server returned was: 554 554 5.7.1 : Relay access denied (state 14). - Original message - MIME-Version: 1.0 Received: by 10.142.237.19 with SMTP id k19mr3677653wfh.31.1245125814649; Mon, 15 Jun 2009 21:16:54 -0700 (PDT) Date: Mon, 15 Jun 2009 21:16:54 -0700 Message-ID: <72746b5e0906152116t13e67918l3eb58b5c304fd...@mail.gmail.com> Subject: question about meaning of character varying without length From: Konstantin Izmailov To: pgsql-gene...@postgresql.com Content-Type: multipart/alternative; boundary=000e0cd24232e205f2046c6f7190 --000e0cd24232e205f2046c6f7190 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Hello, I've found following description: "If character varying is used without length specifier, the type accepts strings of any size. The latter is a PostgreSQL extension." Does this mean that "character varying without length" is equivalent to - Message truncated - On Mon, Jun 15, 2009 at 9:47 PM, Robert Haas wrote: > On Tue, Jun 16, 2009 at 12:45 AM, Konstantin Izmailov > wrote: > > I have tried to send to pgsql-general twice, each time it returns error: > > "Relay access denied (state 14)." Will try to post to pgsql-odbc. > > Could you provide the full bounce message? > > ...Robert >
Re: [HACKERS] question about meaning of character varying without length
I have tried to send to pgsql-general twice, each time it returns error: "Relay access denied (state 14)." Will try to post to pgsql-odbc. Thank you! On Mon, Jun 15, 2009 at 9:29 PM, Robert Haas wrote: > On Tue, Jun 16, 2009 at 12:24 AM, Konstantin Izmailov > wrote: > > Hello, > > I've found following description: "If character varying is used without > > length specifier, the type accepts strings of any size. The latter is a > > PostgreSQL extension." > > > > Does this mean that "character varying without length" is equivalent to > > "text" type. Are there any differences? > > > > I noticed that ODBC driver processes the type differently from "text". > > > > Please help! > > Konstantin > > This question would be more appropriate for pgsql-general or maybe > pgsql-odbc, since it is not a question about the development of > PostgreSQL. > > character varying and text are different types, but there's no real > difference between them. I can't speak to what the ODBC driver does. > > ...Robert >
[HACKERS] question about meaning of character varying without length
Hello, I've found following description: "If character varying is used without length specifier, the type accepts strings of any size. The latter is a PostgreSQL extension." Does this mean that "character varying without length" is equivalent to "text" type. Are there any differences? I noticed that ODBC driver processes the type differently from "text". Please help! Konstantin
Re: [HACKERS] information_schema.columns changes needed for OLEDB
Tom,
this is very helpful - thank you so much!
I had to discover those 'missing' functions one by one, usually after users'
complaints.
Konstantin
On Fri, May 29, 2009 at 11:35 AM, Tom Lane wrote:
> Konstantin Izmailov writes:
> > you know that some Postgres functions are listed in pg_proc while others
> are
> > not. For example, all Data Type Formatting function are in pg_proc
> (to_char,
> > to_hex, ...). While several of the Date/Time Functions are not there
> > (extract, localtime, ...).
>
> The ones that appear not to be there are ones that the SQL standard
> demands special weird syntax for. The grammar translates such calls
> to standard function calls to underlying functions, which usually are
> named a bit differently to avoid confusion. For instance
> extract(field from some_expr) becomes date_part('field', some_expr).
>
> If you want to know what all of these are, see the func_expr production
> in parser/gram.y.
>
> > This causes issues to Windows integration as well.
>
> Complain to the SQL standards committee, especially to those members
> who seem to think COBOL represented the apex of programming language
> syntax design :-(
>
>regards, tom lane
>
Re: [HACKERS] information_schema.columns changes needed for OLEDB
Hi, I'm not sure that it is related to information_schema but I wanted to let you know that some Postgres functions are listed in pg_proc while others are not. For example, all Data Type Formatting function are in pg_proc (to_char, to_hex, ...). While several of the Date/Time Functions are not there (extract, localtime, ...). Why different Postgres function are not equal??? This causes issues to Windows integration as well. Konstantin
Re: [HACKERS] information_schema.columns changes needed for OLEDB
Number 4 is actually numeric_precision (I typed incorrectly). My recollection is that numeric_precision sometimes expressed in radix 2 and it caused issues for Windows apps. I agree on other issues. I was curious if database can help OLEDB driver (to make it simpler). Anyway it can emulate values for specific Windows apps on the fly. Thank you! On Sat, May 23, 2009 at 2:57 PM, Peter Eisentraut wrote: > On Friday 22 May 2009 18:27:01 Konstantin Izmailov wrote: > > As we discussed at pgcon2009 there are some changes/fixes necessary in > > information_schema.columns to allow correct work of applications and > > services via OLEDB on Windows. Here are some: > > > > 1. data_type field contains types names that are not recognized by MS > apps. > > > > Code around: rename types on the fly, e.g. > > > > integer -> int > > > > character varying -> varchar > > > > character -> char > > The spelling of these types in the information schema is fixed by the SQL > standard. We can't change that. > > > timestamp without time zone -> datetime > > And that would certainly be wrong for other applications, because > PostgreSQL > doesn't have a datetime type. > > > bytea -> image > > And that we certainly can't do either. > > > 2. character_maximum_length field > > > > Code around: change value for text abd bytea types > > > > [text] 1073741823 > > (see next item) > > > [bytea] 2147483647 > > But bytea is not a character type in the first place, so this value is > meaningless. > > > 3. character_octet_length should always be double of > > character_maximum_length (due to Unicode character size on Windows which > is > > 2). > > We could do something like that if we exposed the maximum octet length of a > character per encoding. But what I wonder is whether this should reflect > the > server or the client encoding. How do your applications use this value? > > > 4. datetime_precision field is not always correct > > > > Code around: change value of the fly, e.g. if value is not null then > > > > [numeric] keep the value (ok) > > > > [bigint] set value to 19 > > > > all other set to 10 > > Why would numeric and bigint affect *datetime*_precision at all? > > > 5. numeric_precision_radix field should always be equal to 10 > > Why? > > > 6. datetime_precision field, minor changes > > > > Code around: change value on the fly, e.g. > > > > [date] set value to zero > > Makes sense. I think this is not correct at the moment. > > > [datetime] set value to 3 > > Well, it really depends on what you set it to when you declared the column, > no? >
[HACKERS] information_schema.columns changes needed for OLEDB
As we discussed at pgcon2009 there are some changes/fixes necessary in information_schema.columns to allow correct work of applications and services via OLEDB on Windows. Here are some: 1. data_type field contains types names that are not recognized by MS apps. Code around: rename types on the fly, e.g. integer -> int character varying -> varchar character -> char timestamp without time zone -> datetime bytea -> image 2. character_maximum_length field Code around: change value for text abd bytea types [text] 1073741823 [bytea] 2147483647 3. character_octet_length should always be double of character_maximum_length (due to Unicode character size on Windows which is 2). 4. datetime_precision field is not always correct Code around: change value of the fly, e.g. if value is not null then [numeric] keep the value (ok) [bigint] set value to 19 all other set to 10 5. numeric_precision_radix field should always be equal to 10 6. datetime_precision field, minor changes Code around: change value on the fly, e.g. [date] set value to zero [datetime] set value to 3
