Dynamic table_name and nextval_sequence_name in PostgreSQL?

Question

I need a way to create table and nextval sequence name dynamically in PostgreSQL, can this be done without brain meltdown?

My thinking goes along the lines of:

DECLARE
    $tablename varchar(50) = "name_of_table";

-- Sequence and defined type
CREATE SEQUENCE IF NOT EXISTS $tablename_id_seq; <-- PROBABLY NEED SOME SORT OF CONCATENATION HERE

-- Table Definition
CREATE TABLE $tablename (
    "id" int4 NOT NULL DEFAULT nextval($tablename || '_id_seq'::regclass),

 ... (the rest omitted)

What I'm trying to achieve: I'm building an interface where a user can type in the name of the table they'd like to create, and based on the input of that field a table of that name with matching sequence is created ...

Answer 1

Yes, and it's built-in. There are smallserial,serial and bigserial types that do exactly that:

CREATE TABLE $tablename (
    "id" serial NOT NULL);

And there's (better) identity column syntax mentioned by @Frank Heikens above

CREATE TABLE tablename (
    "id" int4 generated by default as identity NOT NULL);

Then, if you want the sequence, you can use pg_get_serial_sequence().

insert into tablename values(default),(default),(default) returning *;

id
1
2
3

select pg_get_serial_sequence('tablename','id'),
       currval(pg_get_serial_sequence('tablename','id'));

pg_get_serial_sequence	currval
public.tablename_id_seq	3

If you decide to keep creating these by hand, make sure to later link them to the column they are responsible for:

alter sequence my_seq owned by my_table.my_column;

---

If you're asking about dynamic SQL syntax in general, not just sequence name dynamically generated based on table name, there are examples in the doc:

DO $r$
DECLARE my_table_name text:='my_table_'||(random()*1000)::int;
BEGIN
EXECUTE FORMAT ('CREATE TABLE %1$I ("id" generated by default as identity NOT NULL);',my_table_name);
END $r$;

select tablename, 
       pg_get_serial_sequence(tablename,'id'),
       nextval(pg_get_serial_sequence(tablename,'id'))
from pg_tables where tablename ilike '%my_table_%';

tablename	pg_get_serial_sequence	nextval
my_table_25	public.my_table_25_id_seq	1

fiddle