Tag Info

New answers tagged

3

I will start with a link: What Every Programmer Should Know About Floating-Point Arithmetic. In short, float-point arithmetic types, like the float and double mysql types should never be used for precise arithmetic. And your col mod 0.1 is trying to do exactly that, a precise arithmetic check. It is trying to find if the value in col is an exact multiple of ...


5

Store timestamps as timestamp, or rather timestamptz (timestamp with time zone) since you are dealing with multiple time zones. That enforces valid data and is typically most efficient. Be sure to understand the data type, there are some misconceptions floating around: Time zone storage in PostgreSQL timestamps Ignoring timezones altogether in Rails and ...


4

You should always store data in it's native datatype so you can use the built-in functions. And the data type of a timestamp is obviously a timestamp. Btw, a timestamp is not stored as a string, it's stored as an 8-byte integer, exactly the same as bigint: PostgreSQL documentation.


2

You have an unresolved naming conflict. You must be using an old version of Postgres without declaring it. Or you are operating with non-default configuration setting. Here you declare a variable named measurement_id: DECLARE measurement_id INTEGER; It's a folly to use ambiguous variable names to begin with. If you do it anyway, you ...


5

Is there any way I can speed this up? Yes. Don't use a varchar column for an integer number. Use integer or bigint if you burn that many IDs - much smaller in table and index and faster to process. Since you are ranking 10 million rows in your test, this is going to make a substantial difference. player_id VARCHAR(200) NOT NULL, player_id int NOT ...


0

You need to grant EXECUTE on package itself. If security is your main concern you may create a new package that has just types, but you cannot specify that a given user can access only some objects defined in package specification - either all or none.


2

CREATE VIEW or CREATE TABLE... AS SELECT have to infer the column types for the relation to create. Sometimes the context is not sufficient to guess a datatype, for example when it's just a string literal. In this case it's created as unknown. Example : test=> CREATE VIEW testview AS SELECT 'bla' AS foobar; WARNING: column "foobar" has type "unknown" ...


2

If the path is being stored in a SQL_VARIANT field, then you might as well CAST / CONVERT to NVARCHAR(4000) on the way out as that is the most that can be stored by a SQL_VARIANT. While the typical upper limit for a path is 260 characters, it is possible to exceed that and go up to 32,767 characters (even if that is unlikely to happen in practice). Since ...



Top 50 recent answers are included