Hot answers tagged

27

SELECT (count(refinst) * 100)::numeric / NULLIF(count(*), 0) AS refinst_pct -- count(refinst) * 100.0 / NULLIF(count(*), 0) AS refinst_pct -- simpler FROM patients; Do not use a subselect. Both aggregates can be derived from the same query. Cheaper. Also, this is not a case for window functions, since you want to compute a single result, and not one ...


24

SELECT (ctid::text::point)[0]::bigint AS page_number FROM t; Your fiddle with my solution. @bma already hinted something similar in a comment. Here is a ... Rationale for the type ctid is of type tid (tuple identifier), called ItemPointer in the C code. Per documentation: This is the data type of the system column ctid. A tuple ID is a pair (block ...


24

Figured it out: SELECT CONVERT ( VARCHAR(MAX), CAST('' AS XML).value('xs:base64Binary(sql:column("BASE64_COLUMN"))', 'VARBINARY(MAX)') ) AS RESULT FROM ( SELECT 'cm9sZToxIHByb2R1Y2VyOjEyIHRpbWVzdGFtcDoxNDY4NjQwMjIyNTcxMDAwIGxhdGxuZ3tsYXRpdHVkZV9lNzo0MTY5ODkzOTQgbG9uZ2l0dWRlX2U3Oi03Mzg5NjYyMTB9IHJhZGl1czoxOTc2NA==' ...


20

This does not work because it's trying to cast a jsonb value to integer. select data->'name' as name from persons where cast(data->'age' as int) > 25 This would actually work: SELECT data->'name' AS name FROM persons WHERE cast(data->>'age' AS int) > 25; Or shorter: SELECT data->'name' AS name FROM persons WHERE (data->>'age')::int > 25; ...


16

This is an interesting finding. Normally, a NULL has no assumed data type, as you can see here: SELECT pg_typeof(NULL); pg_typeof ─────────── unknown This changes when a VALUES table comes into the picture: SELECT pg_typeof(core) FROM ( VALUES (NULL) ) new_values (core); pg_typeof ─────────── text This behaviour is described in the source ...


13

Try this instead: CREATE INDEX user_reputation_idx ON users(cast("user"->>'reputation' AS int)); The Postgres syntax shortcut :: for casts is not allowed without additional parentheses in an index definition (see @bma's comment). It works with the standard SQL function, though: cast(expression AS type) This is not related to the json type per se. ...


11

The (formerly) accepted answer iswas incorrect as it iswas a bad and misleading test. The two queries being compared do not do the same thing due to a simple typo that causes them to not be an apples-to-apples comparison. The test in the accepted answer is unfairly biased in favor of the CAST operation. The issue is that the CONVERT operation is being done ...


11

1. chr(10) ... produces the LINEFEED character (a.k.a. escape sequence \n) and psql displays the character with a newline (indicated by +). Everything correct there. 2. & 3. ascii() produces 128 or 192? It starts with a mistake I made. I carelessly assumed "char" would cover the range of an unsigned 1-byte integer (0 to 255) in the referenced answer (...


11

You can see that in a simpler test case here SELECT '>' || to_char(1, '0') || '<'; ?column? ---------- > 1< (1 row) This is because, as @Abelisto said, the space is reserved for the sign glyph, SELECT '>' || to_char(-1, '0') || '<'; ?column? ---------- >-1< (1 row) You can suppress the sign using FM, from the docs Modifier ...


9

While passing integer numbers, you can either cast the whole array: TG_ARGV::int[] Or you can cast an element, then it must be the element type: TG_ARGV[0]::int I used it that way in my answer to your previous question: SELECT in trigger function in two tables However, you are not passing integer numbers, but the text representation of integer arrays: an ...


9

You can't get more precision from an INT value than a whole number. You need to make them decimals before calculating: CREATE TABLE [grades] ([intGradeTransposeValue] INT, [txtCurrentSubjectReportName] NVARCHAR(50) ); INSERT INTO [grades] ([intGradeTransposeValue], [txtCurrentSubjectReportName]) VALUES (2,'latin'), (3, 'latin'); --Returns whole ...


9

Rationale It's hard to wrap something like SQL Server's TRY_CAST into a generic PostgreSQL function. Input and output can be any data type, but SQL is strictly typed and Postgres functions demand that parameter and return types are declared at creation time. Postgres has the concept of polymorphic types, but function declarations accept at most one ...


8

SELECT thing, sql_variant_property(thing,'basetype') AS basetype, sql_variant_property(thing,'precision') AS precision, sql_variant_property(thing,'scale') AS scale FROM (VALUES (2147483648)) V(thing) Shows you that the literal 2147483648 is interpreted as numeric(10,0). This behaviour pre-dates the introduction of the bigint in SQL ...


8

What you to do is CREATE CAST not an operator. This is the problem: SELECT pg_typeof(uuid), uuid = uuid::varchar AS eq FROM gen_random_uuid() AS t(uuid); ERROR: operator does not exist: uuid = character varying LINE 1: SELECT pg_typeof(uuid), uuid = uuid::varchar FROM gen_random... ^ HINT: No operator matches the given ...


7

CAST simply doesn't support the ability to do anything other than a generic conversion, without any flexibility other than inherent ones (e.g. language or dateformat settings, which have to do with how a date string is interpreted, not how much precision it has). Generic conversions of datetime -> string loses precision, regardless of the method. None of ...


7

Besides Gary's answer, I came up with the following: cast(NULLIF([expected_discharge_dttm],'') as datetime) Can anyone foresee an issue with this code (or Gary's use of CASE)?


7

If casting from one specific type to one other specific type is enough, you can do this with a PL/pgSQL function: create function try_cast_int(p_in text, p_default int default null) returns int as $$ begin begin return $1::int; exception when others then return p_default; end; end; $$ language plpgsql; Then select try_cast_int('42'...


6

You can wrap a CASE inside the CAST(), so the CAST() is only performed on valid numeric values - the other ones will result in NULL unless you put an ELSE in there somewhere. Also, your conversion error probably stems from the WHERE clause, where q.bar is implicitly converted to int in order to compare it to 20140401 (which is an int). This will probably ...


6

I think I've found the answer: I need to drop the default first, and then re-add it. ALTER TABLE logs ALTER COLUMN interface_type DROP DEFAULT; ALTER TABLE logs ALTER COLUMN interface_type TYPE interface_types USING interface_type::text::interface_types; ALTER TABLE logs ALTER COLUMN interface_type SET DEFAULT 'button';


6

What makes you think you are comparing dates? Actually, you are comparing string literals which - in the absence of a cast context and any explicit cast - default to text: SELECT '20150526' > '2015-05-26' AS text2text , '20150526'::date > '2015-05-26'::date AS date2date; text2text | date2date ----------+---------- t | f Try: SELECT ...


6

It selects the logical negation of 0 and returns it in the implicitly converted datatype. Rather than selecting 8 bytes containing all 0's, it selects 8 bytes containing all 1's, returning the maximum possible unsigned value.


6

Since time does not have a date component you need to provide a date. You can just add date and time values in the ALTER TABLE statement: ALTER TABLE students ALTER COLUMN time_since_missing_schedule_notification TYPE timestamp USING ('2000-1-1'::date + time_since_missing_schedule_notification)


6

From sql_variant (Transact-SQL) When sql_variant values of different base data types are compared and the base data types are in different data type families, the value whose data type family is higher in the hierarchy chart is considered the greater of the two values. The base data type family for @v is Exact numeric and the base data type family ...


6

If you have a simple b-tree index on mycolumn, then yes, you would need to avoid calling functions on that column in order to be able to use the index to filter rows. In this case, it would seem to make much more sense to convert your numeric literals to timestamps than to do the reverse SELECT * FROM MYTABLE WHERE my_column > to_timestamp( to_char(...


6

Shamelessly stolen from Craig's answer over SO. You need to create a cast to make the varchar -> macaddr coercion work automatically. It is slightly tricky, as there is no function that does exactly this, so we have to wrap an internal function into something we can use: CREATE TABLE mac (addr macaddr); INSERT INTO mac VALUES ('11:11:11:11:11:11'::...


6

The difference between the string->tsvector cast and the function-constructor to_tsvector(text) the cast (::) assumes the input is already a tsvector that's been casted to text. It does not use the language-specific stubbing mechanisms or store positional information, it simply assumes it is given a space-delimited text-search tokens (positions and ...


6

The question is a bit vague, but typically when you have Unicode characters and you end up with question marks or boxes, the problem is actually relatively simple. When you declare a Unicode string with 'single quotes', you need to prefix them with an N''. N stands for national, not nvarchar (admittedly, a U prefix - for Unicode - would have made more sense)...


6

The Microsoft Docs page for CAST and CONVERT provides a great matrix of conversions in SQL Server. The image included in the above page: As far as I know, there is no built-in mechanism for displaying a list of valid casts.


6

As detailed in the excellent Microsoft Documentation, conversions from text to varchar can indeed be implicit.


5

The CONCAT() function returns NULL when one of the arguments is null. You could use COALESCE() to bypass the issue, converting the nulls to empty strings - or to any other constant value, for example '00:00:00' for the time part: select concat( coalesce(cast(transactionDate as char), ''), ' ', coalesce(transactionTime, '00:00:...


Only top voted, non community-wiki answers of a minimum length are eligible