Your query seems to from v10 and thus is missing two reg types. 'pg_catalog.regcollation' 'pg_catalog.regnamespace'

Use the source, Luke! In ATAddForeignKeyConstraint in src/backend/commands/tablecmds.c, we find the truth about the requirements: /* * There had better be a primary equality operator for the index. * We'll use it for PK = PK comparisons. */ ppeqop = get_opfamily_member(opfamily, opcintype, opcintype, eqstrategy); if (!OidIsValid(ppeqop)) elog(ERROR, "missing operator %d(%u,%u) in opfamily %u", eqstrategy, opcintype, opcintype, opfamily); So, the unique index on the target type has to support equality comparisons. /* * Are there equality operators that take exactly the FK type? Assume * we should look through any domain here. */ fktyped = getBaseType(fktype); pfeqop = get_opfamily_member(opfamily, opcintype, fktyped, eqstrategy); if (OidIsValid(pfeqop)) { pfeqop_right = fktyped; ffeqop = get_opfamily_member(opfamily, fktyped, fktyped, eqstrategy); } else { /* keep compiler quiet */ pfeqop_right = InvalidOid; ffeqop = InvalidOid; } If there is an equality operator between the data types of the referencing column and the referenced column that is supported by the target index, we are good. if (!(OidIsValid(pfeqop) && OidIsValid(ffeqop))) { /* * Otherwise, look for an implicit cast from the FK type to the * opcintype, and if found, use the primary equality operator. * This is a bit tricky because opcintype might be a polymorphic * type such as ANYARRAY or ANYENUM; so what we have to test is * whether the two actual column types can be concurrently cast to * that type. (Otherwise, we'd fail to reject combinations such * as int[] and point[].) */ Oid input_typeids[2]; Oid target_typeids[2]; input_typeids[0] = pktype; input_typeids[1] = fktype; target_typeids[0] = opcintype; target_typeids[1] = opcintype; if (can_coerce_type(2, input_typeids, target_typeids, COERCION_IMPLICIT)) { pfeqop = ffeqop = ppeqop; pfeqop_right = opcintype; } } Otherwise, there must be an implicit cast from the type of the referencing column to the referenced column. if (!(OidIsValid(pfeqop) && OidIsValid(ffeqop))) ereport(ERROR, (errcode(ERRCODE_DATATYPE_MISMATCH), errmsg("foreign key constraint \"%s\" cannot be implemented", fkconstraint->conname), errdetail("Key columns \"%s\" and \"%s\" " "are of incompatible types: %s and %s.", strVal(list_nth(fkconstraint->fk_attrs, i)), strVal(list_nth(fkconstraint->pk_attrs, i)), format_type_be(fktype), format_type_be(pktype)))); If neither is true error out. So you can have foreign keys from integer to smallint because there exists an equality operator between these types that belongs to the index's operator family: \do = List of operators Schema │ Name │ Left arg type │ Right arg type │ Result type │ Description ════════════╪══════╪═════════════════════════════╪═════════════════════════════╪═════════════╪═══════════════ ... pg_catalog │ = │ integer │ smallint │ boolean │ equal ... (63 rows) However, there is no implicit cast between text and integer, so you cannot have foreign key references between these types. \dC List of casts Source type │ Target type │ Function │ Implicit? ═════════════════════════════╪═════════════════════════════╪════════════════════╪═══════════════ ... integer │ bigint │ int8 │ yes integer │ bit │ bit │ no integer │ boolean │ bool │ no integer │ "char" │ char │ no integer │ double precision │ float8 │ yes integer │ money │ money │ in assignment integer │ numeric │ numeric │ yes integer │ oid │ (binary coercible) │ yes integer │ real │ float4 │ yes integer │ regclass │ (binary coercible) │ yes integer │ regcollation │ (binary coercible) │ yes integer │ regconfig │ (binary coercible) │ yes integer │ regdictionary │ (binary coercible) │ yes integer │ regnamespace │ (binary coercible) │ yes integer │ regoper │ (binary coercible) │ yes integer │ regoperator │ (binary coercible) │ yes integer │ regproc │ (binary coercible) │ yes integer │ regprocedure │ (binary coercible) │ yes integer │ regrole │ (binary coercible) │ yes integer │ regtype │ (binary coercible) │ yes integer │ smallint │ int2 │ in assignment ...

