In Postgres I'm trying to automatize the creation of a non-existing db-user (using it in Debian's postinst script) in the following way: SELECT 'CREATE USER :v1' WHERE NOT EXISTS (SELECT FROM pg_user WHERE usename = ':v1')\gexec and calling via psql -v v1="flop" This certainly dosen't work, as :v1 is taken as username string itself and is not substituted. The following error is returned: > LINE 1: CREATE USER :v1 Modifying the line to ... 'CREATE USER ':v1' ' WHERE NOT ... returns: > Syntax error at >>' ' > LINE 1: SELECT 'CREATE USER 'flop' ' WHERE NOT EXISTS (SELECT FROM ... The variable :v1 is substituted, but it seems to me, the whole command, beginning from SELECT, isn't accepted. Changing by adding some single quotes according to some guidelines, to: SELECT 'CREATE USER ':'v1'' ' WHERE NOT EXISTS ... returns > Syntax error at 'flop' > > LINE 1: CREATE USER 'flop' Which looks at me as the substring CREATE USER produces the error due to quotation error. After adding some extra space between the single quotes like SELECT 'CREATE USER ':'v1' ' ' WHERE ... the query returns the following error: > Syntax error at >>' ' > LINE 1: SELECT 'CREATE USER ''flop' ' ' WHERE ... When removing the forst variable :v1 and putting the username explicitly, like SELECT 'CREATE USER flop' WHERE NOT ... everything works together with the variable substitution at usename=. So is seems to me it's "just" a quotation problem. But how cat I remove the single quotes and get the script working?

I have a SQL agent job that runs nightly at 2am successfully for the last year; We haven't made any change to that job specifically but it suddenly gives error: > MERGE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operation I read around and looks like a quick fix is to insert the command "SET QUOTED_IDENTIFIER ON" at the very top of this job. However, I really want to know why this happens? the job does not call any store procedure, instead it uses merge, insert, update using some views, tables. I verified all my views and tables have quoted_identifier set to true which I believe is the default value. I ran the following script and I see that my SQLAgent - Job Invocation Engine has quoted_identifier set to off. SELECT * FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND quoted_identifier = 0 ; This perhaps explains why my job fails? But why it fails all of a sudden with the same job that ran successful the night before? Could a script that adds new index or alter a store procedure caused this to quoted_identifier value to false? can a database restart do it?

