How do I ALTER a PostgreSQL table and make a column unique?
I figured it out from the PostgreSQL docs, the exact syntax is: ALTER TABLE the_table ADD CONSTRAINT constraint_name UNIQUE (thecolumn); Thanks Fred.
sql
Check if value exists in Postgres array
Simpler with the ANY construct: SELECT value_variable = ANY (‘{1,2,3}’::int[]) The right operand of ANY (between parentheses) can either be a set (result of a subquery, for instance) or an array. There are several ways to use it: SQLAlchemy: how to filter on PgArray column types? IN vs ANY operator in PostgreSQL Important difference: Array … Read more
How to write UPDATE SQL with Table alias in SQL Server 2008?
The syntax for using an alias in an update statement on SQL Server is as follows: UPDATE Q SET Q.TITLE = ‘TEST’ FROM HOLD_TABLE Q WHERE Q.ID = 101; The alias should not be necessary here though.
SQL – HAVING vs. WHERE
WHERE clause introduces a condition on individual rows; HAVING clause introduces a condition on aggregations, i.e. results of selection where a single result, such as count, average, min, max, or sum, has been produced from multiple rows. Your query calls for a second kind of condition (i.e. a condition on an aggregation) hence HAVING works … Read more
Need to list all triggers in SQL Server database with table name and table’s schema
Here’s one way: SELECT sysobjects.name AS trigger_name ,USER_NAME(sysobjects.uid) AS trigger_owner ,s.name AS table_schema ,OBJECT_NAME(parent_obj) AS table_name ,OBJECTPROPERTY( id, ‘ExecIsUpdateTrigger’) AS isupdate ,OBJECTPROPERTY( id, ‘ExecIsDeleteTrigger’) AS isdelete ,OBJECTPROPERTY( id, ‘ExecIsInsertTrigger’) AS isinsert ,OBJECTPROPERTY( id, ‘ExecIsAfterTrigger’) AS isafter ,OBJECTPROPERTY( id, ‘ExecIsInsteadOfTrigger’) AS isinsteadof ,OBJECTPROPERTY(id, ‘ExecIsTriggerDisabled’) AS [disabled] FROM sysobjects INNER JOIN sysusers ON sysobjects.uid = sysusers.uid INNER … Read more
mysql Foreign key constraint is incorrectly formed error
I ran into this same problem with HeidiSQL. The error you receive is very cryptic. My problem ended up being that the foreign key column and the referencing column were not of the same type or length. The foreign key column was SMALLINT(5) UNSIGNED and the referenced column was INT(10) UNSIGNED. Once I made them … Read more
Check if a row exists, otherwise insert
I assume a single row for each flight? If so: IF EXISTS (SELECT * FROM Bookings WHERE FLightID = @Id) BEGIN –UPDATE HERE END ELSE BEGIN — INSERT HERE END I assume what I said, as your way of doing things can overbook a flight, as it will insert a new row when there are … Read more
Count the occurrences of DISTINCT values
SELECT name,COUNT(*) as count FROM tablename GROUP BY name ORDER BY count DESC;
Rounding off to two decimal places in SQL
You could cast your result as numeric(x,2). Where x <= 38. select round(630/60.0,2), cast(round(630/60.0,2) as numeric(36,2)) Returns 10.500000 10.50
SQL WITH clause example [duplicate]
The SQL WITH clause was introduced by Oracle in the Oracle 9i release 2 database. The SQL WITH clause allows you to give a sub-query block a name (a process also called sub-query refactoring), which can be referenced in several places within the main SQL query. The name assigned to the sub-query is treated as … Read more