I am trying to see if a specific column exists in any functions/sequences. I have found a script which can search tables, is there anything out there that would allow me to search in functions/sequences (generally anywhere ideally)?

select t.table_schema,
       t.table_name
from information_schema.tables t
inner join information_schema.columns c on c.table_name = t.table_name 
                                and c.table_schema = t.table_schema
where c.column_name = 'product_id'
      and t.table_schema not in ('information_schema', 'pg_catalog')
order by t.table_schema;

The above can show me views/tables that have a product_id column. Currently working with Postgres 12. Can't seem to find one that would list for functions/sequences.

I don't understand why these functions behave differently. The fact that they have a different arity shouldn't affect my ability to quote them.

janus@janus-ux305ca ~ % psql
psql (14.11 (Debian 14.11-1.pgdg110+1))
Type "help" for help.

janus=> select least(1,2);
 least 
-------
     1
(1 row)

janus=> select "least"(1,2);
ERROR:  function least(integer, integer) does not exist
LINE 1: select "least"(1,2);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
janus=> select "count"(1);
 count 
-------
     1
(1 row)

What is the correct terminology that explains the difference in behaviour?

There is a table in my database that starts with the @ symbol. I am able to query it successfully using the following query: select * from [@tablename] This format works in SSMS and Crystal Reports. However, I have a C# application where the same query give an 'invalid object name' error on the table name. Any suggestions?

When you have a high number of ids, but they aren't used anymore. Should you reset the id of the table? And does it have any performance difference?

In this draft of SQL:1992 , I found… Section 5.2 > Leveling Rules > > .... > > 2. The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: > a) No ` or **shall contain more than 18 `s**. …and… > Annex A.1 > 1. Subclause 5.2, "` and `": > a) In conforming Intermediate SQL language, a ` or a **shall not comprise more than 18 `s.** > b) A ` shall not be a or a `. > c) Conforming Intermediate SQL language shall contain no ` that ends in an `. ## For *implementors* of SQL, or for *users* of SQL? I searched the document multiple times, but could never get a sense of the meaning of identifier. - Is the spec referring to labeling within an *implementation* of SQL (such as PostreSQL or Oracle)? So labels used within the SQL product cannot contain identifiers that are over 18 characters or end in an underscore. - Or is the spec referring to *users* of a SQL implementation? So we who are naming our schemas, tables, columns, and other such database objects cannot use names over 18 characters or end in an underscore?

I'm trying to return a product of two subqueries, and thought I'd make life easier by aliasing each of the subqueries, then divide the two aliases to get the number I'm after, like this: `select distinct (select SUM(pest.AREA_HA) from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest where ORG_UNIT_NO IN (1904, 1830,1831, 1902)) as Ha_IN_RKB, (select SUM(pest.AREA_HA) from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest) as Ha_total, ROUND( (Ha_IN_RKB / Ha_total) ,3) from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest` but this results in a 00904. 00000 - "%s: invalid identifier" error (Oracle doesn't like Ha_IN_RKB or Ha_total ). Re-writing the query as follows works, but is rather bulky: `select distinct (select SUM(pest.AREA_HA) from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest where ORG_UNIT_NO IN (1904, 1830,1831, 1902)) Ha_IN_RKB, (select SUM(pest.AREA_HA) from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest) Ha_total, ROUND( ((select SUM(pest.AREA_HA) from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest where ORG_UNIT_NO IN (1904, 1830,1831, 1902)) / (select SUM(pest.AREA_HA) from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest)) ,3) as RKB_pct from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest` Anybody know why I can't just make this ROUND( (Ha_IN_RKB / Ha_total) ,3) work ?

For example, if I'm creating view with a name '4aii', why does SQL Server care that it starts with a 4? I could call the table Fouraii or IVaii. Additionally, what does [] do behind the scenes to allow for any string to be used as a name? A string's a string, amirite?

My Postgres database is getting slow while executing some function. But another database of identical design and with more data is much faster than the first one. I have recently migrated data from MS SQL to PostgreSQL. So I have checked if there is any problem from following queries: SELECT relname AS TableName, to_char(seq_scan, '999,999,999,999') AS TotalSeqScan, to_char(idx_scan, '999,999,999,999') AS TotalIndexScan, to_char(n_live_tup, '999,999,999,999') AS TableRows, pg_size_pretty(pg_relation_size(relname :: regclass)) AS TableSize FROM pg_stat_all_tables WHERE schemaname = 'loan' AND 50 * seq_scan > idx_scan -- more then 2% AND n_live_tup > 10000 AND pg_relation_size(relname :: regclass) > 5000000 ORDER BY relname ASC; And I got the result: > ERROR: relation "mv_transaction_view" does not exist > SQL state: 42P01 What is the solution for this? I have a materialized view named mv_transaction_view.

