Identifiers (including column names) that are not double-quoted are folded to lowercase in PostgreSQL. Column names that were created with double-quotes and thereby retained uppercase letters (and/or other syntax violations) have to be double-quoted for the rest of their life: “first_Name” Values (string literals / constants) are enclosed in single quotes: ‘xyz’ So, yes, PostgreSQL … Read more
You can do it quite easily with (introduced in PostgreSQL 8.2) VALUES (), (). Syntax will be like this: select c.* from comments c join ( values (1,1), (3,2), (2,3), (4,4) ) as x (id, ordering) on c.id = x.id order by x.ordering
Look at an index like this: Cols 1 2 3 ————- | | 1 | | | A |—| | | | 2 | | |—|—| | | | | | | | 1 | 9 | | B | | | | |—| | | | 2 | | | |—| | | | … Read more
i believe you can use CREATE TABLE test ( id INTEGER PRIMARY KEY AUTOINCREMENT, t TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); as of version 3.1 (source)
With a maximum length of 255 characters, the DBMS can choose to use a single byte to indicate the length of the data in the field. If the limit were 256 or greater, two bytes would be needed. A value of length zero is certainly valid for varchar data (unless constrained otherwise). Most systems treat … Read more
What we do, is to create two tables for each multilingual object. E.g. the first table contains only language-neutral data (primary key, etc.) and the second table contains one record per language, containing the localized data plus the ISO code of the language. In some cases we add a DefaultLanguage field, so that we can … Read more
Quick and Easy way: Right click database Point to tasks In SSMS 2017 you need to ignore step 2 – the generate scripts options is at the top level of the context menu Thanks to Daniel for the comment to update. Select generate scripts Click next Choose tables Click next Click advanced Scroll to Types … Read more
I can’t believe the accepted answer has so many upvotes — it’s a horrible method. Here’s the correct way to do it, with date_trunc: SELECT date_trunc(‘month’, txn_date) AS txn_month, sum(amount) as monthly_sum FROM yourtable GROUP BY txn_month It’s bad practice but you might be forgiven if you use GROUP BY 1 in a very simple … Read more
In MySQL: SELECT * FROM mytable WHERE record_date >= NOW() – INTERVAL 1 DAY In SQL Server: SELECT * FROM mytable WHERE record_date >= DATEADD(day, -1, GETDATE()) In Oracle: SELECT * FROM mytable WHERE record_date >= SYSDATE – 1 In PostgreSQL: SELECT * FROM mytable WHERE record_date >= NOW() – ‘1 day’::INTERVAL In Redshift: SELECT … Read more
Something like Decimal(19,4) usually works pretty well in most cases. You can adjust the scale and precision to fit the needs of the numbers you need to store. Even in SQL Server, I tend not to use “money” as it’s non-standard.