Teradata转PostgreSQL：SET表及相关参数的SQL语法转换咨询

Hey there! Below is the PostgreSQL equivalent of your Teradata table creation statement, plus a detailed breakdown of how to handle each Teradata-specific parameter in Postgres:

Converted PostgreSQL SQL #

-- For PostgreSQL 12+, CREATE OR REPLACE TABLE is supported
CREATE OR REPLACE TABLE sample (
    acct_id VARCHAR(60) NOT NULL,
    emp_sal CHAR(1) NOT NULL,
    -- Teradata "SET TABLE" = no duplicate rows; enforce with a primary key
    PRIMARY KEY (acct_id, emp_sal)
);

-- If you're on a PostgreSQL version older than 12, use this instead:
-- DROP TABLE IF EXISTS sample;
-- CREATE TABLE sample (
--     acct_id VARCHAR(60) NOT NULL,
--     emp_sal CHAR(1) NOT NULL,
--     PRIMARY KEY (acct_id, emp_sal)
-- );

Parameter-by-Parameter Explanation #

Let's walk through each Teradata clause and how to handle it in PostgreSQL:

SET TABLE #

Teradata's SET TABLE ensures no duplicate rows exist in the table. In PostgreSQL, you replicate this behavior by adding a primary key constraint (since both your columns are NOT NULL, this is perfect) or a UNIQUE constraint covering all columns. This guarantees no duplicate row combinations, just like Teradata's SET TABLE.

NO FALLBACK #

Teradata's FALLBACK creates a redundant copy of the table for failover. PostgreSQL doesn't have a table-level equivalent—redundancy and high availability are managed at the instance or database level (think streaming replication, point-in-time recovery, etc.). You can safely ignore this clause in Postgres.

NO BEFORE JOURNAL / NO AFTER JOURNAL #

Teradata uses these journals to track table changes for recovery. PostgreSQL relies on Write-Ahead Logging (WAL) for all transaction logging, and you can't disable logging for individual tables. These settings don't exist in Postgres, so just leave them out.

CHECKSUM = DEFAULT #

Teradata uses table-level checksums for data integrity. PostgreSQL handles integrity with instance-level page checksums (enabled by default in newer versions) and other built-in mechanisms. There's no table-level checksum parameter, so this clause is omitted.

DEFAULT MERGEBLOCKRATIO #

This is a Teradata storage optimization that controls how data blocks are merged. PostgreSQL manages storage automatically, so this setting has no equivalent in Postgres—you can ignore it entirely.

NOT CASESPECIFIC #

Teradata's NOT CASESPECIFIC makes string comparisons case-insensitive (e.g., 'JOHN' = 'john' returns true). PostgreSQL doesn't have a direct keyword for this, but you have two solid options:

1. Use the citext extension: First run CREATE EXTENSION IF NOT EXISTS citext;, then define your columns as citext instead of VARCHAR/CHAR. This makes comparisons case-insensitive by default.
2. Use a case-insensitive collation: For example, acct_id VARCHAR(60) COLLATE "en_US.utf8_ci" NOT NULL (note: collation availability depends on your server's installed locales).

If case sensitivity doesn't matter for your use case, you can just stick with standard VARCHAR/CHAR and use Postgres's default case-sensitive behavior.

内容的提问来源于stack exchange，提问作者Karthik k