Postgres Pro
Downloads
Home   >   mailing lists

Thread: [GENERAL] Adding 'serial' to existing column

[GENERAL] Adding 'serial' to existing column

From
Robert Lakes
Date:
I am new to Postgres and I am trying to build this SQL statement in my SQL script:
ALTER TABLE listings_cdc ALTER COLUMN table_id SET DEFAULT nextval('tab_id_seq');

I am trying to build the above-stated command as a dynamic SQL statement:
 EXECUTE 'ALTER TABLE listings_cdc ALTER COLUMN table_id SET DEFAULT nextval('||quote_ident(tab_id_seq)||')';

I've received an error when running the script this above-stated command:
 ERROR:  column "tab_id_seq" does not exist
LINE 1: ...OLUMN table_id SET DEFAULT nextval('||quote_ident(tab_id_seq...

Need help on how to write the above-stated command as a dynamic SQL statement.

In addition, do you know of a tutorial or a book that I can purchase that teaches how to build dynamic SQL statements

Re: [GENERAL] Adding 'serial' to existing column

From
Tom Lane
Date:
Robert Lakes <robertl@propaas.com> writes:
> I am new to Postgres and I am trying to build this SQL statement in my SQL
> script:
> ALTER TABLE listings_cdc ALTER COLUMN table_id SET DEFAULT
> nextval('tab_id_seq');

That looks correct, but this not so much:

> I am trying to build the above-stated command as a dynamic SQL statement:
>  EXECUTE 'ALTER TABLE listings_cdc ALTER COLUMN table_id SET DEFAULT
> nextval('||quote_ident(tab_id_seq)||')';

> I've received an error when running the script this above-stated command:
>  ERROR:  column "tab_id_seq" does not exist
> LINE 1: ...OLUMN table_id SET DEFAULT nextval('||quote_ident(tab_id_seq...

You want quote_literal, not quote_ident, because you're trying to produce
a single-quoted literal.
        regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Adding 'serial' to existing column

From
rob stone
Date:

On Fri, 2017-11-03 at 16:47 -0400, Tom Lane wrote:
> Robert Lakes <robertl@propaas.com> writes:
> > I am new to Postgres and I am trying to build this SQL statement in
> > my SQL
> > script:
> > ALTER TABLE listings_cdc ALTER COLUMN table_id SET DEFAULT
> > nextval('tab_id_seq');
> 
> That looks correct, but this not so much:
> 
> > I am trying to build the above-stated command as a dynamic SQL
> > statement:
> >  EXECUTE 'ALTER TABLE listings_cdc ALTER COLUMN table_id SET
> > DEFAULT
> > nextval('||quote_ident(tab_id_seq)||')';
> > I've received an error when running the script this above-stated
> > command:
> >  ERROR:  column "tab_id_seq" does not exist
> > LINE 1: ...OLUMN table_id SET DEFAULT
> > nextval('||quote_ident(tab_id_seq...
> 
> You want quote_literal, not quote_ident, because you're trying to
> produce
> a single-quoted literal.
> 
>             regards, tom lane
> 
> 

You could also do:-

ALTER SEQUENCE tab_id_seq OWNED BY listings_cdc.table_id;

which would establish the one-to-one relationship between the table and
its sequence for that column.

My 2 cents worth.

HTH,
Rob


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general