CODEWITHSUNDEEP
postgresqlstring-concatenationamazon-redshiftcreate-table
UrVal
UrVal

Reputation: 351

Create table name as concatenated string

I'm using Amazon Redshift via SQL workbench. I need to create a table (actually multiple but...babysteps) whose name is constructed by concatenating 2 strings.

The following statement works:

CREATE TABLE "public.user_111111" (
user_id int,
event_id int,
)

But the following one does not:

CREATE TABLE "public."||"user_111112" (
user_id int,
event_id int,
)

the error is:

[Amazon](500310) Invalid operation: syntax error at or near "||";

Can you please help me understand what I'm doing wrong in the second statement?

Thank you

Upvotes: 2

Views: 3073

Answers (2)

Christian
Christian

Reputation: 7320

Use execute command (dynamic-sql):

Just easy like this:

DO
$$
DECLARE 
  vSQL      TEXT;
  vMYSCHEMA TEXT;
  vMYTABLE  TEXT;
BEGIN
    vMYSCHEMA = 'public';
    vMYTABLE  = 'user_111112';

    vSQL = 'CREATE TABLE ' || vMYSCHEMA || '.' || vMYTABLE || ' (user_id int, event_id int)';
    EXECUTE vSQL;

END;
$$
;

or

DO
$$
BEGIN
    EXECUTE 'CREATE TABLE ' || 'public' || '.' || 'user_111112'|| ' (user_id int, event_id int)';
END;
$$
;

Upvotes: 1

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14097

As lad2025 suggested, you could use Dynamic SQL to do that.

HOWEVER

This is a terrible idea to put DOTS in your table names. It has been asked on DBA site: Is it okay to put dots in SQL Server database names?

Quoting answer by Marek Grzenkowicz

Over the years I found out that many people have trouble understanding the four-part naming convention even though it seems pretty obvious:

server_name.database_name.schema_name.object_name

Imagine what will happen if they see something like this:

MAIN-SQL.[MyApp.Sales].hr.CompetitorsProducts

or:

[MAIN-SQL\EXPRESS].[MyApp.Sales].sch_HR.[Products From.Our-Competitors]

Keeping things simple is important.

I would suggest not putting dots in your tables either. You'll harden your own (or someones else) life by doing that.

Upvotes: 0

Related Questions