I wanted to create a view that sums a table common to a number of schemas, as expressed [here](https://dba.stackexchange.com/questions/333273/can-pl-pgsql-function-take-a-variable-number-of-parameters/333311#333311) , and includes the schema name or a derived expression as a column, so I created a view that includes the schema name as a constant value, by using the quote_literal to wrap the schema name in single quotes. The quote_literal may not be the right function here, but whatever I do gets wrapped in the double quotes. CREATE OR REPLACE FUNCTION create_summary_view_quoted( created_view text, common_view text, VARIADIC schemas text[] ) RETURNS text LANGUAGE sql AS $$SELECT format('CREATE VIEW %I AS ', created_view) || string_agg( format('SELECT %I as source_schema, * FROM %I.%I ', quote_literal(schemas[i]) , schemas[i], common_view), ' UNION ALL ' ) FROM generate_series(1, cardinality(schemas)) AS i$$; ") Applying it to the query SELECT create_summary_view_quoted('newv', 'oldv', 's1', 's2', 's3'); produces create view newv as select "'s1'" as source_schema, * from s1.oldv union all select "'s2'" as source_schema, * from s2.oldv union all select "'s3'" as source_schema, * from s3.oldv The problem is the double-quote around the single-quoted constant which causes Postgres to treat is as a column name and it barfs. I fixed the code generation issue by using double single quotes around the first placeholder and dropping the quote_literal function, i.e. replace format('SELECT %I as source_schema, * FROM %I.%I ', quote_literal(schemas[i]) , schemas[i], common_view), with format('SELECT ''%I'' as source_schema, * FROM %I.%I ', schemas[i] , schemas[i], common_view), My issue is not so much with quote_literal but how the function always wraps the output of any function which wraps a single-quoted string with double-quotes. Even when I used a function to strip off a leading suffix in the schema names because they are redundant the single quoted output gets wrapped in double quotes. eg quote_nullable(right(schemas[i],-10)) the resulting string gets double-quoted. I could apply the || operator somewhere there but I prefer a quoting function. Doesn't Postgres have a built-in single quoting function? Is there some required SQL syntax I'm missing? Is it a Postgres quirk?

I'm trying to create a user that only has access to one database. It's telling me database doesn't exist but clearly it does by following. postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges -----------+----------+----------+---------+---------+------------+-----------------+----------------------- GarsDB | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc | postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc | template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc | =c/postgres + | | | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc | =c/postgres + | | | | | | | postgres=CTc/postgres (4 rows) postgres=# grant all privileges on database GarsDB to gars; ERROR: database "garsdb" does not exist postgres=# grant all privileges on database garsdb to gars; ERROR: database "garsdb" does not exist postgres=# Any ideas what could be going on? I've only been using Postgres recently.

Section 5.6 of the SQL-92 standard contains rules 10...13 per which unquoted identifiers should be upper-cased, so foo becomes FOO but "foo" remains foo. These rules are respected by Oracle , IBM DB2, Snowflake , and ksqlDB but not by Postgres , MySQL or SQLite, for example. The question is, why? In my understanding, the optional quoting of identifiers in a language with a lot of keywords makes sense. Consistent case sensitivity or insensitivity of identifiers would also make sense. But making it dependent on the identifier being quoted doesn't look rational. What am I missing?

I have a tbl_users, where there are a few columns like TUM_First_Name,TUM_Last_Name and so on. However, when I try an insert query, an error says :- column "tum_first_name" of relation "tbl_users" does not exist Here is a photo of the schema :- Update :- Surprisingly, after I renamed all the column names to lower cases, and called this same query, it worked. So, when my table had new column names as tum_first_name,tum_last_name, etc., running the query INSERT INTO tbl_users(TUM_First_Name,TUM_Last_Name,)... worked fine What is this ambiguity ?

I was designing a project which specifies that in a table what column name should be and one of the column name in the specification is "group". I tried creating it but it always throw a syntax error near the word = "group". I am really curious since the keyword in SQL is "group by" not group, so what is the reason, I cannot rename or create a column with a name "group". Syntax and error I am using and getting:

ALTER TABLE test RENAME COLUMN sum TO group;
ERROR:  syntax error at or near "group"
LINE 1: ALTER TABLE test RENAME COLUMN sum TO group;

Would like to be able to add characters like '-' in the schema name when running COPY command in PostgreSQL. Any way to get around this?

psql -d postgres -c "\COPY (SELECT * FROM test-schema.tableName) TO data.csv DELIMITER ',' CSV"

>

> ERROR:  syntax error at or near "-"enter code here
> LINE 1: COPY  ( SELECT * FROM test-schema.tableName ) TO STDOUT DELIMITER ',...
>

I would like to unserstand why I am not able to drop a database from cmd: I execute the command: drop database if exists but the result is as below: postgres=# drop database testV5; ERROR: database "testV5" does not exist knowing that the database exists: postgres=# \l Liste des bases de donnÚes Nom | PropriÚtaire | Encodage | Collationnement | Type caract. | Droits d'accÞs testV4 | postgres | UTF8 | French_Canada.1252 | French_Canada.1252 | testV5 | postgres | UTF8 | French_Canada.1252 | French_Canada.1252 | postgres | postgres | UTF8 | French_Canada.1252 | French_Canada.1252 | template0| postgres | UTF8 | French_Canada.1252 | French_Canada.1252 | template1| postgres | UTF8 | French_Canada.1252 | French_Canada.1252 | Can anyone explain that please ? Thanks in advance.

I have successfully created a table in pgAdmin which generated the code: CREATE TABLE public."Test3" ( "PID" integer, "Name" character varying(20), PRIMARY KEY ("PID") ) TABLESPACE pg_default; ALTER TABLE public."Test3" OWNER to postgres; However when I try to query the table with: Select * from Test3; I get the error:

ERROR:  relation "test3" does not exist 
LINE 1: Select * from Test3;
                      ^ 
SQL state: 42P01 Character: 15

Same thing happens if I leave out the TABLESPACE line. Same thing happens if I try to query a table after importing rows from a .csv file. Seems like I must be missing something obvious. Edit: I am trying to create the table in a database named "ABR"

I used pg_restore to load my postgres db with a dump file. I connected to my db with my user : sudo -u arajguru psql dump select current_user; current_user -------------- arajguru Now I was able to see all the newly created tables: dump=> \dt List of relations Schema | Name | Type | Owner --------+-------------------+-------+---------- public | Approvals | table | arajguru public | Approvers | table | arajguru public | Conditions | table | arajguru public | Entities | table | arajguru public | EntityDefinitions | table | arajguru public | Projects | table | arajguru public | Rules | table | arajguru public | run_history | table | arajguru (8 rows) But when I try to fire a select * query, it gave me this error: dump=> select * from Approvals; ERROR: relation "approvals" does not exist LINE 1: select * from Approvals; What can be the reason for this error? Please help.

In brief, I added a computed column to a table and then Agent jobs began failing and reporting the error: > UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934). An earlier question suggests that stored procedures must be created with QUOTED_IDENTIFIER set ON. In my case, however, there are no stored procedures. One example Agent job has one step and that contains straight SQL to carry out two updates - each update is against the table I altered. None of that SQL contains a double-quote character (which is the subject of QUOTED_IDENTIFIER). Microsoft documentation says: > SET QUOTED_IDENTIFIER must be ON when you are creating or changing > indexes on computed columns or indexed views. ... however I simply added the column (persisted). I have not created or altered any indexes. 1. Why am I getting this error in this scenario? 2. How do I resolve this? Do I need to re-create all the Agent jobs, switching QUOTED_IDENTIFIER ON before I do? Or do I need to switch QUOTED_IDENTIFIER ON before altering the table to add the computed column? Possibly related, the following SQL shows which objects in the database had QUOTED_IDENTIFIER ON at compile time (ie. which will be interpreted with that value when used). From this I see that triggers are also affected by this setting. Perhaps the Agent job update is invoking a trigger which is somehow related? I do note, however, that all objects are listed as uses_quoted_identifier = 1. In other words, if the resolution was to re-create objects while setting QUOTED_IDENTIFIER ON prior to re-creating them, I don't see what difference it would make; all objects already seem to have been created that way. SELECT uses_ansi_nulls, uses_quoted_identifier, name, * FROM sys.sql_modules -- WHERE object_id = object_id('SampleProcedure') left outer join sys.sysobjects on sql_modules.object_id = sysobjects.id

