Microsoft SQL Server has what I consider a remarkably sensible function, try_cast() which returns a null if the cast is unsuccessful, rather than raising an error. This makes it possible to then use a CASE expression or a coalesce to fall back on. For example: SELECT coalesce(try_cast(data as int),0); The question is, does PostgreSQL have something similar? The question is asked to fill in some gaps in my knowledge, but there’s also the general principle that some prefer a less dramatic reaction to some user errors. Returning a null is more easily taken in one's stride in SQL than an error. For example SELECT * FROM data WHERE try_cast(value) IS NOT NULL;. In my experience, user errors are sometimes better handled if there is a plan B.

I have a table with an index and a row-level security policy. Due to [this problem](https://dba.stackexchange.com/q/232789/188406) (more details: (https://stackoverflow.com/q/48230535/1048572) , (https://www.postgresql.org/message-id/flat/2811772.0XtDgEdalL@peanuts2) , (https://stackoverflow.com/q/63008838/1048572) , (https://www.postgresql.org/message-id/flat/CAGrP7a2t+JbeuxpQY+RSvNe4fr3+==UmyimwV0GCD+wcrSSb=w@mail.gmail.com) , (https://stackoverflow.com/q/48230535/1048572)) , the index is not used when the policy applies, which makes my queries unbearably slow. The workaround I am contemplating would be to create a VIEW with security_invoker = false and security_barrier = false. (If I do enable the security_barrier, the query again doesn't use the index). The problem I am facing now is that I cannot just change the queries to use FROM my_view AS example instead of FROM my_table AS example, since some of them use functions that are defined to take the my_table composite type. A simplified example: CREATE TABLE example ( id int, name text, is_visible boolean ); CREATE VIEW test AS SELECT * FROM example WHERE is_visible; CREATE FUNCTION prop(e example) RETURNS text LANGUAGE SQL AS $$ SELECT e.id::text || ': ' || e.name; $$; SELECT e.prop FROM example e; -- works SELECT e.prop FROM test e; -- ERROR: column e.prop does not exist ([online demo](https://dbfiddle.uk/cb0bn3NV)) Now the question is **how to cast the rows to the expected type?** There is [this question](https://dba.stackexchange.com/q/247240/188406) and I also found a way to do this using the ROW constructor, but I'm not certain how good this is: SELECT e.prop FROM (SELECT (ROW(test.*)::example).* FROM test) e; It's nice that I can just use it as a drop-in replacement for the table expression (without changing anything else in the query), and it does work (postgres accepts it and does use my index when I have the respective WHERE clause), but it looks horrible. Are there problems with my approach that I am missing? Is there a better solution?

Currently I have a strange problem and I need clarification (for my mind). Problem description: Today I found a stored procedure which runs two statements. First it drops a table and later it recreates this table again with a SELECT INTO statement. The stored procedure is part of our BI nightly runs and today throws a exception. The exception which was thrown was: 8115 Arithmetic overflow while convert numeric to numeric. --- Normaly when this kind of error happens we have a value which is to big to fit in a smaller datatyp and we try to cast the value to this datatyp. For example lets say we have the value 12345,12 and we want to cast this to decimal(6,2). This wont work and throw this error. Normaly when this happens I do this

ANSI_WARNINGS, ARITHABORT OFF;

and search the result for NULL values to further analyse the values. But this time there are no null values. And the second interessting thing is, if I remove the INTO part of the SELECT INTO statement there is no error. So this exception only occurs when the statement is executed as SELECT INTO. If the same statement is executed as SELECT the error is gone. --- I have tried multible things now to narrow this: * I have tried to change the cast statement to a try_cast statement **and this works** (also with the SELECT INTO (and with no NULLs in the result) -> So the try_cast does exactly what we want the cast to do. Is there a difference? * I have tried to convert the value to varchar and find the decimal sepperator position of each value to check if there is a value which is bigger than expected (which is not the case). -> The highest number I get was 10 so we have 9 digites to the left of the comma and we use decimal(12,3) here, which should be fine. * I have tried to change the cast from decimal(12,3) to decimal(13,3) **which works too** but make no sense to me because the maximum decimal seperator is 9. --- To fix the problem I now have changed the cast to a try_cast and the procedure is working again, so no time preasure for me. **BUT** I want to understand what happens here, because otherwise I think my brain will explode. --- Some sample information: I try to give you a smaller test SQL Statement because the BI Statement is relativ big. So the SELECT INTO Statement in the procedure looks something like this:

SELECT	cast(isnull(t1.IstGesamt / nullif(t1.SollGesamt, 0) - 1, 0) as decimal(12,3)) as AbwGesamt
	INTO	[dwh_global].[FaktAuftragszeit]
	FROM (
		SELECT	(a.ZEIT_JE_EINHEIT*a.AUFTRAGSMENGE) + a.RUESTZEIT as SollGesamt,
				a.BEARBEITUNGSZEIT_IST as IstGesamt
		FROM		 [ods_am].a
				join [ods_am].b ON (...)				
        WHERE b.auftragsart  'WAR'	
		 ) t1

The valuetypes are: * a.ZEIT_JE_EINHEIT -> numeric(8,3) * a.AUFTRAGSMENGE -> numeric(10,3) * a.RUESTZEIT -> numeric(8,3) * a.BEARBEITUNGSZEIT_IST -> numeric(11,3) The maximum value I get when I change the CAST to TRY_CAST is 450948999.000 (and no NULL Values). So this should be the maximum value and I think I should be able to cast 450948999.000 to decimal(12,3), correct? --- Is here maybe someone which knows what this can be and point me to the correct direction? Do I have forgoten something to analyse? As I mention I found a workaround but I want to understand this issue. Thank you in advance! --- UPDATE (Add Execution Plan): Plan for SELECT INTO Plan for SELECT Looks kind of similar expect the parallelism steps. The selected step is the one which filters the Auftragsart.

b.auftragsart  'WAR'

--- UPDATE (Add Execution Plan as XML) Here we have the Execution Plans as XML uploaded to Paste the Plan. First the one for the SELECT. Paste the Plan - Select And the second one is the one for the SELECT INTO. Paste the Plan - Select Into

I am trying to debug a slow query, and when I EXPLAIN the SQL, I get a warning:

Warning: #1739 Cannot use ref access on index '[index name]' due to type or collation conversion on field '[field name]'

The query is quite complex, so I have made an example setup that gets the same warning, the SQL is below, as well as a db fiddle (although the db fiddle doesn't show the warning, so I don't know much help that is!). The original query is

EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t2.ext_id=t1.id WHERE 1;

I also tried

EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t2.ext_id=CAST(t1.id AS CHAR) WHERE 1;

but that doesn't allow the index to be utilized, and still has the same warning. Is there any way I can utilize the index in this LEFT JOIN? https://www.db-fiddle.com/f/s89hwBtMSvcUnipKfadsuM/1

CREATE TABLE t1 (
  id int NOT NULL,
  name varchar(255) NOT NULL
) ENGINE=InnoDB;



INSERT INTO t1 (id, name) VALUES
(1, 'n1'),
(2, 'n2');


CREATE TABLE t2 (
  id int NOT NULL,
  ext_id varchar(255) NOT NULL,
  position varchar(255) NOT NULL
) ENGINE=InnoDB;


INSERT INTO t2 (id, ext_id, position) VALUES
(1, '1', 'p1'),
(2, '2', 'p2');

ALTER TABLE t1
  ADD UNIQUE KEY id (id);

ALTER TABLE t2
  ADD KEY ext_id (ext_id) USING BTREE;

Edit: Using MySQL 5.7

Using SQLite 3, I have some view columns which are defined with an expression, and so have no type affinity. Is it possible to define them in a way that forces them to have a certain type affinity? e.g. if I have a tables t1, t2 and a view definition create view v as select coalesce(t1.c1, t2.c1) c1 from t1, t2 where X; is there some way of giving v1.c1 a type affinity? I've tried casting it select cast(coalesce(t1.c1, t2.c1) as INTEGER) c1 but that doesn't seem to work.

How do I find where the contents of one field are within another field? I have a database table with a auto-generated sequence number in one field, and another character based field which should contain a string which describes a path to a file. The path should be based on the sequence in the other field, sometimes it isn't and I want to identify these rows. This is query I've been trying (among others) but it doesn't work, *position()* is obviously designed to find a hard-coded string within a field, not another field SELECT recordId, path FROM TABLEA WHERE position(recordID IN path) = 0; RecordId is defined as: recordId integer not nulll default nextval('tablea_recordid_seq'::regclass) And path is: character varying(80) I can't help feeling that the correct solution is probably very simple, but damned if I can find it! Ok, this works ... SELECT recordId, path FROM TABLEA WHERE position(CAST(recordID AS CHAR) IN path) = 0;

I've created this two functions, and I want to know if there is a better way to cast json arrays to polygons without having to install the PostGIS extension. Raw type: ((-34.888733,-57.956764),(-34.92303,-57.99367),(-34.953579,-57.95255)) polygon_to_json: ["-34.888733,-57.956764","-34.92303,-57.99367","-34.953579,-57.95255"] json_to_polygon: ((-34.888733,-57.956764),(-34.92303,-57.99367),(-34.953579,-57.95255))

create or replace function json_to_polygon(p_poly json, out p_out polygon) returns polygon
parallel safe
returns null on null input
immutable
language plpgsql
as $$
begin
    if(p_poly is null) then
        return;
    end if;

    select
        polygon(concat('((', string_agg(x, '),('), '))'))
    from
        json_array_elements_text(p_poly) x
    into
        p_out;
end;
$$;


create or replace function polygon_to_json(p_poly polygon) returns json
parallel safe
returns null on null input
immutable
language plpgsql
as $$
begin
    if(p_poly is null) then
        return null;
    end if;

    return to_json(string_to_array(replace(replace(p_poly::text, '((', ''), '))', ''), '),('));
end;
$$;

How can I convert a BIT(256) into 64-byte BYTEA? More generally, how can I convert from BIT(n) to BYTEA when n is large enough that I can't cast the bits through an INT8? Example: I've got a bunch of sha256 hashes stored in a table:

CREATE TABLE foo(sha256 CHARACTER(64) NOT NULL); -- hex encoded sha256 digests
INSERT INTO foo (sha256) VALUES 
   ('e643a277fab67cca43d9348623c7551d7382784033a697eb7f5484178be7c53d')
 , ('73483f797a83373fca1b968c11785b98c4fc4803cdc7d3210811ca8b075d6d76')
 , ('b2b91baab6785f6bc6bc43c3a72f700b6bc64c478b744c539c80a857f55b00ca')
 , ('ba8cf9b1f2827f46d45b5061a65a11e93b849174185af9d92508a38b4df04945')
 , ('7507a8f88da14e9bdd5acc310ad260943bc788249a49de210205c1b537f41406')
 , ('868ca27b41d804c4877275595b4a2cf5d85559d6805693f95b68a09a69dc8634')
 , ('f10480d4b52a000b8fd8643147396b869f7c11727623023525b4ca041242356e')
 , ('53ce065eaa1e2f10eb86d10d07f8c748ce482bea9897018862f91cd142bb1eb9')
 , ('4e5d320a6c34bea34f1746bf8a85d00cf77d6d8a3b52d8649c93b2282d3f64e6')
 , ('9f1b957647c30fdd05ad319092226143e437965014235343113dc8dd6a58dda7');

CREATE TABLE bar AS
  SELECT ('x' || sha256)::BIT(256) AS sha256
  FROM foo;

/* Doesn't work:
SELECT sha256::BYTEA FROM bar; -- cannot cast type bit to bytea
*/

Notes: I got the ('x' || str)::BIT(n) trick from https://stackoverflow.com/questions/17208945/whats-the-easiest-way-to-represent-a-bytea-as-a-single-integer-in-postgresql/17214093#17214093

I am trying to solve The Blunder challenge under MySQL database and I am running into an error in what I thought to be a correct answer: `SELECT CEIL(AVG(Salary) - AVG(CAST(REPLACE(CAST(Salary AS VARCHAR(255)), '0', '') AS DECIMAL(14,2)))) AS avg_error FROM Employees;` In plain words, I tried converting the Salary (which is originally of a number type) into a varchar, remove any 0s appearing in it, convert it back to a number. I then calculated the average of the error and took a ceiling of it. However, when I run the code, I get the following error message: `Compiler Message Runtime Error Error (stderr) ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VARCHAR(255)), '0', '') AS DECIMAL(14,2)))) AS avg_error FROM Employees' at line 1 Your Output (stdout) ~ no response on stdout ~` I don't know where I made a mistake. Could you tell me how I can fix this, and if there is another way of a solution?

nvarchar(100) holds 100 **byte-pairs**, as [documented here](https://learn.microsoft.com/en-us/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql?view=sql-server-ver16#remarks) . LEFT([Your String], 100) truncates your string down to 100 **characters** as [documented here](https://learn.microsoft.com/en-us/sql/t-sql/functions/left-transact-sql?view=sql-server-ver16) . These are not the same. Foolishly, I have used LEFT([Your String], 100) in the hopes of keeping [Your String] within nvarchar(100). What are the idiomatic approaches to solving this problem correctly? I considered using CAST, but I'm uncomfortable with implicitly truncating a string.

I have a varchar column in a table in SQL Server that holds a **Base64-encoded** text string, which I would like to decode into its **plain text** equivalent. Does SQL Server have any **native** functionality to handle this type of thing? Here is a sample base64 string: cm9sZToxIHByb2R1Y2VyOjEyIHRpbWVzdGFtcDoxNDY4NjQwMjIyNTcxMDAwIGxhdGxuZ3tsYXRpdHVkZV9lNzo0MTY5ODkzOTQgbG9uZ2l0dWRlX2U3Oi03Mzg5NjYyMTB9IHJhZGl1czoxOTc2NA== which decodes to: role:1 producer:12 timestamp:1468640222571000 latlng{latitude_e7:416989394 longitude_e7:-738966210} radius:19764

It looks like postgres treats the string 'now()' the same as a call to the now() function. Why does postgres allow this?

select 'now'::timestamp;

or this?

select 'now()'::timestamp;

or even this?

select '  (  (  ))) now)('::timestamp;

It does not give this "a string is the same as a function" treatment to other functions such as clock_timestamp(). [DB fiddle](https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/11195)

How can I cast an array of texts into an array of UUIDs? I need to do a join between two tables: users and projects. The users table has an array field named project_ids containing the project IDs as text. The projects table had a UUID field named id. My initial idea was a query looks like: SELECT * FROM projects JOIN users ON projects.id = ANY(users.project_ids) But that does not work since users.project_ids are not UUIDs so I tried: projects.id = ANY(users.project_ids::uuid[]) and even: projects.id = ANY(ARRAY[users.project_ids]::uuid[]) but neither one works: > ERROR: invalid input syntax for type uuid: "" UPDATE @a_horse_with_no_name is definitely right. The best option should be using an array of UUIDs. The question now is how can I alter an array of text into an array of uuid? The users table is currently empty (0 records). I have tried ALTER TABLE "users" ALTER COLUMN "project_ids" SET DATA TYPE UUID USING "project_ids"::uuid[]; which generates ERROR: result of USING clause for column "product_ids" cannot be cast automatically to type uuid HINT: You might need to add an explicit cast. > ALTER TABLE "users" ALTER COLUMN "product_ids" SET DATA TYPE UUID > USING "product_ids"::UUID; I have also tried ALTER TABLE "users" ALTER COLUMN "project_ids" SET DATA TYPE UUID[] USING "project_ids"::uuid[]; which generates > ERROR: default for column "project_ids" cannot be cast automatically > to type uuid[] The column is set to an empty array as default. I'm running PG version 10.4 and project_ids is currently text[] nullable.

we are using a PostgreSQL version 15 and have added 13 conversions from this blog entry: http://petereisentraut.blogspot.com/2008/03/readding-implicit-casts-in-postgresql.html However, only now we have the problem when we want to join a string e.g. with a number: SELECT 'Test: '||123.45 FROM TEST The SQL returns the error here: > ERROR: operator is not unique: unknown || integer If we remove the conversion "double percision --> text", the SQL works. However, we need the conversions elsewhere. We actually want to avoid using CAST(), ::VARCHAR or ::TEXT, as this affects a large number of queries. Does anyone have any ideas on how we can solve the problem?

While I can

SELECT
  elem, elem
FROM
  (
    VALUES ('1,2'::TEXT)
  ) AS q(arr),
  LATERAL CAST(String_To_Array(q.arr, ',') AS INT[]) AS elem
;

using an explicit call to CAST, I *can't*

SELECT
  elem, elem
FROM
  (
    VALUES ('1,2'::TEXT)
  ) AS q(arr),
  LATERAL String_To_Array(q.arr, ',')::INT[] AS elem
;

using the implicitly calling :: operator: > ERROR: syntax error at or near "::" One other location at which an explicit CAST is required:

CREATE INDEX ON ... ( CAST( AS ) );

` I doubt there is a syntactical reason, e.g. using extra enclosing parenthesis - which is incorrect here. Is the explicit function call simply needed at this point as part of the low level implementation? Or does it follow any language rules?

If I have a table as per https://www.db-fiddle.com/f/arPEdUty3U6AJEQfqYEyRc/1 with a list of releases:

INSERT INTO releases (ReleaseID, ReleaseNumber) VALUES
(1, '10.6'),
(2, '10.8'),
(3, '_TBD'),
(4, '10.9'),
(5, 'Future'),
(6, '10.10'),
(7, '10.11');

How can I sort it so that things starting with _ come first, then numbers, ordered as 10.6, 10.8, 10.9, 10.10, 10.11, then anything else? My current casting attempt (CAST(ReleaseNumber as SIGNED INTEGER) puts Future after _TBD instead of at the end. The desired output of this example would be _TBD, 10.6, 10.8, 10.9, 10.10, 10.11, Future

According to PostgreSQL documentation on the ARRAY constructor : > By default, the array element type is the common type of the member > expressions, determined using the same rules as for UNION or CASE > constructs (see Section 10.5). **You can override this by explicitly > casting the array constructor to the desired type, for example**: SELECT ARRAY[1,2,22.7]::integer[]; array ---------- {1,2,23} (1 row) This works for converting elements to integers, and for simple types: SELECT ARRAY[ 'name', 1]::text[]; array ---------- {name,1} (1 row) But the casting does not seem to work for complex elements such as array elements: SELECT ARRAY[ 'name', 1, ARRAY['world']]::text[]; ERROR: malformed array literal: "name" LINE 1: SELECT ARRAY[ 'name', 1, ARRAY['world']]::text[]; ^ DETAIL: Array value must start with "{" or dimension information. My question is: *Is there a way to make automatic conversion work for text[]?* (i.e. make the above conversion work without having to write SELECT ARRAY[ 'name'::text, 1::text, ARRAY['world']::text];)

This is the error message I get when running the query below: > Msg 6335, Level 16, State 102, Line 43 XML datatype instance has too > many levels of nested nodes. Maximum allowed depth is 128 levels.

SELECT *, cast(plantext as xml)
  FROM [my_database].[dbo].[the_PlanHistory]
  where servername='myServer'
  and procname='MyProcedure'
  order by cachedtime desc

I know the problem is related to the cast to xml, how can I fix it, or work around it?

Need calculation and conversion help... Columns: [Days] INT [hh:mm:ss] Time [AllowedRuntimeMinutes] INT Need to combine [Days]+[hh:mm:ss] as the duration the query has been running, and then calculate the elapsed percentage over the allowed run time. So possibly some form of ([Days]+[hh:mm:ss] as Duration)-AllowedRuntimeMinutes/AllowedRunTimeMinutes*100 ?

In Snowflake, how do I CAST CURRENT_TIMESTAMP up to the minute only. I don't want seconds, milliseconds, or timezone included in my result.