Questions tagged [catalogs]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0
votes
2answers
67 views

Are Cataloge and relation cache in PostgreSQL per connection or global to the server?

I am trying to figure out if Postgres' relation and catalog cache are per connection or per server? I did find that plan caches are per connection. It would make sense for the cache to exist for as ...
1
vote
1answer
121 views

Select FK constraints with ON UPDATE NO ACTION

We use Postgres 9.5. Is it possible to modify this script to show the ON UPDATE action for each foreign key reference at this table? Or get this values somehow? SELECT tc.table_schema, ...
1
vote
2answers
41 views

Does MySQL have a more private and complete catalog other then INFORMATION_SCHEMA?

Most databases provide the standardized INFORMATION_SCHEMA, however most also provide something else, In PostgreSQL we have the system catalog which is pg_catalog In SQL Server, you have the System ...
1
vote
0answers
98 views

SQLServer Linux: Error restoring backup of DB from Windows w/ full-text data file

I'm trying to move a Windows SQL Server database from Windows 10 to Linux. For this, I'm following the instructions in https://docs.microsoft.com/es-es/sql/linux/sql-server-linux-migrate-restore-...
1
vote
1answer
268 views

Where in pg_catalog is the database or catalog information stored about relations?

When I query a table like information_schema.tables SELECT table_catalog, table_schema, table_name FROM information_schema.tables; I get a result like this, table_catalog | table_schema ...
1
vote
1answer
89 views

Missing “public” schema when converting a “regclass” value to text

I am writing a script in which I need to parse the name of a table (in regclass). The parsing (with parse_ident()) works so far. However, the script fails when the table is in the public schema ...
1
vote
1answer
451 views

How do I list all columns for a specified table that begin with a specific string [duplicate]

How to select all the columns whose names begins with "img" from a PostgreSQL table? I wrote that, but doesn't work select 'img%' from mytable
1
vote
1answer
343 views

How to check if a PostgreSQL table is temporary?

How can I write a query to check if a PostgreSQL (10.x) table is a temporary table? I looked at the pg_class system table, but couldn't find any column about whether the relation is temporary. ...
3
votes
2answers
3k views

How to get the schema name of a table of type regclass in PostgreSQL?

In writing a function to test if a column col_name exists in a table _tbl, I'd like to extract the table's schema name, which is passed into the function as a regclass parameter (for security??). ...
3
votes
2answers
584 views

Is it a bad practice to query pg_type for enums on a regular basis?

In Postgres, I'm thinking of query pg_type for an up-to-date list of enumerations I'm using on a regular basis. I'd be using something like this: SELECT pg_type.typname AS enum_type, pg_enum....
2
votes
1answer
5k views

postgres system catalog query columns from table

I am building an integration test against an app that provisions a database for a user. The User created for it is not a super user, and does not have access to schema_information.tables because when ...
1
vote
1answer
2k views

How do I query INFORMATION_SCHEMA.COLUMNS on Amazon RDS?

I received a list of column names from another team. To ensure all field column names are valid inside a specific schema, I put them into a temporary table. I'm now wanting to see if they exist. When ...
3
votes
1answer
2k views

What is the significance of the principal_id in sys.schemas?

What is the meaning of the principal_id in sys.schemas and when would it ever be different from schema_id? 1> SELECT LEFT(name,20), schema_id, principal_id FROM sys.schemas; 2> GO ...
2
votes
1answer
206 views

How does the sys.objects Catalog View pull from itself? [duplicate]

I'm using this stored procedure called OBJECT_DEFINITION as was suggested. It's helping me wrap my head around the SQL Server system. However, there is one area that I can't yet figure out. On this ...
0
votes
1answer
4k views

Why am I getting “Invalid object name 'sys.sysobjvalues'.” [duplicate]

When I run SELECT OBJECT_DEFINITION(OBJECT_ID('sys.assembly_files')); I can see that there is a self-join on sys.sysobjvalues, but when I try to select that table I get, Msg 208, Level 16, State 1,...
2
votes
1answer
36 views