Often SQL Server will have to allocate working memory to run a query. The amount of memory granted depends on many things including column data type and length. For (n)varchar it guesses actual values are, on average, half the declared length and calculates required memory accordingly. So overly-large columns can result in overly-large memory reservations, reduced throughput and poorer system performance. To illustrate this I've created a series of tables. All are of the form create table dbo.t10(c varchar(10) null); : create table dbo.t2000(c varchar(2000) null); The column's length changes from table to table. The shortest will be 10 (as shown) and the longest will be 2,000. Each table is populated with one million rows. Each row consists of the single letter 'a'. So all tables hold the identical amount of data and occupy the same amount of space on disk (exec sp_spaceused 'dbo.t10';). I use a very simple query select c from dbo.t10 order by c option(maxdop 1); The sort means a memory grant will be requested. Using maxdop 1 avoids the risk of some plan going parallel and complicating the comparison. I run this for each table t10 through t2000, capture the actual execution plan and record the MemoryGrantInfo. Here's the plot of how desired memory (in KB) varies by column length. I think it is quite convincing. There is no meaning in the size range 10 to 2,000. They are just arbitrary, chosen for convenience, not to illustrate any particular point. Similarly the letter 'a' was the first thing my fingers typed. My system has ample memory. Desired, Requested and Granted are identical in every test. I chose to plot Desired memory as it would be the largest if there were a difference.

