PostgreSQL – dynamic value as table name [duplicate]

by

You will need to use the PL/PgSQL EXECUTE statement, via a DO block or PL/PgSQL function (CREATE OR REPLACE FUNCTION ... LANGUAGE plpgsql). Dynamic SQL is not supported in the ordinary SQL dialect used by PostgreSQL, only in the procedural PL/PgSQL variant.

DO
$$
BEGIN
EXECUTE format('CREATE TABLE %I AS SELECT * FROM backup', 'backup_' || to_char(CURRENT_DATE,'yyyy-mm-dd'));
END;
$$ LANGUAGE plpgsql;

The format(...) function‘s %I and %L format-specifiers do proper identifier and literal quoting, respectively.

For literals I recommend using EXECUTE ... USING rather than format(...) with %L, but for identifiers like table/column names the format %I pattern is a nice concise alternative to verbose quote_ident calls.

More Related Contents:

  1. Refactor a PL/pgSQL function to return the output of various SELECT queries
  2. Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
  3. PL/pgSQL checking if a row exists
  4. PostgreSQL parameterized Order By / Limit in table function
  5. Compute percents from SUM() in the same SELECT sql query
  6. Postgres analogue to CROSS APPLY in SQL Server
  7. How to find a table having a specific column in postgresql
  8. SQL update fields of one table from fields of another one
  9. DROP FUNCTION without knowing the number/type of parameters?
  10. Postgres function returning table not returning data in columns
  11. PostgreSQL convert columns to rows? Transpose?
  12. COPY with dynamic file name
  13. Create PostgreSQL ROLE (user) if it doesn’t exist
  14. Check if NULL exists in Postgres array
  15. How to use SQL LIKE condition with multiple values in PostgreSQL?
  16. How to create a new database with the hstore extension already installed?
  17. How to perform a select query in a DO block?
  18. Join a count query on generate_series() and retrieve Null values as ‘0’
  19. Select first row in each GROUP BY group?
  20. How to use RETURNING with ON CONFLICT in PostgreSQL?
  21. Escaping keyword-like column names in Postgres
  22. Delete duplicate rows (don’t delete all duplicate)
  23. GROUP BY + CASE statement
  24. Perform this hours of operation query in PostgreSQL
  25. Does the JDBC spec prevent ‘?’ from being used as an operator (outside of quotes)?
  26. How to use an ALIAS in a PostgreSQL ORDER BY clause?
  27. PostgreSQL does not accept column alias in WHERE clause
  28. How to group timestamps into islands (based on arbitrary gap)?
  29. Upsert with a transaction
  30. How to include excluded rows in RETURNING from INSERT … ON CONFLICT

Leave a Comment