SQL Server Buffer Pool Extension in FullText Index Population

I have a server that has 32GB of RAM and SQL Server 2014 Enterprise installed over it. I want to try the impact of Buffer Pool Extension (BPE) on Full Text Population. I created a table and populated ...
6
votes
2answers
829 views

How do you directly modify the system catalogs in SQL Server 2017?

NOTE: I understand the risks involved, and the possibility of destroying a production system by doing this. I'm interested in doing it anyway. Whenever I try to play with system catalogs, I gets ...
4
votes
1answer
1k views

How list all the indexes along with their type (BTREE, BRIN, HASH etc…)

I'm querying to the system catalog in Postgresql 9.6.4 Getting a result set of tables and their indexes is straight forward,what I'm missing is the index type (BTREE, BRIN, etc..) I can't seem to ...
2
votes
0answers
773 views

SQL Server 2016 Database Full Text Catalog not being updated

I have a database with a Full Text Catalog enabled, however the Catalog hasn't updated since about 4 weeks ago. There are no errors in the event logs, the SQL Server logs or the last SQLFT log file, ...
0
votes
1answer
381 views

Amazon Postgres RDS: Update pg_attribute to update my custom attribute

I want to update my attribute to change its length. I am getting Error: Permission denied for relation pg_attribute. My SQL Query is like this: DO $$ BEGIN UPDATE pg_attribute SET ...
2
votes
1answer
716 views

DB2 syscat.routines vs. sysibm.routines

I need to select all routines names from a DB2 luw. I saw the following catalog tables: sysibm.routines syscat.routines If I run the following queries: select count(*) from sysibm.routines; select ...
1
vote
2answers
437 views

How do I navigate PostgreSQL documentation to find a list of all functions that work on a specific type?

Let's say I want to find a list of functions that can work on jsonb, what is the most effective way to do this from the terminal without using google? Assuming only access to man pages psql ...
1
vote
0answers
35 views

What are all the catalogs that RDBMS have to implement to be ANSI compliant?

My goal is to be able to go into any environment, and immediately get a general idea of what is going on in a database server. I know most RDBMS implement INFORMATION_SCHEMA, which has a lot of useful ...
1
vote
1answer
110 views

DB2 - cannot invalidate a view

I work on something and I need to check some views. I use this code: SELECT SUBSTR(T.TABNAME,1,32) AS TABLE, SUBSTR(T.TABSCHEMA,1,32) AS SCHEMA, T.STATUS, T.TYPE FROM SYSCAT.TABLES T WHERE T.TABNAME ...
6
votes
4answers
908 views

Oracle data dictionary, tell built-in roles apart from custom roles

In Oracle, if you describe the DBA_ROLES view, you only get three columns: SQL> describe dba_roles; Name Null Type ------------------------------------------------ ROLE ...
6
votes
1answer
7k views

Inner join on array column in Postgres

In Postgres 9.4, I'm trying to pull the names of all columns that are involved in a UNIQUE constraint for a given table in Postgres. It looks like the names of such columns are contained in ...
0
votes
1answer
2k views

PostgreSQL function thinks table reference exists when it does not

So I run this query: SELECT f_copy_tbl('parts','parts1'); ...while working on code based off of this answer and I get this error message: ERROR: relation "parts1" already exists pgAdmin III ...
1
vote
1answer
174 views

PostgreSQL Primary key disappears from test table

I have a somewhat convoluted scenario where a test table I had created with a primary key won't return the primary key. pgAdmin III reports there are no constraints. I have the entire PostgreSQL query ...
3
votes
1answer
5k views

PostgreSQL SELECT primary key as “serial” or “bigserial”

I've meshed together a way to determine what the data_type is as in the data_type you use in the syntax when creating a new table based off of the PostgreSQL wiki page. If there is something wrong ...
8
votes
3answers
19k views

How can I determine if a table exists in the current search_path with PLPGSQL?