The problem is that PG has to cast original numeric to double precision: Filter: ((total_balance)::double precision = ... So the index that was built contains numeric, but you need double precision - hence the index can't be used. You need to stay away from casting the values in the column: WHERE total_balance = 'NaN'::numeric PS: it's weird to see NaN in a database. Why not store null?

select data_type, count(*) from information_schema.columns where table_schema = 'public' group by data_type ; For other schemas just add: or table_schema = 'your_schema_name'. If this will not satisfy you, you should look in pg_... or information_schema tables. If you are looking for table information: select data_type, count(*) from information_schema.columns where table_schema = 'public' and table_name = 'your_table_name' group by data_type ;

A lot of problems with what you are trying that I just don't think this particular approach is going to work (I think the goal is interesting): returns @result table (reportID bigint) - this is basically schema-bound. You can't alter the schema of the returned table - I see later you are going to attempt to "ALTER" this variable - that's just not going to happen. Even if you could, this part: select @alterTableStr = 'alter table @result add '+ convert(varchar(2000),@dataLabel)+' '+ convert(varchar(2000),@dataType) ; exec sp_executesql @alterTableStr; isn't going to affect the @result in the outer part - these dynamic SQL parts have their own scope and can't get to the variables in the calling part this way And then it really comes down to the error you are currently getting - which is sp_executesql can't be run inside a function anyway. Anyway, to aid you a little in making progress, may I suggest you read the following two articles which are are tangentially related (and I have used recently) regarding parsing CSV and JSON data. Both use his hierarchy idea (an unpivoted name/value thing) and repivoting and you might find some useful techniques there: http://www.simple-talk.com/sql/t-sql-programming/the-tsql-of-csv-comma-delimited-of-errors/ http://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/ If you can use the CLR, this article which was published might also be useful: http://www.sqlservercentral.com/articles/.Net/94922/

See 12.2 Numeric Types (MySQL Reference Manual) Essentially what you listed is the max value for INT and not BIGINT. INT(8) is the equivalent of typing INT with a display width of 8 digits INT(4) is the equivalent of typing just INT with 4 display lengths. The range of BIGINT is -9223372036854775808 to 9223372036854775807 or 0 to 18446744073709551615.

Depending on how you are using your temp tables, you could run into a data truncation issue. This example is a bit contrived, but it illustrates my point. Example: Your user table column is varchar(50). Your temp table column is varchar(255). You have a record with 45 characters in that column in your user table. In your procedure, you concatenate ' - for the win' to the end of that column, prior to merging that temp table into your user table. The temp table would gladly accept the new varchar value with a length of 59. However, your user table could not. Depending on how you handle this in your procedure, this could result in truncation or an error. Unless you document and account for these issues, your procedure could perform in an unexpected manner. Personally, I do not think there is an answer to this question that is correct 100% of the time. It really depends on how you are using those temp tables. Hope this helps

Those two are really synonyms, so you can use them interchangeably. You won't see any difference between them. If you want to allow 0 and 1 only, you can still use the bit type.

It seems pretty easy: postgres=# create table inet_test (address inet); CREATE TABLE postgres=# insert into inet_test values ('192.168.2.1'); INSERT 0 1 postgres=# insert into inet_test values ('192.168.2.1/24'); INSERT 0 1 postgres=# select * from inet_test; address ---------------- 192.168.2.1 192.168.2.1/24 (2 rows)

I don't see any reason to not use integer in Oracle. It will be mapped to the approriate Oracle datatype but keeps the DDL at least somewhat portable. I also always use VARCHAR instead of VARCHAR2 as they are equivalent anyway. Oracle does not recommend this, as in the future this might change, but this has been true since version 6, so I doubt that it will actually change at all

Try providing USING clause as described in docs: A USING clause must be provided if there is no implicit or assignment cast from old to new type.

Interesting question - at first I would have said an overflow, but checking, that's ORA-01426 and if it wasn't a number, ORA-01722. Is it just datatype NUMBER which defaults to NUMBER(38)? But this can be diagnosed with a trace to see what's exactly going on. If you can identify the session, or can modify the connection code, you can do: SQL> alter session set events='6502 trace name errorstack level 12'; Session altered. SQL> create or replace function fn_add(p1 number, p2 number) return number 2 as 3 v1 number(1); 4 begin 5 v1 := p1 + p2; 6 return v1; 7 end; 8 / Function created. SQL> select fn_add('10', '1') from dual; select fn_add('10', '1') from dual * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: number precision too large Now looking in the tracefile: ---- Error Stack Dump ----- ORA-06502: PL/SQL: numeric or value error: number precision too large ----- Current SQL Statement for this session (sql_id=a36psfjj50bpv) ----- select fn_add('10', '1') from dual That should at least get you started on root cause analysis. But be warned that these trace files can get very large, very quickly!

I found this guide which is for Netezza version 5, so that should be close. Documentation for that system is rather difficult to find, or at least it doesn't readily show up on Google. For SQL Server 2008, the MSDN page is here. ABSTIME --> ?? BIGINT --> bigint BOOLEAN --> bit BYTEA --> ?? tinyint? BYTEINT --> smallint (tinyint is positive values only) CHAR --> char(1) CHARACTER VARYING(1..8000) --> varchar(1..8000) CHARACTER VARYING(>8000) --> varchar(MAX) CHARACTER(1..8000) --> char(1..8000) DATE --> date DOUBLE PRECISION --> double precision or float(53) INT2VECTOR --> ?? INTEGER --> int INTERVAL --> No equivalent NAME --> ?? sysname? NATIONAL CHARACTER VARYING(1..4000) --> nvarchar(1..4000) NATIONAL CHARACTER VARYING(>4000) --> nvarchar(MAX) NUMERIC(p,s) --> numeric(p,s) OID --> ?? OIDVECTOR --> ?? REAL --> real REGPROC --> ?? SMALLINT --> smallint TEXT --> ?? Probably either varchar(MAX) or nvarchar(MAX) TIME --> time(6) TIME WITH TIME ZONE --> datetimeoffset(6) TIMESTAMP --> datetime2(6)

The answer to your question is yes. That column specifies the default nullability of a data type, which can be important if the user doesn't express a preference e.g. in a CREATE TABLE statement with an explicit NULL or NOT NULL specification. Sadly, that information is only present in the documentation for the old compatibility view sys.systypes: allownulls (bit) Indicates the default nullability for this data type. This default value is overridden by if nullability is specified by using CREATE TABLE or ALTER TABLE.| For example, one can override the default NOT NULL behaviour of the sysname type: DECLARE @T table (s sysname NULL); INSERT @T (s) VALUES (NULL); SELECT s FROM @T AS T; s NULL The timestamp type (preferred name rowversion) has additional special behaviours due to its intended use to detect row changes. See this other answer for some more details. You can still override its nullability in the same way, but it is not possible to actually store a NULL for a timestamp/rowversion attribute. There are several complex layers to determining attribute nullability. Rather than repeat some of that detail here, I refer you to Why you Should Always Specify Whether a Column Accepts Nulls by Phil Factor for background information and additional reading.

CLR user-defined types can be used with CAST and CONVERT as shown in the documentation. From example 'I' there: CREATE ASSEMBLY mytest FROM 'c:\test.dll' WITH PERMISSION_SET = SAFE; CREATE TYPE Point EXTERNAL NAME mytest.Point; GO DECLARE @p Point = CONVERT(Point, ''); SET @p.SetXY(22, 23); You can find more examples and usage information in the documentation at Working with User-Defined Types - Manipulating UDT Data, e.g. UPDATE dbo.Points SET PointValue.SetXY(5, 99) WHERE ID = 3; Alias data types are a deprecated and much older implementation. They are literally aliases for existing base types, perhaps with restrictions and a default bound to them (also deprecated features).

There are various indirect methods, but nothing quite as simple as pg_typeof. SQL_VARIANT_PROPERTY is often used for convenience, but has limitations since it cannot contain all other types. Writing the subject of inquiry to a table then inspecting metadata is one method. Convenience system stored procedures like sys.sp_help can be employed as a somewhat more convenient method than writing a query against the metadata views. Another, conceptually-related idea is to call: sys.sp_describe_first_result_set (procedure); or sys.dm_exec_describe_first_result_set (function) For example: EXECUTE sys.sp_describe_first_result_set @tsql = N'SELECT 1.0;' SELECT FRS.* FROM sys.dm_exec_describe_first_result_set (N'SELECT 1.0;', DEFAULT, DEFAULT) AS FRS; These return a great deal of internal information, including type details. The above example returns: system_type_name numeric(2,1) An alias type example: CREATE TYPE Banana FROM binary(456) NOT NULL; EXECUTE sys.sp_describe_first_result_set @tsql = N'DECLARE @b Banana; SELECT @b;' SELECT FRS.system_type_id, FRS.system_type_name, FRS.user_type_id, FRS.user_type_schema, FRS.user_type_name FROM sys.dm_exec_describe_first_result_set (N'DECLARE @b Banana; SELECT @b;', DEFAULT, DEFAULT) AS FRS; returns (in part): system_type_id system_type_name user_type_id user_type_schema user_type_name 173 binary(456) 260 dbo Banana

The column defined by your CASE expression has a data type, as all columns do. SQL Server determines the resulting type in its usual way, making use of data type precedence. The type datetime has a higher precedence than integer. You might be expecting the CASE expression to return different types on different paths, but that is not possible (sql_variant aside).

They seem logically equivalent. They are. Even with ANSI_NULLS OFF "a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL." I found an old feedback item for this here, you can go vote for it.

How do I find out why the query planner doesn't consider (or overestimates) Query 1's plan when presented with Query 2? EXPLAIN ANALYZE is great but it doesn't allow me to see the plans that weren't used. You can "disable" various planner methods to see alternative plans. "Disable" quoted, because those methods are then not actually disabled, just estimated to be ridiculously high, so that any other plan seems favorable. Only for debugging! Not meant for permanent use. There is a list in the manual in the chapter Planner Method Configuration. Consider the advice at the top: These configuration parameters provide a crude method of influencing the query plans chosen by the query optimizer. If the default plan chosen by the optimizer for a particular query is not optimal, a temporary solution is to use one of these configuration parameters to force the optimizer to choose a different plan. Better ways to improve the quality of the plans chosen by the optimizer include adjusting the planner cost constants (see Section 19.7.2), running ANALYZE manually, increasing the value of the default_statistics_target configuration parameter, and increasing the amount of statistics collected for specific columns using ALTER TABLE SET STATISTICS. In your particular case, to understand why Postgres chose the this plan and not some other plan, compare the estimated costs before and after disabling the hash join strategy. Spoiler alert: it's because it (falsely) projects a lower cost. For instance: EXPLAIN ; -- take note SET enable_hashjoin = off; EXPLAIN ; -- compare Or use EXPLAIN (ANALYZE, BUFFERS) for more details. You seem to be familiar with EXPLAIN. Another plan will be chosen, and the estimated cost will be higher. And one or both estimates are pretty far from reality. Why Postgres arrives at bad estimates is another question. Basic starters are in the advice from the manual above. Maybe your installation is (also) too optimistic about costs for parallelism. There are a number of GUC parameters to adjust that. You can disable parallelism for testing with: SET max_parallel_workers_per_gather = 0; Force a particular order of joins? For the added question in the comment: see the costs for performing scans in a Nested Loop in the opposite order? You could experiment with join_collapse_limit: Setting it to 1 prevents any reordering of explicit JOINs. Thus, the explicit join order specified in the query will be the actual order in which the relations are joined. Because the query planner does not always choose the optimal join order, advanced users can elect to temporarily set this variable to 1, and then specify the join order they desire explicitly. For more information see Section 14.3. So: SET join_collapse_limit = 1; EXPLAIN ; EXPLAIN ; -- compare Related: Postgres JOIN conditions vs WHERE conditions Are implicit joins as efficient as explicit joins in Postgres? Don't use data type char(n)! One avoidable problem in your setup becomes obvious in the added casts in the EXPLAIN plan: Index Cond: (id = (b.id)::bpchar) Etc. At least one id column is of type character. bpchar is the internal name for the outdated SQL data type character. "bpchar" for "blank padded character". Never use it. Especially not for PK columns. The type character is a Zombi you shoot in the head on sight. Do not confuse character / char / bpchar with the useful data types text or varchar or "char" (with quotes!). See: Any downsides of using data type “text” for storing strings? If your id columns hold integer numbers, use integer (or bigint). Else text or whatever is appropriate. Might contribute to the inferior query plans. Turned out to be the main issue. See OP's follow-up with a bug report.