I use hdparm to test my SSD hard drive I/O benchmark : [root@mail ~]# hdparm -t -T /dev/xvda2 /dev/xvda2: Timing cached reads: 12906 MB in 1.99 seconds = 6477.79 MB/sec Timing buffered disk reads: 1650 MB in 3.00 seconds = 550.21 MB/sec I saw innodb_io_capacity parameter for MariaDB is : Storage type The innodb_io_capacity value HDD 200 RAID 10 HDD 1,000 SSD 5,000 Fusion IO 10,000 Could some body please tell me which value i should use in this case ?

Assume I have a text file with tab terminated fields as below. Where value in row m, column n as vmn. Vmn is a string which could wrapped in double quota. Generally this string variable could have any readable char such as '\' or '"', such as

"Bob the "king""TAB"c:\path\to\file"TAB

Here TAB is tab char '\t' They are not as

"Bob the \"king\""TAB"c:\\path\\to\\file"TAB

header1 header2 header3 ,,headerk,,,headern
v11     v12     v13     ,,v1k    ,,,v1n


...
vm1     vm2     vm3     ,,vmk    ,,,vmn
...

Now I also have a table as create as

|column1  |column2  |,,|columnv  |,,,|columnw  |
------------------------------------------------
t1        t2         ,,tv         ,,,tw

w is not equal to n, w could larger than or smaller than n. tk = function(vi1, vi2,...) Let us look at a real sample of text file.

"Bob the "King""      01/01/1010     "$100"     "1000"      "12/12/1090"
"Jenny the Queen"     11/11/1030     "$10.2"    "100"       "11/02/1070"

I use a slow way to load the data into table as

CREATE TABLE IF NOT EXISTS STATE
(
     KeyID INT UNSIGNED auto_increment primary key,
     Names               VARCHAR(32),
     DOBs                VARCHAR(16),
     Incomes             VARCHAR(16),
     Propertys           VARCHAR(16),       
     Deaths              VARCHAR(16),
     Name                VARCHAR(16),
     Title               VARCHAR(16),
     Ages                INT,
     Income              Double,
     Property            Double,
     DOB                 Date,
     Death               Date,
     Age                 INT,
     Value               Double 
);

LOAD DATA LOCAL INFILE 'state.tsv' IGNORE INTO TABLE STATE
COLUMNS TERMINATED BY '\t'  OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(
 Names,
 DOBs,
 Incomes,
 Propertys,       
 Deaths,
) 
SET
   Name   = SUBSTRING(Names, 0, POSITION("the" IN Names)),INSTR (ori_str, sub_str)
   Title  = CASE 
               WHEN INSTR (Names, "King") THEN "King" 
               WHEN INSTR (Names, "Queen") THEN "Queen" 
               ELSE "No Title" 
            END,
   DOB    = STR_TO_DATE(case when DOBs='' then null else DOBs end, '%m/%d/%Y' ),
   Death  = STR_TO_DATE(case when Deaths='' then null else Deaths end, '%m/%d/%Y' ),
   Income = NULLIF(Incomes,''),
   Property = NULLIF(Propertys,''),   
   Age = Death - DOB,
   Value = MyFunction(Income, Property);

Then I run an alter sql to drop columns like Names, Titles,DOBs, Deaths, Incomes, Propertys. Here MyFunction is a user function to calculate the value. This method is not smart and slow. I would like to have temp variables to hold the strings, then convert them in to table columns. No extra columns to drop afterwards. Any suggestion?

In windows mysql(5.7 version), I want to run mysql commmand using command line for .sql file as input . Also i was to pass some variable whose value should get used in .sql file. somehow I am not able to achieve it. mysql -u root -p xyz -e "set @tmp='abc'; source d:/v_test1.sql" Here I want to file v_test1.sql having mysql code and also variable @tmp being used in v_test1.sql. But getting error: > MySQL Error: Unknown column '@tmp' in 'field list' I think issue might be because of version. Please suggest some solution.

