I don&#39;t believe you can do it like that but you should be able to use the setval function direction which is what the alter does.

    select setval(&#39;sequenceX&#39;, (select max(table_id)+1 from table), false)

The false will make it return the next sequence number as exactly what is given.  

In addition if you have mixed case object names and you&#39;re getting an error like this:

    ERROR: relation &quot;public.mytable_id_seq&quot; does not exist

... the following version using regclass should be useful:

    select setval(&#39;&quot;public&quot;.&quot;MyTable_Id_seq&quot;&#39;::regclass, (select MAX(&quot;Id&quot;) FROM &quot;public&quot;.&quot;MyTable&quot;))



I would like to know what is JAVA_HOME.  Where do I set the path of javac.exe and java.exe.  It is in environment variables.  When I compile a Java program from command prompt, how does the JVM find `javac.exe`?

What is JAVA_HOME? How does the JVM find the javac path stored in JAVA_HOME?

Free of charge, simple to learn/use,  Cross Platform C library for GUI Apps? Am I looking for Qt?

Bonus question: Can I develop with the said library/toolkit on Mac then recompile on PC/Linux?

Super Bonus Question: Link to tutorial and/or download of said library.

The truth is that I&#39;m in the process of catching up on the C family (coming from web development - XHTML/PHP/MySQL) to learn iPhone development. 

I do understand that C is not C++ or ObjectiveC but I want to keep the learning curve as simple as possible. Not to get too off topic, but I am also on the lookout for good starter books and websites. I&#39;ve found [this so far][1].

I&#39;m trying to kill many birds with one stone here. I don understand that there are platform specific extensions, but I will try to avoid those for porting purposes
The idea is that I want to write the code on one machine and just compile thrice. (Mac/Win/Linux) If Objective C will compile on Windows and Linux as well as OS X then that&#39;s good. If I must use C++, that&#39;s also fine. 


  [1]: http://cocoadevcentral.com/articles/000081.php

Cross Platform C library for GUI Apps?

Is it possible to use subqueries within alter expressions in PostgreSQL?

I want to alter a sequence value based on a primary key column value.

I tried using the following expression, but it wouldn&#39;t execute.

    alter sequence public.sequenceX restart with (select max(table_id)+1 from table)

Postgresql - Using subqueries with alter sequence expressions

Is it possible to use subqueries within alter expressions in PostgreSQL?
I want to alter a sequence value based on a primary key column value.
I tried using the following expression, but it wouldn't execute.
<pre><code class="hljs language-sql">alter sequence public.sequenceX restart with (select max(table_id)+1 from table)
</code></pre>

I&#39;m not sure if I can use select into to import data from another table like this:

    select * into
      bookmark1 
    from bookmark;    

Is it true that SQlite doesn&#39;t support this syntax? are there any other alternatives?

SQlite: select into?

Is there a command in Microsoft SQL Server T-SQL to tell the script to stop processing?
I have a script that I want to keep for archival purposes, but I don&#39;t want anyone to run it.


T-SQL STOP or ABORT command in SQL Server

Can anyone explain what is the exact difference between stored procedures and user defined functions, and in which context each is useful?

Difference between stored procedures and user defined functions

I&#39;ll use a concrete, but hypothetical, example.

Each **Order** normally has only one **line item**:

**Orders:**

&lt;!-- language: lang-none --&gt;

    OrderGUID   OrderNumber
    =========   ============
    {FFB2...}   STL-7442-1      
    {3EC6...}   MPT-9931-8A

**LineItems:**

&lt;!-- language: lang-none --&gt;

    LineItemGUID   Order ID Quantity   Description
    ============   ======== ========   =================================
    {098FBE3...}   1        7          prefabulated amulite
    {1609B09...}   2        32         spurving bearing

But occasionally there will be an order with two line items:

&lt;!-- language: lang-none --&gt;

    LineItemID   Order ID    Quantity   Description
    ==========   ========    ========   =================================
    {A58A1...}   6,784,329   5          pentametric fan
    {0E9BC...}   6,784,329   5          differential girdlespring 

Normally when showing the orders to the user:

&lt;!-- language: lang-sql--&gt;

    SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description
    FROM Orders
        INNER JOIN LineItems 
        ON Orders.OrderID = LineItems.OrderID

I want to show the single item on the order. But with this occasional order containing two (or more) items, the orders would *appear* be **duplicated**:

&lt;!-- language: lang-none --&gt;

    OrderNumber   Quantity   Description
    ===========   ========   ====================
    STL-7442-1    7          prefabulated amulite
    MPT-9931-8A   32         spurving bearing
    KSG-0619-81   5          panametric fan
    KSG-0619-81   5          differential girdlespring

What I really want is to have SQL Server *just pick one*, as it will be *good enough*:

&lt;!-- language: lang-none --&gt;

    OrderNumber   Quantity   Description
    ===========   ========   ====================
    STL-7442-1    7          prefabulated amulite
    MPT-9931-8A   32         differential girdlespring
    KSG-0619-81   5          panametric fan

If I get adventurous, I might show the user, an ellipsis to indicate that there&#39;s more than one:

&lt;!-- language: lang-none --&gt;

    OrderNumber   Quantity   Description
    ===========   ========   ====================
    STL-7442-1    7          prefabulated amulite
    MPT-9931-8A   32         differential girdlespring
    KSG-0619-81   5          panametric fan, ...

So the question is how to either
 
 - eliminate &quot;duplicate&quot; rows
 - only join to one of the rows, to avoid duplication


## First attempt ##
My first naive attempt was to only join to the &quot;**TOP 1**&quot; line items:

&lt;!-- language: lang-sql --&gt;

    SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description
    FROM Orders
        INNER JOIN (
           SELECT TOP 1 LineItems.Quantity, LineItems.Description
           FROM LineItems
           WHERE LineItems.OrderID = Orders.OrderID) LineItems2
        ON 1=1

But that gives the error:

&gt; The column or prefix &#39;Orders&#39; does not  
&gt; match with a table name or alias name  
&gt; used in the query.  

Presumably because the inner select doesn&#39;t see the outer table.


How to Join to first row

I want to update two tables in one go. How do I do that in SQL Server 2005?

    UPDATE 
      Table1, 
      Table2
    SET 
      Table1.LastName=&#39;DR. XXXXXX&#39;, 
      Table2.WAprrs=&#39;start,stop&#39;
    FROM 
      Table1 T1, 
      Table2 T2
    WHERE 
      T1.id = T2.id
    AND 
      T1.id = &#39;010008&#39;

How to update two tables in one statement in SQL Server 2005?

I&#39;m writing a shell script (will become a cronjob) that will:

1: dump my production database

2: import the dump into my development database

Between step 1 and 2, I need to clear the development database (drop all tables?). How is this best accomplished from a shell script? So far, it looks like this:

    #!/bin/bash
    time=`date &#39;+%Y&#39;-&#39;%m&#39;-&#39;%d&#39;`
    # 1. export(dump) the current production database
    pg_dump -U production_db_name &gt; /backup/dir/backup-${time}.sql
    
    # missing step: drop all tables from development database so it can be re-populated
    
    # 2. load the backup into the development database
    psql -U development_db_name &lt; backup/dir/backup-${time}.sql


Postgres: clear entire database before re-creating / re-populating from bash script

In PostgreSQL I have a table with a varchar column. The data is supposed to be integers and I need it in integer type in a query. Some values are empty strings.
The following:

    SELECT myfield::integer FROM mytable
yields `ERROR:  invalid input syntax for integer: &quot;&quot;`

How can I query a cast and have 0 in case of error during the cast in postgres?

How do I cast a string to integer and have 0 in case of error in the cast with PostgreSQL?

When I began, I used &lt;code&gt;pg_dump&lt;/code&gt; with the default plain format.  I was unenlightened.

Research revealed to me time and file size improvements with &lt;code&gt;pg_dump -Fc | gzip -9 -c &gt; dumpfile.gz&lt;/code&gt;.  I was enlightened.

When it came time to create the database anew,

    # create tablespace dbname location &#39;/SAN/dbname&#39;;
    # create database dbname tablespace dbname;
    # alter database dbname set temp_tablespaces = dbname;

    % gunzip dumpfile.gz              # to evaluate restore time without a piped uncompression
    % pg_restore -d dbname dumpfile   # into a new, empty database defined above

I felt unenlightened:  the restore took 12 hours to create the database that&#39;s only a fraction of what it will become:

    # select pg_size_pretty(pg_database_size(&#39;dbname&#39;));
    47 GB

Because there are predictions this database will be a few terabytes, I need to look at improving performance now.

Please, enlighten me.

PostgreSQL: improving pg_dump, pg_restore performance

Does psycopg2 have a function for escaping the value of a **LIKE** operand for Postgres?

For example I may want to match strings that start with the string &quot;20% of all&quot;, so I want to write something like this:

    sql = &#39;... WHERE ... LIKE %(myvalue)s&#39;
    cursor.fetchall(sql, { &#39;myvalue&#39;: escape_sql_like(&#39;20% of all&#39;) + &#39;%&#39; }

Is there an existing **escape_sql_like** function that I could plug in here?

(Similar question to [How to quote a string value explicitly (Python DB API/Psycopg2)][1], but I couldn&#39;t find an answer there.)


  [1]: https://stackoverflow.com/questions/309945/how-to-quote-a-string-value-explicitly-python-db-api-psycopg2 &quot;my title&quot;

Escape SQL &quot;LIKE&quot; value for Postgres with psycopg2

I was just curious why all databases in PostgreSQL have a `public` schema that is accessible to all users. I know I can revoke privileges and grant them to one user but why is that not the default?


Why do all databases have a public schema in PostgreSQL?

I am an old-school MySQL user and have always preferred `JOIN` over sub-query. But nowadays everyone uses sub-query, and I hate it; I don&#39;t know why.   

I lack the theoretical knowledge to judge for myself if there is any difference. Is a sub-query as good as a `JOIN` and therefore is there nothing to worry about?


Join vs. sub-query

I am trying to write the following query on postgresql:

	select name, author_id, count(1), 
		(select count(1)
		from names as n2
		where n2.id = n1.id
			and t2.author_id = t1.author_id
		)				
	from names as n1
	group by name, author_id

This would certainly work on Microsoft SQL Server but it does not at all on postegresql. I read its documentation a bit and it seems I could rewrite it as:

	select name, author_id, count(1), total						
	from names as n1, (select count(1) as total
		from names as n2
		where n2.id = n1.id
			and n2.author_id = t1.author_id
		) as total
	group by name, author_id

But that returns the following error on postegresql: &quot;subquery in FROM cannot refer to other relations of same query level&quot;. So I&#39;m stuck. Does anyone know how I can achieve that?

Thanks

How to do a Postgresql subquery in select clause with join in from clause like SQL Server?

I have a table with the following fields:

    id (Unique)
    url (Unique)
    title
    company
    site_id

Now, I need  to remove rows having same `title, company and site_id`. One way to do it will be using the following SQL along with a script (**`PHP`**):

    SELECT title, site_id, location, id, count( * ) 
    FROM jobs
    GROUP BY site_id, company, title, location
    HAVING count( * ) &gt;1

After running this query, I can remove duplicates using a server side script.

But, I want to know if this can be done only using SQL query.

 

Remove duplicate rows in MySQL

I wish to know if I have a **join** query something like this - 

    Select E.Id,E.Name from Employee E join Dept D on E.DeptId=D.Id

and a **subquery** something like this -

    Select E.Id,E.Name from Employee Where DeptId in (Select Id from Dept)

When I consider **performance** which of the two queries would be faster and *why* ?

 Also is there a time when I should prefer one over the other?

Sorry if this is too trivial and asked before but I am confused about it. Also, it would be great if you guys can suggest me **tools** i should use to measure performance of two queries. Thanks a lot!


SQL Joins Vs SQL Subqueries (Performance)?

Example:

    SELECT
       (SELECT SUM(...) FROM ...) as turnover,
       (SELECT SUM(...) FROM ...) as cost,
       turnover - cost as profit

Sure this is invalid (at least in Postgres) but how to achieve the same in a query without rewriting the sub-query twice?

How to reuse a result column in an expression for another result column

We&#39;re considering using UUID values as primary keys for our MySQL database. The data being inserted is generated from dozens, hundreds, or even thousands of remote computers and being inserted at a rate of 100-40,000 inserts per second, and we&#39;ll never do any updates.

The database itself will typically get to around 50M records before we start to cull data, so not a massive database, but not tiny either. We&#39;re also planing to run on InnoDB, though we are open to changing that if there is a better engine for what we&#39;re doing.

We were ready to go with Java&#39;s Type 4 UUID, but in testing have been seeing some strange behavior. For one, we&#39;re storing as varchar(36) and I now realize we&#39;d be better off using binary(16) - though how much better off I&#39;m not sure.

The bigger question is: how badly does this random data screw up the index when we have 50M records? Would we be better off if we used, for example, a type-1 UUID where the leftmost bits were timestamped? Or maybe we should ditch UUIDs entirely and consider auto_increment primary keys?

I&#39;m looking for general thoughts/tips on the performance of different types of UUIDs when they are stored as an index/primary key in MySQL. Thanks!

UUID performance in MySQL?

I am learning JPA and have confusion in the `@SequenceGenerator` annotation.

To my understanding, it automatically assigns a value to the numeric identity fields/properties of an entity.

**Q1.** Does this sequence generator make use of the database&#39;s increasing numeric value generating capability or generates the number on its own?

**Q2.** If JPA uses a database auto-increment feature, then will it work with datastores that don&#39;t have an auto-increment feature?

**Q3.** If JPA generates numeric value on his own, then how does the JPA implementation know which value to generate next? Does it consult with the database first to see what value was stored last in order to generate the value (last + 1)?

---
**Q4.** Please also shed some light on `sequenceName` and `allocationSize` properties of `@SequenceGenerator` annotation.

How does the JPA @SequenceGenerator annotation work

In python is there an easy way to tell if something is not a sequence? I tried to just do:
`if x is not sequence` but python did not like that

Python: check if an object is a sequence

I know that `cons` returns a seq and `conj` returns a collection.  I also know that `conj` &quot;adds&quot; the item to the optimal end of the collection, and `cons` always &quot;adds&quot; the item to the front.  This example illustrates both of these points:

    user=&gt; (conj [1 2 3] 4) ; returns a collection
    [1 2 3 4]
    user=&gt; (cons 4 [1 2 3]) ; returns a seq
    (4 1 2 3)

For vectors, maps, and sets these differences make sense to me.  However, for lists they seem identical.

    user=&gt; (conj (list 3 2 1) 4) ; returns a list
    (4 3 2 1)
    user=&gt; (cons 4 (list 3 2 1)) ; returns a seq
    (4 3 2 1)

Are there any examples using lists where `conj` vs. `cons` exhibit different behaviors, or are they truly interchangeable?  Phrased differently, is there an example where a list and a seq cannot be used equivalently?



Clojure: cons (seq) vs. conj (list)

Why do I get an error of &quot;can&#39;t multiply sequence by non-int of type &#39;float&#39;&quot;? from the following code:


    def nestEgVariable(salary, save, growthRates):
        SavingsRecord = []
        fund = 0
        depositPerYear = salary * save * 0.01
        for i in growthRates:  
            fund = fund * (1 + 0.01 * growthRates) + depositPerYear
            SavingsRecord += [fund,]
        return SavingsRecord 
    
    
    print nestEgVariable(10000,10,[3,4,5,0,3])


can&#39;t multiply sequence by non-int of type &#39;float&#39;

I have a computed column created with the following line:

    alter table tbPedidos 
    add restricoes as (cast(case when restricaoLicenca = 1 or restricaoLote = 1 then 1 else 0 end as bit))

But, now I need to change this column for something like:

    alter table tbPedidos 
    alter column restricoes as (cast(case when restricaoLicenca = 1 or restricaoLote = 1 or restricaoValor = 1 then 1 else 0 end as bit))

But it&#39;s not working. I&#39;m trying to input another condition to the case statement, but it&#39;s not working.

Thanks a lot!

How can I alter this computed column in SQL Server 2008?

I have SQL Server database and I just realized that I can change the type of one of the columns from `int` to `bool`. 

How can I do that without losing the data that is already entered into that table? 


How to change column datatype in SQL Server database without losing data?

I have a table that has 170,002,225 rows with about 35 columns and two indexes.  I want to add a column.  The alter table command took about 10 hours.  Neither the processor seemed busy during that time nor were there excessive IO waits.  This is on a 4 way high performance box with tons of memory.

Is this the best I can do?  Is there something I can look at to optimize the add column in tuning of the db?

Optimize mySql for faster alter table add column

I would like to write a single SQL command to drop multiple columns from a single table in one `ALTER TABLE` statement.

From [MSDN&#39;s ALTER TABLE documentation][1]...

&gt;     DROP { [CONSTRAINT] constraint_name | COLUMN column_name }
&gt; 
&gt; Specifies that constraint_name or column_name is removed from the table. DROP COLUMN is not allowed if the compatibility level is 65 or earlier. Multiple columns and constraints can be listed.

It says that mutliple columns can be listed in the the statement but the syntax doesn&#39;t show an optional comma or anything that would even hint at the syntax.

How should I write my SQL to drop multiple columns in one statement (if possible)?


  [1]: http://msdn.microsoft.com/en-us/library/aa275462%28v=sql.80%29.aspx

How to DROP multiple columns with a single ALTER TABLE statement in SQL Server?

Currently I am having the following MySQL table: `Employees (empID, empName, department);`

I want to change the table to the following: `Employees (empID, department, empName);`

How can this be done using `ALTER` statements?

Note: I want to change only column positions. 

Content Type	Original Author	Original Content on Stackoverflow
Question	Danmaxis	View Question on Stackoverflow
Solution 1 - Sql	Arthur Thomas	View Answer on Stackoverflow
Solution 2 - Sql	Duncan Smart	View Answer on Stackoverflow

Postgresql - Using subqueries with alter sequence expressions

Sql Problem Overview

Sql Solutions

Solution 1 - Sql

Solution 2 - Sql

Cross Platform C library for GUI Apps?

What is JAVA_HOME? How does the JVM find the javac path stored in JAVA_HOME?

Attributions