Adding 'serial' to existing column
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
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
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