I am currently trying to update/modify a number of SQL queries on a system that does not have any internal Documentation (The developer left on short notice and on bad terms). I have enabled Query logging in Postgres and have tracked down the Queries in question to be in a number of approximately 30 queries. From there, I plan on running them and seeing what the output is, and then determining which ones need the updates. The problem is that all of them in the log are in this format (Simplified) Select t.Col1, t2.Col2, t3.Col3 from Table t join table2 t2 on t2.Col1 = t.Col1 join table3 t3 on t3.Col1 = t2.Col3 where t.Col1 = $1 and t2.Col3 = $2 ... and t3.Col20 = $15 etc. For some of the parameters, I've been able to guesstimate what the most likely values are (based on the info in the column it looks up), however there are a few that I've got no idea on what they could be. I'm not super-familiar with Postgres (more of an MSSQL man myself) nor am I familiar with using parameters/variables in queries. It seems to me these values must be either generated somewhere or held somewhere and must be evaluated at some point in order to run a valid query. As such, either logging the values or logging the input or something similar should be possible so I can then finish what is required. Hopefully someone can advise how to get the 'real' values of the variables $1,$2, $3 etc.

I tried to do this: COPY postgres_log FROM $1 WITH csv; And with a separately sent file path, like I make all normal queries (SELECT/UPDATE/INSERT/DELETE). However, it gives: ERROR: syntax error at or near "$1" Um... So does that mean that I'm forced to send it a scary string like this? COPY postgres_log FROM '/full/path/to/logfileblablabla.csv' WITH csv; If so, why? Why don't all query types support the parameterized queries interface? Isn't this like *asking* to enable SQL query injection vulnerabilities?

How can external parameters be passed to a SSRS 2016 mobile report in order to pre-select values / settings for the report? The same scenario is possible for paginated reports as it always was. It's one of the key functionalities. In my scenario I need to integrate the reports into an existing application. So some settings like USERID or LANGUAGE must be passed to the integrated report to initialize it correctly. It's not tolerable to expect a user of the application to choose a language every time he/she runs a report inside of the application... Besides setting parameters for the datasets within the mobile report I could not find any option to take values from external / url...? Any solutions?

I'm trying to find the proper syntax to do the following: SELECT a, b, c FROM foo WHERE foo.id = :id; (the query above is written as in SQLite). The "normal" query would be written as: SELECT a, b, c FROM foo WHERE foo.id = ?; The first query is written with a named parameter (:id); the second with unnamed (?). Searching the internet gives me the syntax for stored procedures and/or functions, but what I am looking for is a SELECT query with a named parameter. When I write my C++ code I will be using BindParameter() ODBC syntax. For the first query in MS SQL Server, what syntax is correct? --- As I understand the comment responses, the proper SQL Server syntax is: SELECT a, b, c FROM foo WHERE foo.id = @id; This means I will need to declare a variable named @id, bind it to the prepared query, then execute it. The question is specifically about SQL Server. My question is independent of the client tool. I am using a plain SELECT query, not a stored procedure or a function. What I am looking for is how to properly write such a query (not procedure or function) for SQL Server so the DB engine will understand it. --- My full code will look like this: std::string query = "SELECT a, b, c FROM foo WHERE foo.id = @id;"; SQLExecute( stmt, "DECLARE @id" ... ); SQLPrepare( stmt, query.c_str()... ); SQLBindParameter(); SQLExecute(); Let me give an example of what I'm looking for. If I have a query like the following: IF NOT EXISTS ( SELECT * FROM WHERE table_name = ? AND table_schema = ?) INSERT INTO (table_name, table_schema, param1, param2,...) VALUES ( ?, ?, ...); With SQLite and PostgreSQL I can write it like this: IF NOT EXISTS ( SELECT * FROM WHERE table_name = ? AND table_schema = ?) INSERT INTO (table_name, table_schema, param1, param2,...) VALUES ( ?1, ?2, ...); Can I do something similar with SQL Server and ODBC? Or I will have to write repeating code for every parameter I'm passing to the query? In fact, SQLite can refer to a parameter either by name or by number in the query. Not sure if either way exists in SQL Server.

So there is a lot of stuff about using SSRS parameters to select multiple values but nothing about using both a parameter and a fixed value using the in select statement. So what I'm trying to do is this: select * from table where id in (Parameter_1, 'total') Yet when I do that it complains its can't find 'Parameter_1'. I've gotten another report to work when its just: select * from table where id = Parameter_2 but this this isn't good enough in this use case.

In my Postgres 14 database, I have a function that updates the updated_at field in a table for which the trigger was fired:

CREATE TEMPORARY TABLE types(
    id SMALLINT GENERATED ALWAYS AS IDENTITY,
    type TEXT UNIQUE,
    updated_at TIMESTAMPTZ
);

CREATE OR REPLACE FUNCTION update_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER column_was_updated BEFORE INSERT OR UPDATE OF type
ON types FOR EACH ROW EXECUTE PROCEDURE
update_column();

