`VARCHAR` is an alias for `CHARACTER VARYING`, so no difference, see [documentation][1] :)

&gt; The notations varchar(n) and char(n)
&gt; are aliases for character varying(n)
&gt; and character(n), respectively.
&gt; character without length specifier is
&gt; equivalent to character(1). If
&gt; character varying is used without
&gt; length specifier, the type accepts
&gt; strings of any size. The latter is a
&gt; PostgreSQL extension.


  [1]: http://www.postgresql.org/docs/current/interactive/datatype-character.html

The [PostgreSQL documentation on Character Types](http://www.postgresql.org/docs/current/static/datatype-character.html) is a good reference for this. They are two different names for the same type.

The short answer: there is no difference.

The long answer: `CHARACTER VARYING` is the official type name from the ANSI SQL standard, which all compliant databases are required to support. `VARCHAR` is a shorter alias which all modern databases also support. I prefer `VARCHAR` because it&#39;s shorter and because the longer name feels pedantic. However, postgres tools like `pg_dump` and `\d` will output `character varying`.

The only difference is that CHARACTER VARYING is more human friendly than VARCHAR

Both are the same thing but many of the databases are not providing the varying char mainly postgreSQL is providing. So for the multi database like Oracle Postgre and DB2 it is good to use the Varchar

Everyone seems to say named pipes are faster than sockets IPC. How much faster are they? I would prefer to use sockets because they can do two-way communication and are very flexible but will choose speed over flexibility if it is by considerable amount.

IPC performance: Named Pipe vs Socket

By which I mean a structure with:

* O(log n) complexity for `x.push()` operations
* O(log n) complexity to find an element
* O(n) complexity to compute `list(x)` which will be sorted


I also had a related question about performance of `list(...).insert(...)` which is now [here][1].


  [1]: https://stackoverflow.com/questions/1110332/performance-of-list-insert

Does python have a sorted list?

[John][1] uses `CHARACTER VARYING` in the places where I use `VARCHAR`.
I am a beginner, while he is an expert.
This suggests me that there is something which I do not know.

**What is the difference between CHARACTER VARYING and VARCHAR in PostgreSQL?**


  [1]: https://stackoverflow.com/questions/1196873/to-prevent-the-use-of-duplicate-tags-in-a-database/1197192#1197192

What is the difference between CHARACTER VARYING and VARCHAR in PostgreSQL?

<a href="https://stackoverflow.com/questions/1196873/to-prevent-the-use-of-duplicate-tags-in-a-database/1197192#1197192" target="_blank" rel="noopener noreferrer">John</a> uses <code>CHARACTER VARYING</code> in the places where I use <code>VARCHAR</code>.
I am a beginner, while he is an expert.
This suggests me that there is something which I do not know.
What is the difference between CHARACTER VARYING and VARCHAR in PostgreSQL?

I [read][1] that there are datatypes which do encryption so passwords are secured in your database.

I use at the moment `varchar` to store passwords. I have had the idea that I should somehow apply a SHA-512 function to the password and put that data somewhere such that the plain text password is removed.

However, the datatype in Perl suggests me that there are a better way in PostgreSQL than varchar.

**What is the datatype for a password in PostgreSQL?**


  [1]: http://search.cpan.org/~jsiracusa/Rose-DB-Object-0.782/lib/Rose/DB/Object/MakeMethods/Pg.pm

What is the datatype for a password in PostgreSQL?

I&#39;m using Rails migrations to manage a database schema, and I&#39;m creating a simple table where I&#39;d like to use a non-integer value as the primary key (in particular, a string). To abstract away from my problem, let&#39;s say there&#39;s a table `employees` where employees are identified by an alphanumeric string, e.g. `&quot;134SNW&quot;`.

I&#39;ve tried creating the table in a migration like this:

    create_table :employees, {:primary_key =&gt; :emp_id} do |t|
        t.string :emp_id
        t.string :first_name
        t.string :last_name
    end

What this gives me is what seems like it completely ignored the line `t.string :emp_id` and went ahead and made it an integer column. Is there some other way to have rails generate the PRIMARY_KEY constraint (I&#39;m using PostgreSQL) for me, without having to write the SQL in an `execute` call?

**NOTE**: I know it&#39;s not best to use string columns as primary keys, so please no answers just saying to add an integer primary key. I may add one anyway, but this question is still valid.

Using Rails, how can I set my primary key to not be an integer-typed column?

This seems like a duplicate even as I ask it, but I searched and didn&#39;t find it. It seems like a good question for SO -- even though I&#39;m sure I can find it on many blogs etc. out there. SO will have more of a debate than you can get on a blog. 

I&#39;m running into an issue with a join: getting back too many records. I think of this as &quot;expansion&quot;. I added a table to the set of joins and the number of rows expanded, way too much. Usually when this happens I add a select of all the ID fields that are involved in the join. That way it&#39;s pretty obvious where the expansion is happening and I can change the ON of the join to fix it. Except in this case, the table that I added doesn&#39;t have an ID field. To me, this is a problem. But perhaps I&#39;m wrong. 

The question: should every table in a database have an IDENTITY field that&#39;s used as the PK? Are there any drawbacks to having an ID field in every table? What if you&#39;re reasonably sure this table will never be used in a PK/FK relationship? 

Related, but not duplicate: https://stackoverflow.com/questions/932913/when-having-an-identity-column-is-not-a-good-idea

Apparently this debate has been [going on for a while][1]. Shoulda known. 

[This post][2] (surrogate vs. natural keys) is also relevant.


  [1]: http://en.wikipedia.org/wiki/Surrogate_key
  [2]: https://stackoverflow.com/questions/63090/surrogate-vs-natural-business-keys

in general, should every table in a database have an identity field to use as a PK?

This is more of a &quot;why do things work this way&quot; question rather than a &quot;I don&#39;t know how to do this&quot; question...

So the gospel on pulling associated records that you know you&#39;re going to use is to use `:include` because you&#39;ll get a join and avoid a whole bunch of extra queries:

    Post.all(:include =&gt; :comments)

However when you look at the logs, there&#39;s no join happening:

    Post Load (3.7ms)   SELECT * FROM &quot;posts&quot;
    Comment Load (0.2ms)   SELECT &quot;comments.*&quot; FROM &quot;comments&quot; 
                           WHERE (&quot;comments&quot;.post_id IN (1,2,3,4)) 
                           ORDER BY created_at asc) 

It _is_ taking a shortcut because it pulls all of the comments at once, but it&#39;s still not a join (which is what all the documentation seems to say). The only way I can get a join is to use `:joins` instead of `:include`:

    Post.all(:joins =&gt; :comments)

And the logs show:

    Post Load (6.0ms)  SELECT &quot;posts&quot;.* FROM &quot;posts&quot; 
                       INNER JOIN &quot;comments&quot; ON &quot;posts&quot;.id = &quot;comments&quot;.post_id

Am I missing something? I have an app with half a dozen associations and on one screen I display data from all of them. Seems like it would be better to have one join-ed query instead of 6 individuals. I know that performance-wise it&#39;s not always better to do a join rather than individual queries (in fact if you&#39;re going by time spent, it looks like the two individual queries above are faster than the join), but after all the docs I&#39;ve been reading I&#39;m surprised to see `:include` not working as advertised.

Maybe Rails _is_ cognizant of the performance issue and doesn&#39;t join except in certain cases?

Rails :include vs. :joins

In multiple courses, books, and jobs, I have seen text fields defined as VARCHAR(255) as kind of the default for &quot;shortish&quot; text.  Is there any good reason that a length of 255 is chosen so often, other than being &lt;a href=&quot;https://stackoverflow.com/questions/164432/what-real-life-bad-habits-has-programming-given-you/164556#164556&quot;&gt;a nice round number&lt;/a&gt;?  Is it a holdout from some time in the past when there was a good reason (whether or not it applies today)?

I realize, of course, that a tighter limit would be more ideal, if you somehow know the maximum length of the string.  But if you are using VARCHAR(255) that probably indicates that you don&#39;t know the max length, only that it is a &quot;shortish&quot; string.

-----

Note: I found this question (&lt;a href=&quot;https://stackoverflow.com/questions/143933/varchar255-v-tinyblob-v-tinytext&quot;&gt;varchar(255) v tinyblob v tinytext&lt;/a&gt;), which says that VARCHAR(*n*) requires *n*+1 bytes of storage for *n*&amp;lt;=255, *n*+2 bytes of storage for *n*&amp;gt;255.  Is this the only reason?  That seems kind of arbitrary, since you would only be saving two bytes compared to VARCHAR(256), and you could just as easily save another two bytes by declaring it VARCHAR(253).

Is there a good reason I see VARCHAR(255) used so often (as opposed to another length)?

Can someone tell me how can I get MS Entity Framework working with PostgreSQL. 

Also: How does Entity Framewok work with Mono? Could you please suggest other similar ORM tools which would run on Mono and what is your opinion about them?

Entity framework PostgreSQL

Given a table name, how do I extract a list of primary key columns and their datatypes from a plpgsql function?

How do I get the primary key(s) of a table from Postgres via plpgsql?

Sometimes I run a Postgres query and it takes 30 seconds. Then, I immediately run the same query and it takes 2 seconds. It appears that Postgres has some sort of caching. Can I somehow see what that cache is holding? Can I force all caches to be cleared for tuning purposes?

I&#39;m basically looking for a Postgres version of the following SQL Server command:

    DBCC FREEPROCCACHE
    DBCC DROPCLEANBUFFERS

But I would also like to know how to see what is actually contained in that buffer.

See and clear Postgres caches/buffers?

Is there a way to copy the structure of a table into a new table, without data, including all keys and constraints?

Copy table structure into new table

I recognize that an email address can basically be indefinitely long so any size I impose on my varchar email address field is going to be arbitrary. However, I was wondering what the &quot;standard&quot; is? How long do you guys make it? (same question for Name field...)

**update:** Apparently the max length for an email address is 320 (&lt;=64 name part, &lt;= 255 domain). Do you use this?

How long should SQL email fields be?

I’m in need of some clarification of the maximum length of a varchar field in MySQL.

I’ve always thought the max length was 255 (255 what? Characters I’ve assumed, but this might be a source of my confusion). Taking a look at the tables of a database set up by an external company we’re working with, I see a field set-up as varchar(20000), holding chunks of xml longer than 255 characters. Why does this work? Is 20000 a valid value?

A bit of googling has revealed that in mysql varchar has a limit of 65,535 bytes, and I see varchar(65535) &lt;a href=&quot;https://stackoverflow.com/questions/332798/equivalent-of-varcharmax-in-mysql&quot;&gt;in use&lt;/a&gt;, so how does the 255 limit relate to this?


Is a VARCHAR(20000) valid in MySQL?

How many characters can a SQL Server 2008 database field contain when the data type is VARCHAR(MAX)?

How many characters in varchar(max)?

When I executed the following command:

    ALTER TABLE `mytable` ADD UNIQUE (
    `column1` ,
    `column2`
    );

I got this error message:

    #1071 - Specified key was too long; max key length is 767 bytes

Information about column1 and column2:

    column1 varchar(20) utf8_general_ci
    column2  varchar(500) utf8_general_ci

I think `varchar(20)` only requires 21 bytes while `varchar(500)` only requires 501 bytes. So the total bytes are 522, less than 767. So why did I get the error message?

    #1071 - Specified key was too long; max key length is 767 bytes

Content Type	Original Author	Original Content on Stackoverflow
Question	Léo Léopold Hertz 준영	View Question on Stackoverflow
Solution 1 - Database	Charles Ma	View Answer on Stackoverflow
Solution 2 - Database	Greg Hewgill	View Answer on Stackoverflow
Solution 3 - Database	Gordon Hopper	View Answer on Stackoverflow
Solution 4 - Database	inor	View Answer on Stackoverflow
Solution 5 - Database	Daya Shanker	View Answer on Stackoverflow

What is the difference between CHARACTER VARYING and VARCHAR in PostgreSQL?

Database Problem Overview

Database Solutions

Solution 1 - Database

Solution 2 - Database

Solution 3 - Database

Solution 4 - Database

Solution 5 - Database

Does python have a sorted list?

IPC performance: Named Pipe vs Socket

Attributions