I'm writing a setup script for an application that's an addon for another application, so I want to check if the tables for the other application exist. If not, I want to give the user a useful error....
2
votes
2answers
2k views

How do I list the constraints of a DOMAIN?

Consider the following scenario: CREATE DOMAIN dom_zipcode AS text; ALTER DOMAIN dom_zipcode ADD CONSTRAINT zipchk CHECK (char_length(VALUE) = 5); Now, if I want to drop that constraint with ALTER ...
2
votes
1answer
468 views

Full text catalog missing a keyword

We have a full text catalog on one of our databases but one keyword does not work. e.g: select * from table where name = 'jon' -- result set returns as expected select * from table where ...
1
vote
2answers
3k views

SQL Server 2012: Full Text Catalog taking up all the disk space

We have created full text catalog and index for a pretty large table. The column on which the index is being created has a datatype of varchar(max). The problem started as soon as we started ...
8
votes
7answers
15k views

Permission to view execution report in SSIS Catalog

We are currently using SSIS 2012. Is there any way for a user to view execution reports under SSIS Catalog without being ssis_admin or sysadmin? This is for production environment and we don't want ...
18
votes
5answers
122k views

Query to return output column names and data types of a query, table or view

Is there a PostgreSQL query or command that returns the field names and field types of a query, table or view? E.g., a solution if applied to simple SELECT query like SELECT * from person should ...
7
votes
3answers
8k views

Restore All Databases Script

I am migrating SQL Server DBs to a new instance. I have been told that it is possible to dynamically build a RESTORE script from available backups in the system catalog. Does anyone know of a sample ...
1
vote
1answer
156 views

Rebuild the Fulltext catalog

I have a Database (80GB) that is able to store attachments. These attachments are full text indexed. For some reasons I had to rebuild the fulltext catalog and this took 30 hours. Our business users ...
0
votes
0answers
668 views

SQL Server full text indexing disabled

I have a SQL Server 2008 R2 server instance where I'm unable to use full text catalogs. It was upgraded from SQL Server 2008. When I go to the database properties of an existing database or try to ...
4
votes
2answers
19k views

db2 database alias not listed via list database directory but still unable to create that database

I am trying to create a database by logging on to the linux server on which the db2 server is installed, as the instance owner db2 => list database directory System Database Directory Number ...
8
votes
2answers
15k views

Check if postgresql database exists (case insensitive way)

Is there a "elegant built-in" case-insensitive way to check if db is exists? I've found only SELECT datname FROM pg_catalog.pg_database WHERE datname='dbname', but this is a CS check. The first thing ...
3
votes
2answers
5k views

Dropping a group of schemas with similar name patterns

Consider a situation where one need to perform a bunch of essentially identical operations, with the only variable being the name of some object. In my case, I need to drop some schemas, all of the ...
4
votes
2answers
92 views

disallow SET command in a postgresql server

Im planning to publish my postgresql server to a few untrusted clients. I dont want them to modify any runtime setting, like work_mem or something risky to my server. In general I assume the ...
7
votes
3answers
19k views

Display user-defined types and their details

I've created a few new UDTs in PostgreSQL. However, now I have two problems: how to see which UDTs have been defined? how to see the columns defined within these UDTs? Unfortunately, I couldn't ...
3
votes
2answers
6k views

ERROR: could not find array type for datatype information_schema.sql_identifier

I am trying to run the below sql command: SELECT ARRAY( SELECT column_name FROM information_schema.columns WHERE table_name ='gis_field_configuration_stage' ); and I get the below ...
281
votes
4answers
489k views

How do I list all columns for a specified table

I'm looking for a precise piece of information in a database which I have no knowledge about. It's a 3rd party product, they are slow on answering some questions, and I know the data is lying inside ...
13
votes
5answers
11k views

Is there a means to set the owner of all objects in a PostgreSQL database at the same time?

https://stackoverflow.com/questions/1348126/modify-owner-on-all-tables-simultaneously-in-postgresql describes some nifty ways to change table and other objects to a specific user, and it works ...