In PostgreSQL, `ALTER TABLE` can take a series of operations. So:

    ALTER TABLE &lt;tablename&gt; RENAME &lt;oldcolumn&gt; TO &lt;newcolumn&gt;;
    ALTER TABLE &lt;tablename&gt; ALTER COLUMN &lt;columnname&gt; TYPE &lt;newtype&gt;;

is the same as

    ALTER TABLE &lt;tablename&gt; 
      ALTER COLUMN &lt;columnname&gt; TYPE &lt;newtype&gt;
      RENAME &lt;oldcolumn&gt; TO &lt;newcolumn&gt;;

However... why? IIRC the rename won&#39;t cause a full-table scan, so there&#39;s no benefit over just doing the two statements separately, within one transaction. What problem are you actually trying to solve with this?

PostgreSQL: Alter table column name and data-type:

    ALTER TABLE &lt;TableName&gt; 
       ALTER [ COLUMN ] column [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]
      RENAME [ COLUMN ] column TO new_column;

See [`ALTER TABLE`](http://www.postgresql.org/docs/current/static/sql-altertable.html).

can anybody explain what is the main difference between functions and procedures in Oracle? Why must I use procedures if I can do everything with functions? 

 
 1. If I cannot call procedure in sql statement, ok, I&#39;ll write a function to do the same work.
 2. Procedures don&#39;t return values, ok, I&#39;ll return only sql%rowcount or 1(success), 0(exception) after any dml operation
 3. Both procedures and functions can pass variables to calling environment via OUT/IN OUT parameters

I heard that the main difference is in performance, &#39;procedures are faster than functions&#39;. But without any detail. 

Thanks in advance.

Functions vs procedures in Oracle

I am trying to fulfill [this rest api][1]:

    public async Task&lt;bool&gt; AddTimetracking(Issue issue, int spentTime)
    {
        // POST /rest/issue/{issue}/timetracking/workitem
        var workItem = new WorkItem(spentTime, DateTime.Now);
        var httpContent = new StringContent(workItem.XDocument.ToString());
        var requestUri = string.Format(&quot;{0}{1}issue/{2}/timetracking/workitem&quot;, url, YoutrackRestUrl, issue.Id);
        var respone = await httpClient.PostAsync(requestUri, httpContent);
        if (!respone.IsSuccessStatusCode)
        {
            throw new InvalidUriException(string.Format(&quot;Invalid uri: {0}&quot;, requestUri));
        }

        return respone.IsSuccessStatusCode;
    }

`workItem.XDocument` contains the following elements:

&lt;!-- language: lang-xml --&gt;

    &lt;workItem&gt;
      &lt;date&gt;1408566000&lt;/date&gt;
      &lt;duration&gt;40&lt;/duration&gt;
      &lt;desciption&gt;test&lt;/desciption&gt;
    &lt;/workItem&gt;

I am getting an error from the API: `Unsupported Media Type`

I really have no idea how to resolve this, help is greatly appreciated. How do I *marshall* an XML file via a HTTP POST URI, using `HttpClient`? 

[1]: http://confluence.jetbrains.com/display/YTD5/Create+New+Work+Item


How to send XML content with HttpClient.PostAsync?

In PostgreSQL if I need to rename and change a column data type, I run two separate queries to do so. 

To rename:

&lt;!-- language: lang-sql --&gt;
      
    ALTER TABLE tblName RENAME &lt;oldColumn&gt; TO &lt;newColumn&gt; 

and to change column type:
          
    ALTER TABLE tblName ALTER COLUMN &lt;newColumn&gt; &lt;columnType&gt;.

But is there any way to do both of these works with a single query like the following MySQL query:

    ALTER TABLE tblName CHANGE COLUMN &lt;oldColumn&gt; &lt;newColumn&gt; &lt;columnType&gt;

PostgreSQL query to rename and change column type with single query

In PostgreSQL if I need to rename and change a column data type, I run two separate queries to do so.
To rename:

<pre><code class="hljs language-css">ALTER TABLE tblName RENAME &#x3C;oldColumn> TO &#x3C;newColumn> 
</code></pre>
and to change column type:
<pre><code class="hljs language-sql">ALTER TABLE tblName ALTER COLUMN &#x3C;newColumn> &#x3C;columnType>.
</code></pre>
But is there any way to do both of these works with a single query like the following MySQL query:
<pre><code class="hljs language-php-template">ALTER TABLE tblName CHANGE COLUMN &#x3C;oldColumn> &#x3C;newColumn> &#x3C;columnType>
</code></pre>

I have the following log table for user messages (simplified form) in Postgres 9.2:

    CREATE TABLE log (
        log_date DATE,
        user_id  INTEGER,
        payload  INTEGER
    );

It contains up to one record per user and per day. There will be approximately 500K records per day for 300 days. payload is ever increasing for each user (if that matters).

I want to efficiently retrieve the latest record for each user before a specific date. My query is:

    SELECT user_id, max(log_date), max(payload) 
    FROM log 
    WHERE log_date &lt;= :mydate 
    GROUP BY user_id

which is extremely slow. I have also tried:

    SELECT DISTINCT ON(user_id), log_date, payload
    FROM log
    WHERE log_date &lt;= :mydate
    ORDER BY user_id, log_date DESC;

which has the same plan and is equally slow.

So far I have a single index on `log(log_date)`, but doesn&#39;t help much.

And I have a `users` table with all users included. I also want to retrieve the result for some some users (those with `payload &gt; :value`).

Is there any other index I should use to speed this up, or any other way to achieve what I want?

Optimize GROUP BY query to retrieve latest row per user

I&#39;m trying to import a flat file into an oledb target sql server database.

here&#39;s the field that&#39;s giving me trouble:

![enter image description here][1]

here are the properties of that flat file connection, specifically the field:

![enter image description here][2]

here&#39;s the error message:

&gt; [Source - 18942979103_txt [424]] Error: Data conversion failed. The
&gt; data conversion for column &quot;recipient-name&quot; returned status value 4
&gt; and status text &quot;Text was truncated or one or more characters had no
&gt; match in the target code page.&quot;.


What am I doing wrong?


  [1]: http://i.stack.imgur.com/CBYws.png
  [2]: http://i.stack.imgur.com/GTBcj.png

Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot

    Select * from [User] U
    where  U.DateCreated = &#39;2014-02-07&#39;     

but in the database the user was created on `2014-02-07 12:30:47.220` and when I only put `&#39;2014-02-07&#39;`

It does not show any data

How to compare datetime with only date in SQL Server

I am trying to understand how to UPDATE multiple rows with different values and I just don&#39;t get it. The solution is everywhere but to me it looks difficult to understand.

For instance, three updates into 1 query:

	UPDATE table_users
    SET cod_user = &#39;622057&#39;
        , date = &#39;12082014&#39;
    WHERE user_rol = &#39;student&#39;
        AND cod_office = &#39;17389551&#39;; 

	UPDATE table_users
    SET cod_user = &#39;2913659&#39;
        , date = &#39;12082014&#39;
    WHERE user_rol = &#39;assistant&#39;
        AND cod_office = &#39;17389551&#39;; 

	UPDATE table_users
    SET cod_user = &#39;6160230&#39;
        , date = &#39;12082014&#39;
    WHERE user_rol = &#39;admin&#39;
        AND cod_office = &#39;17389551&#39;; 

I [read][1] an example, but I really don&#39;t understand how to make the query. i.e:

    UPDATE table_to_update
    SET cod_user= IF(cod_office = &#39;17389551&#39;,&#39;622057&#39;,&#39;2913659&#39;,&#39;6160230&#39;)
        ,date = IF(cod_office = &#39;17389551&#39;,&#39;12082014&#39;)
    WHERE ?? IN (??) ;

I&#39;m not entirely clear how to do the query if there are multiple condition in the WHERE and in the IF condition..any ideas?

  [1]: http://www.karlrixon.co.uk/writing/update-multiple-rows-with-different-values-and-a-single-sql-query/

MySQL - UPDATE multiple rows with different values in one query

I have two tables with similar column names and I need to return records from the left table which are not found in the right table? I have a primary key(column) which will help me to compare both tables. Which join is preferred?

How to return rows from left table not found in right table?

I have created an ubuntu image with nginx, php and postgres.

I want to connect the postgres database in my current image with `pgadmin` located on my local machine. 

I have tried using docker inspector to try to use the image ip to make a connection with my local pgadmin but without much success. I&#39;ve also tried configuring some ports on local to make connection work.

docker postgres pgadmin local connection

I&#39;m using rails 4.1.5 with postgresql 9.1 under Debian 7, and I&#39;m not able to create a database in my development environment. When I run

    bin/rake db:create

I get

    home/rs/.rvm/gems/ruby-2.1.2/gems/activerecord-4.1.5/lib/active_record/connection_adapters/postgresql_adapter.rb:898:in `rescue in connect&#39;: FATAL:  database &quot;direct-dev&quot; does not exist
    Run `$ bin/rake db:create db:migrate` to create your database (ActiveRecord::NoDatabaseError)
	from /home/rs/.rvm/gems/ruby-2.1.2/gems/activerecord-4.1.5/lib/active_record/connection_adapters/postgresql_adapter.rb:888:in `connect&#39;
    from ...

I am trying to create the database so, naturally, it does not exist. However rails should create it ... Here&#39;s my config/database.yml:

    default: &amp;default
      adapter: postgresql
      encoding: unicode
      pool: 5
    
    development:
      &lt;&lt;: *default
      database: direct-dev

And here&#39;s a part of the postgresql log:

    2014-09-01 19:30:40 CEST LOG:  connection received: host=[local]
    2014-09-01 19:30:40 CEST LOG:  connection authorized: user=rs database=direct-dev
    2014-09-01 19:30:40 CEST FATAL:  database &quot;direct-dev&quot; does not exist

Do you have any pointers? I&#39;ve been at this for more than an hour, and still can&#39;t understand why this is happening ...

Thanks!

rake db:create throws &quot;database does not exist&quot; error with postgresql

How do I delete an enum type value that I created in postgresql? 

    create type admin_level1 as enum(&#39;classifier&#39;, &#39;moderator&#39;, &#39;god&#39;);

E.g. I want to remove `moderator` from the list.

I can&#39;t seem to find anything on the docs. 

I&#39;m using Postgresql 9.3.4.

How to delete an enum type value in postgres?

I am trying to write a bulk upsert in python using the SQLAlchemy module (not in SQL!).

I am getting the following error on a SQLAlchemy add:

    sqlalchemy.exc.IntegrityError: (IntegrityError) duplicate key value violates unique constraint &quot;posts_pkey&quot;
    DETAIL:  Key (id)=(TEST1234) already exists.

I have a table called `posts` with a primary key on the `id` column.

In this example, I already have a row in the db with `id=TEST1234`. When I attempt to `db.session.add()` a new posts object with the `id` set to `TEST1234`, I get the error above. I was under the impression that if the primary key already exists, the record would get updated. 

**How can I upsert with Flask-SQLAlchemy based on primary key alone? Is there a simple solution?**

If there is not, I can always check for and delete any record with a matching id, and then insert the new record, but that seems expensive for my situation, where I do not expect many updates.




Content Type	Original Author	Original Content on Stackoverflow
Question	taufique	View Question on Stackoverflow
Solution 1 - Sql	Craig Ringer	View Answer on Stackoverflow
Solution 2 - Sql	jainvikram444	View Answer on Stackoverflow

PostgreSQL query to rename and change column type with single query

Sql Problem Overview

Sql Solutions

Solution 1 - Sql

Solution 2 - Sql

How to send XML content with HttpClient.PostAsync?

Functions vs procedures in Oracle

Attributions