I am a Postgres newbie and i have the following table to create through c# code:
string create = "CREATE TABLE " + articles
+ "("
+ " SEQU_NK SERIAL PRIMARY KEY,"
+ " ARTICLE_NK bigint NOT NULL,"
+ " DEPOT_NK bigint NOT NULL ,"
+ " EXPED_NK bigint NOT NULL,"
+ " CLIENT_NK bigint NOT NULL,"
+ " SIGNE_TRANSP character varying(2) NOT NULL,"
+ " NB_PLIS_TOTAL bigint DEFAULT 0 ,"
+ " DH_PREM_ETIQUETTE timestamp DEFAULT '0001-01-01 00:00:00',"
+ " DATE_DEPOT_ARTICLE character varying(10) DEFAULT NULL,"
+ " ARTICLE_NK character varying(10) references articles(ARTICLE_NK),"
+ " DEPOT_NK character varying(10) references depots(DEPOT_NK),"
+ " EXPED_NK character varying(10) references expeds(EXPED_NK),"
+ " CLIENT_NK character varying(10) references clients(CLIENT_NK),"
+ ") ";
The also try to write query on PGAdmin3 :
CREATE TABLE articles( SEQU_NK SERIAL PRIMARY KEY,
ARTICLE_NK bigint NOT NULL,
DEPOT_NK bigint NOT NULL ,
EXPED_NK bigint NOT NULL,
CLIENT_NK bigint NOT NULL,
SIGNE_TRANSP character varying(2) NOT NULL,
NB_PLIS_TOTAL bigint DEFAULT 0 ,
DH_PREM_ETIQUETTE timestamp DEFAULT '0001-01-01 00:00:00',
ARTICLE_NK character varying(10) references articles(ARTICLE_NK),
DEPOT_NK character varying(10) references depots(DEPOT_NK),
EXPED_NK character varying(10) references expeds(EXPED_NK),
CLIENT_NK character varying(10) references clients(CLIENT_NK)) ;
And error obtained is :
ERROR: column "article_nk" specified more than once
********** Error **********
ERROR: column "article_nk" specified more than once
SQL state: 42701
How to fix this error as it is a foreign key? As I am changing the query from Mysql to Postgres?
The equivlaent Mysql query is (which I am translating to Postgres is):
CREATE TABLE IF NOT EXISTS `articles` (
`SEQU_NK` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Auto: Numero sequence',
`ARTICLE_NK` int(11) NOT NULL,
`DEPOT_NK` int(11) NOT NULL DEFAULT '0',
`EXPED_NK` int(11) NOT NULL,
`CLIENT_NK` int(11) NOT NULL,
`SIGNE_TRANSP` varchar(2) NOT NULL DEFAULT '',
`NB_PLIS_TOTAL` int(11) DEFAULT '0',
`DH_PREM_ETIQUETTE` datetime DEFAULT '0001-01-01 00:00:00',
PRIMARY KEY (`SEQU_NK`),
KEY `ARTICLE_NK` (`ARTICLE_NK`),
KEY `DEPOT_NK` (`DEPOT_NK`),
KEY `EXPED_NK` (`EXPED_NK`),
KEY `CLIENT_NK` (`CLIENT_NK`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=18 ;
- Remove From My Forums
-
Question
-
Hello all,
we are facing problem in creating table with the column names V and W.
we get error like this — if i specify ‘ V ‘ first column and second column ‘ W ‘
Unable to create table.
Column names in each table must be unique. Column name ‘w’ in table ‘Table_1’ is specified more than once.we get error like this — if i specify ‘ W ‘ first column and second column ‘ V ‘
Column names in each table must be unique. Column name ‘v’ in table ‘Table_1’ is specified more than once.
My doubt is it the Database language or some other settings , which treating ‘ V ‘ and ‘W’ as same.
Database built on SQLSERVER2005, Language — English(US) and Server Collation — Finnish_Swedish_CI_AI
it will be very much helpful if anyone give me the solution or cause for this crazy problem
Thanks in Advance!
Sree
Answers
-
First thing to know is in SQL Server that the collation for metadata is the same as for data. (Unless you are on SQL 2012 and your database is partially contained. Contained databases always have the same collation for metadata, Latin1_General_100_CI_AS_KS_WS_SC,
no matter what the data collation is.)In the Swedish alphabet W was not a separate letter until the most recent edition of the dictionary from the Swedish Academy. That is, W is considered to be an accented version of V. It is possible that later versions of SQL Server will include a collation
like Finnish_Swedish_120_CI_AI where V and W are separate, to align with the recent changes from the Swedish Academy. However, existing collations will never change.Thus, if you need to support Finnish_Swedish_CI_AI, you will need to sort out your V and Ws. You can always replace them with A and Ä — they are distinct in this collation. (But be careful with Y and ü — they are the same.)
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
-
Proposed as answer by
Wednesday, July 18, 2012 10:08 AM
-
Marked as answer by
amber zhang
Tuesday, July 24, 2012 7:56 AM
-
Proposed as answer by
Error Message:
Msg 169, Level 15, State 1, Line 1
A column has been specified more than once in the order by list. Columns in the order by list must be unique.
Severity level:
15.
Description:
This error message appears when a column name in the ORDER BY clause appears more than once.
Consequences:
The T-SQL statement can be parsed, but causes the error at runtime.
Resolution:
Error of the Severity Level 15 are generated by the user and can be fixed by the SQL Server user. Each column name in the ORDER BY clause can appear just once.
Versions:
All versions of SQL Server
Example(s):
SELECT *
FROM Northwind.dbo.Orders
ORDER BY OrderID, OrderID
Remarks:
In the above example the error is raised by the double specification of the OrderID column. This error message also appears when you don’t refer to the actual column name, but rather also when you reference the column by its ordinal position in the SELECT list. So, this SELECT also raises this error:
SELECT *, 1
FROM Northwind.dbo.Orders
ORDER BY OrderID, 1
The error is not raised when you give the column an alias in the SELECT list and refer to this alias instead in the ORDER BY clause. The following example does not raise this error:
SELECT *, OrderID AS t
FROM Northwind.dbo.Orders
ORDER BY OrderID, t
]]>
There are two possible solutions
Do a USING join like below
CREATE VIEW report_view
AS
WITH d as(
SELECT *
FROM report.get_sa001('2013-01-01'::date", TO_CHAR(NOW()", 'YYYY-MM-DD')::date", 32)
)",
a as
( SELECT *
FROM report."Axis_Reference_All"
WHERE ("Site"", "Reference_internal"", "Customer_code") IN
( SELECT "Site"",
"Internal_reference"", "Customer_code"
FROM d ) )
SELECT *
FROM d
LEFT JOIN a USING ("Site","Internal_reference","Customer_code")
-- here is the USING. Courtesy of Jack Douglas for the tip
However as I’m retrieving datas from 5 years ago, it takes more than 5 minutes if I’m using a USING.
The best way is to call each columns separately like below and be sure to name the origin of the column, here a for Site and Customer_code.
CREATE VIEW report_view AS WITH d as
( SELECT *
FROM report.get_sa001('2015-01-01'::date, TO_CHAR(NOW(), 'YYYY-MM-DD')::date, 32) ),
a as
( SELECT *
FROM report."Axis_Reference_All"
WHERE ("Site", "Reference_internal", "Customer_code") IN
( SELECT "Site", "Internal_reference", "Customer_code"
FROM d ) )
SELECT "Period_date",
a."Site",
a."Customer_code",
"Internal_reference",
"InvoiceNumber",
"Value_in_currency",
"Value_in_EUR",
"Value_Budget_in_EUR",
"Selling_price_CUR",
"Selling_price_EUR",
"Currency_code",
"Selling_quantity",
"Variance_price_CUR",
"Variance_price_EUR",
"Variance_value_CUR",
"Variance_value_EUR",
"Selling_date",
"Reference",
"Reference_internal",
"Reference_customer",
"Reference_supplier",
"Reference_description",
"Reference_workshop",
"Reference_line",
"Reference_segment",
"Reference_purchasingfamily",
"Reference_motorapplication",
"Reference_interco",
"Reference_trading",
"Cogs_rm",
"Cogs_dl",
"Cogs_voh",
"Supplier_global",
"Supplier_code",
"Supplier_name",
"Supplier_account_manager",
"Supplier_interco",
"Supplier_incoterm",
"Supplier_incoterm_location",
"Supplier_incoterm_via",
"Supplier_continent",
"Supplier_country",
"Supplier_city",
"Supplier_zipcode",
"Purchasing_unit",
"Purchasing_price",
"Purchasing_currency",
"Purchasing_payment_term_days",
"Purchasing_payment_term_type",
"Purchasing_consigned",
"Purchasing_grossweight",
"Purchasing_grosscube",
"Purchasing_eco_order_qty",
"Purchasing_pack_order_qty",
"Purchasing_moq",
"Purchasing_mov",
"Purchasing_leadtime_days",
"Customer_global",
"Customer_name",
"Customer_account_manager",
"Customer_interco",
"Customer_incoterm",
"Customer_incoterm_location",
"Customer_incoterm_via",
"Customer_continent",
"Customer_country",
"Customer_city",
"Customer_zipcode",
"Selling_unit",
"Selling_price",
"Selling_currency",
"Selling_payment_term_days",
"Selling_payment_term_type",
"Selling_consigned",
"Selling_grossweight",
"Selling_grosscube",
"Selling_eco_order_qty",
"Selling_pack_order_qty",
"Selling_moq",
"Selling_mov",
"Selling_leadtime_days",
"Reference_netweight",
"Sc_storage_unit",
"Sc_production_unit",
"Sc_inventory_status",
"Sc_inventory_price",
"Sc_inventory_type"
FROM d
LEFT JOIN a ON d."Site" = a."Site"
AND d."Internal_reference" = a."Reference_internal"
AND d."Customer_code" = a."Customer_code"
This will give the required speed in term of data retrieval.
Cheers
|
Home > SQL Server Error Messages > Msg 4506 — Column names in each view or function must be unique. Column name ‘<Column Name>’ in view or function ‘<View or Function Name>’ is specified more than once. |
||
|
SQL Server Error Messages — Msg 4506 — Column names in each view or function must be unique. Column name ‘<Column Name>’ in view or function ‘<View or Function Name>’ is specified more than once. |
||
Views are created using the CREATE VIEW statement. The basic syntax of the CREATE VIEW is as follows: CREATE VIEW [ <schema_name>. ] <view_name> [ ( column [, …n ] ) ]
[ WITH { [ENCRYPTION], [SCHEMABINDING], [VIEW_METADATA] } ]
AS <select_statement>
[ WITH CHECK OPTION ]
Just like in a table, one of the restrictions when creating a view is that the names of the columns must be unique; otherwise this error message will be raised. Here’s a simple script that illustrates how this error message can be encountered: CREATE TABLE [dbo].[Manager] (
[ManagerID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
[FirstName] VARCHAR(50),
[LastName] VARCHAR(50)
)
GO
CREATE TABLE [dbo].[Employee] (
[EmployeeID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
[FirstName] VARCHAR(50),
[LastName] VARCHAR(50),
[ManagerID] INT NOT NULL REFERENCES [dbo].[Manager] ( [ManagerID] )
)
GO
CREATE VIEW [dbo].[EmployeeManager]
AS
SELECT *
FROM [dbo].[Employee] [Emp] INNER JOIN [dbo].[Manager] [Mgr]
ON [Emp].[ManagerID] = [Mgr].[ManagerID]
GO
Msg 4506, Level 16, State 1, Procedure EmployeeManager, Line 4 Column names in each view or function must be unique. Column name 'ManagerID' in view or function 'EmployeeManager' is specified more than once. This error message is raised because both tables have the [ManagerID] as a column and both columns are being included in the view as separate columns. The error only raised the first column that was encountered that violated the unique column rule. As can be seen from the table definition, aside from the [ManagerID], both tables also have the [FirstName] and [LastName] columns. Solution / Work Around: This error can easily be avoided by making sure that the names of the columns included in the view are unique. One way to avoid this is by not using the “*” wild card in the SELECT statement particularly when the view references more than 1 table. Here’s an updated version of the CREATE VIEW statement earlier which lists the columns to be included in the view and making sure that they are all unique: CREATE VIEW [dbo].[EmployeeManager]
AS
SELECT [Emp].[EmployeeID], [Emp].[FirstName], [Emp].[LastName],
[Mgr].[ManagerID], [Mgr].[FirstName] AS [ManagerFirstName],
[Mgr].[LastName] AS [ManagerLastName]
FROM [dbo].[Employee] [Emp] INNER JOIN [dbo].[Manager] [Mgr]
ON [Emp].[ManagerID] = [Mgr].[ManagerID]
GO
A couple of things were done with the view object creation that avoided this error. First is that it returned the [ManagerID] column only once. Since this column was used in the INNER JOIN statement, the value of this column on both tables will be the same. Second, since both tables contain [FirstName] and [LastName] columns, one of these columns were assigned a different name in the view. In this particular case, the manager’s first name and last name were given column aliases of [ManagerFirstName] and [ManagerLastName], respectively, thus avoiding this error. |
||
| Related Articles : | ||
|