PostgreSQL – Max Length or Number of VALUES Parameters in Insert Query

The answer is no.

Related advice in the Postgres Wiki.

Don't add a length modifier to varchar if you don't need it. (Most of the time, you don't.) Just use text for all character data. Make that varchar (standard SQL type) without length modifier if you need to stay compatible with RDBMS which don't have text as generic character string type.

Performance is almost the same, text is a bit faster in rare situations, and you save the cycles for the check on the length. Related:

• Any downsides of using data type “text” for storing strings?

If you actually need to enforce a maximum length, varchar(n) is a valid choice, but I would still consider text with a CHECK constraint like:

ALTER TABLE tbl ADD CONSTRAINT tbl_col_len CHECK (length(col) < 51);

You can modify or drop such a constraint at any time without having to mess with the table definition and depending objects (views, functions, foreign keys, ...). And you can enforce other requirements in the (same) constraint.

Length modifiers used to cause problems like this or this or this ...

PostgreSQL 9.1 introduced a new feature to alleviate the pain somewhat. The release notes:

Allow ALTER TABLE ... SET DATA TYPE to avoid table rewrites in appropriate cases (Noah Misch, Robert Haas)

For example, converting a varchar column to text no longer requires a rewrite of the table. However, increasing the length constraint on a varchar column still requires a table rewrite.

More issues with varchar(n) have been fixed in later releases.

Instead of typing the entire list of column names, you can simply drag-and-drop the column list from the Object Explorer window in SQL Server Management Studio. In the image below, you drag-and-drop the highlighted section over to the query window:

However, if you insist on automatically creating the list of columns, you'll need to run the MERGE command using dynamic SQL. Erland Sommarskog has the definitive article on how to use dynamic SQL, along with a great set of advice on how not to do it, at http://www.sommarskog.se/dynamic_sql.html

Something like this:

DECLARE @cmd NVARCHAR(MAX);
SET @cmd = 'merge targ_table_name
using (select * from openquery(zdata,''select * from source_table_name'')) 
     as Tsource 
on (targ_table_name.ID = Tsource.ID) 
when matched then
Update set ' + @TargetToSourceColumns + '
when not matched by target then
insert values (' + @SourceColumns + ');';

EXEC sp_executesql @cmd;

The above statement is not syntactically correct, but essentially shows how you need to concatenate parts of the query that are dynamic. You'll need to compile the @TargetToSourceColumns and @SourceColumns variables as needed.