You can use the `rpad` and `lpad` functions to pad numbers to the right or to the left, respectively. Note that this does not work directly on numbers, so you&#39;ll have to use `::char` or `::text` to cast them:

    SELECT RPAD(numcol::text, 3, &#39;0&#39;), -- Zero-pads to the right up to the length of 3
           LPAD(numcol::text, 3, &#39;0&#39;)  -- Zero-pads to the left up to the length of 3
    FROM   my_table

 



The `to_char()` function is there to format numbers:

    select to_char(column_1, &#39;fm000&#39;) as column_2
    from some_table;


The `fm` prefix (&quot;fill mode&quot;) avoids leading spaces in the resulting varchar. The `000` simply defines the number of digits you want to have.

&lt;pre&gt;
psql (9.3.5)
Type &quot;help&quot; for help.

postgres=&amp;gt; with sample_numbers (nr) as (
postgres(&amp;gt;     values (1),(11),(100)
postgres(&amp;gt; )
postgres-&amp;gt; select to_char(nr, &#39;fm000&#39;)
postgres-&amp;gt; from sample_numbers;
 to_char
---------
 001
 011
 100
(3 rows)

postgres=&amp;gt;&lt;/pre&gt;

For more details on the format picture, please see the manual: &lt;br&gt;
http://www.postgresql.org/docs/current/static/functions-formatting.html

As easy as

    SELECT lpad(42::text, 4, &#39;0&#39;)

References:

* http://www.postgresql.org/docs/current/static/functions-string.html

sqlfiddle: http://sqlfiddle.com/#!15/d41d8/3665

The easiest way:

    ltrim(to_char(Column1, &#39;000&#39;))

It is commonly understood that `Cloneable` interface in Java is broken. There are many reasons for this, which I will not mention; [others][1] already did it. It is also the position of [Java architects](http://www.artima.com/intv/bloch13.html) themselves.

My question is therefore: why has is not been deprecated yet? If the core Java team have decided that it is broken, then they must also have considered deprecation. What are their reasons against doing so (in Java 8 it is [still not deprecated][2])? 


  [1]: http://howtodoinjava.com/2012/11/10/cloneable-interface-is-broken-in-java/
  [2]: http://docs.oracle.com/javase/8/docs/api/java/lang/Cloneable.html
  [3]: http://www.artima.com/intv/bloch13.html

Why is Cloneable not deprecated?

When I run `mongo`, I get the warning:

&gt; Failed global initialization: BadValue Invalid or no user locale set. Please ensure LANG and/or LC_* environment variables are set correctly.

BadValue Invalid or no user locale set. Please ensure LANG and/or LC_* environment variables are set correctly

I am relatively new to PostgreSQL and I know how to pad a number with zeros to the left in SQL Server but I&#39;m struggling to figure this out in PostgreSQL.

I have a number column where the maximum number of digits is 3 and the min is 1: if it&#39;s one digit it has two zeros to the left, and if it&#39;s 2 digits it has 1, e.g. 001, 058, 123.

In SQL Server I can use the following:

    RIGHT(&#39;000&#39; + cast([Column1] as varchar(3)), 3) as [Column2]
This does not exist in PostgreSQL. Any help would be appreciated.

Padding zeros to the left in postgreSQL

I am relatively new to PostgreSQL and I know how to pad a number with zeros to the left in SQL Server but I'm struggling to figure this out in PostgreSQL.
I have a number column where the maximum number of digits is 3 and the min is 1: if it's one digit it has two zeros to the left, and if it's 2 digits it has 1, e.g. 001, 058, 123.
In SQL Server I can use the following:
<pre><code class="hljs language-sql">RIGHT('000' + cast([Column1] as varchar(3)), 3) as [Column2]
</code></pre>
This does not exist in PostgreSQL. Any help would be appreciated.

I want to track mutual locks in postgres constantly.

I came across [Locks Monitoring][1] article and tried to run the following query:

    SELECT bl.pid     AS blocked_pid,
         a.usename  AS blocked_user,
         kl.pid     AS blocking_pid,
         ka.usename AS blocking_user,
         a.query    AS blocked_statement
    FROM  pg_catalog.pg_locks         bl
     JOIN pg_catalog.pg_stat_activity a  ON a.pid = bl.pid
     JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
     JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
    WHERE NOT bl.granted;

Unfortunately, it never returns non-empty result set. If I simplify given query to the following form:

    SELECT bl.pid     AS blocked_pid,
         a.usename  AS blocked_user,
         a.query    AS blocked_statement
    FROM  pg_catalog.pg_locks         bl
     JOIN pg_catalog.pg_stat_activity a  ON a.pid = bl.pid
    WHERE NOT bl.granted;

then it returns queries which are waiting to acquire a lock. But I cannot manage to change it so that it can return both blocked and blocker queries.

Any ideas?

  [1]: https://wiki.postgresql.org/wiki/Lock_Monitoring

How to detect query which holds the lock in Postgres?

I tried to do a lot of research but I&#39;m more of a db guy - so even the explanation in the MSDN doesn&#39;t make any sense to me. Can anyone please explain, and provide some examples on what `Include()` statement does in the term of `SQL` query?

What does Include() do in LINQ?

I am trying to find an elegant way in Eloquent and Laravel to say

    select * from UserTable where Age between X and Y

Is there a between operator in Eloquent (I can&#39;t find it).

The closest i have gotten so far is chainging my query like this

    $query-&gt;where(age, &#39;&gt;=&#39;, $ageFrom)
	      -&gt;where(age, &#39;&lt;=&#39;, $ageTo);

I also came across whereRaw that seems to work

    $query-&gt;whereRaw(&#39;age BETWEEN &#39; . $ageFrom . &#39; AND &#39; . $ageTo . &#39;&#39;);

Is there an actual Eloquent way (not raw) that deals with ranges?



Laravel Eloquent how to use between operator

I have duplicate rows in my table and I want to delete duplicates in the most efficient way since the table is big. After some research, I have come up with this query:

    WITH TempEmp AS
    (
    SELECT name, ROW_NUMBER() OVER(PARTITION by name, address, zipcode ORDER BY name) AS duplicateRecCount
    FROM mytable
    )
    -- Now Delete Duplicate Records
    DELETE FROM TempEmp
    WHERE duplicateRecCount &gt; 1;

But it only works in SQL, not in Netezza. It would seem that it does not like the `DELETE` after the `WITH` clause?


How to delete duplicate rows without unique identifier

My requirement is to get each client&#39;s latest order, and then get top 100 records.

I wrote one query as below to get latest orders for each client. Internal query works fine. But I don&#39;t know how to get first 100 based on the results.

        SELECT * FROM (
          SELECT id, client_id, ROW_NUMBER() OVER(PARTITION BY client_id ORDER BY create_time DESC) rn 
          FROM order
        ) WHERE rn=1

Any ideas? Thanks.


How to Select Top 100 rows in Oracle?

I have been trying to set up a container for a development postgres instance by creating a custom user &amp; database. I am using the [official postgres docker image](https://registry.hub.docker.com/_/postgres/). In the documentation it instructs you to insert a bash script inside of the `/docker-entrypoint-initdb.d/` folder to set up the database with any custom parameters.

## My bash script: make_db.sh

    su postgres -c &quot;createuser -w -d -r -s docker&quot;
    su postgres -c &quot;createdb -O docker docker&quot;

## Dockerfile

    FROM library/postgres

    RUN [&quot;mkdir&quot;, &quot;/docker-entrypoint-initdb.d&quot;]
    ADD make_db.sh /docker-entrypoint-initdb.d/

The error I get from the `docker logs -f db` (db is my container name) is:

&gt; createuser: could not connect to database postgres: could not connect to server: No such file or directory

It seems that the commands inside of the `/docker-entrypoint-initdb.d/` folder are being executed before postgres is started. My question is, how do I set up a user/database programmatically using the official postgres container? Is there any way to do this with a script?

How to create User/Database in script for Docker Postgres

I am trying to update a text field in a table of my postgresql database.

    UPDATE public.table SET long_text = &#39;First Line&#39; + CHAR(10) + &#39;Second line.&#39; WHERE id = 19;

My intended result is that the cell will look like this:

&lt;pre&gt;
First Line
Second line
&lt;/pre&gt;

The above syntax returns an error.

Insert line break in postgresql when updating text field

I&#39;m logged in with a superuser account and this is the process I&#39;m doing:

    1-&gt; CREATE ROLE test WITH IN ROLE testroles PASSWORD &#39;testpasswd&#39;
    2-&gt; CREATE SCHEMA AUTHORIZATION test

The role is correctly created but I&#39;m getting this error when trying to create the Schema:

    ERROR:  must be member of role &quot;test&quot;



&quot;ERROR: must be member of role&quot; When creating schema in PostgreSQL

I try to copy the content of a CSV file into my postgresql db and I get this error &quot;extra data after last expected column&quot;.

The content of my CSV is 

        agency_id,agency_name,agency_url,agency_timezone,agency_lang,agency_phone
    100,RATP (100),http://www.ratp.fr/,CET,,

and my postgresql command is 

    COPY agency (agency_name, agency_url, agency_timezone) FROM &#39;myFile.txt&#39; CSV HEADER DELIMITER &#39;,&#39;;

Here is my table 

    CREATE TABLE agency (
        agency_id character varying,
        agency_name character varying NOT NULL,
        agency_url character varying NOT NULL,
        agency_timezone character varying NOT NULL,
        agency_lang character varying,
        agency_phone character varying,
        agency_fare_url character varying
    );

         Column      |       Type        | Modifiers 
    -----------------+-------------------+-----------
     agency_id       | character varying | 
     agency_name     | character varying | not null
     agency_url      | character varying | not null
     agency_timezone | character varying | not null
     agency_lang     | character varying | 
     agency_phone    | character varying | 
     agency_fare_url | character varying | 



Content Type	Original Author	Original Content on Stackoverflow
Question	Ben	View Question on Stackoverflow
Solution 1 - Sql	Mureinik	View Answer on Stackoverflow
Solution 2 - Sql	a_horse_with_no_name	View Answer on Stackoverflow
Solution 3 - Sql	zerkms	View Answer on Stackoverflow
Solution 4 - Sql	Vladimir Pankov	View Answer on Stackoverflow

Padding zeros to the left in postgreSQL

Sql Problem Overview

Sql Solutions

Solution 1 - Sql

Solution 2 - Sql

postgres=> with sample_numbers (nr) as ( postgres(> values (1),(11),(100) postgres(> ) postgres-> select to_char(nr, 'fm000') postgres-> from sample_numbers; to_char

Solution 3 - Sql

Solution 4 - Sql

BadValue Invalid or no user locale set. Please ensure LANG and/or LC_* environment variables are set correctly

Why is Cloneable not deprecated?

Attributions