Re: Creating Primary Key after CREATE TABLE: Is Sequence created?

On 9/27/2013 12:40 PM, mdr wrote:
> Is it possible to create a column of type SQL:2011 types (INTEGER or such)
> and then connect a SEQUENCE to it and make that column a PRIMARY KEY -
> without creating a column of type SERIAL?

yes, its just more work.

something like...

CREATE TABLE fred (id integer, val character varying(100))
PRIMARY KEY (id);
CREATE SEQUENCE fred_id_seq OWNED BY fred.id;
ALTER TABLE fred ALTER COLUMN id SET DEFAULT
nextval('fred_id_seq'::regclass);

nextval() and OWNED BY are both postgres extensions.

>
> It seems column of type SERIAL is specific to Postgres and will make my
> script Postgres dependent?
>
> Just in case I decide to go to MySQL. Most likely not, but just wanted to
> manage to SQL:2011.

MySQL uses some kind of nonstandard 'autoincrement' attribute rather
than SEQUENCE

--
john r pierce 37N 122W
somewhere on the middle of the left coast