Postgres is happy, if it can detect types of untyped constants from the context. But when any context is not possible, and when query is little bit more complex than trivial, then this mechanism fails. These rules are specific for any SELECT clause, and some are stricter, some not. If I can say, then older routines are more tolerant (due higher compatibility with Oracle and less negative impact on beginners), modern are less tolerant (due higher safety to type errors).

There was some proposals try to work with any unknown literal constant like text constant, but was rejected for more reasons. So I don&#39;t expect significant changes in this area. This issue is usually related to synthetic tests - and less to real queries, where types are deduced from column types. 

Is there a Pythonic way to assign the values of a dictionary to its keys, in order to convert the dictionary entries into variables?
I tried this out:

    &gt;&gt;&gt; d = {&#39;a&#39;:1, &#39;b&#39;:2}
    &gt;&gt;&gt; for key,val in d.items():
            exec(&#39;exec(key)=val&#39;)
        	    
            exec(key)=val
          	         ^ 
            SyntaxError: invalid syntax


I am certain that the key-value pairs are correct because they were previously defined as variables by me before. I then stored these variables in a dictionary (as key-value pairs) and would like to reuse them in a different function. I could just define them all over again in the new function, but because I may have a dictionary with about 20 entries, I thought there may be a more efficient way of doing this.

Convert dictionary entries into variables

Error:

    Gradle: Execution failed for task &#39;:vertretungsplan:dexDebug&#39;.
    &gt; Failed to run command:
    	P:\Android-Studio\sdk\build-tools\18.0.1\dx.bat --dex --output P:\Projekte\VertretungsplanProject\vertretungsplan\build\libs\vertretungsplan-debug.dex P:\Projekte\VertretungsplanProject\vertretungsplan\build\classes\debug P:\Projekte\VertretungsplanProject\vertretungsplan\build\dependency-cache\debug P:\Android-Studio\sdk\extras\android\m2repository\com\android\support\support-v4\18.0.0\support-v4-18.0.0.jar P:\Projekte\VertretungsplanProject\vertretungsplan\libs\commons-io-2.4.jar P:\Projekte\VertretungsplanProject\vertretungsplan\build\exploded-bundles\VertretungsplanProjectLibrariesActionbarsherlockUnspecified.aar\classes.jar
    Error Code:
    	2
    Output:
    	trouble processing:
    	bad class file magic (cafebabe) or version (0033.0000)
    	...while parsing de/MayerhoferSimon/Vertretungsplan/LoginActivity$2.class
    	...while processing de/MayerhoferSimon/Vertretungsplan/LoginActivity$2.class
    	trouble processing:
    	bad class file magic (cafebabe) or version (0033.0000)
    	...while parsing de/MayerhoferSimon/Vertretungsplan/MainActivity$1.class
    	...while processing de/MayerhoferSimon/Vertretungsplan/MainActivity$1.class
    	trouble processing:
    	bad class file magic (cafebabe) or version (0033.0000)
    	...while parsing de/MayerhoferSimon/Vertretungsplan/YQL/YqlVplanParser.class
    	...while processing de/MayerhoferSimon/Vertretungsplan/YQL/YqlVplanParser.class
    	3 warnings
    	UNEXPECTED TOP-LEVEL EXCEPTION:
    	com.android.dx.util.DexException: Multiple dex files define Landroid/support/v4/accessibilityservice/AccessibilityServiceInfoCompat$AccessibilityServiceInfoVersionImpl;
    		at com.android.dx.merge.DexMerger.readSortableTypes(DexMerger.java:592)
    		at com.android.dx.merge.DexMerger.getSortedTypes(DexMerger.java:550)
    		at com.android.dx.merge.DexMerger.mergeClassDefs(DexMerger.java:531)
    		at com.android.dx.merge.DexMerger.mergeDexBuffers(DexMerger.java:168)
    		at com.android.dx.merge.DexMerger.merge(DexMerger.java:186)
    		at com.android.dx.command.dexer.Main.mergeLibraryDexBuffers(Main.java:300)
    		at com.android.dx.command.dexer.Main.run(Main.java:232)
    		at com.android.dx.command.dexer.Main.main(Main.java:174)
    		at com.android.dx.command.Main.main(Main.java:91)


Project structure:


![enter image description here][1]


  [1]: http://i.stack.imgur.com/czZPS.png


**build.gradle (actionbarsherlock)**

    buildscript {
    	repositories {
    		mavenCentral()
    	}
    	dependencies {
    		classpath &#39;com.android.tools.build:gradle:0.5.+&#39;
    	}
    }
    apply plugin: &#39;android-library&#39;
    
    dependencies {
        compile &#39;com.android.support:support-v4:18.0.0&#39;
    }
    
    android {
    	compileSdkVersion 18
    	buildToolsVersion &quot;18.0.1&quot;
    
    	defaultConfig {
    		minSdkVersion 8
    		targetSdkVersion 11
    	}
    
    	sourceSets {
    		main {
    			manifest.srcFile &#39;AndroidManifest.xml&#39;
    			java.srcDirs = [&#39;src&#39;]
    			resources.srcDirs = [&#39;src&#39;]
    			res.srcDirs = [&#39;res&#39;]
    		}
    	}
    }

**build.gradle (vertretungsplan)**

    buildscript {
    	repositories {
    		mavenCentral()
    	}
    	dependencies {
    		classpath &#39;com.android.tools.build:gradle:0.5.+&#39;
    	}
    }
    apply plugin: &#39;android&#39;
    
    dependencies {
    	compile files(&#39;libs/commons-io-2.4.jar&#39;)
    	compile project(&#39;:libraries:actionbarsherlock&#39;)
    }
    
    android {
    	compileSdkVersion 18
    	buildToolsVersion &quot;18.0.1&quot;
    
    	defaultConfig {
    		minSdkVersion 8
    		targetSdkVersion 11
    	}
    }

**settings.gradle**

    include &#39;:vertretungsplan&#39;, &#39;:libraries:actionbarsherlock&#39;

How can I fix this error?

Android Studio: Gradle - build fails -- Execution failed for task &#39;:dexDebug&#39;

In one of my select statements I&#39;ve got the following error:

    ERROR:  failed to find conversion function from unknown to text
    ********** Error **********
    ERROR: failed to find conversion function from unknown to text
    SQL state: XX000

This was easy to fix using `cast`, but I do not fully understand why it happened. I will ilustrate my confusion with two simple statements.

This one is OK:

    select &#39;text&#39;
    union all
    select &#39;text&#39;;

This will return error:

    with t as (select &#39;text&#39;)    
    select * from t
    union all
    select &#39;text&#39;

I know I can fix it easily:

    with t as (select &#39;text&#39;::text)    
    select * from t
    union all
    select &#39;text&#39;

Why does the conversion fail in the second example? Is there some logic I do not understand or this will be fixed in future version of PostgreSQL?

PostgreSQL 9.1.9

The same behavior on PostgreSQL 9.2.4 ([SQL Fiddle][1])


  [1]: http://sqlfiddle.com/#!12/d41d8/1262

Failed to find conversion function from unknown to text

In one of my select statements I've got the following error:
<pre><code class="hljs language-vbnet">ERROR: failed to find conversion function from unknown to text
********** Error **********
ERROR: failed to find conversion function from unknown to text
SQL state: XX000
</code></pre>
This was easy to fix using <code>cast</code>, but I do not fully understand why it happened. I will ilustrate my confusion with two simple statements.
This one is OK:
<pre><code class="hljs language-sql">select 'text'
union all
select 'text';
</code></pre>
This will return error:
<pre><code class="hljs language-sql">with t as (select 'text') 
select * from t
union all
select 'text'
</code></pre>
I know I can fix it easily:
<pre><code class="hljs language-sql">with t as (select 'text'::text) 
select * from t
union all
select 'text'
</code></pre>
Why does the conversion fail in the second example? Is there some logic I do not understand or this will be fixed in future version of PostgreSQL?
PostgreSQL 9.1.9
The same behavior on PostgreSQL 9.2.4 (<a href="http://sqlfiddle.com/#!12/d41d8/1262" target="_blank" rel="noopener noreferrer">SQL Fiddle</a>)

I have a simple table in PostgreSQL that has three columns:

 - id serial primary key
 - key varchar
 - value varchar

I have already seen this question here on SO: https://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql but I&#39;m wondering just how to get the id if it exists, instead of updating. If the standard practice is to always either &quot;insert&quot; or &quot;update if exists&quot;, why is that? Is the cost of doing a SELECT (LIMIT 1) greater than doing an UPDATE?

I have the following code

    INSERT INTO tag
        (&quot;key&quot;, &quot;value&quot;)
        SELECT &#39;key1&#39;, &#39;value1&#39;
    WHERE
        NOT EXISTS (
            SELECT id,&quot;key&quot;,&quot;value&quot; FROM tag WHERE key = &#39;key1&#39; AND value = &#39;value1&#39;
        );

which works in the sense that it doesn&#39;t insert if exists, but I&#39;d like to get the id. Is there a &quot;RETURNING id&quot; clause or something similar that I could tap in there?

Insert if not exists, else return id in postgresql

With postgresql 9.3 I can `SELECT` specific fields of a JSON data type, but how do you modify them using `UPDATE`? I can&#39;t find any examples of this in the postgresql documentation, or anywhere online. I have tried the obvious:

```sql
postgres=# create table test (data json);
CREATE TABLE
postgres=# insert into test (data) values (&#39;{&quot;a&quot;:1,&quot;b&quot;:2}&#39;);
INSERT 0 1
postgres=# select data-&gt;&#39;a&#39; from test where data-&gt;&gt;&#39;b&#39; = &#39;2&#39;;
 ?column?
----------
 1
(1 row)
postgres=# update test set data-&gt;&#39;a&#39; = to_json(5) where data-&gt;&gt;&#39;b&#39; = &#39;2&#39;;
ERROR:  syntax error at or near &quot;-&gt;&quot;
LINE 1: update test set data-&gt;&#39;a&#39; = to_json(5) where data-&gt;&gt;&#39;b&#39; = &#39;2...
```


How do I modify fields inside the new PostgreSQL JSON datatype?

I have a table in my PostgreSQL database which has 3 columns - `c_uid`, `c_defaults` and `c_settings`. `c_uid` simply stores the name of a user and `c_defaults` is a long piece of text which contains a lot of data w.r.t that user.

I have to execute a statement from a bash script which selects the value of the `c_defaults` column based on the `c_uid` value and this needs to be done by the database user &#39;postgres&#39;.

On the CLI I can do the following:

    [mymachine]# su postgres
    bash-4.1$psql
    postgres=#\c database_name
    You are now connected to database &quot;database_name&quot; as user &quot;postgres&quot;.
    database_name=#SELECT c_defaults  FROM user_info WHERE c_uid = &#39;testuser&#39;;
However, how do I achieve this through a bash script?

The aim is to get the information from that column, edit it and write it back into that column - all through a bash script.




PostgreSQL - query from bash script as database user &#39;postgres&#39;

Running [`COPY`](http://www.postgresql.org/docs/9.2/static/sql-copy.html) results in **`ERROR:  invalid input syntax for integer: &quot;&quot;`** error message for me. What am I missing?

My `/tmp/people.csv` file:

    &quot;age&quot;,&quot;first_name&quot;,&quot;last_name&quot;
    &quot;23&quot;,&quot;Ivan&quot;,&quot;Poupkine&quot;
    &quot;&quot;,&quot;Eugene&quot;,&quot;Pirogov&quot;

My `/tmp/csv_test.sql` file:

    CREATE TABLE people (
      age        integer,
      first_name varchar(20),
      last_name  varchar(20)
    );
    
    COPY people
    FROM &#39;/tmp/people.csv&#39;
    WITH (
      FORMAT CSV,
      HEADER true,
      NULL &#39;&#39;
    );
    
    DROP TABLE people;

Output:

    $ psql postgres -f /tmp/sql_test.sql
    CREATE TABLE
    psql:sql_test.sql:13: ERROR:  invalid input syntax for integer: &quot;&quot;
    CONTEXT:  COPY people, line 3, column age: &quot;&quot;
    DROP TABLE

Trivia:

* PostgreSQL 9.2.4

PG COPY error: invalid input syntax for integer

Is the default way of calling a function `select * from my_function()`?

I ask because I have built a function that doesn&#39;t return anything, just inserts data into a table and (coming from a SQL Server background) it &quot;feels&quot; strange to call it with `select * from...`

I was expecting something like `exec my_function()`

Calling functions with exec instead of select

I&#39;m using PostgreSQL 9.1. I have the column name of a table. Is it possible to find the table(s) that has/have this column? If so, how?

How to find a table having a specific column in postgresql

I have two questions about using SELECT … FOR UPDATE row-level locking in a Postgres function:

* Does it matter which columns I select? Do they have any relation to what data I need to lock and then update?

        SELECT * FROM table WHERE x=y FOR UPDATE;

   vs

        SELECT 1 FROM table WHERE x=y FOR UPDATE;

* I can&#39;t do a select in a function without saving the data somewhere, so I save to a dummy variable. This seems hacky; is it the right way to do things?

Here is my function:

&lt;!-- language: lang-sql --&gt;

    CREATE OR REPLACE FUNCTION update_message(v_1 INTEGER, v_timestamp INTEGER, v_version INTEGER)
    RETURNS void AS $$
    DECLARE
    	v_timestamp_conv TIMESTAMP;
    	dummy INTEGER;
    BEGIN
    	SELECT timestamp &#39;epoch&#39; + v_timestamp * interval &#39;1 second&#39; INTO v_timestamp_conv;
    	SELECT 1 INTO dummy FROM my_table WHERE userid=v_1 LIMIT 1 FOR UPDATE;
    	UPDATE my_table SET (timestamp) = (v_timestamp_conv) WHERE userid=v_1 AND version &lt; v_version;
    END;
    $$  LANGUAGE plpgsql;

Postgres SELECT ... FOR UPDATE in functions

I have a table that looks like this caller &#39;makerar&#39;

     cname  | wmname |          avg           
    --------+-------------+------------------------
     canada | zoro   |     2.0000000000000000
     spain  | luffy  | 1.00000000000000000000
     spain  | usopp  |     5.0000000000000000

And I want to select the maximum avg for each cname. 

    SELECT cname, wmname, MAX(avg)  FROM makerar GROUP BY cname;

but I will get an error, 

    ERROR:  column &quot;makerar.wmname&quot; must appear in the GROUP BY clause or be used in an   aggregate function 
    LINE 1: SELECT cname, wmname, MAX(avg)  FROM makerar GROUP BY cname;

so i do this

    SELECT cname, wmname, MAX(avg)  FROM makerar GROUP BY cname, wmname;

however this will not give the intented results, and the incorrect output below is shown.

     cname  | wmname |          max           
    --------+--------+------------------------
     canada | zoro   |     2.0000000000000000
     spain  | luffy  | 1.00000000000000000000
     spain  | usopp  |     5.0000000000000000

Actual Results should be

     cname  | wmname |          max           
    --------+--------+------------------------
     canada | zoro   |     2.0000000000000000
     spain  | usopp  |     5.0000000000000000

How can I go about fixing this issue?

Note: This table is a VIEW created from  a previous operation.


must appear in the GROUP BY clause or be used in an aggregate function

In my database table I am having one boolean column. which have some transaction with will False, True and Null.

These are the cases i have tried:

**Case:1**

    select * from table_name where
    boolean_column is null;

works well. Give the result with all transaction having null value for that column.

**Case:2**

    select *from table_name where boolean_column = False;

Works well. Gives result with all the transaction having False value for that column.

Case:3
This is requirement which does not works. I want all the transaction having value False and Null.

I have tried these.

i)  `select *from table_name where boolean_column is False or Null;`

*Which only gives the result for False it does not shows null records.*


ii) `select *from table_name where boolean_column is Null or False;`

*Which only gives the result for null it does not shows records with False value. *

iii) `select *from table_name where boolean_column is Null or boolean_column = False;`

*This is simply display all the transaction does not applied any condition at all.*


How to resolve this issue.
Any guidance appreciated.

Thanks in Advance.

Rgds,
Anil.




Postgresql SQL: How check boolean field with null and True,False Value?

I want to export my database as a .sql file. 
Can someone help me? The solutions I have found don&#39;t work.
A detailed description please.
On Windows 7.

Postgresql Database export to .sql file

I am using Ubuntu 12.04 and Postgress 9.2.

I need to create this user with this password e.g.

    postgres://admin:test101@127.0.0.1:5432

How to do that from the command line?  I need to automate with a bash script.  I have a fresh install.

How to create a user for Postgres from the command line for bash automation

I have pgAdmin version `1.16.1`

So, for exporting table dumm I do:

Right click on table, then  in menu click on `backup`, then in `Format` choice `Plain` and save file as `some_name.sql` 


Then I remove table.

Ok, now I need import table backup `some_name.sql` into database.

How to do this?  I can&#39;t find how to import table&#39;s `.sql` dump into database using pgAdmin. 

Can you help me please?

Export and import table dump (.sql) using pgAdmin

What is the command to find the size of *all* the databases?

I am able to find the size of a *specific* database by using following command:

    select pg_database_size(&#39;databaseName&#39;);

Postgres DB Size Command

In PostgreSql 9.2.4 I have two tables: `user (id, login, password, name)` and `dealer (id, user_id)`.

And I want to insert into both tables returning id of created dealer.

Currently I&#39;m doing it with two queries:

    WITH rows AS (
        INSERT INTO &quot;user&quot;
    	    (login, password, name)
        VALUES
    	    (&#39;dealer1&#39;, &#39;jygbjybk&#39;, &#39;Dealer 1&#39;)
        RETURNING id
    )
    INSERT INTO dealer (user_id)
        SELECT id
        FROM rows;
    SELECT currval(&#39;dealer_id_seq&#39;);

But can I implement this with a single `INSERT` query using `RETURNING` statement?

PostgreSql INSERT FROM SELECT RETURNING ID

I have a column `added_at` of type `timestamp without time zone`. I want it&#39;s default value to be the current date-time but without time zone. The function `now()` returns a timezone as well.

How do I solve that problem?

Content Type	Original Author	Original Content on Stackoverflow
Question	Tomas Greif	View Question on Stackoverflow
Solution 1 - Postgresql	Pavel Stehule	View Answer on Stackoverflow

Failed to find conversion function from unknown to text

Postgresql Problem Overview

Postgresql Solutions

Solution 1 - Postgresql

Android Studio: Gradle - build fails -- Execution failed for task ':dexDebug'

Convert dictionary entries into variables

Attributions