I´ve 2 questions. I am using a T-SQL Server 2014. When creating a table with a filtered index on a T-SQL server, it is a must-have to set QUOTED_IDENTIFIER to ON. Why is this so? I´ve some SPs which have set quoted_identifiers to OFF, my question is, if I call the SP with QUOTED_IDENTIFIER ON, is it possible to inject these with values which have doublequotes inside? The SP makes an insert and just uses the parameters. As I understand it, it should not take an effect if QUOTED_IDENTIFIER is set to ON or OFF. This is my first SP which calls the second: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO DECLARE @PreviousVersion bigint IF @EntryTime IS NULL BEGIN SET @EntryTime = GETUTCDATE() END IF @ID = 0 BEGIN EXEC dbo.GetNextDataID 'Object', @ID OUTPUT SET @PreviousVersion = 0 END ELSE BEGIN SET @PreviousVersion = @Version END EXEC dbo.GetNextVersion 'Object', @Version OUTPUT EXEC dbo.Insert_Object @ID, @Version, @PreviousVersion, @deleted, @Parent The second looks like this: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET NOCOUNT ON; IF ISNULL(@ID, 0) = 0 BEGIN RAISERROR('Die ID ist 0 oder leer.', 11, 1) RETURN END IF ISNULL(@Version, 0) = 0 BEGIN RAISERROR('Die Version ist 0 oder leer.', 11, 2) RETURN END IF @EntryTime IS NULL BEGIN SET @EntryTime = GETUTCDATE() END UPDATE dbo.ObjectTable SET ID = @ID INSERT INTO dbo.ObjectTable ( ID , Version , PreviousVersion , deleted , Parent) VALUES ( @ID , @Version , @PreviousVersion , @deleted , @Parent) If I add the filtered index without setting QUOTED_IDENTIFIER to ON, it raises an error.

