You can alias the column names one by one, like so SELECT col1 as `MyNameForCol1`, col2 as `MyNameForCol2` FROM `foobar` Edit You can access INFORMATION_SCHEMA.COLUMNS directly to mangle a new alias like so. However, how you fit this into a query is beyond my MySql skills 🙁 select CONCAT(‘Foobar_’, COLUMN_NAME) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = … Read more
If create_time is of type TEXT with valid date value, it’ll be easier to proceed with the change as follows (Be advised to first do a table dump as a backup): — Create a temporary TIMESTAMP column ALTER TABLE AB ADD COLUMN create_time_holder TIMESTAMP without time zone NULL; — Copy casted value over to the … Read more
Try something like this: SELECT * FROM clients WHERE CONCAT(field1, ”, field2, ”, fieldn) LIKE “%Mary%” You may want to see SQL docs for additional information on string operators and regular expressions. Edit: There may be some issues with NULL fields, so just in case you may want to use IFNULL(field_i, ”) instead of just … Read more
Using collations eg: if (‘a’=’A’ Collate Latin1_General_CI_AI) print’same 1′ else print ‘different 1’ if (‘a’=’A’ Collate Latin1_General_CS_AI) print’same 2′ else print ‘different 2’ The CS in the collation name indicates Case Sensitive (and CI, Case Insensitive). The AI/AS relates to accent sensitivity. or in your example SUBSTRING(author,1,1) <> LOWER(SUBSTRING(author,1,1)) COLLATE Latin1_General_CS_AI
No you can’t use case and in like this. But you can do SELECT * FROM Product P WHERE @Status=”published” and P.Status IN (1,3) or @Status=”standby” and P.Status IN (2,5,9,6) or @Status=”deleted” and P.Status IN (4,5,8,10) or P.Status IN (1,3) BTW you can reduce that to SELECT * FROM Product P WHERE @Status=”standby” and P.Status … Read more
Use alter table to add a new constraint: alter table foo add constraint check_positive check (the_column > 0); More details and examples are in the manual: http://www.postgresql.org/docs/current/static/sql-altertable.html#AEN70043 Edit Checking for specific values is done in the same way, by using an IN operator: alter table foo add constraint check_positive check (some_code in (‘A’,’B’));
You can use a deadlock graph and gather the information you require from the log file. The only other way I could suggest is digging through the information by using EXEC SP_LOCK (Soon to be deprecated), EXEC SP_WHO2 or the sys.dm_tran_locks table. SELECT L.request_session_id AS SPID, DB_NAME(L.resource_database_id) AS DatabaseName, O.Name AS LockedObjectName, P.object_id AS LockedObjectId, … Read more
If you want to average date only: SELECT CAST(AVG(CAST(datefield AS INT)) AS DATETIME) FROM dates; And if you want to consider time: SELECT CAST(AVG(CAST(datefield AS FLOAT)) AS DATETIME) FROM dates; See fiddle to test.
Here’s a generic way to transform this update query from SQL-server form to PostgreSQL: UPDATE Users SET bUsrActive = false WHERE ctid IN ( SELECT u.ctid FROM Users u LEFT JOIN Users u2 ON u.sUsrClientCode = u2.sUsrClientCode AND u2.bUsrAdmin = 1 AND u2.bUsrActive = 1 WHERE u.bUsrAdmin = 0 AND u.bUsrActive = 1 AND u2.nkUsr … Read more