INSERT INTO types (type)
VALUES ('type1'), ('type2');

SELECT * FROM types;

I want to do the following: when calling the function from the trigger, pass a column name as an argument to the function to replace the literal column name updated_at, something like this:

CREATE OR REPLACE FUNCTION update_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.column_name = now();
    RETURN NEW;
END;
$$ language 'plpgsql';


CREATE TRIGGER column_was_updated BEFORE INSERT OR UPDATE OF type
ON types FOR EACH ROW EXECUTE PROCEDURE
update_column('column_name');

How can I achieve this?

I have seeing a strange behaviour with PostgreSQL13 pg_settings system view. I tried updating max_connections using SET command and left without a restart. But if I query from the same session or any new session the pending_restart column in pg_settings is not showing true for this parameter. However if I issue a statement like I attached in the screenshot then pending_restart column shows correctly as `true. What's this behaviour related to?

**PLS-00306: wrong number or types of arguments in call to 'string'** > Cause: This error occurs when the named subprogram call cannot be matched to any declaration for that subprogram name. The subprogram name might be misspelled, a parameter might have the wrong datatype, the declaration might be faulty, or the declaration might be placed incorrectly in the block structure. For example, this error occurs if the built-in square root function SQRT is called with a misspelled name or with a parameter of the wrong datatype. > > Action: Check the spelling and declaration of the subprogram name. Also confirm that its call is correct, its parameters are of the right datatype, and, if it is not a built-in function, that its declaration is placed correctly in the block structure. How do I quickly identify the wrong argument? I have a stored-procedure with dozens of parameters. Is there an easy way to check the differences between the used and defined procedure? I don't want to check it line by line..

I'm wondering if this is possible in Postgres: Best explained using a contrived example: create or replace function test_function(filter_param1 varchar default null , filter_param2 varchar default null) returns integer as $$ declare stmt text; args varchar[]; wher varchar[]; retid integer; begin if filter_param1 is not null then array_append(args, filter_param1); array_append(wher, 'parameter_name = $1'); end if; if filter_param2 is not null then array_append(args, filter_param2); array_append(wher, 'parameter_name = $2'); end if; stmt := 'select id from mytable where ' || array_to_string(wher, ' or '); execute stmt into retid using args; return retid; end; $$ language plpgsql; In Python there is *args - perhaps PostgreSQL has a similar mechanism? EDIT for Erwin Brandstetter questions: - All filter parameters will be applied to different columns, but should be AND'ed. - Returning setof makes much more sense here. - All parameters can be of the same column type (ie. varchar).

I have some sql procedure (MSSQL 2019) if relevant

Create PROCEDURE [dbo].[test] @tmp varchar(32)  AS
print @tmp

why when i do

EXEC	@return_value = [dbo].[test]
		@tmp = N'SSL99999999999999999999999999999900000006785999999999999999'

I se it use 32 chars substring in @tmp ? SSL99999999999999999999999999999 And no exception that string or binary data would be truncated as i expected ? Can i turn on such behavouur ? i would need this if possible ;) Thanks and regards !

I came across a situation where I need expert opinion. Our application ingests data into table A in aurora mysql 3.02 every 5 seconds. This is a write intensive process. During this time, I am using DMS to replicate data for table B from a different source into the same aurora db where table A is located. After few seconds the application queue piles up with many files which are waiting to be written into table A. We have bumped up the instance from 2x - 8x and there is no luck. I cannot relate how writing data in table B affects data ingesting in table A and how do I approach this issue?

I have a database with multiple schemas which have the same tables and views, and I want to create a procedure that creates a view from one the views in the schema. CREATE OR REPLACE FUNCTION create_summary_view( created_view character varying, common_view character varying, schema_1 character varying, schema_2 character varying, schema_3 character varying, ... schema_x character varying, ) create_view is the view that will be created, common_view is the view that is identical in all the schemas, and schema_x are the list of schemas whose common_views are being joined into created_view. Does pl/pgsql have a syntax for indicating a variable number of parameters? created_view, common_view and at least one schema_x is required? An alternative I'm considering it to pass a comma or space separated string of the schemas and use regexp_split_to_array to break it up, but it would be good to know of variable length arguments are possible.

Given this setup in current Postgres 9.4 (from this related question ): CREATE TABLE foo (ts, foo) AS VALUES (1, 'A') -- int, text , (7, 'B'); CREATE TABLE bar (ts, bar) AS VALUES (3, 'C') , (5, 'D') , (9, 'E'); *dbfiddle [here](https://dbfiddle.uk/?rdbms=postgres_13&fiddle=1cbb193db0f3662c4192ec82b0f5e609)* (also from the previous question). I wrote a SELECT with a FULL JOIN to achieve the objective of the referenced question. Simplified:

SELECT ts, f.foo, b.bar
FROM   foo f
FULL   JOIN bar b USING (ts);

As per specifications, the correct way to address the column ts is without table qualification. Either of the *input* values (f.ts or b.ts) can be NULL. The USING clause creates a bit of an odd case: introducing an "input" column that's not actually present in the input. So far so elegant. I put this in a plpgsql function. For convenience (or requirements) I want the same column names for the result of the table function. So we have to avoid naming conflicts between identical column names and function parameters. Should best be avoided by picking different names, but here we are:

CREATE OR REPLACE FUNCTION f_merge_foobar()
  RETURNS TABLE(ts int, foo text, bar text)
  LANGUAGE plpgsql AS
$func$
BEGIN
   FOR ts, foo, bar IN
      SELECT COALESCE(f.ts, b.ts), f.foo, b.bar
      FROM   foo f
      FULL   JOIN bar b USING (ts)
   LOOP
      -- so something
      RETURN NEXT;
   END LOOP;
END
$func$;

Bold emphasis to highlight the **problem**. I can't use ts without table qualification like before, because plpgsql would raise an exception (not strictly necessary, but probably useful in most cases): > ERROR: column reference "ts" is ambiguous > LINE 1: SELECT ts, f.foo, b.bar > ^ > DETAIL: It could refer to either a PL/pgSQL variable or a table column. I know I can use different names or a subquery or use another function. But I wonder if there's a way to reference the column. I can't use table-qualification. One would think there ***should*** be a way. Is there?

Reading about the Data Type of parameters inferred when the Forced Parameterization is set, the official documentation doesn't mention either datetime or uniqueidentifier. It seems that these two data types cannot be inferred from natural string formats. Can anyone confirm this?

I can set log_statement parameter to 'all' on the current connection. I want to do it for a few other connections on my database that are controlled by a connected web application. I know that I can execute

database my_db set log_statement = 'all'

, but it will take effect after the application restart. Is it possible to switch that parameter on behalf of an application connections without restarting it?

I use the query store to get an overview of executed queries on our databases and to generate performance tests where I execute those queries in parallel on multiple hosts. My task is to test how many concurrent users we can sustain before database response times increase. In all databases that I looked at with query store, the XML query plans included a tag "ParameterList" from which I read out all the parameters for the query. Now I stumbled across a database where not a single query captured in the query-store has its parameter values listed in the query plan. What is be the reason for that? If its relevant: The database in question is used by Microsoft Dynamics. Server is 13.0.4259.0. Compatibility Level of the DB is 2016(130') The queries in the Query Store look like this: (@0 int,@1 nvarchar(20),@2 int)SELECT "39"."timestamp",...... WITH(READUNCOMMITTED) ON ("39"."Document Type" = "39_e1"."Document Type") AND ("39"."Document No_" = "39_e1"."Document No_") AND ("39"."Line No_" = "39_e1"."Line No_") WHERE ("39"."Document Type"=@0 AND "39"."Document No_"=@1 AND "39"."Line No_"=@2) OPTION(OPTIMIZE FOR UNKNOWN)

I have a stored procedure with an INOUT parameter, which value is modified inside the SP. I need to call that SP, providing a variable as that parameter, and then use the modified variable. But the tricky part is that I need to call the SP using dynamic SQL. When I do that, the variable's value is not changed: CREATE OR REPLACE PROCEDURE pr_test( INOUT p_rows INTEGER DEFAULT NULL ) AS $body$ BEGIN RAISE NOTICE 'Inside SP, p_rows before modify = %', p_rows; p_rows := 100; RAISE NOTICE 'Inside SP, p_rows after modify = %', p_rows; RETURN; END $body$ LANGUAGE 'plpgsql' ; Trying to use it... DO $$ DECLARE v INTEGER = 7; v_sql TEXT; BEGIN RAISE NOTICE 'v before SP call = %', v; v_sql := 'CALL pr_test(p_rows => $1);'; EXECUTE v_sql USING v; RAISE NOTICE 'v after SP call = %', v; END;$$; And the output is: v before SP call = 7 Inside SP, p_rows before modify = 7 Inside SP, p_rows after modify = 100 v after SP call = 7 Please advise on how to do it right.