Thread: A few SQL questions

Hi all, I have a few slightly more advanced SQL questions (which is why I'm here in the first place I suppose). I'm looking to migrate some applications and databases from Sybase to Postgres, and I have a few portability-type questions, or rather, questions on how to do the equivalent in Postgres.

1. In sybase, each column can have the same rule applied to it. You don't have to create multiple rules for columns in different tables. For instance, let's suppose that we require 5 digit numeric ids, using only a char(5) type, but there's a constraint on that column that says it must adhere to idrule, and idrule is defined as:

create rule idrule as @id like "[0-9][0-9][0-9][0-9][0-9]" (ugly, I know, but i didn't write it)

Looking at the docs, it looks like postgres is quite different. I could create a new type, I suppose, but is there any other way to do something like this in postgres?

2. Is there any nice way to generate number sequences via select? In other words, something along the lines of:
select 1 through 10 as number;
 number
--------
 1
 2
 3
 4
 5
 6
 7
 8
 9
 10

but more syntactically correct and working.

Thanks much,
Peter Koczan

> 1. In sybase, each column can have the same rule applied to it. You don't
> have to create multiple rules for columns in different tables. For instance,
> let's suppose that we require 5 digit numeric ids, using only a char(5)
> type, but there's a constraint on that column that says it must adhere to
> idrule, and idrule is defined as:
>
> create rule idrule as @id like "[0-9][0-9][0-9][0-9][0-9]" (ugly, I know,
> but i didn't write it)

A domain will do this:
http://www.postgresql.org/docs/8.2/interactive/sql-createdomain.html
http://www.postgresql.org/docs/8.2/interactive/functions-matching.html#FUNCTIONS-POSIX-REGEXP
in the constraint portion of the syntax you can specify a regular expression
id ~ '^[0-9]{5}$'

> 2. Is there any nice way to generate number sequences via select? In other
> words, something along the lines of:
> select 1 through 10 as number;

generate_series() should do what you want.
http://www.postgresql.org/docs/8.2/interactive/functions-srf.html

Regards,

Richard Broersma Jr.

Perfect. Exactly what I needed. Thank you very much Richard.

Peter

Richard Broersma Jr wrote:
>> 1. In sybase, each column can have the same rule applied to it. You don't
>> have to create multiple rules for columns in different tables. For instance,
>> let's suppose that we require 5 digit numeric ids, using only a char(5)
>> type, but there's a constraint on that column that says it must adhere to
>> idrule, and idrule is defined as:
>>
>> create rule idrule as @id like "[0-9][0-9][0-9][0-9][0-9]" (ugly, I know,
>> but i didn't write it)
>>
>
> A domain will do this:
> http://www.postgresql.org/docs/8.2/interactive/sql-createdomain.html
> http://www.postgresql.org/docs/8.2/interactive/functions-matching.html#FUNCTIONS-POSIX-REGEXP
> in the constraint portion of the syntax you can specify a regular expression
> id ~ '^[0-9]{5}$'
>
>
>> 2. Is there any nice way to generate number sequences via select? In other
>> words, something along the lines of:
>> select 1 through 10 as number;
>>
>
> generate_series() should do what you want.
> http://www.postgresql.org/docs/8.2/interactive/functions-srf.html
>
> Regards,
>
> Richard Broersma Jr.
>
>