I referenced a few other blogs and I can't isolate the issue to this logic. Code is below. Throws the error message attached. -- 1. Import Multiple Delimited Text Files into a SQL Database -- 1.1 Define the path to the input and define the terminators USE [Openair_Integration] DECLARE @Path NVARCHAR(255) = 'C:\\Users\nicolas.gutierrez.su\Downloads\\' DECLARE @RowTerminator NVARCHAR(5) = CHAR(13) + CHAR(10) DECLARE @ColumnTerminator NVARCHAR(5) = CHAR(9) -- 1.2 Define the list of input and output in a temporary table IF OBJECT_ID('[dbo].[Files_Temporary]', 'U') IS NOT NULL DROP TABLE [dbo].[Files_Temporary]; CREATE TABLE [dbo].[Files_Temporary] ( [ID] INT , [FileName] NVARCHAR(255) , [TableName] NVARCHAR(255) ); INSERT INTO [dbo].[Files_Temporary] SELECT 1, 'booking.csv', 'dbo.booking' -- 1.3 Loop over the list of input and output and import each file to the correct table DECLARE @Counter INT = 1 WHILE @Counter 0 BEGIN SET @CreateHeader = @CreateHeader + '[' + LTRIM(RTRIM(SUBSTRING(@Header, 1, CHARINDEX(@ColumnTerminator, @Header) - 1))) + '] NVARCHAR(255), ' SET @Header = SUBSTRING(@Header, CHARINDEX(@ColumnTerminator, @Header) + 1, LEN(@Header)) END SET @CreateHeader = @CreateHeader + '[' + @Header + '] NVARCHAR(255)' SET @SQL_CreateHeader = 'CREATE TABLE [' + @TableName + '] (' + @CreateHeader + ')' EXEC(@SQL_CreateHeader) END ------------------------------------------------------------------------------------------------------------------------------------------------------------- PRINT 'Inserting data from ''' + @FileName + ''' to ''' + @TableName + '''.' DECLARE @SQL NVARCHAR(MAX) SET @SQL = ' BULK INSERT [dbo].[' + @TableName + '] FROM ''' + @Path + @FileName + ''' WITH ( FIRSTROW = 2, MAXERRORS = 0, FIELDTERMINATOR = ''' + @ColumnTerminator + ''', ROWTERMINATOR = ''' + @RowTerminator + ''' )' EXEC(@SQL) SET @Counter = @Counter + 1 END; -- 1.4 Cleanup temporary tables IF OBJECT_ID('[dbo].[Files_Temporary]', 'U') IS NOT NULL DROP TABLE [dbo].[Files_Temporary]; IF OBJECT_ID('[dbo].[Header_Temporary]', 'U') IS NOT NULL DROP TABLE [dbo].[Header_Temporary];

I'm using SQL Server Management Studio 17.4, which supports search-and-replace using a Microsoft version of regular expressions (regex). I need a regex expression that can reliably remove square brackets from source code, such as large CREATE TABLE statements that have been scripted from existing an database. I'm currently using this regex in the "find" dialog: \[((?!\d+)(?!PRIMARY))(([A-Z]|_|[0-9])*?)\] The "replacement" is $2. It seems to work very well, however I'm concerned this may eliminate required square brackets under some conditions. In the regex above, the (?!PRIMARY) piece ensures ON [PRIMARY] does not have the square brackets removed. Are there other exceptions I need to be aware of? I'm willing to exclude the possibility of column names being reserved words, such as [GROUP]. The environment I work in has specific naming convention that typically eliminates that possibility.

In CSS, the more HTML elements you specify, the more precedence the selector gets. This is known as "specificity." For example, div p span {...} Is more specific than, and thus would override: span {...} In SQL, one can start a query like: SELECT database.table.columnOne, database.table.columnTwo FROM ... The same query can be expressed, less specifically, as: SELECT columnOne, columnTwo FROM ... Is there a proper term in SQL for being more specific in queries, or is it just called "specificity" like in CSS?

I realize I'll need to update the queries, but if I have a database that has [camelCase](https://en.wikipedia.org/wiki/Camel_case) or spaces in it, you have to use double quotes on the identifiers (viz. schema, table, column). How do I migrate away from taking into account that I can't have capital letters, nor spaces in my identifiers. I need to normalize all of them to [snake_case](https://en.wikipedia.org/wiki/Snake_case) .

I'm just wondering if there is an established method for testing if a string can be used as an *unquoted* PostgreSQL identifier? (unquoted because almost any string can be a quoted identifier). I ask because as shown in a previous question (https://dba.stackexchange.com/questions/200419) , there are times when I would need to specify an identifier (such as the name of a table to be created) that does not yet exist, as string values (text) instead of a safer type such as regclass. Quoting the string/name can be problematic as shown there and probably else where. Without quoting, it's susceptible to SQL injection. I guess if one programs it hard enough, a string parsing function can be written ultimately. Just wanted to check if there are existing solutions. Related: https://dba.stackexchange.com/questions/45589 https://dba.stackexchange.com/questions/196015 (sql-server)