I have a table that is missing a column in its primary key constraint. Instead of editing it through SQL Server, I want to put this in a script to add it as part of our update scripts. What syntax can I use to do this? Must I drop and recreate the key constraint? Answer Yes. The only way would
PostgreSQL unnest() with element number
When I have a column with separated values, I can use the unnest() function: How can I include element numbers? I.e.: I want the original position of each element in the source string. I’ve tried with window functions (row_number(), rank() etc.) but I always get 1. Maybe because they are in the same row…
Showing what quarter of a financial year a date is in
I’m trying to construct a query that will map two columns, one, a date from a table, the second column an alias to show what quarter and financial year the date falls into. Unfortunately I don’t have enough knowledge of SQL to know where to begin. I know that I’d do this with a combination o…
SQL: Return only first occurrence
I seldomly use SQL and I cannot find anything similar in my archive so I’m asking this simple query question: I need a query which one returns personID and only the first seenTime Records: Wanted result: That’s what I did & failed: P.S: Notice SQL CE 4 Answer If your seenTime increases as seen…
Rename a constraint in SQL Server?
Is it possible to rename a constraint in SQL Server? I don’t want to have to delete and create a new one because this constraint affects other already existing constraints and I will have to recreate/alter those. Answer You can rename using sp_rename using @objtype = ‘OBJECT’ This works on o…
Difference between natural join and inner join
What is the difference between a natural join and an inner join? Answer One significant difference between INNER JOIN and NATURAL JOIN is the number of columns returned. Consider: The INNER JOIN of TableA and TableB on Column1 will return The NATURAL JOIN of TableA and TableB on Column1 will return: The repea…
Best way to select random rows PostgreSQL
I want a random selection of rows in PostgreSQL, I tried this: select * from table where random() < 0.01; But some other recommend this: select * from table order by random() limit 1000; I have a …
DateTime query on only year in SQL Server
I want to select multiple records on the basis of a matching year, for example in table tab where columns are [id] int, [name] varchar, [bookyear] datetime I want to select all records where the …
Exit the SQLite shell on Android
I’m using SQLite 3 on Mac OS X’s ADB shell to view my application database using: After I wrongly typed the quit command above, instead of the .quit for example, the shell goes into the …> prompt where none of the known commands seem to work. I can’t even get out by Ctrl + C, Ctrl +…
Get number of partitions in PostgreSQL database
What is the most efficient way to get the number of partitions created in the database? I am using PostgreSQL API for C++. Answer This is how you can select all the names of the table partitions: It can be used to count as well: