Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2014

    Unanswered: Syntax error in TYPE creation

    CREATE TYPE ut_1000_long_description as (varchar(1000));

  2. #2
    Join Date
    Jun 2004
    Arizona, USA
    1. What is the entire error message?
    2. Have you looked at CREATE TYPE SQL Reference in the manual?

    It states:
    There are five forms of CREATE TYPE, as shown in the syntax synopsis above. They respectively create a composite type, an enum type, a range type, a base type, or a shell type.
    Your attempted usage doesn't 'fit' any of the 5 types allowed. (It appears that you just want to rename the varchar type, but with a fixed length.) As far as I can tell, the 'closest' match would be a composite type, but it still doesn't allow you to fix the size of any data types in the definition.

    Now, PostgreSQL does have support for this sort of thing - you can achieve this sort of alias by using domains.

    For instance,
    CREATE DOMAIN usernames AS varchar(20);

    will be treated by postgreSQL as an alias for varchar(20). It shows up on pgAdmin as a data type when creating tables. And, you can enter data into the table, and it is limited to the data type/length specified. It's not perfect though. For instance, with the above definition, I could supply a 30 character username, and it is accepted. But, it is silently truncated to fit the underlying data type. If I define the column data type as a 20 character varchar, and I supply data that's too long, I get an error. Which is what I should see. Silently accepting data that doesn't fit, and truncating it 'feels' like mySQL.

    Also, don't go overboard with this sort of thing; If you do, it ends up obscuring the original data types, and can be confusing to someone who may need to support your work. (When they look at a table definition, it will display the domain as a data type, and that data type won't be in the postgresql manual...
    Last edited by loquin; 08-29-14 at 19:49. Reason: broken tag
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert

  3. #3
    Join Date
    Nov 2003
    Provided Answers: 12
    This is what domains where made for:

    create domain ut_1000_long_description as text 
           constraint check_length check (length(value) < 1000);
    Note that I used a check constraint to limit the length. If you define the domain as varchar(1000) you can't change that later because you cannot change the data type of a domain.

    But when using a check constraint you can drop and re-create the constraint even if the domain is currently used.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags:

    Tips for good questions:

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts