ALTER TABLE foobar_data MODIFY COLUMN col VARCHAR(255) NOT NULL DEFAULT ‘{}’; A second possibility which does the same (thanks to juergen_d): ALTER TABLE foobar_data CHANGE COLUMN col col VARCHAR(255) NOT NULL DEFAULT ‘{}’;
( SELECT * FROM table1 EXCEPT SELECT * FROM table2) UNION ALL ( SELECT * FROM table2 EXCEPT SELECT * FROM table1)
psql‘s inline help: \h ALTER TABLE Also documented in the postgres docs (an excellent resource, plus easy to read, too). ALTER TABLE tablename ADD CONSTRAINT constraintname UNIQUE (columns);
The @@identity function returns the last identity created in the same session. The scope_identity() function returns the last identity created in the same session and the same scope. The ident_current(name) returns the last identity created for a specific table or view in any session. The identity() function is not used to get an identity, it’s … Read more
I am consistently disappointed by most programmers’ tendency to mix their UI-logic in the data access layer: SELECT FirstName + ‘ ‘ + LastName as “Full Name”, case UserRole when 2 then “Admin” when 1 then “Moderator” else “User” end as “User’s Role”, case SignedIn when 0 then “Logged in” else “Logged out” end as … Read more
You can only use column aliases in GROUP BY, ORDER BY, or HAVING clauses. Standard SQL doesn’t allow you to refer to a column alias in a WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined. Copied from MySQL documentation As pointed in … Read more
Use following syntax to create new table from old table in SQL server 2008 Select * into new_table from old_table
For INNER joins, no, the order doesn’t matter. The queries will return same results, as long as you change your selects from SELECT * to SELECT a.*, b.*, c.*. For (LEFT, RIGHT or FULL) OUTER joins, yes, the order matters – and (updated) things are much more complicated. First, outer joins are not commutative, so … Read more
Add a WHERE clause UPDATE dbo.TestStudents SET LASTNAME = CASE WHEN LASTNAME = ‘AAA’ THEN ‘BBB’ WHEN LASTNAME = ‘CCC’ THEN ‘DDD’ WHEN LASTNAME = ‘EEE’ THEN ‘FFF’ ELSE LASTNAME END WHERE LASTNAME IN (‘AAA’, ‘CCC’, ‘EEE’)
Use the EXISTS key word for TRUE / FALSE return: select exists(select 1 from contact where id=12)