The following query is giving an error at ‘CASE WHEN NOT LIKE’ (Msg 156, Level 15, State 1, Line 14 Incorrect syntax near the keyword ‘LIKE’) SELECT TimeStamp, TimeStampNS, ISNULL(TagName, ‘…
Tag: sql
Trigger for checking a given value before INSERT or UPDATE on PostgreSQL
I have to create a trigger to update a database where there are products. Products have an expiration date and before inserting or updating a product I must check if the expirationDate is superior to the current timestamp. If it is I must do the insert/update regularly (this is what I have problems with). If …
Query for array elements inside JSON type
I’m trying to test out the json type in PostgreSQL 9.3. I have a json column called data in a table called reports. The JSON looks something like this: { “objects”: [ {“src”:”foo.png”}, {“…
Compare two time strings in SQL
I have a string variable that denotes a time: I need to check if the current time getdate() is after or before @time. How can I do this in SQL? Answer This should do it: SQL 2008+ Earlier:
How to use date variable in sql developer’s “Enter Binds” dialog?
I am trying to run a query from sql developer and query has variables (:var). I am having problem with the date variables. I used all the possible combinations to format date using to_date() function….
MYSQL show all rows where no duplicates found
If I had a set of data like this: What would be the SQL query to display only “James Peters”. So I am not wanting to remove duplicates (DISTINCT command) but rather show all rows where there are no duplicates found. Answer You would use group by for this with a having clause:
Grant privileges on future tables in PostgreSQL?
I am running PostgreSQL 9.3.1. I have test database and backup user which is used to backup the database. I have no problems with granting privileges to all current tables, but I have to grant privileges each time the new table is added to schema. Is it possible to grant access to tables which will be created…
Real, mathematical “modulo” operation in Postgres?
In Ruby: In Postgres: I can easily write one myself, but I’m curious whether Postgres has a real modulus operation, as opposed to remainder after division. Answer will return 22 instead of -2
How to merge all integer arrays from all records into single array in postgres
I have a column which is of type integer array. How can I merge all of them into a single integer array? For example: If I execute query: I get result set as: How can I get {1,2,3,4,5} as final result? Answer You could use unnest to open up the arrays and then array_agg to put them back together:
How to write a program to automatically download a file from email
I have an email account (like gmail or something) that gets a daily email with a file attached to it that has some data in it. Is there a way to have a program check the email everyday, say at midnight or something, download that file, and store the contents into a SQL database? I’m familiar with Java, …