New answers tagged datatypes
I'm still not sure why it was happening, but I was able to fix it by changing the parameter to be passed in as a string (%s) instead of a number (%d).
This is due to relation attributes (defined in pg_class and pg_attribute, or defined dynamically from a select statement) supporting modifiers (via pg_attribute.atttypmod), whilst function parameters do not. Modifiers are lost when processed through functions, and since all operators are handled via functions, modifiers are lost when processed by operators ...
Sounds like your PHP code is converting the string to a integer and chopping off the decimal place. Possibly in the prepare statement. You could try binding the param as a string, or possibly a cast as a (float). Example PHP: $number='2.90'; echo (int)$number; Will output 2 $number='2.90'; echo (float)$number; Will output 2.9 I would just leave ...
To reproduce the problem: SELECT *, (CASE WHEN IsGun=1 THEN CEILING(Price1Avg) ELSE Price1 END) FROM ( SELECT UPC, IsGun, Price1, AVG(CAST(Price1 AS numeric(8, 2))) OVER (PARTITION BY UPC) AS Price1Avg FROM ( VALUES ('A', 0, 14.99), ('B', 0, 29.99), ('C', 1, 319.00), ('D', 1, ...
First, find out if "mail" guarantees that all data is utf8 or ascii or whatever. If utf8, then use MEDIUMTEXT CHARACTER SET utf8mb4 for a limit of 16MB. For a 4GB limit, change to LONGTEXT. If the mail is not forced into some character encoding, then use MEDIUMBLOB or LONGBLOB (without a charset).
Specifically for objects, there is a DMV called sys.dm_exec_describe_first_result_set_for_object which will describe the first result set if SQL Server can figure out what it should be (dynamic SQL, for example won't return a valid result). Specifically for T-SQL or batch related items there is a different DMV and accompanied system stored procedure. The ...
You should be able to use the new system stored procedure sp_describe_first_result_set for that - see the MSDN docs for details: EXEC sp_describe_first_result_set N'YourStoredProcedureNameHere'
If you use Openquery to insert the results of the stored procedure into a temp table, you can query the columns for the temp table. Note, you cannot use a @variable in Openquery, so you will need to run it through dynamic sql and an exec. The problem with that is now the temp table doesn't exist outside the dynamic query. But, if you put your temp table ...
Pay attention that the correct time zone (UTC in your case) is applied during the conversion. If are not explicit about this, the time zone of the current session is assumed - typically not UTC. ALTER TABLE tbl ALTER ts_column TYPE timestamptz USING ts_column AT TIME ZONE 'UTC'; Check a possible column default for sanity. Any explicit timestamp expression ...
Normalization is often a good idea, either for flexibility or space. But Normalizing "continuous" values, such as DATE, DATETIME, FLOAT, etc, is generally a mistake. Don't do it. Perhaps the biggest problem occurs when you decide to filter on a date range, and you find the JOIN is killing performance. Even if you had fewer than 255 dates (I'm thinking of ...
Seems to me that is a classic example of over-thinking your design. Keep the dates in the same table so they can be easily indexed with other columns if necessary, and not take up 3 times as many bytes, and not make life a living hell for future developers.
Top 50 recent answers are included