If I have a table field of type: enum('first', 'second'), how do I construct a query that will give me back all possible values (here the words 'first' and 'second') for this field?

    you should be able to use:

    show fields from tablename;

    then iterate across the set to find the field name.

    I'm not a big MySQL user, but it sounds like it might work.

      Yeah, I remember trying to do this in mysql. I couldn't find any way to get the enum values back as rows (or a row). Like Sxooter said, I think it's SHOW FIELDS FROM table, then parse/regexp the results for the enum values.

        Sxooter-

        Instead of enums in PG you'd set a constraint to check a field's value was in a certain set - correct? Is there an easy way in PG to get those values?

          For now, yes, a check constraint with an in() condition is the answer. Note, however, that enumerated types are specified in the SQL3 standard (or were at one time, they may have gotten pulled by now, I haven't read the every changing SQL 3 spec in a while) so eventually Postgresql may well have an enumerated type.

          Assuming you created a table like this:

          create table test (color text check (color in('red','green','blue')),id serial);

          then you'd likely need a query like this:

          SELECT 
  consrc, 
  conname 
FROM 
  pg_catalog.pg_constraint r 
JOIN 
  pg_catalog.pg_class c 
ON (r.conrelid=c.oid) 
WHERE 
  c.relname ~ '^test$';

          Simple huh? 🙂 To figure out how to do this kind of crazy stuff, you can run psql with the -E switch, which will make psql show you all the funky SQL it uses in the backend to talk to the pg_* tables. In this case, the queries are for 7.4. beta 1, so they may be a little different for older versions.

          Sometime around 7.5 the full SQL99 information schema should be online, making a lot of this jumping through hoops unnecessary.

            🙂 - the underlying voodoo of PG that are the pg_ tables. I've been meaning to learn more about them (and probably end up REALLY fubar'ing things 😉 ).

            I thought I remember some posts in hackers/general about the information schema. What exactly is that? Is it primarily about getting meta information about tables, databases, columns, etc.?

              Ahhh. Schemas. Schemas are basically namespaces that live between the database and table level. Imagine a department in a company that handles financials having a database called just that, finance. Now, if we have accounts receiveable, accts payable, billables, etc, we can put them each in a different schema, so we have

              psql template1

              create database finance;
              \c finance
              create schema customers;
              create schema accts_payable;
              create schema accts_receiveable;
              create schema billables;

              create table customers.customer_accts (....;
              create table accts_payable.sometable ....;
              create table accts_receiveable.someothertable

              Now we can put default different security models in each schema, so that accts_receiveable people can READ from the accts_payable, but not change it, and vice versa...

              So, where MySQL lets you join databases like:

              select * from db1.table1, db2.table2 join on ()...

              In Postgresql the same syntax would be withing a single database, but across multiple schemas. This means large joins and what not can benefit from the ability to play in one area so to speak, while you get much the same security you would from multiple databases.

              In 7.4, they are now implementing the information schema. Here's a short list of all the tables currently in the information schema:

              applicable_roles
              check_constraints
              column_domain_usage
              column_privileges
              column_udt_usage
              columns
              constraint_column_usage
              constraint_table_usage
              data_type_privileges
              domain_constraints
              domain_udt_usage
              domains
              element_types
              enabled_roles
              information_schema_catalog_name
              key_column_usage
              parameters
              referential_constraints
              role_column_grants
              role_routine_grants
              role_table_grants
              role_usage_grants
              routine_privileges
              routines
              schemata
              sql_features
              sql_implementation_info
              sql_languages
              sql_packages
              sql_sizing
              sql_sizing_profiles
              table_constraints
              table_privileges
              tables
              triggered_update_columns
              triggers
              usage_privileges
              view_column_usage
              view_table_usage
              views

              select * from information_schema.oneoftheabove;

              gives a pretty neat little display.

              Using the information_schema you can get:

              select * from check_constraints where constraint_name='constraint_name';

                Definitely some Good Stuff 🙂.

                BTW, is 7.4 going to have a native Windows port? I didn't see it in the changelog

                  Nope, 7.4 will NOT have native windows, or PITR either.

                  It's mainly a consolidation / performance tweaking release. There's tons of stuff that's been done, but the big projects were running too late to wait for, so they'll have to try and make the 7.5 release in 6 to 9 months.

                  Note, however, that a lot of work HAS been done on both the windows release and PITR, that will translate straight over to 7.5, so it is quite likely that the 7.5 release will have them.

                  Initial testing from most users on 7.4 beta is showing a 10 to 30% increase in overall speed, and huge gains in things like select * from table where id in (select ids from othertable); and a few other problem performance areas of the past.

                    Write a Reply...