I am running into some strange problem in PostgreSQL $ sudo -u postgres psql Blogging psql (10.10 (Ubuntu 10.10-0ubuntu0.18.04.1)) Type "help" for help. Blogging=# \dt List of relations Schema | Name | Type | Owner --------+-----------------------+-------+------- public | Blogs | table | e public | Posts | table | e public | __EFMigrationsHistory | table | e (3 rows) Blogging=# select * from Blogs Blogging-# ; ERROR: relation "blogs" does not exist LINE 1: select * from Blogs ^ The same problem occurs when I log in as the owner of the tables: $ psql -U e Blogging psql (10.10 (Ubuntu 10.10-0ubuntu0.18.04.1)) Type "help" for help. Blogging=# \dt List of relations Schema | Name | Type | Owner --------+-----------------------+-------+------- public | Blogs | table | e public | Posts | table | e public | __EFMigrationsHistory | table | e (3 rows) Blogging=# select * from Blogs; ERROR: relation "blogs" does not exist I created the database when running my application which uses Entity Framework, following https://learn.microsoft.com/en-us/ef/core/get-started/?tabs=netcore-cli . But I had some problem with the application, not sure if they are related to my question here.

I want to generically use any SQL database(I'm using jdbc, it works if the driver and the URL are provided). It seems, that different DBMS use different quoting for table names. For example, I have the following in MySQL: SELECT * FROM tablename; while it is SELECT * FROM [tablename]; in MS SQL Server. Is there an "uniform" quoting that all databases support? Or is there a jdbc method that gives me tells me the correct quoting?

I am trying to execute the below statement which creates a database called service2019: DECLARE @dbname VARCHAR(50); SET @dbname = 'service' + CAST(YEAR(GETDATE()) AS VARCHAR(4)); EXEC ( 'CREATE DATABASE ' + @dbname + ' ON PRIMARY ( NAME =' + '' + @dbname +'' + ', FILENAME = ''C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014TEST\MSSQL\DATA\''' + '''' + @dbname + '''' + '''.mdf''' + ', SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 100KB ) LOG ON ( NAME =' + '' + @dbname + '_log' + ', FILENAME = ''C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014TEST\MSSQL\DATA\''' + '''' + @dbname + ''''+'''.ldf''' + ', SIZE = 10MB , MAXSIZE = 2048GB , FILEGROWTH = 100KB )' ) I get the correct database name and logical names. The problem is I get wrong physical names: 'service2019'.mdf 'service2019'.ldf Could someone explain to me how these quotes actually work? There are some parts where it is clear but sometimes it becomes a challenge to figure out where to put those quotes.

Is there a way to catch a QUOTED_IDENTIFIER error without failing the transaction? I have assembled a minimal test case here. The trigger must have SET QUOTED_IDENTIFIER ON because it uses XML data processing to concatenate the message it builds. In my particular case, the database where it was installed - the script did not SET QUOTED_IDENTIFIER ON because the trigger was not installed by my normal installation process. I'd like to know if there is a way to trap this without failing the transaction. In normal operations, I don't want any errors in the trigger to fail transactions, because it is solely designed to construct a message and send it to the service broker for later processing, and should not interfere with operations on the table otherwise. SET NOCOUNT ON; GO IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'TestTable') BEGIN DROP TABLE dbo.TestTable ; END GO CREATE TABLE dbo.TestTable ( ID int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED ,Name varchar(50) NOT NULL ); GO INSERT INTO dbo.TestTable (Name) VALUES ('Test 1'), ('Test 2'); GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER dbo.QuoteTest ON dbo.TestTable AFTER INSERT, UPDATE, DELETE AS BEGIN SET NOCOUNT ON; SET XACT_ABORT OFF; BEGIN TRY DECLARE @ChangeTemplate nvarchar(max) = N'{Type}{Name}{Value}'; DECLARE @ChangeMsg XML; WITH ChangeRows AS ( SELECT [Type] = CASE WHEN inserted.ID IS NULL THEN 'D' WHEN deleted.ID IS NULL THEN 'I' ELSE 'U' END ,[Name] = 'ID' ,[ID] = COALESCE(inserted.[ID], deleted.[ID]) FROM inserted FULL OUTER JOIN deleted ON inserted.[ID] = deleted.[ID] ) SELECT @ChangeMsg = (SELECT REPLACE(REPLACE(REPLACE(@ChangeTemplate , '{Type}', [Type]) , '{Name}', [Name]) , '{Value}', [ID]) FROM ChangeRows ORDER BY ID FOR XML PATH(''), TYPE).value('.', 'varchar(max)') ; PRINT 'Message Built'; PRINT CAST(@ChangeMsg AS varchar(max)); END TRY BEGIN CATCH PRINT 'Catch Error in Trigger'; PRINT ERROR_MESSAGE(); END CATCH END GO UPDATE TestTable SET Name = Name; GO IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'TestTable') BEGIN DROP TABLE dbo.TestTable ; END GO CREATE TABLE dbo.TestTable ( ID int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED ,Name varchar(50) NOT NULL ); GO INSERT INTO dbo.TestTable (Name) VALUES ('Test 1'), ('Test 2'); GO SET QUOTED_IDENTIFIER OFF GO CREATE TRIGGER dbo.QuoteTest ON dbo.TestTable AFTER INSERT, UPDATE, DELETE AS BEGIN SET NOCOUNT ON; SET XACT_ABORT OFF; BEGIN TRY DECLARE @ChangeTemplate nvarchar(max) = N'{Type}{Name}{Value}'; DECLARE @ChangeMsg XML; WITH ChangeRows AS ( SELECT [Type] = CASE WHEN inserted.ID IS NULL THEN 'D' WHEN deleted.ID IS NULL THEN 'I' ELSE 'U' END ,[Name] = 'ID' ,[ID] = COALESCE(inserted.[ID], deleted.[ID]) FROM inserted FULL OUTER JOIN deleted ON inserted.[ID] = deleted.[ID] ) SELECT @ChangeMsg = (SELECT REPLACE(REPLACE(REPLACE(@ChangeTemplate , '{Type}', [Type]) , '{Name}', [Name]) , '{Value}', [ID]) FROM ChangeRows ORDER BY ID FOR XML PATH(''), TYPE).value('.', 'varchar(max)') ; PRINT 'Message Built'; PRINT CAST(@ChangeMsg AS varchar(max)); END TRY BEGIN CATCH PRINT 'Catch Error in Trigger'; PRINT ERROR_MESSAGE(); END CATCH END GO UPDATE TestTable SET Name = Name; GO Gives the following output: Message Built UID1UID2 Catch Error in Trigger SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. Reviewed this, but not sure where QUOTED_IDENTIFIER fits in the possible error modes: http://www.sommarskog.se/error_handling/Part2.html

I have pgAdmin 4 and PostgreSQL 10. I can't execute a query without quote marks i.e. I want to excute this select * from table but it only works if I use select * from databasename."table" How can I remove this restriction? The name of the table is Complexitie. I was using postgresql the last year and I remember I could write queries in a simple way without mandatory syntax things like case senstive, mandatory quotes. I read something about to setup arbitrary SQL queries en Postgres, but I can not find steps to configure it. thanks