[GENERAL] Concerned to discover DOMAINs can be the same as built in types with no escaping

[Justin Dearing]

Hello,

We happen to have in our schema the following domain.

CREATE DOMAIN public.name varchar(50) NOT NULL;

This was done before me. We assumed this was used in many tables in our
app. Then I wrote a function with a return clause like the following:

RETURNS (
id INT,
name name,
other_names name ARRAY
)

This worked fine until CI tried to build this on a postgres 9.3 box (we are
mostly 9.1, but are fixing that). Then it failed.

So then I discovered that there is a built in type called pg_catalog.name
as well as my public.name. Followed by the discovery that you can't have
arrays of domains. This query showed two rows

SELECT typnamespace, typname
FROM pg_catalog.pg_type typ
WHERE typname = 'name';

Then there was wailing and gnashing of teeth, and I made everything
explicitly varchar, and everything was all good, except I have to fix unit
tests. Oh and nothing is actually using our domain, as demonstrated by this
query:

SELECT attrelid::regclass AS table_name,
  attname, atttypid::REGTYPE
  FROM pg_catalog.pg_attribute
  WHERE atttypid::REGTYPE IN ('name', 'public.name')
  ORDER BY atttypid DESC, attrelid::regclass


Based on this, and some consultations with friends who know more about
postgres than I, I'd like to propose that domains not be allowed to be the
same name as built in types or at the very least give a warning. The fact
that I have to quote keywords, but not even need to quote built in types is
bothersome. Here are examples of queries and behaviors I expect


CREATE DOMAIN "INTO" char(5); -- Does work. Should work without a warning.
The error you get for doing it unquoted is sufficient IMHO
CREATE DOMAIN int CHAR(50); -- Does work. Id prefer it not to work.
Alternatively it could work but emit a warning.
CREATE DOMAIN public.int CHAR(50); -- Does work. I could see the argument
for it working, but would prefer it didn't work. Should still emit a
warning its overriding a base

Since I'm returning to postgres after close to a decade, I figured I'd ask
here for feedback before posting to the hackers list.

Regards,

Justin Dearing

[GENERAL] Default value of column not respecting character length or domain restraints.

[Justin Dearing]

It seems I can feed a default value to a domain that won't fit in the
underlying base type. I have the domain html_colors as so:

CREATE DOMAIN html_color AS char(7) CHECK (VALUE ~ '^#[A-Fa-f0-9]{6}$');

I then defined a column of html_colors as so:
ALTER TABLE users ALTER COLUMN profile_color SET DEFAULT '#RFF';

This worked fine and I didn't notice it until I added arecord to the
users table and got the error:
ERROR: value too long for type character(7).

This behavior seems undesirable to me. Is this a known bug or is there
a reason for this?

Regards,
Justin Dearing

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] custom type for storing a HTML color

[Justin Dearing]

Hello, I currently store html color codes in that database with the
following DOMAIN:

CREATE DOMAIN html_color AS char(7) CHECK (VALUE ~ '^#[A-Fa-f0-9]{6}$');

Has anyone created a custom type that has additional functionality (eg
format the input or output to other formats, retrieve red, green or
blue values as 0-255 integers, etc. This is good enough for my uses at
the moment, but has anyone invented a better wheel than mine?

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[GENERAL] Syncing postgres data with Pocket PC

[Justin Dearing]

Hello,

I need a way to sync a postgres view with a table on a Windows CE device.
The table will be read only on the mobile device. I am seeking to replace an
access database that syncs a table with a pocket pc table via active sync. I
would really like to use postgres for the desktop side of things, but need
to be able to syn change to the database with pocket PCs via active sync.