ALTER TABLE ADD CONSTRAINT gets Error 1064
Omit the parenthesis: ALTER TABLE User ADD CONSTRAINT userProperties FOREIGN KEY(properties) REFERENCES Properties(ID)
ddl
PostgreSQL: create database with UTF8 encoding same as in MySQL (including character set, encoding, and lc_type)
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
Is it possible to run multiple DDL statements inside a transaction (within SQL Server)?
I know most databases have restrictions, but Postgres doesn’t. You can run any number table creations, column changes and index changes in a transaction, and the changes aren’t visible to other users unit COMMIT succeeds. That’s how databases should be! 🙂 As for SQL Server you can run DDL inside of a transaction, but SQL … Read more
CREATE UNIQUE INDEX IF NOT EXISTS in postgreSQL
You can check, if an index with a given name exists with this statement. If your index name is some_table_some_field_idx SELECT count(*) > 0 FROM pg_class c WHERE c.relname=”some_table_some_field_idx” AND c.relkind = ‘i’; Starting from Postgres 9.5 you can even use CREATE INDEX IF NOT EXISTS
Mysql Add a new value to a column of data type enum
Unfortunately, you need to re-list all the existing enum values when adding a new value to the the enum. ALTER TABLE mytable MODIFY COLUMN mycolumn ENUM(‘a’,’b’,’c’,’d’,’e’); You don’t really want to use CONCAT() in this situation.
Can a number be used to name a MySQL table column?
From the docs: Identifiers may begin with a digit but unless quoted may not consist solely of digits. Which means you must quote it with back ticks like `25`: UPDATE table SET `25`=’100′ WHERE id=’1′
Create a temporary table from a selection, or insert if table already exists
CREATE TABLE AS is the simplest and fastest way: CREATE TEMP TABLE tbl AS SELECT * FROM tbl WHERE … ; Do not use SELECT INTO for this purpose. See: Combine two tables into a new one so that select rows from the other one are ignored Not sure whether table already exists CREATE TABLE … Read more
How to drop a unique constraint on a column in Postgres?
To find the name of the unique constraint, run SELECT conname FROM pg_constraint WHERE conrelid = ‘cart’::regclass AND contype=”u”; Then drop the constraint as follows: ALTER TABLE cart DROP CONSTRAINT cart_shop_user_id_key; Replace cart_shop_user_id_key with whatever you got from the first query.
How to add a new column with foreign key constraint in a single statement in oracle
alter table tab1 add c1 number(20) constraint tab1_c1_fk references tab2(c2); c1 new column in the table tab1 with the FK tab1_c1_fk on the table tab2 column c2.
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