ddl

How do I get column datatype in Oracle with PL-SQL with low privileges?

ALL_TAB_COLUMNS should be queryable from PL/SQL. DESC is a SQL*Plus command. SQL> desc all_tab_columns; Name Null? Type —————————————– ——– —————————- OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME NOT NULL VARCHAR2(30) DATA_TYPE VARCHAR2(106) DATA_TYPE_MOD VARCHAR2(3) DATA_TYPE_OWNER VARCHAR2(30) DATA_LENGTH NOT NULL NUMBER DATA_PRECISION NUMBER DATA_SCALE NUMBER NULLABLE VARCHAR2(1) COLUMN_ID NUMBER DEFAULT_LENGTH NUMBER DATA_DEFAULT LONG NUM_DISTINCT … Read more

Drop column from SQLite table

Update: SQLite 2021-03-12 (3.35.0) now supports DROP COLUMN. From: http://www.sqlite.org/faq.html: (11) How do I add or delete columns from an existing table in SQLite. SQLite has limited ALTER TABLE support that you can use to add a column to the end of a table or to change the name of a table. If you want … Read more

PostgreSQL create table if not exists

This feature has been implemented in Postgres 9.1: CREATE TABLE IF NOT EXISTS myschema.mytable (i integer); For older versions, here is a function to work around it: CREATE OR REPLACE FUNCTION create_mytable() RETURNS void LANGUAGE plpgsql AS $func$ BEGIN IF EXISTS (SELECT FROM pg_catalog.pg_tables WHERE schemaname=”myschema” AND tablename=”mytable”) THEN RAISE NOTICE ‘Table myschema.mytable already exists.’; … Read more

DROP FUNCTION without knowing the number/type of parameters?

Basic query This query creates all necessary DDL statements: SELECT ‘DROP FUNCTION ‘ || oid::regprocedure FROM pg_proc WHERE proname=”my_function_name” — name without schema-qualification AND pg_function_is_visible(oid); — restrict to current search_path Output: DROP FUNCTION my_function_name(string text, form text, maxlen integer); DROP FUNCTION my_function_name(string text, form text); DROP FUNCTION my_function_name(string text); Execute the commands after checking plausibility. … Read more

Why can I create a table with PRIMARY KEY on a nullable column?

Because the PRIMARY KEY makes the included column(s) NOT NULL automatically. I quote the manual here: The primary key constraint specifies that a column or columns of a table can contain only unique (non-duplicate), nonnull values. Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL. Bold emphasis mine. I ran a test … Read more

How to get function parameter lists (so I can drop a function)

Postgres has a dedicated function for that purpose. Introduced with Postgres 8.4. The manual: pg_get_function_identity_arguments(func_oid) … get argument list to identify a function (without default values) … pg_get_function_identity_arguments returns the argument list necessary to identify a function, in the form it would need to appear in within ALTER FUNCTION, for instance. This form omits default … Read more