ddl

Upgrading a varchar column to enum type in postgresql

by

You need to define a cast to be used because there is no default cast available. If all values in the varcharColumn comply with the enum definition, the following should work: alter table foo ALTER COLUMN varcharColumn TYPE enum_type using varcharColumn::enum_type; I personally don’t like enums because they are quite unflexible. I prefer a check … Read more

Is there a postgres command to list/drop all materialized views?

by

Pure SQL Show all: SELECT oid::regclass::text FROM pg_class WHERE relkind = ‘m’; Names are automatically double-quoted and schema-qualified where needed according to your current search_path in the cast from regclass to text. In the system catalog pg_class materialized views are tagged with relkind = ‘m’. The manual: m = materialized view To drop all, you … Read more

Get definition of function, sequence, type etc. in Postgresql with SQL query

by

To get the definition of a function use pg_get_functiondef(): select pg_get_functiondef(oid) from pg_proc where proname=”foo”; There are similar functions to retrieve the definition of an index, a view, a rule and so on. For details see the manual: http://www.postgresql.org/docs/current/static/functions-info.html Getting the definition of a user type is a bit more tricky. You will need to … Read more

How to alter “REFERENCES” in PostgreSQL?

by

Internal dependencies between tables and / or other objects are never bound to the object name. Internally, every object is stored in a catalog table and the OID (internal primary key) of the object is used for everything else. Accordingly, a FOREIGN KEY reference is stored in the catalog tables pg_constraint (the constraint itself incl. … Read more

Rename column only if exists

by

Please read this article on codingvila.com for a detailed explanation. Rename Column Only If Exists in PostgreSQL DO $$ BEGIN IF EXISTS(SELECT * FROM information_schema.columns WHERE table_name=”your_table” and column_name=”your_column”) THEN ALTER TABLE “public”.”your_table” RENAME COLUMN “your_column” TO “your_new_column”; END IF; END $$;

What does the number in parenthesis really mean?

by

INT(2) will generate an INT with the minimum display width of 2: MySQL supports an extension for optionally specifying the display width of integer data types in parentheses following the base keyword for the type. For example, INT(4) specifies an INT with a display width of four digits. This optional display width may be used … Read more

UTF8 Postgresql Create Database Like MySQL (including character set, encoding, and lc_type)

by

Yes, you can be more specific. For example: CREATE DATABASE “scratch” WITH OWNER “postgres” ENCODING ‘UTF8’ LC_COLLATE = ‘en_US.UTF-8’ LC_CTYPE = ‘en_US.UTF-8’; Also I recommend to read the following pages about locales and collations in PostgreSQL: http://www.postgresql.org/docs/current/interactive/locale.html http://www.postgresql.org/docs/current/interactive/collation.html